Automating Conditional Model Retraining in BigQuery ML

PMLE By islever Editorial Last updated

Automating model retraining. Building models given the task considering cost, complexity, latency, and scalability.

Short answer

D Use BigQuery procedural language to write a query that runs the ML.EVALUATE statement, stores the MAE in a variable, and uses an IF statement to conditionally execute the CREATE OR REPLACE MODEL statement.

Question

You developed a demand forecasting model using BigQuery ML. New sales data is ingested into a BigQuery table daily. You want to implement an automated retraining strategy where the model is evaluated against the previous week’s data every Sunday, and the model is retrained if the mean absolute error (MAE) exceeds 500 units. You want to use native BigQuery capabilities to automate this workflow and minimize external dependencies. What should you do?
  1. Create an Agent Platform Pipelines workflow containing a BigQueryJobOp component that runs the ML.EVALUATE statement. Add a conditional step to check the MAE and conditionally execute a training step. Schedule the pipeline using Agent Platform Pipelines.
  2. Write a CREATE OR REPLACE MODEL statement with the OPTIONS clause set to warm_start = TRUE. Schedule the query to run every Sunday using a BigQuery scheduled query.
  3. Create a Cloud Run function triggered by a Cloud Scheduler job. Configure the function to use the BigQuery client library to run the evaluation query. If the MAE threshold is reached, issue a second API call to retrain the model.
  4. Use BigQuery procedural language to write a query that runs the ML.EVALUATE statement, stores the MAE in a variable, and uses an IF statement to conditionally execute the CREATE OR REPLACE MODEL statement. Schedule the script using a BigQuery scheduled query. Correct Answer

Key Takeaways

The main trap is assuming scheduled queries alone can handle conditional logic; BigQuery scripting (procedural SQL) is required to store evaluation metrics and use IF statements for decision-making.

Learn how to implement automated, conditional retraining for BigQuery ML models using native SQL scripting and scheduled queries without external orchestration.

Common mistake: Option B is incorrect because 'warm_start' only optimizes retraining speed; it does not provide the conditional evaluation logic needed to decide whether to retrain based on MAE thresholds.

Expert Analysis

The question requires an automated retraining strategy using native BigQuery capabilities to minimize external dependencies. The workflow must evaluate the model against new data and conditionally trigger retraining based on a specific metric (MAE > 500).

Option D is the correct solution because it leverages BigQuery Procedural Language (SQL scripting). This feature allows you to write a single script that executes ML.EVALUATE, stores the resulting MAE in a variable, uses an IF statement to check the threshold, and conditionally runs CREATE OR REPLACE MODEL. By scheduling this script via a BigQuery scheduled query, the entire logic remains within BigQuery, satisfying the constraint of minimizing external dependencies.

The most attractive but wrong answer is Option B. It suggests using warm_start = TRUE with a scheduled query. While warm_start is a valid option for efficient retraining, it always retrains the model when executed. It lacks the conditional logic required to evaluate performance first; thus, it cannot implement the "retrain if MAE exceeds 500" requirement. As noted in community feedback, options A and C introduce external services (Agent Platform Pipelines, Cloud Run), which violates the "minimize external dependencies" constraint.

Key distinction: Native conditional logic inside BigQuery vs. always-on scheduled training.

Exam Strategy

When a question emphasizes 'native' or 'minimal external dependencies,' prioritize solutions that stay within the core service's built-in features. For BigQuery ML, this means using SQL scripting (DECLARE, SET, IF) and scheduled queries rather than external tools like Cloud Functions or Vertex AI Pipelines unless explicitly required by complexity constraints.

Frequently Asked Questions

Can I use warm_start for conditional retraining?

No. Warm start optimizes training efficiency but always executes the training step. It cannot evaluate metrics or apply conditional logic to skip training.

What is BigQuery Procedural Language?

It is a feature allowing SQL scripts with variables, loops, and conditionals (IF/ELSE). It enables complex workflows like conditional model retraining directly in BigQuery.

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 • It makes the decision inside BigQuery. BigQuery procedural language (scripting) lets you write several SQL statements together with variables and IF conditions. In one script it evaluates the model with ML.EVALUATE, stores the MAE in a variable, and runs CREATE OR REPLACE MODEL only if the MAE is above 500. • A scheduled query runs the script every Sunday, so the whole workflow is automatic. • No external dependencies and low cost: nothing outside BigQuery is involved, and retraining only happens when it's needed. BigQuery procedural language (scripting) means writing several SQL statements together in one script, with the programming features SQL normally lacks: variables, IF conditions and loops. It's what answer D uses. Why the others are wrong • A: An Agent Platform Pipelines workflow can do it, but it's an extra external service with extra setup, and the question wants native BigQuery. • B: warm_start = TRUE retrains every Sunday from the existing model, whatever the error is. There's no condition on the MAE, so it doesn't meet "retrain only if MAE exceeds 500". • C: Cloud Scheduler plus a Cloud Run function (now a Cloud Run Function) adds external services and custom code.
  2. n2183712847 👍 1 Answered D
    Correct answer: D. BigQuery scripting can natively run ML.EVALUATE, store the MAE in a variable, use an IF condition to retrain only when the threshold is exceeded, and run automatically through a scheduled query; A and C add unnecessary external orchestration services, while B always retrains on schedule and does not evaluate the MAE or conditionally trigger retraining.

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.

Monitoring ML Model Performance with Precision and Recall Sampling

S-grade

Optimizing ML Model Recall for Compliance Risk

S-grade

How to Minimize Development Time for Weekly TensorFlow Retraining in Vertex AI?

S-grade

Building CLV Models with BigQuery ML in Vertex AI Workbench

S-grade

Practice All PMLE Questions

Access 65 questions with complete answers and detailed explanations.

View Full PMLE Practice Test →

← Back to PMLE Study Guide