2 packs: 15% off with code BUY2. 3 or more: 20% off with code BUY3.

Free Google Associate Data Practitioner Practice Questions

26 free Google Associate Data Practitioner practice questions from the CertStash pack of 103, each with the correct answer and a full explanation. The rule is the same for every exam: ten free on the product page, 26 free here, all 103 in the pack, and each step includes the one before it.

Get all 103 questions (US$39) · Download the first 10 as a PDF

Question 1

Your organization uses Dataflow pipelines to process real-time financial transactions. You discover that one of your Dataflow jobs has failed. You need to troubleshoot the issue as quickly as possible. What should you do?

  1. Set up a Cloud Monitoring dashboard to track key Dataflow metrics, such as data throughput, error rates, and resource utilization.
  2. Create a custom script to periodically poll the Dataflow API for job status updates, and send email alerts if any errors are identified.
  3. Navigate to the Dataflow Jobs page in the Google Cloud console. Use the job logs and worker logs to identify the error.
  4. Use the gcloud CLI tool to retrieve job metrics and logs, and analyze them for errors and performance bottlenecks.
Show answer and explanation

Correct answer: C. Navigate to the Dataflow Jobs page in the Google Cloud console. Use the job logs and worker logs to identify the error.

When a Dataflow job fails, the fastest way to troubleshoot is to directly examine the job logs and worker logs available in the Google Cloud console's Dataflow Jobs page. This provides immediate visibility into error messages, stack traces, and failure reasons without requiring additional setup or custom tooling. The Dataflow Jobs page displays detailed logs that pinpoint exactly what went wrong, allowing rapid identification and resolution of the issue.

Why the other options are wrong

  • A. Setting up a monitoring dashboard is useful for ongoing observability but does not directly troubleshoot an existing failure and requires time to configure.
  • B. Creating a custom polling script is inefficient and unnecessary when native logging and monitoring tools already exist in the console.
  • D. While the gcloud CLI can retrieve metrics and logs, accessing the console directly is faster for immediate troubleshooting of a known failure.

Question 2

You work for a financial organization that stores transaction data in BigQuery. Your organization has a regulatory requirement to retain data for a minimum of seven years for auditing purposes. You need to ensure that the data is retained for seven years using an efficient and cost-optimized approach. What should you do?

  1. Create a partition by transaction date, and set the partition expiration policy to seven years.
  2. Set the table-level retention policy in BigQuery to seven years.
  3. Set the dataset-level retention policy in BigQuery to seven years.
  4. Export the BigQuery tables to Cloud Storage daily, and enforce a lifecycle management policy that has a seven-year retention rule.
Show answer and explanation

Correct answer: A. Create a partition by transaction date, and set the partition expiration policy to seven years.

Partition expiration policies in BigQuery allow you to automatically delete data older than a specified period, providing both cost optimization and automated compliance. This is more efficient than table-level or dataset-level retention policies because it operates at the partition granularity, deleting only expired partitions rather than entire tables. Option A directly addresses the regulatory requirement while maintaining cost efficiency through automatic deletion of old data.

Why the other options are wrong

  • B. Table-level retention policies delete the entire table after the retention period, which is inefficient and doesn't allow for archival of historical data.
  • C. Dataset-level retention policies apply to all tables in a dataset uniformly and lack the granularity needed for efficient data management.
  • D. Exporting to Cloud Storage and managing lifecycle policies adds unnecessary operational complexity and management overhead compared to built-in BigQuery partition expiration.

Question 3

You need to create a weekly aggregated sales report based on a large volume of data. You want to use Python to design an efficient process for generating this report. What should you do?

  1. Create a Cloud Run function that uses NumPy. Use Cloud Scheduler to schedule the function to run once a week.
  2. Create a Colab Enterprise notebook and use the bigframes.pandas library. Schedule the notebook to execute once a week.
  3. Create a Cloud Data Fusion and Wrangler flow. Schedule the flow to run once a week.
  4. Create a Dataflow directed acyclic graph (DAG) coded in Python. Use Cloud Scheduler to schedule the code to run once a week.
Show answer and explanation

Correct answer: B. Create a Colab Enterprise notebook and use the bigframes.pandas library. Schedule the notebook to execute once a week.

Colab Enterprise with bigframes.pandas library provides a Python-native environment optimized for data analysis at scale, with built-in integration to BigQuery and pandas-compatible APIs. The ability to schedule Colab notebooks through Vertex AI makes this the most efficient Python-based solution for weekly aggregation reports. This approach minimizes setup complexity while leveraging Google's managed environment.

Why the other options are wrong

  • A. Cloud Run functions are better suited for event-driven workloads rather than scheduled data aggregation tasks, and NumPy alone isn't optimized for BigQuery operations.
  • C. Cloud Data Fusion is a visual ETL tool better suited for complex data integration scenarios rather than Python-based aggregation logic.
  • D. Dataflow is designed for streaming or large-scale batch processing and introduces unnecessary complexity for simple weekly aggregation tasks.

Question 4

Your organization has decided to move their on-premises Apache Spark-based workload to Google Cloud. You want to be able to manage the code without needing to provision and manage your own cluster. What should you do?

  1. Migrate the Spark jobs to Dataproc Serverless.
  2. Configure a Google Kubernetes Engine cluster with Spark operators, and deploy the Spark jobs.
  3. Migrate the Spark jobs to Dataproc on Google Kubernetes Engine.
  4. Migrate the Spark jobs to Dataproc on Compute Engine.
Show answer and explanation

Correct answer: A. Migrate the Spark jobs to Dataproc Serverless.

Dataproc Serverless is the fully managed solution that eliminates the need to provision or manage clusters while running Apache Spark jobs. It automatically handles cluster lifecycle management, scaling, and resource optimization, allowing users to focus solely on their Spark code without infrastructure management overhead.

Why the other options are wrong

  • B. GKE with Spark operators still requires provisioning and managing the Kubernetes cluster itself.
  • C. Dataproc on GKE requires managing the underlying Kubernetes cluster, which contradicts the requirement to avoid cluster management.
  • D. Dataproc on Compute Engine requires manual cluster provisioning and management, which is the on-premises model the organization wants to move away from.

Question 5

You are developing a data ingestion pipeline to load small CSV files into BigQuery from Cloud Storage. You want to load these files upon arrival to minimize data latency. You want to accomplish this with minimal cost and maintenance. What should you do?

  1. Use the bq command-line tool within a Cloud Shell instance to load the data into BigQuery.
  2. Create a Cloud Composer pipeline to load new files from Cloud Storage to BigQuery and schedule it to run every 10 minutes.
  3. Create a Cloud Run function to load the data into BigQuery that is triggered when data arrives in Cloud Storage.
  4. Create a Dataproc cluster to pull CSV files from Cloud Storage, process them using Spark, and write the results to BigQuery.
Show answer and explanation

Correct answer: C. Create a Cloud Run function to load the data into BigQuery that is triggered when data arrives in Cloud Storage.

Cloud Run functions triggered by Cloud Storage events provide the ideal balance of minimal latency, cost efficiency, and low maintenance. The function executes only when files arrive, minimizing costs compared to scheduled polling, and Cloud Run's serverless nature eliminates cluster or pipeline management overhead.

Why the other options are wrong

  • A. Manual bq command-line operations are not automated and require human intervention, defeating the goal of minimizing data latency.
  • B. Cloud Composer polling every 10 minutes creates unnecessary latency and continuous costs even when no files arrive.
  • D. Dataproc clusters incur persistent costs and require significant management overhead for a simple file loading task.

Question 6

Your organization has a petabyte of application logs stored as Parquet files in Cloud Storage. You need to quickly perform a one-time SQL-based analysis of the files and join them to data that already resides in BigQuery. What should you do?

  1. Create a Dataproc cluster, and write a PySpark job to join the data from BigQuery to the files in Cloud Storage.
  2. Launch a Cloud Data Fusion environment, use plugins to connect to BigQuery and Cloud Storage, and use the SQL join operation to analyze the data.
  3. Create external tables over the files in Cloud Storage, and perform SQL joins to tables in BigQuery to analyze the data.
  4. Use the bq load command to load the Parquet files into BigQuery, and perform SQL joins to analyze the data.
Show answer and explanation

Correct answer: C. Create external tables over the files in Cloud Storage, and perform SQL joins to tables in BigQuery to analyze the data.

Creating external tables over Parquet files in Cloud Storage allows direct SQL queries against the data without loading it into BigQuery, perfect for one-time analysis. This approach minimizes cost and latency since data remains in place and BigQuery's SQL engine can efficiently query external sources while joining with native BigQuery tables.

Why the other options are wrong

  • A. Creating a Dataproc cluster and writing PySpark code adds unnecessary complexity and cost for a simple one-time SQL analysis.
  • B. Cloud Data Fusion is overkill for a simple one-time analysis and requires environment setup and management.
  • D. Loading a petabyte of data into BigQuery is prohibitively expensive and unnecessary for a one-time query, whereas external tables provide instant queryability.

Question 7

Your team is building several data pipelines that contain a collection of complex tasks and dependencies that you want to execute on a schedule, in a specific order. The tasks and dependencies consist of files in Cloud Storage, Apache Spark jobs, and data in BigQuery. You need to design a system that can schedule and automate these data processing tasks using a fully managed approach. What should you do?

  1. Use Cloud Scheduler to schedule the jobs to run.
  2. Use Cloud Tasks to schedule and run the jobs asynchronously.
  3. Create directed acyclic graphs (DAGs) in Cloud Composer. Use the appropriate operators to connect to Cloud Storage, Spark, and BigQuery.
  4. Create directed acyclic graphs (DAGs) in Apache Airflow deployed on Google Kubernetes Engine. Use the appropriate operators to connect to Cloud Storage, Spark, and BigQuery.
Show answer and explanation

Correct answer: C. Create directed acyclic graphs (DAGs) in Cloud Composer. Use the appropriate operators to connect to Cloud Storage, Spark, and BigQuery.

Cloud Composer provides a fully managed Apache Airflow environment specifically designed for scheduling and automating complex data pipelines with task dependencies. It includes pre-built operators for Cloud Storage, Spark (via Dataproc), and BigQuery, making it ideal for orchestrating workflows with multiple dependency relationships across these services.

Why the other options are wrong

  • A. Cloud Scheduler handles simple time-based scheduling but lacks the capability to express and manage complex task dependencies.
  • B. Cloud Tasks is designed for asynchronous task processing and HTTP callbacks, not for orchestrating interdependent data pipeline workflows.
  • D. Deploying Apache Airflow on GKE requires infrastructure management and operational overhead, whereas Cloud Composer is the fully managed alternative.

Question 8

You are responsible for managing Cloud Storage buckets for a research company. Your company has well-defined data tiering and retention rules. You need to optimize storage costs while achieving your data retention needs. What should you do?

  1. Configure the buckets to use the Archive storage class.
  2. Configure a lifecycle management policy on each bucket to downgrade the storage class and remove objects based on age.
  3. Configure the buckets to use the Standard storage class and enable Object Versioning.
  4. Configure the buckets to use the Autoclass feature.
Show answer and explanation

Correct answer: B. Configure a lifecycle management policy on each bucket to downgrade the storage class and remove objects based on age.

When the tiering and retention rules are already well defined, Object Lifecycle Management lets you encode them directly. Lifecycle rules move objects to cheaper storage classes and delete them based on age, which cuts cost while enforcing the retention requirements.

Why the other options are wrong

  • A. Putting everything in Archive ignores the tiering rules and adds high retrieval costs and 365-day minimum storage charges.
  • C. Standard storage with Object Versioning keeps extra copies and raises cost instead of lowering it.
  • D. Autoclass moves objects based on observed access patterns and cannot enforce your own tiering or deletion rules.

Question 9

You are using your own data to demonstrate the capabilities of BigQuery to your organization’s leadership team. You need to perform a one-time load of the files stored on your local machine into BigQuery using as little effort as possible. What should you do?

  1. Write and execute a Python script using the BigQuery Storage Write API library.
  2. Create a Dataproc cluster, copy the files to Cloud Storage, and write an Apache Spark job using the spark-bigquery-connector.
  3. Execute the bq load command on your local machine.
  4. Create a Dataflow job using the Apache Beam FileIO and BigQueryIO connectors with a local runner.
Show answer and explanation

Correct answer: C. Execute the bq load command on your local machine.

The bq load command is the simplest and most direct method for loading local files into BigQuery with minimal effort. It requires only the BigQuery CLI installation on the local machine and a single command execution, making it ideal for one-time demonstrations without any infrastructure setup.

Why the other options are wrong

  • A. Writing a Python script with the BigQuery Storage Write API requires more effort and code than the bq command-line tool.
  • B. Creating a Dataproc cluster and writing Spark jobs introduces unnecessary complexity and cost for a simple one-time load operation.
  • D. Setting up a Dataflow job with Apache Beam connectors and configuring a local runner is overly complex for a one-time data load.

Question 10

Your company has developed a website that allows users to upload and share video files. These files are most frequently accessed and shared when they are initially uploaded. Over time, the files are accessed and shared less frequently, although some old video files may remain very popular. You need to design a storage system that is simple and cost-effective. What should you do?

  1. Create a single-region bucket with Autoclass enabled.
  2. Create a single-region bucket. Configure a Cloud Scheduler job that runs every 24 hours and changes the storage class based on upload date.
  3. Create a single-region bucket with custom Object Lifecycle Management policies based on upload date.
  4. Create a single-region bucket with Archive as the default storage class.
Show answer and explanation

Correct answer: A. Create a single-region bucket with Autoclass enabled.

Autoclass automatically transitions objects between access tiers based on access patterns, reducing costs for data that becomes less frequently accessed over time without requiring manual intervention. This is ideal for video files that are accessed frequently when new but less so as they age, while still allowing old popular files to remain accessible. It provides the simplicity and cost-effectiveness required without manual management or complex scheduling.

Why the other options are wrong

  • B. Requires manual Cloud Scheduler job configuration and maintenance overhead, which is more complex than necessary.
  • C. While lifecycle policies can work, they require manual date-based rules that are less flexible than Autoclass for handling unpredictable access patterns.
  • D. Setting Archive as default makes recently uploaded files (which need frequent access) expensive to retrieve, contradicting the requirement.

Question 11

You need to design a data pipeline that ingests data from CSV, Avro, and Parquet files into Cloud Storage. The data includes raw user input. You need to remove all malicious SQL injections before storing the data in BigQuery. Which data manipulation methodology should you choose?

  1. EL
  2. ELT
  3. ETL
  4. ETLT
Show answer and explanation

Correct answer: C. ETL

ETL (Extract, Transform, Load) is the correct methodology because the data must be transformed (cleaned to remove SQL injections) before loading into BigQuery. The transformation step is essential to ensure malicious input is sanitized before the data reaches the target system, making this a required pre-load operation.

Why the other options are wrong

  • A. EL (Extract, Load) skips the transformation step entirely, allowing malicious data into BigQuery.
  • B. ELT (Extract, Load, Transform) loads unsanitized data into BigQuery first, violating security practices by storing malicious input.
  • D. ETLT is not a standard data pipeline methodology.

Question 12

You manage a web application that stores data in a Cloud SQL database. You need to improve the read performance of the application by offloading read traffic from the primary database instance. You want to implement a solution that minimizes effort and cost. What should you do?

  1. Use Cloud CDN to cache frequently accessed data.
  2. Store frequently accessed data in a Memorystore instance.
  3. Migrate the database to a larger Cloud SQL instance.
  4. Enable automatic backups, and create a read replica of the Cloud SQL instance.
Show answer and explanation

Correct answer: D. Enable automatic backups, and create a read replica of the Cloud SQL instance.

Creating a read replica of a Cloud SQL instance is the most efficient and cost-effective solution for offloading read traffic. Read replicas handle read-only queries while the primary instance handles writes, improving overall performance with minimal effort. This is a native Cloud SQL feature that requires no application redesign and is more cost-effective than upgrading to a larger instance or adding caching layers.

Why the other options are wrong

  • A. Cloud CDN caches HTTP responses from web applications, not database queries, and is not suitable for direct database performance improvements.
  • B. Memorystore requires application code changes to implement caching logic and adds operational complexity compared to native read replicas.
  • C. Migrating to a larger instance does not reduce read load and increases cost without addressing the actual problem of read traffic overload.

Question 13

You manage a Cloud Storage bucket that stores temporary files created during data processing. These temporary files are only needed for seven days, after which they are no longer needed. To reduce storage costs and keep your bucket organized, you want to automatically delete these files once they are older than seven days. What should you do?

  1. Set up a Cloud Scheduler job that invokes a weekly Cloud Run function to delete files older than seven days.
  2. Configure a Cloud Storage lifecycle rule that automatically deletes objects older than seven days.
  3. Develop a batch process using Dataflow that runs weekly and deletes files based on their age.
  4. Create a Cloud Run function that runs daily and deletes files older than seven days.
Show answer and explanation

Correct answer: B. Configure a Cloud Storage lifecycle rule that automatically deletes objects older than seven days.

Cloud Storage lifecycle rules are the native, purpose-built managed solution for automatically managing object retention and deletion based on age. Configuring a Delete action for objects older than seven days is the most efficient, cost-effective, and maintenance-free approach. Lifecycle rules execute automatically without requiring external services or custom code.

Why the other options are wrong

  • A. Cloud Scheduler with Cloud Run is unnecessarily complex and requires custom code; it's less efficient than native lifecycle rules.
  • C. Dataflow is overkill for a simple age-based deletion task and requires more infrastructure and cost than lifecycle rules.
  • D. A daily Cloud Run function adds unnecessary compute costs and operational overhead compared to the automated lifecycle rule approach.

Question 14

Your organization sends IoT event data to a Pub/Sub topic. Subscriber applications read and perform transformations on the messages before storing them in the data warehouse. During particularly busy times when more data is being written to the topic, you notice that the subscriber applications are not acknowledging messages within the deadline. You need to modify your pipeline to handle these activity spikes and continue to process the messages. What should you do?

  1. Retry messages until they are acknowledged.
  2. Implement flow control on the subscribers.
  3. Forward unacknowledged messages to a dead-letter topic.
  4. Seek back to the last acknowledged message.
Show answer and explanation

Correct answer: B. Implement flow control on the subscribers.

Flow control on subscribers prevents message overload by limiting the rate at which messages are pulled, allowing the subscriber applications sufficient time to process and acknowledge messages before the deadline is reached. This addresses the root cause of the problem, subscribers being overwhelmed during activity spikes, by ensuring the incoming message rate matches processing capacity rather than causing messages to be dropped or left unacknowledged.

Why the other options are wrong

  • A. Retrying unacknowledged messages does not solve the underlying problem of subscribers being unable to keep up with the message rate during spikes.
  • C. Dead-letter topics are for handling messages that cannot be processed successfully, not for managing processing delays during high traffic periods.
  • D. Seeking back to the last acknowledged message does not prevent future messages from exceeding the acknowledgment deadline during periods of high volume.

Question 15

You have created a LookML model and dashboard that shows daily sales metrics for five regional managers to use. You want to ensure that the regional managers can only see sales metrics specific to their region. You need an easy-to-implement solution. What should you do?

  1. Create a sales_region user attribute, and assign each manager’s region as the value of their user attribute. Add an access_filter Explore filter on the region_name dimension by using the sales_region user attribute.
  2. Create five different Explores with the sql_always_filter Explore filter applied on the region_name dimension. Set each region_name value to the corresponding region for each manager.
  3. Create separate Looker dashboards for each regional manager. Set the default dashboard filter to the corresponding region for each manager.
  4. Create separate Looker instances for each regional manager. Copy the LookML model and dashboard to each instance. Provision viewer access to the corresponding manager.
Show answer and explanation

Correct answer: A. Create a sales_region user attribute, and assign each manager’s region as the value of their user attribute. Add an access_filter Explore filter on the region_name dimension by using the sales_region user attribute.

User attributes combined with access filters provide a scalable, maintainable row-level security solution. By assigning each manager a sales_region user attribute and applying an access_filter on the region_name dimension, data visibility is automatically restricted without duplicating dashboards or models. This is the standard Looker approach for filtering by user identity.

Why the other options are wrong

  • B. Creating five separate Explores duplicates model logic and is difficult to maintain when business logic changes.
  • C. Creating five separate dashboards duplicates work and makes maintenance cumbersome as the dashboard design evolves.
  • D. Creating separate Looker instances is extremely inefficient, wasteful of resources, and impractical to maintain and update.

Question 16

You need to create a data pipeline that streams event information from applications in multiple Google Cloud regions into BigQuery for near real-time analysis. The data requires transformation before loading. You want to create the pipeline using a visual interface. What should you do?

  1. Push event information to a Pub/Sub topic. Create a Dataflow job using the Dataflow job builder.
  2. Push event information to a Pub/Sub topic. Create a Cloud Run function to subscribe to the Pub/Sub topic, apply transformations, and insert the data into BigQuery.
  3. Push event information to a Pub/Sub topic. Create a BigQuery subscription in Pub/Sub.
  4. Push event information to Cloud Storage, and create an external table in BigQuery. Create a BigQuery scheduled job that executes once each day to apply transformations.
Show answer and explanation

Correct answer: A. Push event information to a Pub/Sub topic. Create a Dataflow job using the Dataflow job builder.

The Dataflow job builder provides a visual interface for creating data pipelines that process streaming data from Pub/Sub with transformations before loading into BigQuery. This directly addresses the requirement for a visual interface to handle multi-region event streaming with near real-time transformation and loading.

Why the other options are wrong

  • B. Cloud Run functions lack a visual interface and require coding, contradicting the requirement to use a visual interface.
  • C. BigQuery subscriptions do not exist in Pub/Sub; Pub/Sub subscriptions deliver messages to BigQuery but do not handle transformations.
  • D. Batch processing with daily scheduled jobs does not meet the near real-time analysis requirement, and Cloud Storage external tables do not directly support streaming from multiple regions.

Question 17

You need to create a new data pipeline. You want a serverless solution that meets the following requirements: • Data is streamed from Pub/Sub and is processed in real-time. • Data is transformed before being stored. • Data is stored in a location that will allow it to be analyzed with SQL using Looker. Which Google Cloud services should you recommend for the pipeline?

  1. 1. Dataproc Serverless 2. Bigtable
  2. 1. Cloud Composer 2. Cloud SQL for MySQL
  3. 1. BigQuery 2. Analytics Hub
  4. 1. Dataflow 2. BigQuery
Show answer and explanation

Correct answer: D. 1. Dataflow 2. BigQuery

The requirements specify a serverless solution that streams data from Pub/Sub, transforms it in real-time, and stores it for SQL analysis with Looker. Dataflow (position 1) is Google Cloud's serverless, fully managed service for stream and batch data processing that directly consumes Pub/Sub messages and performs transformations. BigQuery (position 2) is the serverless data warehouse that natively integrates with Dataflow, stores transformed data, and provides SQL querying capabilities that Looker can directly connect to for analysis. This combination satisfies all requirements: serverless architecture, real-time streaming, transformation capability, and SQL-queryable storage with Looker integration.

Why the other options are wrong

  • A. Dataproc Serverless is for Apache Spark/Hadoop jobs and requires cluster management; Bigtable is a NoSQL database that doesn't provide the SQL querying capability needed for Looker analysis.
  • B. Cloud Composer is a workflow orchestration tool, not a stream processing engine; Cloud SQL for MySQL lacks the scalability and Looker-native integration that BigQuery provides, and neither component handles real-time streaming from Pub/Sub efficiently.
  • C. BigQuery is correct for storage and SQL analysis, but Analytics Hub is a data sharing platform, not a stream processing or transformation service like Dataflow.

Question 18

Your company is building a near real-time streaming pipeline to process JSON telemetry data from small appliances. You need to process messages arriving at a Pub/Sub topic, capitalize letters in the serial number field, and write results to BigQuery. You want to use a managed service and write a minimal amount of code for underlying transformations. What should you do?

  1. Use a Pub/Sub to BigQuery subscription, write results directly to BigQuery, and schedule a transformation query to run every five minutes.
  2. Use a Pub/Sub to Cloud Storage subscription, write a Cloud Run service that is triggered when objects arrive in the bucket, performs the transformations, and writes the results to BigQuery.
  3. Use the “Pub/Sub to BigQuery” Dataflow template with a UDF, and write the results to BigQuery.
  4. Use a Pub/Sub push subscription, write a Cloud Run service that accepts the messages, performs the transformations, and writes the results to BigQuery.
Show answer and explanation

Correct answer: C. Use the “Pub/Sub to BigQuery” Dataflow template with a UDF, and write the results to BigQuery.

The Pub/Sub to BigQuery Dataflow template is a managed service that handles near real-time streaming with minimal code. It supports User Defined Functions (UDFs) written in SQL or JavaScript to perform transformations like capitalizing the serial number field. This approach meets all requirements: managed service, minimal code, and direct streaming to BigQuery without manual scheduling or additional services.

Why the other options are wrong

  • A. Direct Pub/Sub to BigQuery subscriptions don't support transformations; scheduling queries every five minutes introduces latency and doesn't process messages in near real-time.
  • B. Using Cloud Storage as an intermediate step adds unnecessary complexity and latency; Cloud Run introduces additional code and operational overhead.
  • D. While Cloud Run can work, it requires writing custom code for message handling and transformations, violating the minimal code requirement compared to the Dataflow template approach.

Question 19

You are working with a large dataset of customer reviews stored in Cloud Storage. The dataset contains several inconsistencies, such as missing values, incorrect data types, and duplicate entries. You need to clean the data to ensure that it is accurate and consistent before using it for analysis. What should you do?

  1. Use the PythonOperator in Cloud Composer to clean the data and load it into BigQuery. Use SQL for analysis.
  2. Use BigQuery to batch load the data into BigQuery. Use SQL for cleaning and analysis.
  3. Use Storage Transfer Service to move the data to a different Cloud Storage bucket. Use event triggers to invoke Cloud Run functions to load the data into BigQuery. Use SQL for analysis.
  4. Use Cloud Run functions to clean the data and load it into BigQuery. Use SQL for analysis.
Show answer and explanation

Correct answer: B. Use BigQuery to batch load the data into BigQuery. Use SQL for cleaning and analysis.

BigQuery is optimized for batch processing large datasets and provides SQL capabilities to handle missing values, data type corrections, and duplicate removal efficiently in a single platform. Loading data directly into BigQuery and using SQL for both cleaning and analysis minimizes complexity, reduces operational overhead, and leverages BigQuery's native capabilities for data quality tasks.

Why the other options are wrong

  • A. Cloud Composer with PythonOperator adds unnecessary orchestration complexity for a straightforward batch cleaning task.
  • C. Storage Transfer Service is designed for moving data between storage systems, not for data cleaning; event-driven Cloud Run adds unnecessary complexity.
  • D. Cloud Run functions add unnecessary complexity when BigQuery SQL can handle all cleaning operations more efficiently in a unified environment.

Question 20

You work for a healthcare company that has a large on-premises data system containing patient records with personally identifiable information (PII) such as names, addresses, and medical diagnoses. You need a standardized managed solution that de-identifies PII across all your data feeds prior to ingestion to Google Cloud. What should you do?

  1. Use Cloud Run functions to create a serverless data cleaning pipeline. Store the cleaned data in BigQuery.
  2. Use Cloud Data Fusion to transform the data. Store the cleaned data in BigQuery.
  3. Load the data into BigQuery, and inspect the data by using SQL queries. Use Dataflow to transform the data and remove any errors.
  4. Use Apache Beam to read the data and perform the necessary cleaning and transformation operations. Store the cleaned data in BigQuery.
Show answer and explanation

Correct answer: B. Use Cloud Data Fusion to transform the data. Store the cleaned data in BigQuery.

Cloud Data Fusion is a managed ETL platform with built-in masking and de-identification plugins and transformations specifically designed for handling sensitive data like PII. It provides a standardized, reusable framework for de-identifying data across multiple data feeds prior to ingestion, meeting the requirement for a standardized managed solution that handles healthcare compliance requirements.

Why the other options are wrong

  • A. Cloud Run functions require custom code development for de-identification logic; this approach lacks the standardized, managed nature of a dedicated platform.
  • C. Loading data into BigQuery first violates security best practices by exposing PII; inspection and transformation should occur before ingestion to Google Cloud.
  • D. Apache Beam requires significant custom code development and operational overhead; it's not a managed solution compared to Cloud Data Fusion.

Question 21

Your organization has several datasets in their data warehouse in BigQuery. Several analyst teams in different departments use the datasets to run queries. Your organization is concerned about the variability of their monthly BigQuery costs. You need to identify a solution that creates a fixed budget for costs associated with the queries run by each department. What should you do?

  1. Create a custom quota for each analyst in BigQuery.
  2. Create a single reservation by using BigQuery editions. Assign all analysts to the reservation.
  3. Assign each analyst to a separate project associated with their department. Create a single reservation by using BigQuery editions. Assign all projects to the reservation.
  4. Assign each analyst to a separate project associated with their department. Create a single reservation for each department by using BigQuery editions. Create assignments for each project in the appropriate reservation.
Show answer and explanation

Correct answer: D. Assign each analyst to a separate project associated with their department. Create a single reservation for each department by using BigQuery editions. Create assignments for each project in the appropriate reservation.

BigQuery editions with reservations allow organizations to allocate fixed compute capacity to different departments. By creating separate reservations for each department and assigning their respective projects to those reservations, each department gets a predictable, fixed budget for query costs. This approach provides granular control and prevents cost variability across departments while enabling fair resource allocation.

Why the other options are wrong

  • A. Custom quotas only limit query volume or slot usage per user, not provide fixed budgets for departments.
  • B. A single reservation for all analysts does not provide department-level cost isolation or budget control.
  • C. While assigning analysts to separate projects is good practice, assigning all projects to a single reservation does not create fixed budgets per department.

Question 22

Your organization uses a BigQuery table that is partitioned by ingestion time. You need to remove data that is older than one year to reduce your organization’s storage costs. You want to use the most efficient approach while minimizing cost. What should you do?

  1. Create a scheduled query that periodically runs an update statement in SQL that sets the “deleted" column to “yes” for data that is more than one year old. Create a view that filters out rows that have been marked deleted.
  2. Create a view that filters out rows that are older than one year.
  3. Require users to specify a partition filter using the alter table statement in SQL.
  4. Set the table partition expiration period to one year using the ALTER TABLE statement in SQL.
Show answer and explanation

Correct answer: D. Set the table partition expiration period to one year using the ALTER TABLE statement in SQL.

Setting partition expiration using the ALTER TABLE statement is the most efficient and cost-effective way to automatically remove old data from a partitioned table. This native BigQuery feature automatically deletes partitions older than the specified period, directly reducing storage costs without requiring scheduled queries or additional views. It is the recommended approach for time-based data retention.

Why the other options are wrong

  • A. Using a scheduled query with soft deletes and a filtering view still stores the marked data and does not actually reduce storage costs.
  • B. A view that filters old data does not remove the data from storage; it still incurs storage costs while only hiding it from queries.
  • C. The ALTER TABLE statement does not enforce user-specified partition filters; partition filters must be specified at query time by users.

Question 23

You recently inherited a task for managing Dataflow streaming pipelines in your organization and noticed that proper access had not been provisioned to you. You need to request a Google-provided IAM role so you can restart the pipelines. You need to follow the principle of least privilege. What should you do?

  1. Request the Dataflow Developer role.
  2. Request the Dataflow Viewer role.
  3. Request the Dataflow Worker role.
  4. Request the Dataflow Admin role.
Show answer and explanation

Correct answer: A. Request the Dataflow Developer role.

The Dataflow Developer role grants the minimum permissions needed to restart pipelines and manage Dataflow resources without providing administrative capabilities. This follows the principle of least privilege by allowing the necessary task without over-provisioning access rights.

Why the other options are wrong

  • B. Dataflow Viewer role only provides read-only access and cannot restart pipelines.
  • C. Dataflow Worker role is for worker nodes within pipelines, not for managing or restarting pipelines.
  • D. Dataflow Admin role grants excessive permissions beyond what is needed to restart pipelines, violating least privilege.

Question 24

Your retail organization stores sensitive application usage data in Cloud Storage. You need to encrypt the data without the operational overhead of managing encryption keys. What should you do?

  1. Use Google-managed encryption keys (GMEK).
  2. Use customer-managed encryption keys (CMEK).
  3. Use customer-supplied encryption keys (CSEK).
  4. Use customer-supplied encryption keys (CSEK) for the sensitive data and customer-managed encryption keys (CMEK) for the less sensitive data.
Show answer and explanation

Correct answer: A. Use Google-managed encryption keys (GMEK).

Google-managed encryption keys (GMEK) provide automatic encryption at rest without operational overhead for key management. Google handles key rotation, storage, and lifecycle automatically, meeting the requirement for encryption without the burden of managing keys.

Why the other options are wrong

  • B. CMEK requires operational overhead to manage key creation, rotation, and access policies in Cloud KMS.
  • C. CSEK requires the organization to manage and supply keys with every API request, creating significant operational complexity.
  • D. Using both CMEK and CSEK creates unnecessary complexity when GMEK alone meets the stated requirement of encryption without operational overhead.

Question 25

You want to process and load a daily sales CSV file stored in Cloud Storage into BigQuery for downstream reporting. You need to quickly build a scalable data pipeline that transforms the data while providing insights into data quality issues. What should you do?

  1. Create a batch pipeline in Cloud Data Fusion by using a Cloud Storage source and a BigQuery sink.
  2. Load the CSV file as a table in BigQuery, and use scheduled queries to run SQL transformation scripts.
  3. Load the CSV file as a table in BigQuery. Create a batch pipeline in Cloud Data Fusion by using a BigQuery source and sink.
  4. Create a batch pipeline in Dataflow by using the Cloud Storage CSV file to BigQuery batch template.
Show answer and explanation

Correct answer: A. Create a batch pipeline in Cloud Data Fusion by using a Cloud Storage source and a BigQuery sink.

Cloud Data Fusion is a managed ETL/ELT service designed for building scalable data pipelines with a visual interface, requiring minimal code. Using Cloud Storage as the source and BigQuery as the sink provides the most direct path. Cloud Data Fusion includes built-in plugins for data quality monitoring and transformation, making it ideal for quickly identifying data quality issues during the pipeline execution.

Why the other options are wrong

  • B. Scheduled queries alone lack the transformation and data quality insight capabilities; this approach treats BigQuery as both source and processing engine rather than a destination.
  • C. Loading first then creating a pipeline adds an unnecessary step and doesn't leverage Cloud Data Fusion's direct integration with Cloud Storage sources.
  • D. Dataflow requires more code and setup than Cloud Data Fusion; while it works, it's not the quickest solution for a standardized batch use case.

25 questions in. Want the rest? The first half of the pack, free by email · The full pack

Question 26

You are a data analyst at your organization. You have been given a BigQuery dataset that includes customer information. The dataset contains inconsistencies and errors, such as missing values, duplicates, and formatting issues. You need to effectively and quickly clean the data. What should you do?

  1. Develop a Dataflow pipeline to read the data from BigQuery, perform data quality rules and transformations, and write the cleaned data back to BigQuery.
  2. Use Cloud Data Fusion to create a data pipeline to read the data from BigQuery, perform data quality transformations, and write the clean data back to BigQuery.
  3. Export the data from BigQuery to CSV files. Resolve the errors using a spreadsheet editor, and re-import the cleaned data into BigQuery.
  4. Use BigQuery's built-in functions to perform data quality transformations.
Show answer and explanation

Correct answer: D. Use BigQuery's built-in functions to perform data quality transformations.

BigQuery's built-in functions provide a quick and effective way to clean data directly where it resides, without moving data or building complex pipelines. Functions like COALESCE, REGEXP_REPLACE, ARRAY_AGG, and EXCEPT can handle missing values, formatting issues, and duplicates efficiently at scale. This approach minimizes overhead and cost by processing data in place.

Why the other options are wrong

  • A. Dataflow pipelines add unnecessary complexity and overhead for data cleaning tasks that can be handled directly in BigQuery with SQL functions.
  • B. Cloud Data Fusion, while capable, adds operational overhead and is more suited for complex ETL workflows rather than straightforward data quality tasks.
  • C. Exporting to CSV files and using a spreadsheet editor is inefficient, not scalable for large datasets, and introduces manual error-prone processes.

That was 26 of 103.

The full Google Associate Data Practitioner pack has all 103 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.

Get the full pack

Not ready yet? Get the first 50 free as one PDF by email. Get the Half Pack →