ASSOCIATE-DATA-PRACTITIONER 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.

Using Transfer Appliances is the best solution for securely and efficiently moving over 500 TB of data

to Cloud Storage within a limited timeframe, especially with network bandwidth below 1 Gbps.

Transfer Appliances are physical devices provided by Google Cloud to securely transfer large amounts

of data. After copying the data to the appliances, they are shipped back to Google, where the data is

uploaded to Cloud Storage. This approach bypasses bandwidth limitations and ensures the data is

migrated quickly and securely.

Configuring a lifecycle management policy on each Cloud Storage bucket allows you to automatically

transition objects to lower-cost storage classes (such as Nearline, Coldline, or Archive) based on their

age or other criteria. Additionally, the policy can automate the removal of objects once they are no

longer needed, ensuring compliance with retention rules and optimizing storage costs. This approach

aligns well with well-defined data tiering and retention needs, providing cost efficiency and

automation.

Extract from Google Documentation: From "Object Lifecycle Management"

(https://cloud.google.com/storage/docs/lifecycle): "Use lifecycle management policies to

automatically transition objects to lower-cost storage classes (e.g., Nearline, Coldline) and delete

them based on age, optimizing costs according to your specific tiering and retention requirements."

Reference: Google Cloud Documentation - "Cloud Storage Lifecycle"

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

Using the BigQuery Python client library to query and preprocess data directly in BigQuery and then

leveraging BigQueryML to train the churn prediction model is the Google-recommended approach

for this scenario. BigQueryML allows you to build machine learning models directly within BigQuery

using SQL, eliminating the need to export data or manage additional infrastructure. This minimizes

overhead, scales effectively for a dataset as large as 50 PB, and simplifies the end-to-end process of

building and training the churn prediction model.

Online transactions: Storing the transactional data in BigQuery is ideal because BigQuery is a serverless data warehouse optimized for querying and analyzing structured data at scale. It supports SQL queries and is suitable for structured transactional data. Customer feedback: Storing customer feedback in Cloud Storage is appropriate as it allows you to store unstructured text files reliably and at a low cost. Cloud Storage also integrates well with data processing and ML tools for further analysis. Social media activity: Storing real-time social media activity in BigQuery is optimal because BigQuery supports streaming inserts, enabling real-time ingestion and analysis of data. This allows immediate analysis and integration into dashboards or ML pipelines.

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).

In BigQuery ML, when creating a logistic regression model to predict customer churn, the correct

query should:

Exclude the target label column (in this case, churned) from the feature columns, as it is used for

training and not as a feature input.

Rename the target label column to label, as BigQuery ML requires the target column to be named

label.

The chosen query satisfies these requirements:

SELECT * EXCEPT(churned), churned AS label: Excludes churned from features and renames it to

label.

The OPTIONS(model_type='logistic_reg') specifies that a logistic regression model is being trained.

This setup ensures the model is correctly trained using the features in the dataset while targeting the

churned column for predictions.

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).

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).

Copying the storm event dataset into your BigQuery project and using BigQuery Studio to query and

visualize the data in Looker Studio is the best approach. This solution allows you to create reusable

and automated workflows for generating risk reports. BigQuery handles the querying efficiently, and

Looker Studio provides powerful tools for creating and sharing dynamic charts and dashboards. This

setup ensures that reports can be easily re-run with updated data, minimizing manual effort and

providing a scalable, interactive solution for visualizing risk reports.