BigQuery ML for practitioners

On Friday, October 23, Lena brings a question that SQL alone cannot answer. Web orders are returned far more often than store purchases, and each return costs Birchline shipping both ways plus the time to inspect and restock. She asks whether the team could flag orders that are likely to come back, so customer service can send a sizing guide or a short call before the parcel ships. Ravi sees a good first machine learning project: a clear question, a label in historical data, and a decision that a prediction could improve.

What BigQuery ML is

BigQuery ML lets you create, train, evaluate and use machine learning models with SQL, inside BigQuery. The training data never leaves the warehouse, there is no separate infrastructure to manage, and the results can be queried like any table. For an analyst who already knows SQL, it removes most of the barriers to trying a model.

BigQuery ML offers several kinds of model, and the exam expects you to match a use case to a type.

Some model types train inside BigQuery; others, such as boosted trees, deep neural networks and AutoML, are trained by Google's managed machine learning platform, Vertex AI, and appear in BigQuery as models you can use with the same SQL. Google's current documentation calls that platform the Agent Platform in places; the BigQuery SQL is the same either way. AutoML tries many model designs automatically and is a good choice when the team needs the best accuracy it can get and accepts a longer, more expensive training run.

Planning a machine learning project

Ravi insists on writing the plan before any SQL. A model is only useful if the prediction leads to a decision, and if the data at prediction time matches the data used in training.

Two items in the plan prevent common failures. Label timing: orders from the last month cannot be labelled yet, because their 30 days have not passed, so including them would teach the model that recent orders are rarely returned. Leakage: a feature that is only known after the outcome, such as the return reason, makes a model look brilliant in testing and useless in practice. The return reason is perfectly predictive of a return, and it is never available at checkout.

Training a model with SQL

A model is created with CREATE MODEL, and its training data is simply a SELECT statement. The column named in INPUT_LABEL_COLS is the label; every other column is a feature.

CREATE OR REPLACE MODEL `bl-data-prod.analytics.return_risk`
OPTIONS (
  model_type = "LOGISTIC_REG",
  input_label_cols = ["returned"],
  data_split_method = "RANDOM",
  data_split_eval_fraction = 0.2,
  auto_class_weights = TRUE
) AS
SELECT
  category, items_in_basket, sizes_in_basket, is_first_order,
  order_value, delivery_region, customer_prior_return_rate,
  returned
FROM `bl-data-prod.analytics.web_orders_training`
WHERE order_date BETWEEN "2024-01-01" AND "2026-06-30";

The options say what kind of model to train and how to hold back data for evaluation. Here, 20 rows in every 100 are set aside at random and not used for training, so the evaluation measures how the model does on orders it has never seen. For time-based data, a split by date, training on older orders and evaluating on newer ones, often gives a more honest picture, and BigQuery ML supports that with a SEQ split on a date column. auto_class_weights helps when one outcome is much rarer than the other: only about 15 in every 100 web orders are returned, and without weighting a model can score well simply by predicting "kept" for everything.

Training cost follows the bytes processed by the training query, with BigQuery ML's own rates for model creation, so filtering and selecting columns matter just as much as in any other query.

Evaluating a classification model

ML.EVALUATE reports how well the model does on the held-out rows. For a classifier, it returns precision, recall, accuracy, F1 score, log loss and ROC AUC. The first four come from the confusion matrix, which counts the four ways a yes-or-no prediction can turn out.

Precision answers: of the orders we flagged, how many were really returned? Recall answers: of the orders that were returned, how many did we flag? Accuracy is the share of all predictions that were right, which is misleading when one outcome is rare: a model that never flags anything would be right on 1,700 of these 2,000 orders, an accuracy of 0.85, while catching no returns at all. F1 combines precision and recall into one number. ROC AUC summarizes how well the model ranks returned orders above kept ones across all thresholds, from 0.5 for guessing to 1.0 for perfect.

A classifier produces a probability, and a threshold turns it into a yes or no. BigQuery ML uses 0.5 by default, but the right threshold depends on costs. A sizing email costs almost nothing, so Lena would rather flag more orders and catch more returns, accepting more false alarms. The calculation below shows how the choice of threshold changes the counts for the 2,000 test orders.

def metrics(tp, fn, fp, tn):
    precision = tp / (tp + fp)
    recall = tp / (tp + fn)
    accuracy = (tp + tn) / (tp + fn + fp + tn)
    f1 = 2 * precision * recall / (precision + recall)
    return precision, recall, accuracy, f1

# Counts on the same 2,000 held-out orders at three thresholds (300 were returned)
by_threshold = {
    0.7: (120, 180, 40, 1660),
    0.5: (180, 120, 90, 1610),
    0.3: (240, 60, 260, 1440),
}
print("threshold  flagged  precision  recall  accuracy  f1")
for t, (tp, fn, fp, tn) in by_threshold.items():
    p, r, a, f = metrics(tp, fn, fp, tn)
    print(f"{t:9}  {tp + fp:7}  {p:9.2f}  {r:6.2f}  {a:8.3f}  {f:.2f}")

threshold  flagged  precision  recall  accuracy  f1
      0.7      160       0.75    0.40     0.890  0.52
      0.5      270       0.67    0.60     0.895  0.63
      0.3      500       0.48    0.80     0.840  0.60

At 0.3, the model flags 500 orders and catches 240 of the 300 returns, a recall of 0.80, but less than half the flagged orders are really returned. For a free email that is a good trade; for a phone call that takes ten minutes of an agent's time, the 0.5 or 0.7 threshold may be better. Choosing the threshold is a business decision, and the analyst's job is to lay out the trade-off clearly.

Making predictions

ML.PREDICT applies a trained model to new rows and returns the original columns plus the prediction and the probability of each class. For regression models it returns the predicted number. ML.EXPLAIN_PREDICT also returns the features that contributed most to each prediction, which helps customer service understand why an order was flagged.

SELECT order_id, predicted_returned, prob.prob AS return_probability
FROM ML.PREDICT(
  MODEL `bl-data-prod.analytics.return_risk`,
  (SELECT * FROM `bl-data-prod.analytics.web_orders_today`),
  STRUCT(0.3 AS threshold)),
  UNNEST(predicted_returned_probs) AS prob
WHERE prob.label = TRUE
ORDER BY return_probability DESC;

Forecasting

Lena's second request is a sales forecast for the holiday season. ARIMA_PLUS models a time series, handling trend, weekly and yearly patterns and holidays, and can train one model per store in a single statement by naming a time series ID column. ML.FORECAST then returns predicted values with a prediction interval for each future date. BigQuery also offers AI.FORECAST, which uses Google's pretrained TimesFM time series model and needs no training step at all; it is a quick baseline to compare against a trained model.

CREATE OR REPLACE MODEL `bl-data-prod.analytics.store_sales_forecast`
OPTIONS (model_type = "ARIMA_PLUS", time_series_timestamp_col = "sale_date",
         time_series_data_col = "net_sales", time_series_id_col = "store_id",
         holiday_region = "CA")
AS SELECT sale_date, store_id, net_sales
   FROM `bl-data-prod.curated.v_store_daily`
   WHERE sale_date BETWEEN "2023-01-01" AND "2026-10-22";

SELECT * FROM ML.FORECAST(MODEL `bl-data-prod.analytics.store_sales_forecast`,
                          STRUCT(70 AS horizon, 0.9 AS confidence_level));

Generative AI models from BigQuery

BigQuery can also call Google's pretrained large language models. After an administrator creates a connection to Vertex AI and a remote model that points at a Gemini model, SQL functions such as ML.GENERATE_TEXT, and the newer AI.GENERATE family, send each row's text to the model and return the response as a column. Lena's team plans to use this to group the free-text return comments into themes. The usual cautions apply: the output should be checked against a sample read by a person, and the data sent must be allowed to leave the table, which rules out personal details unless policy says otherwise.

Managing models

Models are objects in a dataset, with their own permissions, labels and descriptions. They can also be registered in the Vertex AI Model Registry, which keeps versions, records evaluation results and allows a model to be deployed to an endpoint for online predictions outside BigQuery. Ravi registers the return-risk model with a description of its training window, features and evaluation, so that anyone retraining it next year can compare like with like. Models also drift: if customers or products change, last year's patterns stop holding, so the team plans to re-evaluate the model each quarter on recent orders.