1.3 Generating Predictions with BigQuery ML

1.3 Generating Predictions with BigQuery ML

Blueprint reference: Section 1.1, "Generating predictions using BigQuery ML."

Training a model in SQL is the easy half. The exam concentrates on the consequences of BigQuery ML's serving model, because that is where architectural decisions actually get made: BigQuery ML inference is serverless, analytical, and batch-shaped. There is no endpoint to deploy, no minimum replica count, and no path to a 20 ms p99. Every scenario that mentions real-time inference is testing whether you know to leave BigQuery for the serving step.

The Inference Function Family

The single most common exam slip is reaching for ML.PREDICT on a time-series model. ARIMA_PLUS models forecast forward from the training horizon; they do not score arbitrary input rows, so ML.FORECAST with a horizon and confidence_level is the only correct call.

SELECT * FROM ML.FORECAST(
  MODEL `analytics.daily_demand`,
  STRUCT(30 AS horizon, 0.95 AS confidence_level)
);

Classification Output Shape

A binary classifier does not return only a class. It returns the predicted label plus an array of (label, prob) structs, which you must unnest to threshold on probability rather than accepting the default 0.5 cut.

SELECT
  customer_id,
  predicted_churned,
  (SELECT prob FROM UNNEST(predicted_churned_probs)
   WHERE label = TRUE) AS churn_probability
FROM ML.PREDICT(MODEL `analytics.churn_model`,
                TABLE `analytics.active_customers`)
WHERE (SELECT prob FROM UNNEST(predicted_churned_probs)
       WHERE label = TRUE) > 0.72;

This matters commercially. If a retention offer costs money, the operating threshold is a business decision, not a modelling default. ML.ROC_CURVE gives you the true-positive and false-positive rates at each threshold so you can pick the point that maximizes expected value.

Per-Row Explanations

ML.EXPLAIN_PREDICT returns each prediction with the feature attributions that produced it, ordered by absolute contribution. top_k_features bounds the output width.

SELECT * FROM ML.EXPLAIN_PREDICT(
  MODEL `analytics.credit_model`,
  TABLE `analytics.applications`,
  STRUCT(5 AS top_k_features)
);

For linear models the attributions are exact coefficient contributions. For tree and DNN models BigQuery ML uses Shapley values, computed by sampling. ML.GLOBAL_EXPLAIN aggregates the same machinery across the whole training set to answer "which features matter overall," and requires enable_global_explain = TRUE at CREATE MODEL time — a flag you cannot retrofit without retraining.

The adverse-action notice requirement in lending is the canonical scenario: regulators require a per-applicant reason, which is a row-level explanation, so ML.EXPLAIN_PREDICT is correct and ML.GLOBAL_EXPLAIN is not.

When Predictions Must Leave BigQuery

BigQuery ML query latency is measured in seconds. Three patterns move a BigQuery-trained model to a low-latency surface.

1. EXPORT MODEL to Cloud Storage. Linear, logistic, DNN, Wide-and-Deep, k-means, autoencoder, and AutoML Tables models export as a TensorFlow SavedModel. Boosted tree and random forest models export as an XGBoost Booster file. The exported artifact can be uploaded to the Model Registry and deployed to an Agent Platform Inference endpoint, or run in any container that speaks TensorFlow Serving.

EXPORT MODEL `analytics.churn_model`
OPTIONS (URI = 'gs://my-models/churn/v3');

2. Register a remote model. The inverse direction: create a BigQuery model object that points at an already-deployed Agent Platform endpoint through a BigQuery connection. Analysts keep writing ML.PREDICT while inference executes on the endpoint, which means one model version serves both the warehouse and the application.

3. Scheduled batch materialization. For recommendations, propensity scores, and lead ranking, real-time inference is often unnecessary. A scheduled query writes scores to a serving table, and the application reads that table — or an exported copy in Bigtable or Firestore — with millisecond latency. This is the most cost-effective answer whenever the scenario tolerates hours-old scores.

Cost and Performance Notes

• ML.PREDICT is billed as a normal BigQuery query on the bytes scanned. Predicting on SELECT * over a wide table is expensive; select only the model's input columns.
• Partition and cluster the input table on the columns used in the WHERE clause; inference over a partition-pruned scan can be an order of magnitude cheaper.
• Batch inference over very large tables benefits from writing results to a destination table rather than streaming them to the client.
• ML.PREDICT on an imported TensorFlow model runs the model inside BigQuery slots and has model size limits, so very large deep models belong on an endpoint rather than imported.

Exam Traps

• Real-time serving from BigQuery ML. If the scenario says "under 100 ms" or "per user request," the answer involves exporting or registering with Agent Platform Inference, not a scheduled query.
• ML.PREDICT on ARIMA_PLUS. Use ML.FORECAST.
• Assuming ML.GLOBAL_EXPLAIN works retroactively. It requires the flag at creation time.
• Forgetting the probability array. predicted_<label> already applied a 0.5 threshold; business thresholds require unnesting the probabilities.