10 free Google Associate Data Practitioner practice questions with the correct answer and a full explanation for each, taken from the CertStash pack of 103 questions. Work through them, then open each answer to check your reasoning.
Get all 103 questions (US$39) · Download these 10 as a PDF
Question 1
Your retail company wants to predict customer churn using historical purchase data stored in BigQuery. The dataset includes customer demographics, purchase history, and a label indicating whether the customer churned or not. You want to build a machine learning model to identify customers at risk of churning. You need to create and train a logistic regression model for predicting customer churn, using the customer_data table with the churned column as the target label.
Which BigQuery ML query should you use?
Show answer and explanation
Correct answer: B. CREATE OR REPLACE MODEL
OPTIONS(model_type='logistic_reg') AS SELECT * EXCEPT(churned), churned AS label FROM customer_data; BigQuery ML requires the target label column to be explicitly aliased as 'label' in the SELECT statement. Option B correctly uses SELECT * EXCEPT(churned) to include all features except the target column, then explicitly aliases churned AS label, which tells the model which column contains the target variable for the logistic regression. This is the proper syntax for creating a supervised learning model in BigQuery ML.
Why the other options are wrong
- A. Selects all columns including churned without aliasing it as label, so BigQuery ML cannot identify the target variable.
- C. Excludes the churned column entirely but never defines it as a label, so there is no target variable for the model to predict.
- D. Selects only the churned column and no features, leaving no predictor variables for the model to learn from.
Question 2
Your company has several retail locations. Your company tracks the total number of sales made at each location each day. You want to use SQL to calculate the weekly moving average of sales by location to identify trends for each store.
Which query should you use?
Show answer and explanation
Correct answer: C. SELECT store_id, date, total_sales, AVG(total_sales)
OVER (PARTITION BY store_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as rolling_avg FROM store_sales_daily To calculate a weekly moving average of sales by location, the query must partition by store_id to separate data for each store, and order by date to establish the temporal sequence needed for the rolling window. The ROWS BETWEEN 6 PRECEDING AND CURRENT ROW frame specification calculates the average across the current row plus 6 preceding rows (7 days total for a weekly average). Option C correctly uses PARTITION BY store_id and ORDER BY date with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW, which properly computes the rolling average trend for each store over time.
Why the other options are wrong
- A. Uses ORDER BY total_sales instead of ORDER BY date, which breaks the temporal ordering needed for a moving average and uses RANGE instead of ROWS.
- B. Partitions by date instead of store_id, which groups by date rather than by individual store locations, defeating the purpose of tracking trends per store.
- D. Partitions by total_sales instead of store_id, which groups by sales amount rather than by store, and uses RANGE which is inappropriate for row-based moving averages.
Question 3
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?
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 designed specifically for this use case. It processes streaming JSON messages from Pub/Sub topics, applies transformations via User-Defined Functions (UDFs) written in SQL, and writes results directly to BigQuery. This requires minimal code, maintains near real-time processing, and is fully managed by Google Cloud. The UDF capability allows you to capitalize the serial number field with simple SQL logic without building a custom pipeline.
Why the other options are wrong
- A. Direct Pub/Sub to BigQuery subscriptions do not support transformations; running transformation queries every five minutes introduces latency and is not near real-time.
- B. Using Cloud Storage as an intermediary adds unnecessary latency and complexity; Cloud Run triggers add operational overhead compared to a managed template.
- D. Writing a custom Cloud Run service requires more code and operational management than using a managed Dataflow template designed for this pattern.
Question 4
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?
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 integration service optimized for building scalable batch and ETL pipelines. Using Cloud Storage as the source and BigQuery as the sink allows direct transformation of the CSV file while leveraging Cloud Data Fusion's built-in data quality and transformation capabilities. This approach provides quick development time, scalability, and integrated data quality insights without requiring manual SQL scripting or intermediate loading steps.
Why the other options are wrong
- B. Loading the file first and then using scheduled queries requires an extra step and doesn't leverage data quality tools during transformation.
- C. Pre-loading the data into BigQuery before creating the pipeline is inefficient and defeats the purpose of using Cloud Data Fusion for direct source-to-sink transformation.
- D. Dataflow batch templates are powerful but require more coding than Cloud Data Fusion's visual development environment for a straightforward CSV-to-BigQuery transformation.
Question 5
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?
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, managed mechanism for automatically deleting objects based on age criteria. Configuring a lifecycle rule to delete objects older than seven days eliminates manual processes, requires no code, incurs no additional costs, and runs automatically without operational overhead. This is the most efficient and cost-effective solution for this straightforward retention requirement.
Why the other options are wrong
- A. Cloud Scheduler with Cloud Run functions is unnecessarily complex and incurs compute costs when a native lifecycle rule handles the requirement automatically.
- C. Dataflow is overkill for simple file deletion based on age and introduces unnecessary complexity and cost compared to lifecycle rules.
- D. Creating a custom Cloud Run function requires development and operational management when lifecycle rules provide the same functionality automatically.
Question 6
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?
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, standardized solution purpose-built for data transformation and de-identification workflows. It provides a visual interface to define transformation logic, includes built-in connectors for both on-premises and cloud data sources, and offers pre-built patterns for handling sensitive data. As a managed service, it requires minimal custom development and is specifically designed to enforce consistent de-identification standards across all data feeds.
Why the other options are wrong
- A. Cloud Run functions require more custom coding for de-identification logic and lack the built-in data governance and standardization features of a managed ETL platform.
- C. Loading data into BigQuery first violates security best practices by storing und-dentified PII in the cloud before transformation.
- D. Apache Beam requires writing significant custom code for de-identification logic; Cloud Data Fusion provides a standardized, managed alternative that requires less development.
Question 7
You manage a large amount of data in Cloud Storage, including raw data, processed data, and backups. Your organization is subject to strict compliance regulations that mandate data immutability for specific data types. You want to use an efficient process to reduce storage costs while ensuring that your storage strategy meets retention requirements.
What should you do?
Show answer and explanation
Correct answer: D. Use object holds to enforce immutability for specific objects, and configure lifecycle management rules to transition objects to appropriate storage classes based on age and access patterns.
Object holds enforce immutability at the object level, meeting compliance requirements for specific data types that must not be modified or deleted. Lifecycle management rules optimize storage costs by transitioning objects to cheaper storage classes based on age and access patterns. Together, these features provide both compliance enforcement and cost optimization. Object holds preserve immutability while lifecycle rules handle retention and cost efficiency.
Why the other options are wrong
- A. Object versioning maintains historical versions but does not prevent deletion or modification of current objects; it does not provide true immutability enforcement.
- B. Cloud KMS encryption provides security but does not enforce immutability; encryption and immutability are separate concerns and encryption alone cannot meet immutability compliance requirements.
- C. A Cloud Run function checking metadata and moving objects is a manual, cod-ased approach when lifecycle management rules automate this process more efficiently and reliably.
Question 8
You work for an ecommerce company that has a BigQuery dataset that contains customer purchase history, demographics, and website interactions. You need to build a machine learning (ML) model to predict which customers are most likely to make a purchase in the next month.
You have limited engineering resources and need to minimize the ML expertise required for the solution.
What should you do?
Show answer and explanation
Correct answer: A. Use BigQuery ML to create a logistic regression model for purchase prediction.
BigQuery ML enables building ML models using SQL queries without requiring significant ML expertise or engineering resources. A logistic regression model is the appropriate algorithm for binary purchase prediction classification. Since the company already has data in BigQuery, this solution requires no data export, minimal code, and leverages existing infrastructure. BigQuery ML abstracts away underlying ML complexity and integrates seamlessly with the existing dataset.
Why the other options are wrong
- B. Vertex AI Workbench requires more ML expertise and involves notebook-based development, which is more resource-intensive than BigQuery ML's SQL-based approach.
- C. Colab Enterprise also requires custom model development and more ML expertise than BigQuery ML's simplified interface.
- D. Exporting data to Cloud Storage and using AutoML Tables adds unnecessary complexity and data movement when BigQuery ML can train directly on the existing dataset.
Question 9
You are designing a pipeline to process data files that arrive in Cloud Storage by 3:00 am each day. Data processing is performed in stages, where the output of one stage becomes the input of the next. Each stage takes a long time to run. Occasionally a stage fails, and you have to address the problem. You need to ensure that the final output is generated as quickly as possible.
What should you do?
Show answer and explanation
Correct answer: D. Design the processing as a directed acyclic graph (DAG) in Cloud Composer. Clear the state of the failed task after correcting any stage output data errors.
(DAG) in Cloud Composer. Clear the state of the failed task after correcting any stage output data errors. Cloud Composer, based on Apache Airflow, models workflows as directed acyclic graphs (DAGs) and provides built-in task-level state management. When a stage fails, you can correct the output data and clear only the failed task's state, allowing the workflow to resume from that point without reprocessing earlier stages. This enables the fastest recovery time and minimizes reprocessing of long-running stages. Composer is designed specifically for complex multi-stage workflows with failure handling.
Why the other options are wrong
- A. Dataproc with Spark requires waiting for user input, which delays processing and is not an automated recovery mechanism; manually re-running requires reprocessing all stages.
- B. Dataflow requires restarting the entire pipeline, causing all stages to reprocess, which violates the requirement to generate output as quickly as possible after a failure.
- C. Cloud Workflow input parameters allow jumping to a stage but lack the native task state management and scheduling capabilities that Composer provides for multi-stage pipeline orchestration.
Question 10
Another team in your organization is requesting access to a BigQuery dataset. You need to share the dataset with the team while minimizing the risk of unauthorized copying of data. You also want to create a reusable framework in case you need to share this data with other teams in the future.
What should you do?
Show answer and explanation
Correct answer: B. Create a private exchange using Analytics Hub with data egress restriction, and grant access to the team members.
Analytics Hub with data egress restrictions provides a standardized, reusable framework for sharing datasets while minimizing unauthorized data copying. Data egress restrictions prevent users from exporting or copying shared data, maintaining security while granting query access. A private exchange creates a dedicated, controlled sharing environment that can be reused for multiple teams. This is a managed, enterprise-grade solution designed specifically for secure data sharing.
Why the other options are wrong
- A. Authorized views do not prevent copying once data is queried; they only control which rows and columns are visible, not data egress.
- C. Domain restricted sharing does not prevent copying; it only limits access to members of a specific domain, and BigQuery Data Viewer role grants data access without preventing export.
- D. Exporting data to Cloud Storage makes a copy vulnerable to unauthorized access and does not prevent copying; it also doesn't provide a reusable framework for future sharing needs.
That was 10 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.