# BigQuery ML in SQL

> BigQuery ML trains and runs models with CREATE MODEL and ML.PREDICT. Built-in models train inside BigQuery and are billed per byte read, at 50 times the normal query rate. Here is what each step returns, what it costs, and the defaults that quietly change your results.

Source: https://docs.querylab.io/bigquery/bigquery-ml/

Updated: 2026-10-06

BigQuery ML lets you train a model with `CREATE MODEL ... AS SELECT`, then call it from a query with `ML.PREDICT`, `ML.EVALUATE` or `ML.EXPLAIN_PREDICT`. The model is an object in a dataset. Training reads your data once or many times depending on the model type, and you pay for the bytes it reads at $312.50 per TiB for the simple built-in models, 50 times the $6.25 per TiB of an on-demand query. Prediction and evaluation are billed at the normal query rate. BigQuery ML isn't available in the Standard edition.

## The steps, run on a tiny model

Everything below we ran on real BigQuery against a linear regression of house price on size and bedrooms. These are plain blocks, not runnable on this page, because a model has to be created in a dataset: `CREATE TEMP MODEL` failed in our test.

```sql
CREATE OR REPLACE MODEL mydataset.price_model
OPTIONS (model_type = 'LINEAR_REG', input_label_cols = ['price'], data_split_method = 'NO_SPLIT')
AS
SELECT sqft, bedrooms, price FROM UNNEST([
  STRUCT(600 AS sqft, 1 AS bedrooms, 150.0 AS price),
  STRUCT(800, 2, 200.0), STRUCT(1000, 2, 240.0), STRUCT(1200, 3, 310.0),
  STRUCT(1500, 3, 365.0), STRUCT(1800, 4, 450.0), STRUCT(2000, 4, 480.0), STRUCT(2400, 5, 600.0)])
```

The training data came from an inline `UNNEST`, so the job processed 0 bytes and was billed 0. Eight rows still took 100 seconds, and the job statistics show 20 iterations of 2.5 to 6.5 seconds each (the default `max_iterations` is 20). Creating a model takes minutes even when the data is tiny.

`ML.PREDICT` returns your input columns plus `predicted_<label>`:

```sql
SELECT * FROM ML.PREDICT(MODEL mydataset.price_model, (SELECT 1000 AS sqft, 2 AS bedrooms));
-- predicted_price 240.337, sqft 1000, bedrooms 2
```

`ML.EVALUATE` takes held-out rows with the label and returns error metrics. With two rows we got `mean_absolute_error` 3.57 and `r2_score` 0.997. Called with no data argument it evaluates on the data the model was trained on or held out, which with `NO_SPLIT` or under 500 rows is the training data. `ML.WEIGHTS` returns the coefficients (0.150 per square foot, 47.1 per bedroom, an intercept of -4.0 here), and `ML.EXPLAIN_PREDICT` adds the contribution of each feature to a prediction, with a baseline of 349.4 and attributions of -61.9 for size and -47.1 for bedrooms.

## What ML.PREDICT does with bad input

The input to `ML.PREDICT` is matched to the model by column name. We tested what happens when it isn't what the model was trained on:

| Input to `ML.PREDICT`                    | Result                                                                          |
| ---------------------------------------- | ------------------------------------------------------------------------------- |
| An extra column named `extra_col`        | Passed through to the output                                                    |
| An extra column named `price`, the label | Passed through, next to `predicted_price`                                       |
| `bedrooms` missing                       | Error: "Column bedrooms is not found in the input data to the PREDICT function" |
| `bedrooms` is a `STRING`                 | Error: "Data type STRING does not match what model expects: FLOAT64"            |
| `bedrooms` is `NULL`                     | A prediction, 287.5 instead of 240.3                                            |

The last row is the dangerous one. A `NULL` feature isn't an error. The 287.5 equals the prediction for 3 bedrooms, the average of the training data, so BigQuery substituted the mean, and nothing in the output says so. Filter or flag `NULL` features yourself before you trust a prediction. The type error also shows that the `INT64` columns of the training data became `FLOAT64` features.

## Splitting and evaluating

`data_split_method` defaults to `AUTO_SPLIT`. With fewer than 500 rows all rows train. From 500 to 50,000 rows, 20% is held out for evaluation. Above 50,000 rows, 10,000 rows are held out. Use `NO_SPLIT` for a tiny table, `RANDOM` for control over the fractions, `SEQ` to split in the order of a column and `CUSTOM` to mark the evaluation rows yourself.

## Preprocessing with TRANSFORM

Put the feature preprocessing in a `TRANSFORM` clause and BigQuery stores it with the model, so `ML.PREDICT` gets raw columns and applies the same scaling and bucketing:

```sql
CREATE OR REPLACE MODEL mydataset.price_model_t
TRANSFORM (
  ML.STANDARD_SCALER(sqft) OVER () AS sqft_z,
  ML.BUCKETIZE(bedrooms, [2, 4]) AS bed_bucket,
  price
)
OPTIONS (model_type = 'LINEAR_REG', input_label_cols = ['price'])
AS SELECT sqft, bedrooms, price FROM mydataset.listings;

SELECT * FROM ML.PREDICT(MODEL mydataset.price_model_t, (SELECT 1000 AS sqft, 2 AS bedrooms));
```

On the same data this gave a prediction of 250.7 from the raw `sqft` and `bedrooms`. `ML.WEIGHTS` then lists the processed inputs: `sqft_z`, `bed_bucket` (with a weight for each of its categories) and the intercept. The scaling functions also work outside a model, in a plain query:

**bucketing and scaling one column**

```sql
SELECT
  sqft,
  ML.BUCKETIZE(sqft, [800, 1500]) AS size_bucket,
  ROUND(ML.MIN_MAX_SCALER(sqft) OVER (), 3) AS min_max,
  ROUND(ML.STANDARD_SCALER(sqft) OVER (), 3) AS standardized,
  ROUND(ML.MAX_ABS_SCALER(sqft) OVER (), 3) AS max_abs
FROM UNNEST([600, 1000, 1500, 2400]) AS sqft
ORDER BY sqft
```

Result in BigQuery (4 rows):

| sqft | size_bucket | min_max | standardized | max_abs |
| --- | --- | --- | --- | --- |
| 600 | bin_1 | 0.0 | -1.153 | 0.25 |
| 1000 | bin_2 | 0.222 | -0.558 | 0.417 |
| 1500 | bin_3 | 0.5 | 0.186 | 0.625 |
| 2400 | bin_3 | 1.0 | 1.525 | 1.0 |

`ML.BUCKETIZE` puts a value equal to a split point in the higher bucket, and names buckets `bin_1`, `bin_2` and so on:

**10 and 20 are the starts of the next buckets**

```sql
SELECT x, ML.BUCKETIZE(x, [10, 20]) AS bucket
FROM UNNEST([5, 10, 15, 20, 25]) AS x
ORDER BY x
```

Result in BigQuery (5 rows):

| x | bucket |
| --- | --- |
| 5 | bin_1 |
| 10 | bin_2 |
| 15 | bin_2 |
| 20 | bin_3 |
| 25 | bin_3 |

### Rare categories become 0

`ML.ONE_HOT_ENCODER` and `ML.LABEL_ENCODER` only keep categories that appear at least `frequency_threshold` times, and the default is 5. Every other category is encoded as 0. On a small table that is every category:

**four cities, all encoded as 0**

```sql
-- each city appears fewer than 5 times, so the default frequency_threshold of 5 encodes all of them as 0
SELECT city, ML.LABEL_ENCODER(city) OVER () AS default_code
FROM UNNEST(['leeds', 'york', 'leeds', 'bath']) AS city
ORDER BY city
```

Result in BigQuery (4 rows):

| city | default_code |
| --- | --- |
| bath | 0 |
| leeds | 0 |
| leeds | 0 |
| york | 0 |

Pass the arguments explicitly, `ML.LABEL_ENCODER(city, 10, 1)` for the top 10 values with a threshold of 1:

**the same cities with a threshold of 1**

```sql
-- label_encoder(value, top_k, frequency_threshold)
SELECT city, ML.LABEL_ENCODER(city, 10, 1) OVER () AS code
FROM UNNEST(['leeds', 'york', 'leeds', 'bath']) AS city
ORDER BY city
```

Result in BigQuery (4 rows):

| city | code |
| --- | --- |
| bath | 1 |
| leeds | 2 |
| leeds | 2 |
| york | 3 |

On a real table the threshold of 5 is fine. It matters on test data, in small segments, and for any column where most values are rare.

## Which model types exist

| Model                                         | `model_type`                                                                           | Trained in                                                          | Dry run of `CREATE MODEL` |
| --------------------------------------------- | -------------------------------------------------------------------------------------- | ------------------------------------------------------------------- | ------------------------- |
| Linear and logistic regression                | `LINEAR_REG`, `LOGISTIC_REG`                                                           | BigQuery                                                            | Yes                       |
| K-means, PCA                                  | `KMEANS`, `PCA`                                                                        | BigQuery                                                            | Yes                       |
| Matrix factorization                          | `MATRIX_FACTORIZATION`                                                                 | BigQuery                                                            | Yes (editions only)       |
| Time series                                   | `ARIMA_PLUS`, `ARIMA_PLUS_XREG`                                                        | BigQuery                                                            | Yes                       |
| Contribution analysis                         | `CONTRIBUTION_ANALYSIS`                                                                | BigQuery                                                            | Yes                       |
| Boosted trees, random forest                  | `BOOSTED_TREE_CLASSIFIER`, `RANDOM_FOREST_CLASSIFIER`, and regressor variants          | Vertex AI (which Google now calls Gemini Enterprise Agent Platform) | No                        |
| Deep neural network, Wide & Deep, autoencoder | `DNN_CLASSIFIER`, `DNN_LINEAR_COMBINED_CLASSIFIER`, `AUTOENCODER`                      | Vertex AI                                                           | No                        |
| AutoML                                        | `AUTOML_REGRESSOR`, `AUTOML_CLASSIFIER`                                                | Vertex AI                                                           | No                        |
| Imported                                      | `ONNX`, `TENSORFLOW`, `TENSORFLOW_LITE`, `XGBOOST`                                     | Your file, run in BigQuery                                          | CREATE processes no bytes |
| Remote                                        | `CREATE MODEL ... REMOTE WITH CONNECTION`, see [AI functions](https://docs.querylab.io/bigquery/ai-functions/) | Vertex AI endpoint                                                  | CREATE processes no bytes |

The dry-run column is from Google's introduction: you can dry-run `CREATE MODEL` for the built-in models to get the training bytes, and you can't for models trained on Vertex AI. Contribution analysis and matrix factorization are listed with the built-in models; the pricing page puts matrix factorization under editions or reservations only.

## What it costs

| Operation                                                                                          | On-demand rate                                                |
| -------------------------------------------------------------------------------------------------- | ------------------------------------------------------------- |
| `CREATE MODEL` for linear or logistic regression, k-means, PCA, time series, contribution analysis | $312.50 per TiB read                                          |
| `CREATE MODEL` for boosted trees, random forest, DNN, Wide & Deep, AutoML, autoencoder             | $6.25 per TiB read, plus Vertex AI training billed separately |
| `ML.EVALUATE`, `ML.PREDICT`, inspection functions                                                  | $6.25 per TiB read, inside the monthly free terabyte          |

Google's pricing note says `CREATE MODEL` stops at 50 iterations for iterative models. Time series models with `auto_arima` are billed for each candidate model they fit, so the bytes are multiplied by the number of candidates, 3 to 42 depending on `auto_arima_max_order` and the differencing order. A model's storage is billed at the normal storage rates. A remote model's queries are billed by BigQuery for the bytes and by Vertex AI for the model calls.

Because built-in training is 50 times the query rate, the cost is the table you train on. Training on 1 TiB costs $312.50 once, however small the model. Dry-run the `CREATE MODEL` first, and train on a sample table or a narrow query that selects only the label and the features. See [how queries are billed](https://docs.querylab.io/bigquery/how-queries-are-billed/) for how bytes are counted.

Forecasting models are covered in [forecasting](https://docs.querylab.io/bigquery/forecasting/), and the models that call a Vertex AI endpoint in [AI functions](https://docs.querylab.io/bigquery/ai-functions/).

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Introduction to ML in BigQuery](https://cloud.google.com/bigquery/docs/bqml-introduction)

- [The CREATE MODEL statement for generalized linear models](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create-glm)

- [BigQuery ML pricing](https://cloud.google.com/bigquery/pricing#bqml)

- [The ML.ONE_HOT_ENCODER function](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-one-hot-encoder)