Free Associate-Data-Practitioner Practice Test Questions and Answers (2026) | Cert Empire Practice Questions

Using Cloud Composer to design the processing pipeline as a Directed Acyclic Graph (DAG) is the

most suitable approach because:

Fault tolerance: Cloud Composer (based on Apache Airflow) allows for handling failures at specific

stages. You can clear the state of a failed task and rerun it without reprocessing the entire pipeline.

Stage-based processing: DAGs are ideal for workflows with interdependent stages where the output

of one stage serves as input to the next.

Efficiency: This approach minimizes downtime and ensures that only failed stages are rerun, leading

to faster final output generation.

The storage system must balance cost, simplicity, and access patterns: high initial access, decreasing

over time, with some files remaining popular. Google Cloud Storage offers tailored options for this:

Option A: Custom Object Lifecycle Management (OLM) policies (e.g., transition to Nearline after 30

days, Archive after 90 days) are effective but static. They don’t adapt to actual usage, so popular old

files in Archive would incur high retrieval costs.

Option B: Autoclass automatically adjusts storage classes (Standard, Nearline, Coldline, Archive)

based on object access patterns, not just age. It keeps frequently accessed files in Standard (low

latency/cost for access) and moves inactive ones to cheaper classes, minimizing costs while

preserving simplicity. This fits the “some files remain popular” nuance.

Option C: A Cloud Scheduler job to manually change classes daily is complex (requires scripting,

monitoring), error-prone, and less cost-effective than automated solutions like Autoclass or OLM.

Option D: Defaulting to Archive is cheapest for storage but disastrous for access—retrieval costs and

latency would skyrocket for initial high-access periods.

Why B is Best: Autoclass simplifies management (no rules to define) and optimizes costs dynamically.

For videos, where access varies unpredictably, it ensures popular files stay accessible without manual

intervention, aligning with Google’s cost-optimization guidance.

Extract from Google Documentation: From "Autoclass in Cloud Storage"

(https://cloud.google.com/storage/docs/autoclass): "Autoclass automatically transitions objects to

the most cost-effective storage class based on access patterns, simplifying management and

reducing costs for workloads with variable access, such as media files."

Reference: Google Cloud Documentation - "Cloud Storage Autoclass"

(https://cloud.google.com/storage/docs/autoclass).

:

Using BigQuery ML is the best solution in this case because:

Ease of use: BigQuery ML allows users to build machine learning models using SQL, which requires

minimal ML expertise.

Integrated platform: Since the data already exists in BigQuery, there’s no need to move it to another

service, saving time and engineering resources.

Logistic regression: This is an appropriate model for binary classification tasks like predicting the

likelihood of a customer making a purchase in the next month.

Comprehensive and Detailed In-Depth

BigQuery uses IAM for access control, adhering to least privilege by granting only necessary

permissions.

Option A: IAM roles (e.g., roles/bigquery.dataViewer for read-only) restrict query access to

authorized users, aligning with Google’s security best practices.

Option B: BigQuery doesn’t support SQL GRANT for dataset privileges; access is managed via IAM or

authorized views.

Option C: Exporting to Cloud Storage with signed URLs bypasses BigQuery’s native controls and adds

complexity.

Option D: CMEK encrypts data at rest but doesn’t control query access.

Extract from Google Documentation: From "Controlling Access to BigQuery"

(https://cloud.google.com/bigquery/docs/access-control): "Use IAM to manage access to BigQuery

resources. Predefined roles like roles/bigquery.dataViewer allow you to grant query access while

following the principle of least privilege."

Reference: Google Cloud Documentation - "BigQuery IAM"

(https://cloud.google.com/bigquery/docs/access-control).

Using the "Pub/Sub to BigQuery" Dataflow template with a UDF (User-Defined Function) is the

optimal choice because it combines near real-time processing, minimal code for transformations,

and scalability. The UDF allows for efficient implementation of custom transformations, such as

capitalizing letters in the serial number field, while Dataflow handles the rest of the managed

pipeline seamlessly.

Since your existing data pipeline tools already support connectors to BigQuery, the most efficient

approach is to use the existing data pipeline tool's BigQuery connector to reconfigure the data

mapping. This leverages your current tools, reducing migration complexity and setup time, while

optimizing migration speed. By reconfiguring the data mapping within the existing pipeline, you can

seamlessly direct the data into BigQuery without needing additional services or intermediary steps.

Using customer-managed encryption keys (CMEK) allows you to encrypt highly sensitive data in

BigQuery with encryption keys managed by your organization. When an employee leaves the

organization, you can render the data unreadable by deleting or revoking access to the encryption

keys associated with the data. This approach ensures compliance with strict data privacy regulations

by making the data inaccessible without the encryption keys, providing strong control over data

access and security.

Using Analytics Hub to create a private exchange with data egress restrictions ensures controlled

sharing of the dataset while minimizing the risk of unauthorized copying. This approach allows you to

provide secure, managed access to the dataset without giving direct access to the raw data. The

egress restriction ensures that data cannot be exported or copied outside the designated boundaries.

Additionally, this solution provides a reusable framework that simplifies future data sharing with

other teams or projects while maintaining strict data governance.

Extract from Google Documentation: From "Analytics Hub Overview"

(https://cloud.google.com/analytics-hub/docs): "Analytics Hub enables secure, controlled data

sharing with private exchanges. Combine with organization policies like restrictDataEgress to prevent

data copying, providing a reusable framework for sharing BigQuery datasets across teams."

Reference: Google Cloud Documentation - "Analytics Hub" (https://cloud.google.com/analytics-hub).

Comprehensive and Detailed In-Depth

For a 25 TB dataset, efficiency and cost require minimizing data movement and leveraging

BigQuery’s scalability within Colab Enterprise.

Option A: Exporting 25 TB to Google Drive and loading via Pandas is impractical (size limits, transfer

costs) and slow.

Option B: BigQuery magic commands (%%bigquery) in Colab Enterprise allow direct querying of

BigQuery data, keeping processing in the cloud, reducing costs, and enabling collaboration.

Option C: Dataproc with Spark adds cluster costs and complexity, unnecessary when BigQuery can

handle the workload.

Option D: Copying 25 TB to local storage is infeasible due to size and cost.

Extract from Google Documentation: From "Using BigQuery with Colab Enterprise"

(https://cloud.google.com/colab/docs/bigquery): "You can use BigQuery magic commands

(%%bigquery) in Colab Enterprise to execute SQL queries directly against BigQuery datasets,

providing efficient access to large-scale data without moving it."

Reference: Google Cloud Documentation - "Colab Enterprise with BigQuery"

(https://cloud.google.com/colab/docs).

Comprehensive and Detailed in Depth

Why D is correct:Creating a BigQuery external table directly from the Google Sheet allows for real-

time updates.

Joining the external table with the actual cost table in BigQuery performs the calculation.

Connected Sheets allows the team to access and analyze the results directly in Google Sheets, with

the data being updated.

Why other options are incorrect:A: Saving as a CSV file loses the live connection and daily updates.

B: Downloading and uploading as a CSV file adds unnecessary steps and loses the live connection.

C: Same issue as B, losing the live connection.

Reference:

BigQuery External Tables: https://cloud.google.com/bigquery/docs/external-tables

Connected Sheets: https://support.google.com/sheets/answer/9054368?hl=en

Creating a materialized view with the limited data in a new dataset and granting the data analyst the

BigQuery Data Viewer role on the dataset and the BigQuery Job User role in the project ensures that

the analyst can query only the non-sensitive data without access to sensitive datasets. Materialized

views allow you to predefine what subset of data is visible, providing a secure and efficient way to

control access while maintaining compliance with data governance policies. This approach follows

the principle of least privilege while meeting the requirements.

Comprehensive and Detailed in Depth

Why A is correct:Storage Transfer Service with agent-based transfer allows for online migrations and

provides the ability to set bandwidth limits.

Agents are installed on-premises and can be configured to respect network constraints.

Why other options are incorrect:B: The --daisy-chain option is not related to bandwidth control.

C: Transfer Appliance is for offline migrations and is not suitable for online transfers with bandwidth

constraints.

D: The --no-clobber option prevents overwriting existing files but does not control bandwidth.

Reference:

Storage Transfer Service: https://cloud.google.com/storage-transfer-service/docs

Storage Transfer Service Agents: https://cloud.google.com/storage-transfer-service/docs/agentoverview

gcloud storage cp: https://cloud.google.com/storage/docs/gsutil/commands/cp

Comprehensive and Detailed in Depth

Why C is correct:Setting the Shared folder to "View" ensures everyone can see the content.

Creating Looker groups simplifies access management.

Subfolders allow granular permissions for each team.

Granting "Manage Access, Edit" allows teams to modify only their own content.

Why other options are incorrect:A: Grants View access only, so teams can't edit.

B: Moving content to personal folders defeats the purpose of sharing.

D: Grants edit access to all members of the team, not the team as a whole, which is not ideal.

Reference:

Looker Access Control: https://cloud.google.com/looker/docs/access-control

Looker Groups: https://cloud.google.com/looker/docs/groups

Setting the table partition expiration period to one year using the ALTER TABLE statement is the most

efficient and cost-effective approach. This automatically deletes data in partitions older than one

year, reducing storage costs without requiring manual intervention or additional queries. It

minimizes administrative overhead and ensures compliance with your data retention policy while

optimizing storage usage in BigQuery.

Extract from Google Documentation: From "Managing Partitioned Tables in BigQuery"

(https://cloud.google.com/bigquery/docs/partitioned-tables#expiration): "Set a partition expiration

time using ALTER TABLE to automatically remove partitions older than a specified duration, reducing

storage costs efficiently for ingestion-time partitioned tables."

Reference: Google Cloud Documentation - "BigQuery Partitioned Tables"

(https://cloud.google.com/bigquery/docs/partitioned-tables).

Comprehensive and Detailed in Depth

Why A is correct:Analytics Hub allows you to share datasets with external partners while maintaining

control over access.

Subscriptions allow granular control.

Why other options are incorrect:B: Cloud storage is for files, not bigquery datasets.

C: IAM roles do not allow for granular row and column level control.

D: Creating a separate project for each partner is complex and not scalable.

Reference:

Analytics Hub: https://cloud.google.com/analytics-hub/docs

To calculate the weekly moving average of sales by location: The query must group by store_id (partitioning the calculation by each store). The ORDER BY date ensures the sales are evaluated chronologically. The ROWS BETWEEN 6 PRECEDING AND CURRENT ROW specifies a rolling window of 7 rows (1 week if each row represents daily data). The AVG(total_sales) computes the average sales over the defined rolling window. Chosen query meets these requirements: PARTITION BY store_id groups the calculation by each store. https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/GOOGLE_Cloud_Associate-Data-Practitioner_/page_6_img_1.jpg ORDER BY date orders the rows correctly for the rolling average. https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/GOOGLE_Cloud_Associate-Data-Practitioner_/page_6_img_2.jpg ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ensures the 7-day moving average. https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/GOOGLE_Cloud_Associate-Data-Practitioner_/page_6_img_3.jpg Extract from Google Documentation: From "Analytic Functions in BigQuery" (https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts): "Use ROWS BETWEEN n PRECEDING AND CURRENT ROW with ORDER BY a time column to compute moving averages over a fixed number of rows, such as a 7-day window, partitioned by a grouping key like store_id." Reference: Google Cloud Documentation - "BigQuery Window Functions" (https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls).

Comprehensive and Detailed in Depth

Why C is correct:Materialized views in BigQuery are precomputed views that periodically cache the

results of a query. This ensures up-to-date results automatically.

A UNION is the correct operation to combine the data from multiple regional sales tables.

RANK() function is correct to rank the sales regions. ROW_NUMBER() would create a unique number

for each row, even if sales amount is the same, this is not the desired function.

Why other options are incorrect:A and B: Standard tables do not provide automatic updates.

D: A CROSS JOIN would produce a Cartesian product, which is not appropriate for combining regional

sales data.

Cross join is used when you want every combination of rows from tables, not a aggregation of data.

Reference:

BigQuery Materialized Views: https://cloud.google.com/bigquery/docs/materialized-views

BigQuery Window Functions: https://cloud.google.com/bigquery/docs/reference/standardsql/window-functions

BigQuery UNION: https://cloud.google.com/bigquery/docs/reference/standard-sql/querysyntax#union_all

BigQuery CROSS JOIN: https://cloud.google.com/bigquery/docs/reference/standard-sql/querysyntax#cross_join

Comprehensive and Detailed In-Depth

The solution must support SQL-based ELT, be serverless and cost-effective, and include advanced

features like version control and quality checks. Let’s dive in:

Option A: Cloud Data Fusion is a visual ETL tool, not SQL-centric (uses plugins), and isn’t fully

serverless (requires instance management). It lacks native source code control and parameterization.

Option B: Dataform is a serverless, SQL-based ELT platform for BigQuery. It uses SQLX scripts,

integrates with Git for version control, supports environment variables (parameterization), and offers

assertions for data quality—all meeting the requirements cost-effectively.

Option C: Dataproc is for Spark/MapReduce, not SQL ELT, and requires cluster management,

contradicting serverless and cost goals.

Option D: Cloud Composer orchestrates workflows (Python DAGs), not SQL pipelines natively. It’s

managed but not optimized for ELT within BigQuery alone.

Why B is Best: Dataform leverages your team’s SQL skills, runs in BigQuery (no extra infrastructure),

and provides Git integration (e.g., GitHub), parameterization (e.g., DECLARE env STRING DEFAULT

'prod';), and quality checks (e.g., assert 'no_nulls' AS SELECT COUNT(*) FROM table WHERE col IS

NULL). It’s the perfect fit.

Extract from Google Documentation: From "Dataform Overview"

(https://cloud.google.com/dataform/docs): "Dataform is a fully managed, serverless solution for

building SQL-based ELT pipelines in BigQuery, with built-in Git version control, environment

parameterization, and data quality assertions for robust data warehouse management."

Reference: Google Cloud Documentation - "Dataform" (https://cloud.google.com/dataform).

Comprehensive and Detailed in Depth

Why C is correct:Dataflow is the recommended service for real-time stream processing on Google

Cloud.

It provides scalable and reliable processing with low latency and high throughput.

Dataflow's streaming API is optimized for Pub/Sub integration and BigQuery streaming inserts.

Why other options are incorrect:A: Cloud Composer is for batch orchestration, not real-time

streaming.

B: Dataproc and Spark streaming are more complex and not as efficient as Dataflow for this task.

D: Cloud Run functions are for stateless, event-driven applications, not continuous stream

processing.

Reference:

Dataflow Streaming: https://cloud.google.com/dataflow/docs/concepts/streaming-pipelines

Pub/Sub to BigQuery with Dataflow: https://cloud.google.com/dataflow/docs/tutorials/pubsub-tobigquery