BigQuery SQL for Data Transformation and Feature Engineering

PMLE By islever Editorial Last updated

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

You are in the exploratory phase of development of a demand forecasting model. Several terabytes of historical sales and inventory records are stored in a BigQuery table. You need to create a new, clean table to perform preliminary analyses on metrics, such as moving averages of sales figures and weekly aggregates, without additional data movement. You want to use the simplest approach and minimize overhead. What should you do?
  1. Create a PreprocessingOp component in the Agent Platform Pipelines workflow to compute the moving averages and extract time-based features and write the processed data to a new destination table in BigQuery.
  2. Read the data from BigQuery into a Dask DataFrame using Agent Platform Workbench. Perform exploratory data analysis (EDA) and feature engineering transformations, and write the processed data back to a new destination table in BigQuery.
  3. Write a SQL query in the BigQuery console. Use the ML.TRANSFORM function in BigQuery ML to compute the moving averages and extract time-based features. Write the processed data to a new destination table in BigQuery.
  4. Write a SQL query in the BigQuery console. Use standard SQL functions like CAST, EXTRACT, and window functions with OVER() clauses to perform the transformations and calculations. Materialize the results into a new destination table in BigQuery. Correct Answer

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?

Yes, BigQuery supports window functions like ROW_NUMBER(), RANK(), and AVG() OVER() which allow you to calculate moving averages and other rolling metrics directly in SQL.

Why not use Agent Platform Workbench for EDA?

While Workbench is excellent for EDA, it often requires pulling data into memory or distributed frames. If the data is already in BigQuery and needs to stay there, native SQL is faster and cheaper.

More PMLE FAQ →

Community Votes

D
100%

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

  1. taylz876 👍 1 Answered D
    Why 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.
  2. n2183712847 👍 1 Answered D
    Correct 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.

Log in to comment, report an error, or add a note about this question.

Practice All PMLE Questions

Access 65 questions with complete answers and detailed explanations.

View Full PMLE Practice Test →

← Back to PMLE Study Guide