BigQuery ML, in depth: model types and where they train, data splits, TRANSFORM, a churn model end to end, forecasting, remote models and operations

How BigQuery ML works

There are four kinds of model behind the same syntax, and they differ in where the work happens and how it is billed.

• Built-in models train inside BigQuery: linear and logistic regression, k-means, matrix factorization, PCA, the ARIMA_PLUS and ARIMA_PLUS_XREG time-series models, and contribution analysis.
• Externally trained models use the same SQL but train on Vertex AI: boosted trees (XGBoost), random forests, DNNs, wide-and-deep models, autoencoders and AutoML.
• Imported models bring trained ONNX, TensorFlow, TensorFlow Lite or XGBoost artefacts in for inference.
• Remote models point at an endpoint, such as a Gemini model, through a BigQuery connection; inference calls leave BigQuery.

Google's introduction page states that training is charged for the compute it uses, with charges depending on model type; that queries against models use BigQuery compute pricing; that CREATE MODEL for imported and remote models processes no bytes; and that remote models also incur Vertex AI charges. It also states that BigQuery ML "isn't available in the Standard edition", so check your reservation's edition before planning on it. Training cost scales with the bytes your training query reads, so the partitioning and clustering advice in BigQuery partitioning and clustering applies to feature tables as much as to dashboards.

How BigQuery ML works

There are four kinds of model behind the same syntax, and they differ in where the work happens and how it is billed.

• Built-in models train inside BigQuery: linear and logistic regression, k-means, matrix factorization, PCA, the ARIMA_PLUS and ARIMA_PLUS_XREG time-series models, and contribution analysis.
• Externally trained models use the same SQL but train on Vertex AI: boosted trees (XGBoost), random forests, DNNs, wide-and-deep models, autoencoders and AutoML.
• Imported models bring trained ONNX, TensorFlow, TensorFlow Lite or XGBoost artefacts in for inference.
• Remote models point at an endpoint, such as a Gemini model, through a BigQuery connection; inference calls leave BigQuery.

Google's introduction page states that training is charged for the compute it uses, with charges depending on model type; that queries against models use BigQuery compute pricing; that CREATE MODEL for imported and remote models processes no bytes; and that remote models also incur Vertex AI charges. It also states that BigQuery ML "isn't available in the Standard edition", so check your reservation's edition before planning on it. Training cost scales with the bytes your training query reads, so the partitioning and clustering advice in BigQuery partitioning and clustering applies to feature tables as much as to dashboards.

Data splits decide whether evaluation means anything

Every supervised model needs data it has not trained on. The DATA_SPLIT_METHOD option decides how BigQuery ML carves it out, and the default is easy to misuse. The boosted-tree reference describes AUTO_SPLIT like this: fewer than 500 rows, all are used for training; 500 to 50,000 rows, a random 20% is held out for evaluation; above 50,000 rows, 10,000 rows are held out. The random split is deterministic, based on a fingerprint of the data.

A random split is wrong for most business problems, because rows from the same customer, or from the same week, land on both sides. The model is then evaluated on a future it has already seen. The other methods give you control:

Two details matter. The split column must be INT64, NUMERIC, BIGNUMERIC, FLOAT64 or TIMESTAMP for SEQ (not DATE), and BOOL for CUSTOM. The split column is never used as a feature. With hyperparameter tuning a CUSTOM split takes a STRING column with TRAIN, EVAL and TEST values instead.

TRANSFORM keeps training and scoring consistent

Training-serving skew is the classic failure of hand-built pipelines: the training query bucketises tenure one way and the scoring job does it another. The TRANSFORM clause removes that class of bug. Preprocessing written in TRANSFORM is stored with the model and reapplied automatically by ML.PREDICT and ML.EVALUATE, including statistics computed at training time, such as the quantile boundaries of ML.QUANTILE_BUCKETIZE or the mean and variance of ML.STANDARD_SCALER.

Only columns output by TRANSFORM reach training. Aggregates, non-ML analytic functions and UDFs are not allowed inside it, and the label and split columns must appear untransformed, by name. At prediction time you pass only the raw columns the clause reads.

Worked example, part 1: a leak-free feature table

The example predicts which subscribers will cancel in the next 30 days. One row per user per weekly snapshot date: features use only data before the snapshot, and the label looks at the 30 days after it. A 30-day label window creates a subtle leak. Training rows from the 30 days before the evaluation cut-off have labels that observe the evaluation period, so they are dropped.

CREATE OR REPLACE TABLE subs.churn_training
PARTITION BY snapshot_date AS
SELECT
  user_id,
  snapshot_date,
  plan,
  country,
  tenure_days,
  sessions_28d,
  minutes_28d,
  support_tickets_90d,
  failed_payments_90d,
  IF(cancelled_within_30d, 1, 0) AS churned,
  snapshot_date >= DATE '2026-06-01' AS is_eval
FROM subs.user_snapshots
WHERE snapshot_date BETWEEN DATE '2025-09-01' AND DATE '2026-08-31'
  -- labels of these rows would observe the evaluation period
  AND NOT (snapshot_date >= DATE '2026-05-02' AND snapshot_date < DATE '2026-06-01');

Worked example, part 2: training a boosted-tree classifier

A boosted-tree classifier is a strong default for tabular data. The options below are taken from the boosted-tree CREATE MODEL reference. AUTO_CLASS_WEIGHTS rebalances a rare positive class; ENABLE_GLOBAL_EXPLAIN must be set at training time if you want ML.GLOBAL_EXPLAIN later.

CREATE OR REPLACE MODEL subs.churn_bt_20261004
TRANSFORM (
  plan,
  country,
  ML.QUANTILE_BUCKETIZE(tenure_days, 10) OVER () AS tenure_bucket,
  LOG(1 + minutes_28d) AS log_minutes_28d,
  sessions_28d,
  support_tickets_90d,
  failed_payments_90d,
  churned,
  is_eval
)
OPTIONS (
  model_type = 'BOOSTED_TREE_CLASSIFIER',
  input_label_cols = ['churned'],
  data_split_method = 'CUSTOM',
  data_split_col = 'is_eval',
  max_iterations = 300,
  early_stop = TRUE,
  min_rel_progress = 0.005,
  learn_rate = 0.1,
  max_tree_depth = 6,
  subsample = 0.8,
  auto_class_weights = TRUE,
  enable_global_explain = TRUE
) AS
SELECT * EXCEPT (user_id, snapshot_date)
FROM subs.churn_training;

Early stopping watches loss on the evaluation rows and stops when relative improvement falls below MIN_REL_PROGRESS (default 0.01; MAX_ITERATIONS defaults to 20 for boosted trees). ML.TRAINING_INFO returns per-iteration training and evaluation loss; a widening gap is overfitting. For tuning, set NUM_TRIALS and give hyperparameters ranges such as LEARN_RATE = HPARAM_RANGE(0.02, 0.3) or MAX_TREE_DEPTH = HPARAM_CANDIDATES([4, 6, 8]), then compare trials with ML.TRIAL_INFO.

Worked example, part 3: evaluation, thresholds and explanations

Evaluation is three queries. Without an input table, they use the evaluation split from training.

SELECT * FROM ML.EVALUATE(MODEL subs.churn_bt_20261004);

SELECT * FROM ML.ROC_CURVE(MODEL subs.churn_bt_20261004);

SELECT * FROM ML.CONFUSION_MATRIX(
  MODEL subs.churn_bt_20261004,
  STRUCT(0.35 AS threshold));

Choose the threshold from the business action, not from 0.5. Suppose a retention offer costs 5 per user and saves a churner worth 60 in a quarter of cases, so a true positive is worth 60 * 0.25 - 5 = 10 and a false positive costs 5. Read the true and false positive counts at each threshold from ML.ROC_CURVE, compute 10 * TP - 5 * FP, and pick the maximum. Because the model was trained with class weights, its scores are not calibrated probabilities, so 0.5 has no special meaning.

Then explain the model. ML.GLOBAL_EXPLAIN ranks features by overall attribution. If failed_payments_90d dominates, the model may mostly find involuntary churn, which a payment-retry fix serves better than a discount. ML.EXPLAIN_PREDICT gives per-row attributions, TOP_K_FEATURES of them (default 5), which turns a score into a reason a support agent can read.

Worked example, part 4: batch scoring and skew checks

ML.PREDICT appends predicted_churned and, for classifiers, predicted_churned_probs, an array of label and probability structs. A scheduled query, run when each weekly snapshot lands, writes scores into a date-partitioned table that downstream systems read:

INSERT INTO subs.churn_scores (score_date, user_id, churn_score, model_name)
SELECT
  CURRENT_DATE(),
  user_id,
  (SELECT p.prob FROM UNNEST(predicted_churned_probs) AS p WHERE p.label = 1),
  'churn_bt_20261004'
FROM ML.PREDICT(
  MODEL subs.churn_bt_20261004,
  (SELECT *, FALSE AS is_eval
   FROM subs.user_snapshots
   WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM subs.user_snapshots)));

Storing the model name with each score makes every downstream decision traceable to a model version. Before trusting a new week's scores, compare serving data with the training data:

SELECT * FROM ML.VALIDATE_DATA_SKEW(
  MODEL subs.churn_bt_20261004,
  (SELECT * FROM subs.user_snapshots
   WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM subs.user_snapshots)),
  STRUCT(0.3 AS categorical_default_threshold));

It reports, per feature, whether serving statistics differ from the training statistics stored with the model; for a model with a TRANSFORM clause they describe the raw inputs, before preprocessing. A new plan name, a country code switched to lowercase, or a column suddenly NULL shows up here before it shows up as a bad retention campaign. ML.VALIDATE_DATA_DRIFT compares two serving windows with each other.

Forecasting and remote models

Two other model families cover common warehouse needs. ARIMA_PLUS forecasts many series in one statement: name TIME_SERIES_TIMESTAMP_COL, TIME_SERIES_DATA_COL and TIME_SERIES_ID_COL, and it fits one model per series, with optional holiday effects and spike cleaning. ML.FORECAST takes STRUCT(30 AS horizon, 0.9 AS confidence_level) and returns forecasts with intervals; ML.DETECT_ANOMALIES flags points outside them.

Remote models bring generative models to the same tables. Create a model over a Vertex AI endpoint through a connection, then call it row by row:

CREATE OR REPLACE MODEL subs.embedder
  REMOTE WITH CONNECTION DEFAULT
  OPTIONS (ENDPOINT = 'gemini-embedding-001');

SELECT ticket_id, ml_generate_embedding_result AS embedding
FROM ML.GENERATE_EMBEDDING(
  MODEL subs.embedder,
  (SELECT ticket_id, body AS content FROM subs.support_tickets
   WHERE created_date = CURRENT_DATE() - 1),
  STRUCT(TRUE AS flatten_json_output, 'CLUSTERING' AS task_type));

The input text must be in a column named content. Embeddings of support tickets can be clustered with k-means to find complaint themes, then used as churn features. Remote calls are slower, quota-bound and billed by the endpoint, so run them incrementally over new rows rather than over the whole table each day.

Operating models

• Version by name. CREATE OR REPLACE MODEL overwrites without a trace. Use dated names, keep the previous model until the new one has scored a week, and promote by changing one configuration value in the scoring job.
• Register when other teams consume the model. MODEL_REGISTRY = 'VERTEX_AI' with VERTEX_AI_MODEL_ID and version aliases registers it in the Vertex AI Model Registry; Vertex AI covers endpoints for online serving.
• Export when you must serve outside BigQuery. EXPORT MODEL writes supported model types to Cloud Storage.
• Retrain on a schedule, gate on metrics. A scheduled script trains the candidate, compares ML.EVALUATE on a fixed recent month with the current model, and promotes only if it is better.
• Control access. Models inherit dataset permissions; prediction needs read access to the model and the input data.

Failure modes

• Optimistic evaluation. A random or AUTO split mixes users and weeks across the split. Use a time-based CUSTOM split with a label-window gap.
• Leaky features. A column updated after the snapshot, such as current plan status, makes the model look excellent and fail in production. Build features from snapshot tables, never from current-state tables.
• Skew from hand-written scoring SQL. Preprocessing done outside TRANSFORM drifts. Keep it in the clause.
• Silent schema change. A renamed input column fails the scoring query or, worse, a new categorical value is treated as unseen. Run skew validation daily.
• Runaway training cost. Training queries that scan unpartitioned history every night cost more than the model is worth. Materialise features into a partitioned table and train on a bounded window.

Trade-offs

What to do next

1. Confirm your reservation's edition supports BigQuery ML and that you have a dataset for models.
2. Build a snapshot feature table, partitioned by snapshot date, with a label window and a gap before the evaluation cut-off.
3. Train a logistic regression baseline and a boosted-tree model with a CUSTOM time split and preprocessing in TRANSFORM.
4. Compare them with ML.EVALUATE and ML.ROC_CURVE, and choose the threshold from the cost of each error.
5. Check ML.GLOBAL_EXPLAIN for features that should not be there.
6. Schedule scoring after each snapshot into a partitioned table that records the model name, with ML.VALIDATE_DATA_SKEW run first.
7. Use dated model names and a metric gate for retraining, and register models in Vertex AI when other teams depend on them.