BigQuery ML: Machine Learning with SQL

Building a machine learning model usually begins with getting data out of the warehouse: an export, a notebook, a Python environment, a way to bring the predictions back. BigQuery ML removes those steps. The model is created by a SQL statement, lives in a dataset beside the tables, and is used by another SQL statement. For someone who knows SQL and the data, that puts a first model an afternoon away. It does not put a good model an afternoon away, and the difference is the subject of the second half of this guide.

Three statements

The whole workflow is three statements (Google Cloud: classification model tutorial). Suppose the question is which taxi trips end with a generous tip.

Train. CREATE MODEL takes options and a query. Every column the query returns is a feature, except the one named as the label:

CREATE OR REPLACE MODEL analytics.generous_tip_model
OPTIONS (
  model_type = 'LOGISTIC_REG',
  input_label_cols = ['generous_tip'],
  data_split_method = 'RANDOM',
  data_split_eval_fraction = 0.2
) AS
SELECT trip_distance,
       fare_amount,
       passenger_count,
       EXTRACT(HOUR FROM pickup_at) AS pickup_hour,
       EXTRACT(DAYOFWEEK FROM pickup_at) IN (1, 7) AS is_weekend,
       pickup_zone IN (132, 138) AS airport_pickup,
       SAFE_DIVIDE(tip_amount, fare_amount) >= 0.25 AS generous_tip
FROM analytics.trips
WHERE payment_type = 1 AND fare_amount > 0 AND passenger_count IS NOT NULL;

Evaluate. ML.EVALUATE returns one row of metrics, computed on rows held back from training:

SELECT * FROM ML.EVALUATE(MODEL analytics.generous_tip_model);

Predict. ML.PREDICT adds a predicted label and its probabilities to any rows you give it:

SELECT *
FROM ML.PREDICT(MODEL analytics.generous_tip_model,
                (SELECT * FROM analytics.trips_today));

A fourth, ML.EXPLAIN_PREDICT, adds the features that contributed most to each prediction.

Two details in the training statement deserve attention. Feature preparation is ordinary SQL in the SELECT. And the split between training and evaluation rows is automatic unless you set it: by default, an input of more than 50,000 rows keeps 10,000 for evaluation (Google Cloud: CREATE MODEL for generalized linear models).

What you can build

BigQuery ML covers more than regression (Google Cloud: ML in BigQuery):

The first-listed models in each row are trained inside BigQuery. Tree, neural network and AutoML models are trained on Google's separate ML platform and returned to BigQuery. Models built elsewhere, in ONNX, TensorFlow or XGBoost format, can be imported and used for prediction.

Start with the simplest type that fits the question. Logistic and linear regression train quickly, cost least and can be explained.

What it costs

Training is priced differently from querying. At on-demand list prices for us-central1 on 2 October 2026, creating a built-in model such as a logistic regression costs USD 312.50 per TiB processed, fifty times the USD 6.25 per TiB of an ordinary query. Evaluation and prediction are charged at the ordinary rate (Google Cloud: BigQuery pricing).

So the training query's column list matters: select only the feature columns, from only the rows you need. With capacity pricing, BigQuery ML requires the Enterprise or Enterprise Plus edition.

The same model, reproduced locally

To show what the evaluation means, the script below builds the same kind of model on real data: 300,000 card-paid New York taxi trips from January 2024 (NYC TLC: trip record data). It uses scikit-learn, not BigQuery ML, so the library, defaults and exact numbers differ. Only card payments are used because the data dictionary states that cash tips are not recorded (TLC: data dictionary).

# The model a BigQuery ML logistic regression would build, reproduced locally with scikit-learn on
# real trip data. It is not BigQuery ML: the library, defaults and results differ. It shows the
# workflow that the SQL below asks for, and the checks to make on the evaluation it returns.
import pandas as pd
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import accuracy_score, log_loss, precision_score, recall_score, roc_auc_score
from sklearn.model_selection import train_test_split
from sklearn.pipeline import make_pipeline
from sklearn.preprocessing import StandardScaler

columns = ["tpep_pickup_datetime", "passenger_count", "trip_distance", "PULocationID",
           "payment_type", "fare_amount", "tip_amount"]
trips = pd.read_parquet("../../dedata/yellow_tripdata_2024-01.parquet", columns=columns)
trips = trips[(trips.payment_type == 1) & (trips.fare_amount > 0) & trips.passenger_count.notna()]
trips = trips.sample(n=300_000, random_state=7)                 # card payments only: tips are recorded

features = pd.DataFrame({
    "trip_distance": trips.trip_distance,
    "fare_amount": trips.fare_amount,
    "passenger_count": trips.passenger_count,
    "pickup_hour": trips.tpep_pickup_datetime.dt.hour,
    "is_weekend": (trips.tpep_pickup_datetime.dt.dayofweek >= 5).astype(int),
    "airport_pickup": trips.PULocationID.isin([132, 138]).astype(int),
})
label = (trips.tip_amount / trips.fare_amount >= 0.25).astype(int)       # a generous tip: 25% or more

x_train, x_test, y_train, y_test = train_test_split(features, label, test_size=0.2, random_state=7)
model = make_pipeline(StandardScaler(), LogisticRegression(max_iter=200)).fit(x_train, y_train)
predicted = model.predict(x_test)
probability = model.predict_proba(x_test)[:, 1]

majority = int(y_train.mean() >= 0.5)
print(f"rows: {len(x_train):,} to train, {len(x_test):,} to evaluate; share with a generous tip: {label.mean():.3f}")
print(f"baseline, always predict {majority}:  accuracy {accuracy_score(y_test, [majority] * len(y_test)):.3f}")
print(f"logistic regression:         accuracy {accuracy_score(y_test, predicted):.3f}  "
      f"precision {precision_score(y_test, predicted):.3f}  recall {recall_score(y_test, predicted):.3f}  "
      f"roc_auc {roc_auc_score(y_test, probability):.3f}  log_loss {log_loss(y_test, probability):.3f}")
print("\nweights on standardized features")
for name, weight in sorted(zip(features.columns, model[-1].coef_[0]), key=lambda pair: -abs(pair[1])):
    print(f"  {name:16} {weight:+.3f}")

rows: 240,000 to train, 60,000 to evaluate; share with a generous tip: 0.580
baseline, always predict 1:  accuracy 0.577
logistic regression:         accuracy 0.702  precision 0.681  recall 0.910  roc_auc 0.740  log_loss 0.604

weights on standardized features
  fare_amount      -1.372
  airport_pickup   +0.467
  pickup_hour      +0.107
  is_weekend       -0.044
  passenger_count  +0.014
  trip_distance    +0.005

Three ways an easy model misleads

1. Accuracy without a baseline

The model is right 70.2% of the time. That sounds respectable until you see that 58% of these trips have a generous tip. A rule that predicts "generous" for every trip, with no model at all, is right 57.7% of the time. The model's real contribution is the difference, about 12 points.

Always compute the baseline. On a different target the trap is worse. More than 95% of card-paid trips in this file record some tip, so a model predicting "any tip at all" would score 95% by learning nothing.

2. One metric out of six

ML.EVALUATE returns precision, recall, accuracy, F1, log loss and ROC AUC for a classifier. Read them together. Here recall is 0.910 and precision is 0.681: the model finds most generous tips by predicting "generous" very often, and a third of those predictions are wrong. Whether that is acceptable depends on what a wrong prediction costs, which is a business question the metrics cannot answer. The ROC AUC of 0.740 says the ranking is better than chance and far from excellent.

3. A feature that contains the answer

The weights show the fare dominating everything else, with a large negative sign. Part of that is real: higher fares do attract smaller percentage tips. Part of it is arithmetic, because the label was built by dividing by the fare. A model can look strong simply because a feature is entangled with how the label was defined.

The severe form of this is leakage: a feature that would not be known at the time of prediction, such as the total amount paid, which already includes the tip. BigQuery ML will train on it without complaint and report excellent metrics. Before trusting a model, ask of every feature whether it is known at the moment you would make the prediction.

A workable routine

1. State the decision first. Who will act on the prediction, and what will they do differently?
2. Build the label in SQL and check its balance.
3. List the features and remove any that are not known in advance.
4. Train the simplest model type.
5. Compare with the baseline, and read all the metrics.
6. Look at the explanation from ML.EXPLAIN_PREDICT or ML.GLOBAL_EXPLAIN. If the top feature surprises you, find out why before celebrating.
7. Only then try a more powerful model type, and check that it beats the simple one by enough to justify its cost.

When to leave BigQuery ML

It is the right place for tabular data that is already in the warehouse and for predictions consumed by SQL: scores written to a table, segments used in a report. Move to a full ML platform when you need custom training code, data that is not tabular, such as images, or serving predictions to an application with low latency.

For the SQL that prepares features, see BigQuery SQL tips and window functions. For keeping training costs down, see BigQuery cost optimization.

Machine learning workflows are introduced in the Data Science course, and warehouse SQL in the Data Engineering course.

Sources

• Introduction to ML in BigQuery (Google Cloud Documentation) — Google Cloud
• Build and use a classification model on census data (Google Cloud Documentation) — Google Cloud
• The CREATE MODEL statement for generalized linear models (Google Cloud Documentation) — Google Cloud
• BigQuery pricing (Google Cloud) — Google Cloud
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• Data dictionary: yellow taxi trip records (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission