BigQuery ML SQL Commands: CREATE MODEL, ML.EVALUATE, and ML.PREDICT

GCP Study Hub
November 8, 2026

BigQuery ML lets teams train, evaluate, score, and explain machine learning models directly inside BigQuery using SQL. Each stage of the workflow maps to a specific SQL command: CREATE MODEL trains the model, ML.EVALUATE and ML.CONFUSION_MATRIX measure performance, ML.PREDICT generates predictions on unseen rows, and ML.EXPLAIN_PREDICT and ML.GLOBAL_EXPLAIN return feature attributions. On the Professional Machine Learning Engineer exam, scenarios often hinge on matching a requirement to the right function, its input schema, and its output shape.

Training models with CREATE MODEL

The CREATE MODEL statement trains a model directly on BigQuery tables. Using CREATE OR REPLACE MODEL creates a new model in the target dataset and replaces it with the newly trained version if a model with that name already exists. The OPTIONS clause sets the model type, such as logistic_reg for binary classification tasks like predicting customer churn.

CREATE OR REPLACE MODEL `my_dataset.churn_model`
OPTIONS(model_type='logistic_reg') AS
SELECT
  age,
  account_age_days,
  num_logins_last_week,
  churned AS label
FROM
  `my_dataset.customer_churn`

The SELECT clause chooses the input features (age, account_age_days, and num_logins_last_week) and aliases the target column as label so BigQuery ML knows which variable to predict. The training table holds historical customer rows covering both customers who churned and customers who stayed, allowing the model to learn patterns that separate the two outcomes.

Scoring performance with ML.EVALUATE

ML.EVALUATE scores a trained model against evaluation data with known labels. BigQuery runs predictions on the unseen rows and compares them against the true outcomes.

SELECT
  *
FROM
  ML.EVALUATE(
    MODEL `my_dataset.churn_model`,
    (
      SELECT
        age,
        account_age_days,
        num_logins_last_week,
        churned AS label
      FROM
        `my_dataset.customer_churn`
    )
  )

The evaluation subquery must supply the label column and the feature columns used during training with matching names, because BigQuery needs the true values to score the predictions. Passing an evaluation subquery is optional: calling ML.EVALUATE with only the model reference evaluates against the split BigQuery reserved automatically during training.

ML.EVALUATE returns a single row of aggregate metrics calculated across the entire evaluation set rather than per prediction. The metrics returned depend on the model type:

  • Logistic regression models return precision, recall, accuracy, F1 score, log loss, and ROC area under the curve.
  • Linear regression models return error metrics such as mean absolute error and mean squared error.

Inspecting classification errors with ML.CONFUSION_MATRIX

ML.CONFUSION_MATRIX computes a confusion matrix from a trained classification model against evaluation data, comparing predicted classes with actual classes. Because regression models do not produce class labels, this function is limited to classification models.

SELECT
  *
FROM
  ML.CONFUSION_MATRIX(
    MODEL `my_dataset.churn_model`,
    (
      SELECT
        *
      FROM
        `my_dataset.customer_churn`
    )
  )

The output reports counts of correct and incorrect predictions for each class, which helps when two models have the same overall accuracy while making different kinds of mistakes. Using SELECT * inside the subquery works as long as the label column is present and the feature column names match what the model was trained on.

Generating predictions with ML.PREDICT

Once evaluation shows acceptable performance, ML.PREDICT uses the trained model to score new rows.

SELECT
  *
FROM
  ML.PREDICT(
    MODEL `my_dataset.churn_model`,
    (
      SELECT
        age,
        account_age_days,
        num_logins_last_week
      FROM
        `my_dataset.customer_churn`
      WHERE
        churned IS NULL
    )
  )

The outer SELECT * returns all columns of the prediction results. Inside ML.PREDICT, the subquery selects the same feature columns used during training so the input matches the structure the model expects. Filtering with WHERE churned IS NULL isolates rows with no label yet, targeting customers whose churn status is unknown. BigQuery ML returns predictions for each customer, typically including both a predicted class and a probability score reflecting confidence in each prediction.

Explaining predictions with ML.EXPLAIN_PREDICT

When you need to understand why a model produced a specific prediction, ML.EXPLAIN_PREDICT returns feature attribution scores showing which factors drove each individual result. The query follows the same form as ML.PREDICT, adding a STRUCT parameter to limit how many features appear per prediction.

SELECT
  *
FROM
  ML.EXPLAIN_PREDICT(
    MODEL `my_dataset.churn_model`,
    (
      SELECT
        age,
        account_age_days,
        num_logins_last_week
      FROM
        `my_dataset.customer_churn`
      WHERE
        churned IS NULL
    ),
    STRUCT(3 AS top_k_features)
  )

Setting top_k_features to 3 restricts the output to the top three most important features for each prediction, keeping results manageable. The function returns multiple rows per prediction, one for each feature. For example, a customer (user 1023) with a churn probability of 0.87 might have a positive attribution of 0.52 for num_logins_last_week (fewer logins pushing toward churn) and a negative attribution of -0.18 for account_age_days (working against churn). A customer (user 2047) with a churn probability of 0.23 shows negative attributions across most features, pushing toward a prediction of 0.

Measuring overall importance with ML.GLOBAL_EXPLAIN

ML.GLOBAL_EXPLAIN zooms out from individual predictions to return overall feature importance across the model.

SELECT
  *
FROM
  ML.GLOBAL_EXPLAIN(MODEL `my_dataset.churn_model`)

Unlike ML.EXPLAIN_PREDICT, this query takes only the model reference with no input data subquery and no feature list, because it operates on the evaluation data the model already holds. BigQuery takes each feature's attribution across every evaluation row and averages the absolute values into one score per feature, returning one row per feature.

In an example output where num_logins_last_week scores 0.41, account_age_days scores 0.27, and recent_promotion scores 0.09, login activity is the strongest signal, account age contributes less, and recent promotion has minimal influence. That summary helps decide which features to keep and which ones mostly add noise.

Distinguishing the commands on the exam

For the Professional Machine Learning Engineer exam, the main distinctions across these commands come down to inputs, model support, and output granularity:

  • CREATE MODEL trains a model from BigQuery rows using a model_type and a target column aliased as label.
  • ML.EVALUATE returns one aggregate metrics row tailored to the model type, with an optional evaluation query.
  • ML.CONFUSION_MATRIX applies only to classification models and counts right and wrong predictions per class.
  • ML.PREDICT scores unlabeled rows with matching training features and returns predictions and probability scores.
  • ML.EXPLAIN_PREDICT returns multiple rows per prediction with per-feature attribution scores, optionally capped with top_k_features.
  • ML.GLOBAL_EXPLAIN takes only the model reference and returns one row per feature with the average absolute attribution across evaluation rows.

Our Professional Machine Learning Engineer course covers BigQuery ML SQL commands alongside model evaluation and explainability across Google Cloud, with practice questions that drill these distinctions.

Get tips and updates from GCP Study Hub

arrow