Google Associate Data Practitioner Exam Questions

Using BigQuery to batch load the data and perform cleaning and analysis with SQL is the best approach for this scenario. BigQuery provides powerful SQL capabilities to handle missing values, enforce correct data types, and remove duplicates efficiently. This method simplifies the pipeline by leveraging BigQuery's built-in processing power for both cleaning and analysis, reducing the need for additional tools or services and minimizing complexity.

Comprehensive and Detailed in Depth

Why B is correct:AutoML Natural Language is designed for text classification tasks, including sentiment analysis, and can handle diverse language patterns without extensive preprocessing.

AutoML can train a custom model with minimal coding.

Why other options are incorrect:A: Unnecessary extra preprocessing. AutoML can handle the raw data.

C: Dataproc and Spark are overkill for this task. AutoML is more efficient and easier to use.

D: Developing a custom TensorFlow model requires significant expertise and time, which is not efficient for this scenario.

AutoML Natural Language: https://cloud.google.com/natural-language/automl/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:

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:Dataflow is a fully managed service for transforming and enriching data in both batch and streaming modes.

Dataflow is googles recomended way to transform large datasets.

It is designed for parallel processing, making it suitable for large datasets.

Why other options are incorrect:A: Manual downloading and scripting is not scalable or efficient.

B: Cloud Run functions are for stateless applications, not large data transformations.

D: While Cloud Data fusion could work, Dataflow is more optimized for large scale data transformation.

Dataflow: https://cloud.google.com/dataflow/docs

Query successful

Comprehensive and Detailed In-Depth

Streaming data requiring enrichment and cleaning before ML training suggests an ETL (Extract, Transform, Load) approach, with a focus on real-time processing and a data warehouse for ML.

Option A: ETL with Dataflow (streaming transformations) and BigQuery (storage/ML training) is Google's recommended pattern for streaming pipelines. Dataflow handles enrichment/cleaning, and BigQuery supports ML model training (BigQuery ML).

Option B: ETL with Cloud Data Fusion to Cloud Storage is batch-oriented and lacks streaming focus. Cloud Storage isn't ideal for ML training directly.

Option C: ELT (load then transform) with Cloud Storage to Bigtable is misaligned---Bigtable is for NoSQL, not ML training or post-load transformation.

Option D: ELT with Cloud SQL to Analytics Hub is for relational data and data sharing, not streaming or ML. Reference: Google Cloud Documentation - 'Dataflow: ETL Patterns' (https://cloud.google.com/dataflow/docs/guides), 'BigQuery ML' (https://cloud.google.com/bigquery-ml).

Option D: ELT with Cloud SQL to Analytics Hub is for relational data and data sharing, not streaming or ML. Reference: Google Cloud Documentation - 'Dataflow: ETL Patterns' (https://cloud.google.com/dataflow/docs/guides), 'BigQuery ML' (https://cloud.google.com/bigquery-ml).