Minimize overhead in BigQuery ML preprocessing

You developed a BigQuery ML linear regressor model by using a training dataset stored in a BigQuery table. New data is added to the table every minute. You are using Cloud Scheduler and Vertex AI Pipelines to automate hourly model training, and use the model for direct inference. The feature preprocessing logic includes quantile bucketization and MinMax scaling on data received in the last hour. You want to minimize storage and computational overhead. What should you do?

Preprocess and stage the data in BigQuery prior to feeding it to the model during training and inference.
Use the TRANSFORM clause in the CREATE MODEL statement in the SQL query to calculate the required statistics. Source Reference Answer
Create a component in the Vertex AI Pipelines directed acyclic graph (DAG) to calculate the required statistics, and pass the statistics on to subsequent components.
Create SQL queries to calculate and store the required statistics in separate BigQuery tables that are referenced in the CREATE MODEL statement.

Community Votes

56%

22%

56% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

The question tests the ability to use the BigQuery ML TRANSFORM clause for native preprocessing, avoiding the trap of manual data staging or complex pipeline orchestration.

To minimize storage and computational overhead for BigQuery ML preprocessing, use the TRANSFORM clause in the CREATE MODEL statement. This native feature handles feature engineering like scaling and bucketization without manual data staging.

A common mistake is choosing Option A or D, assuming that preprocessed data or statistics must be manually stored, rather than leveraging BQML's automatic handling of transformations.

Community Discussion (7 comments)

Wuthuong1234 👍 1 Selected: B

B is the right solution. Keep in mind that it is asking for a solution where you "minimize storage and computational overhead". You end up storing more data with A and D. While in C you create more computational overhead. All solutions would work perfectly fine, but B matches best with the requirements in the question.

Ankit267 👍 1 Selected: B

BQ is sufficient

AB_C 👍 1 Selected: A

While the TRANSFORM clause can perform preprocessing, it's applied during model creation, not for inference. You'll need to recalculate statistics for each inference request, increasing computational overhead.

shubhachandra 👍 2 Selected: B

The TRANSFORM clause in BigQuery ML allows you to directly define feature preprocessing logic (such as quantile bucketization and MinMax scaling) within the SQL query itself. This approach minimizes storage and computational overhead because: No additional storage: Statistics for preprocessing are calculated on-the-fly during model training and inference, without needing to store preprocessed data or statistics separately. Integrated workflow: The preprocessing logic is tightly coupled with the model creation process, ensuring consistency between training and inference without external dependencies.

lunalongo 👍 1 Selected: B

B is the best option because: 1) TRANSFORM saves processing, storage and computation by performing feature preprocessing directly within the CREATE MODEL. 2) This method integrates preprocessing with model training, streamlining the entire process.

f084277 👍 2 Selected: C

Docs say BQ is not suitable for full-pass transformations such as Minmax.

carolctech 👍 1 Selected: A

A) Preprocessing and staging the data in BigQuery before training and inference, is the most efficient approach because: 1) You can use BQ’s optimized processing by preprocessing data before training 2) Avoiding redundant calculations, by directly using the preprocessed data (already bucketized and scaled) for training and inference; 3) Reducing storage by keeping only preprocessed data, not raw data and statistics separately.

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

The TRANSFORM clause in BigQuery ML allows you to define preprocessing logic, such as quantile bucketization and MinMax scaling, directly within the CREATE MODEL statement. BigQuery calculates the necessary statistics from the training data and stores the transformation logic with the model, applying it automatically during inference. This native integration eliminates the need for intermediate storage or custom compute resources, effectively minimizing both storage and computational overhead.

Why the Other Options Are Wrong

Option A involves preprocessing and staging data, which creates duplicate data storage and adds ETL steps. Option D requires storing statistics in separate tables, increasing storage and management overhead. Option C suggests building a custom Vertex AI Pipeline component to calculate statistics, which introduces unnecessary complexity and computational costs compared to the optimized, built-in SQL functionality of the TRANSFORM clause.

Community Comment Notes

Community comments highlight that Option B is the most efficient because it avoids the storage penalties associated with Options A and D and the computational complexity of Option C. Comment [1] emphasizes that the TRANSFORM clause calculates statistics on-the-fly without extra storage. While Comment [2] raised concerns about full-pass transformations, the consensus confirms that BQML handles these operations effectively within the TRANSFORM clause.

Official Reference

Exam Strategy

For BigQuery ML questions focused on efficiency and overhead, always prioritize native SQL features like the TRANSFORM clause over manual data staging or external pipeline orchestration.

Related Analysis

Practice All PMLE Questions

Access 65 questions with complete answers and detailed explanations.

View Full PMLE Practice Test →

← Back to PMLE Study Guide