BigQuery ML: what it is, model types and a working example

The fastest way to see it work

You do not need to train anything to try it. AI.FORECAST uses a built-in time series model (TimesFM, in preview as of October 2026) and takes a query as input. This forecasts the next seven days of a daily metric:

SELECT
  forecast_timestamp,
  ROUND(forecast_value) AS forecast,
  ROUND(prediction_interval_lower_bound) AS low,
  ROUND(prediction_interval_upper_bound) AS high
FROM AI.FORECAST(
  (SELECT order_date, SUM(amount) AS revenue
   FROM mydataset.orders
   WHERE order_date >= '2026-04-01'
   GROUP BY order_date),
  data_col => 'revenue',
  timestamp_col => 'order_date',
  horizon => 7)
ORDER BY forecast_timestamp;

We ran this shape of query on 6 October 2026 against six months of a real daily series. It returned seven rows, each with a forecast and a lower and upper bound, and it reproduced the weekly pattern in the data without being told about it: the two weekend days came out at roughly half of the weekdays. It scanned about 104 MiB.

Read the bounds, not just the middle number. On that series the upper bound was more than double the lower one for most days. A forecast is a range, and a report that shows only the midpoint is promising more than the model said.

What BigQuery ML can build

The model types, as Google’s documentation groups them (checked October 2026).

Trained inside BigQuery:

Trained outside, used from SQL: deep neural networks, Wide & Deep, autoencoders, boosted trees (XGBoost), random forest and AutoML. BigQuery hands the training to Google’s managed ML platform and keeps the SQL interface.

Remote models point at a model deployed elsewhere, including Gemini models, so you can call it from a query. Imported models bring in something trained outside BigQuery, in ONNX, TensorFlow, TensorFlow Lite or XGBoost format.

For a first model, the choice is short: a number means linear regression or boosted trees, a yes or no means logistic regression, a series over time means ARIMA_PLUS or AI.FORECAST, and “find groups” means k-means.

The workflow: create, evaluate, predict

Three statements. They follow Google’s reference syntax and were not executed for this article, because CREATE MODEL writes to a dataset.

1. Train. A model that predicts whether a customer will order again:

CREATE OR REPLACE MODEL mydataset.repeat_buyer
OPTIONS (
  model_type = 'LOGISTIC_REG',
  input_label_cols = ['will_reorder']
) AS
SELECT
  orders_last_90d,
  avg_order_value,
  days_since_last_order,
  will_reorder
FROM mydataset.customer_features
WHERE snapshot_date < '2026-07-01';

Every column in the SELECT except the label becomes a feature. BigQuery splits the rows into training and evaluation sets on its own.

2. Evaluate. Check the model on data it has not seen:

SELECT *
FROM ML.EVALUATE(
  MODEL mydataset.repeat_buyer,
  (SELECT orders_last_90d, avg_order_value,
          days_since_last_order, will_reorder
   FROM mydataset.customer_features
   WHERE snapshot_date >= '2026-07-01'));

For a classifier this returns precision, recall, accuracy and ROC AUC. Evaluating on a later period, as here, is stricter than a random split and closer to how the model will be used.

3. Predict.

SELECT
  customer_id,
  predicted_will_reorder,
  predicted_will_reorder_probs
FROM ML.PREDICT(
  MODEL mydataset.repeat_buyer,
  (SELECT * FROM mydataset.customer_features
   WHERE snapshot_date = CURRENT_DATE()));

The output is a table, so it can be saved, joined and reported on like any other query result.

Two more functions are worth knowing from the start: ML.EXPLAIN_PREDICT shows which features pushed each prediction, and ML.FEATURE_INFO describes the inputs the model saw.

Feature preprocessing in ML: What BigQuery ML does for you

Feature preprocessing in ML turns raw columns into inputs a model can train on, and BigQuery ML gives you two preprocessing techniques: automatic and manual. During CREATE MODEL it imputes missing values and transforms features on its own: a NULL in a numeric column becomes the column mean, numeric columns are standardized for most model types, and most non-numeric columns are one-hot encoded (boosted tree and random forest models are the exceptions to both). For control, put your own transformations in a TRANSFORM clause; Google’s documentation says the same transformations are then restored automatically at prediction time, so the training and prediction queries cannot drift apart on this step. ML.BUCKETIZE, one of the manual preprocessing functions, turns a number into a bucket label:

SELECT
  ML.BUCKETIZE(price, [25, 100, 500]) AS price_bucket,
  COUNT(*) AS products,
  MIN(price) AS min_price,
  MAX(price) AS max_price
FROM `owox-demo.ecommerce.products`
GROUP BY price_bucket
ORDER BY min_price;

We ran this on OWOX’s synthetic demo shop. The 4,200 products fell into three buckets: bin_2 for 1,227 products priced 40.00 to 99.85, bin_3 for 2,399 priced 100.00 to 499.20, and bin_4 for 574 priced 500.00 and up.

ML.TFDV_VALIDATE: BigQuery ML data validation with TFDV

ML.TFDV_VALIDATE is BigQuery ML data validation built on TFDV, TensorFlow Data Validation: it compares the statistics of two datasets and reports where they differ too much. Google documents two uses: SKEW compares training data with serving data, and DRIFT compares two sets of serving data. It takes statistics, not rows, and ML.TFDV_DESCRIBE produces them from a table or a query. This compares the demo shop’s sessions before and after 1 September 2026, with a stricter threshold on medium:

SELECT ML.TFDV_VALIDATE(
  (SELECT dataset_feature_statistics_list
   FROM ML.TFDV_DESCRIBE((
     SELECT device_category, medium, conversion_seed
     FROM `owox-demo.ecommerce.sessions`
     WHERE date < '2026-09-01'))),
  (SELECT dataset_feature_statistics_list
   FROM ML.TFDV_DESCRIBE((
     SELECT device_category, medium, conversion_seed
     FROM `owox-demo.ecommerce.sessions`
     WHERE date >= '2026-09-01'))),
  'DRIFT',
  0.3, 'L_INFTY',
  0.3, 'JENSEN_SHANNON_DIVERGENCE',
  [('medium', 0.05)]
) AS anomalies;

It returned one anomaly, on medium: the L-infinity distance between the two periods was 0.0828537, above the 0.05 threshold, and the value that moved most was cpc. At the default threshold of 0.3 the same comparison flags nothing. The function does not validate the schema, so a column that changed type needs a separate check.

ML.VALIDATE_DATA_DRIFT: Drift between two sets of serving data

ML.VALIDATE_DATA_DRIFT computes the data drift between two sets of serving data. Google’s reference says it computes and compares the statistics of both sets and identifies where they differ anomalously. It does in one call what the DRIFT example above did in two steps, because it takes tables or queries directly and needs no ML.TFDV_DESCRIBE first. It returns one row per column:

SELECT *
FROM ML.VALIDATE_DATA_DRIFT(
  (SELECT device_category, medium, conversion_seed
   FROM `owox-demo.ecommerce.sessions`
   WHERE date < '2026-09-01'),
  (SELECT device_category, medium, conversion_seed
   FROM `owox-demo.ecommerce.sessions`
   WHERE date >= '2026-09-01'),
  STRUCT(0.05 AS categorical_default_threshold))
ORDER BY input;

The output has the columns input, metric, threshold, value and is_anomaly. On the demo shop, medium came back with an L-infinity distance of 0.0829 against the 0.05 threshold, so is_anomaly was true; device_category (0.0032) and conversion_seed (about 0.00006, against the default 0.3) were not flagged.

ML.RECOMMEND: Predicted ratings from a matrix factorization model

ML.RECOMMEND is the function that turns a matrix factorization model into recommendations: Google’s reference says it generates a predicted rating for every user and item combination, including the combinations missing from the sparse training matrix. Given a table of users and items it returns a rating per pair, and given only users it returns ratings for all items. The output can be large, so Google suggests saving it to a table. It needs a trained matrix factorization model, so this is syntax from Google’s reference, not run here: CREATE MODEL writes to a dataset.

SELECT *
FROM ML.RECOMMEND(
  MODEL `mydataset.mymodel`,
  (SELECT user, item FROM `mydataset.mytable`));

What it costs

BigQuery ML is billed as BigQuery compute. Under capacity pricing, Google’s pricing page says slot capacity “applies to query costs, including BigQuery ML”. On demand, training and prediction have their own rates on the same pricing page, and they differ by model type, so look yours up before training on a large table.

Two facts from the documentation help you estimate. You can dry-run CREATE MODEL for internally trained models to see how much data it will process. You cannot for externally trained ones. And creating a remote model processes no bytes at all.

The general cost rules still apply: the training query scans the columns it selects, so select only the features. See BigQuery pricing.

When BigQuery ML is the right tool, and when it is not

It fits when the data is already in BigQuery, the problem is a standard one on tabular data (a number, a class, a segment, a forecast), and the people who will maintain it write SQL.

It does not fit when you need custom model architectures, fine control over training, or real-time predictions with strict latency. And it does not remove the hard part. The documentation says you can build a model “even without much ML knowledge” and, in the next sentence, that knowing feature engineering and training “helps you optimize both your data and your model”. Both halves are true.

The model is the easy part

A CREATE MODEL statement is a dozen lines. What decides whether its predictions mean anything is the query underneath it:

• Is the label defined the way the business defines it? “Will reorder” within 30 days or 90?
• Does any feature leak the answer? A column that is only filled in after the outcome is known will produce a model that looks excellent and predicts nothing.
• Is the training query the same as the prediction query? If a feature is computed one way in July and another way in October, the model is scoring data it was not trained on.

The third one is where projects quietly fail, and it is not an ML problem. It is the ordinary problem of the same logic written twice.

That is where a governed definition helps. In OWOX Data Marts a Data Mart is your SQL, saved with a description and an owner and run in your warehouse; the feature table a model trains on and the one it predicts on can be the same Data Mart. OWOX does not train models, pick features or run BigQuery ML for you. It keeps the query both steps depend on from drifting apart.

Google BigQueryBigQuery for Marketing Analytics: What Changed in 2026September 24, 2026→Google BigQueryIs BigQuery a Data Warehouse or a Data Lake? A Marketing GuideOctober 6, 2026→Google BigQueryBigQuery ROW_NUMBER, RANK, DENSE_RANK and NTILE, with Tested ExamplesOctober 16, 2026→

See all articles →