BigQuery SQL for Data Transformation and Feature Engineering
Short answer
D Write a SQL query using standard SQL functions and window functions to transform data and materialize results in a new BigQuery table, avoiding data movement.
Question
Key Takeaways
The main trap is over-engineering with pipelines or external frameworks when BigQuery's built-in SQL capabilities can handle complex aggregations efficiently without data movement.
Learn how to perform preliminary data analysis and feature engineering in BigQuery using standard SQL window functions. This page explains why native SQL is the optimal solution for minimizing overhead.
Common mistake: Choosing Option B (Dask/Workbench) because it seems like a flexible EDA tool, but it fails the 'no data movement' constraint and adds unnecessary latency.
Expert Analysis
The scenario specifies that data resides in BigQuery and requires transformations like moving averages and weekly aggregates. The critical constraints are to avoid additional data movement, use the simplest approach, and minimize overhead.
Option D is correct because BigQuery SQL natively supports window functions (e.g., OVER) and date extraction functions (EXTRACT). Writing a single SQL query to compute these metrics and materializing the results into a new table leverages BigQuery's serverless architecture directly where the data lives. This eliminates the need for external tools or data transfer, satisfying all constraints.
Option A involves creating a preprocessing component in an Agent Platform Pipeline. While powerful, this adds orchestration complexity and infrastructure overhead for a task that can be solved with a simple SQL statement. It violates the 'simplest approach' requirement.
Option B suggests reading data into a Dask DataFrame via Workbench. This explicitly moves data out of BigQuery into a distributed computing environment, violating the 'without additional data movement' constraint. It also introduces significant latency and operational overhead compared to native SQL.
Option C mentions using ML.TRANSFORM. This function is not a standard BigQuery SQL function for general feature engineering; it is specific to certain ML model training contexts within BigQuery ML but does not replace standard SQL window functions for custom aggregations like moving averages. Standard SQL is the robust, documented method for this.
Comments confirm that Option D is the right choice because it stays within BigQuery, uses standard SQL capabilities, and avoids data movement.
Official Reference
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions
This link opens the official vendor documentation in a new tab.
Exam Strategy
When a question emphasizes 'no data movement' and 'simplest approach,' look for solutions that leverage the native capabilities of the storage service itself. Avoid options that introduce external compute engines or complex orchestration layers unless the task inherently requires them (e.g., non-SQL compatible algorithms). Always prioritize serverless, in-database SQL operations for aggregation and transformation tasks.
Frequently Asked Questions
Can BigQuery compute moving averages?
Why not use Agent Platform Workbench for EDA?
Community Votes
100% of anonymous learners picked answer D. Votes are pick records left by other test-takers — they are not the verified answer.
Discussion 2 contributions
Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.
From public community threads 2
-
taylz876 👍 1 Answered DWhy D is right • The data is in BigQuery, so the simplest approach stays there. Moving averages and weekly aggregates are standard SQL window functions and date functions, so one query computes them, and the results are saved into a new table. There's no data movement and no extra tools. Why the others are wrong • A: A pipeline component adds orchestration for what is a single query. • B: Reading several terabytes into Dask from a notebook moves the data out of BigQuery. • C: ML.TRANSFORM applies the preprocessing that is saved inside a BigQuery ML model. It isn't a general tool for moving averages.
-
n2183712847 👍 1 Answered DCorrect answer: D. BigQuery SQL can perform the required aggregations, window functions, and feature transformations directly where the data resides, avoiding data movement and minimizing development and infrastructure overhead.
Reader comments & corrections
No reader corrections yet — spotted an error or have a note? Share it below.
Related Analysis
Practice All PMLE Questions
Access 65 questions with complete answers and detailed explanations.
View Full PMLE Practice Test →