2.1 Choosing, Training & Predicting with BigQuery ML Models

BigQuery ML (BQML) lets you create, evaluate, and use ML models with GoogleSQL. The data stays in BigQuery, SQL-skilled analysts can build models, and BigQuery's engine handles the scaling. On the exam, BQML is often the right low-code answer when the data is already in BigQuery and the team knows SQL better than Python.

Match the Business Problem to a Model Type

BQML can also import trained models (TENSORFLOW, TENSORFLOW_LITE, ONNX, XGBOOST) to score them in SQL. It can also create remote models over Gemini and other Agent Platform models for generative tasks (Section 2.3).

Choosing among the supervised options

• Linear/logistic regression: fastest to train, easy to interpret through ML.WEIGHTS, and a good baseline.
• Boosted trees and random forests (XGBoost-based): usually the strongest choice for tabular data with non-linear interactions. They handle categorical features through label encoding.
• DNN and Wide & Deep: for large datasets with complex interactions. They are slower and harder to explain.
• AutoML classifier/regressor: BQML sends training to Agent Platform AutoML, which handles feature engineering and tuning. You control the BUDGET_HOURS value (1.0-72.0, default 1.0) and an OPTIMIZATION_OBJECTIVE such as MAXIMIZE_AU_ROC.

Anatomy of CREATE MODEL

CREATE OR REPLACE MODEL `retail.churn_bt`
OPTIONS (
  model_type = 'BOOSTED_TREE_CLASSIFIER',
  input_label_cols = ['churned'],
  auto_class_weights = TRUE,
  data_split_method = 'AUTO_SPLIT',
  enable_global_explain = TRUE,
  model_registry = 'VERTEX_AI'
) AS
SELECT tenure_months, plan, monthly_spend, support_tickets, churned
FROM `retail.customer_features`;

• input_label_cols names the label.
• auto_class_weights = TRUE reweights classes by inverse frequency, which helps with imbalanced labels.
• DATA_SPLIT_METHOD defaults to AUTO_SPLIT. Below 500 rows, all rows go to training. From 500 to 50,000 rows, 20% goes to evaluation. Above 50,000 rows, 10,000 rows are held out. With hyperparameter tuning, the split is 80/10/10 for training, evaluation, and test. RANDOM, CUSTOM (a BOOL column), SEQ (ordered column, for time-aware splits), and NO_SPLIT are also available.
• Hyperparameter tuning uses NUM_TRIALS, HPARAM_RANGE/HPARAM_CANDIDATES, and HPARAM_TUNING_ALGORITHM (VIZIER_DEFAULT, RANDOM_SEARCH, or GRID_SEARCH). You can check trials with ML.TRIAL_INFO.

Forecasting Choices

ARIMA_PLUS trains one model per time series. Setting TIME_SERIES_ID_COL to a store or product ID fits thousands of series in a single statement. ML.EXPLAIN_FORECAST breaks forecasts into components, and ML.ARIMA_EVALUATE compares candidate models. TimesFM needs no training and offers little customization or explainability, so it fits quick baselines rather than cases that require explanations.

Evaluation and Prediction Functions

SELECT customer_id, predicted_churned, predicted_churned_probs
FROM ML.PREDICT(
  MODEL `retail.churn_bt`,
  (SELECT * FROM `retail.active_customers`),
  STRUCT(0.35 AS threshold));

Lowering the threshold to 0.35 catches more likely churners (higher recall) and flags more false positives (lower precision). Choose it from ML.ROC_CURVE output and the cost of each error type.

Getting Predictions to Consumers

1. Batch in BigQuery: schedule ML.PREDICT as a scheduled query or pipeline step and write results to a table for dashboards or activation.
2. Online through Agent Platform: register the model (MODEL_REGISTRY = 'VERTEX_AI', optionally with VERTEX_AI_MODEL_ID and version aliases). Then deploy it from Model Registry to an endpoint. You don't need to export it or build a serving container.
3. Export: EXPORT MODEL writes to Cloud Storage. Most model types export as TensorFlow SavedModel, and boosted tree and random forest models export as an XGBoost Booster. Automatic preprocessing is saved with the model, so clients send raw features that match what ML.PREDICT expects.

Worked Scenario: Picking the Right Low-Code Path

A subscription company keeps 40 million customer-month rows in BigQuery. Marketing wants weekly churn scores for campaign targeting, and the analytics team writes SQL but not Python. Walk through the decision:

1. Problem type: a yes/no label (churned next month), so this is binary classification.
2. Where the data lives: BigQuery, which strongly favors BQML because nothing has to be exported.
3. Model choice: start with LOGISTIC_REG as a baseline, then train BOOSTED_TREE_CLASSIFIER and compare ML.EVALUATE results on the same holdout. If there's more time than feature-engineering skill, try AUTOML_CLASSIFIER with a budget of a few hours.
4. Consumption: weekly scores go into a table, so a scheduled ML.PREDICT query is enough. There's no need for an online endpoint.
5. Imbalance: churners are rare, so use AUTO_CLASS_WEIGHTS = TRUE and pick the threshold from ML.ROC_CURVE.

If the same company later needs a churn score during a live support chat, the need changes to online inference. Register the model in Model Registry and deploy it to an endpoint.

Exam Traps

• Choosing Python custom training when the data is already in BigQuery and a standard classifier or forecast would do. BQML avoids moving data and adds almost no operational overhead.
• Using LINEAR_REG for a yes/no label. Use a classifier.
• Using a random split for time series. Use SEQ or a time-based CUSTOM split so future rows don't leak into training.
• Expecting BQML to host a low-latency REST endpoint by itself. Register the model or export it for online serving.