BigQuery ML Models, Features and Predictions

What it is

BigQuery ML lets you create, evaluate and use machine learning models with SQL, inside BigQuery. The data never leaves the warehouse, and analysts who know SQL can build models without Python or a separate training environment.

The exam tests whether you can map a business question to the right model type, prepare features in SQL, and pick the right prediction function.

How it works

Matching the model type to the business problem

The model_type option in CREATE MODEL sets the algorithm. Start from what the business wants to know.

Classification and regression need a label column (input_label_cols). Clustering needs none. Forecasting needs a timestamp column and a value column, plus an ID column when you forecast many series at once.

Feature engineering in SQL

BigQuery ML already does some preprocessing for you: for many model types it standardizes numeric columns and one-hot encodes string columns. For anything more, use the TRANSFORM clause. Preprocessing written there is saved with the model and applied again automatically at prediction time, so training and serving cannot drift apart.

CREATE OR REPLACE MODEL `retail.churn_model`
TRANSFORM (
  ML.STANDARD_SCALER(tenure_months) OVER () AS tenure_scaled,
  ML.QUANTILE_BUCKETIZE(monthly_spend, 4) OVER () AS spend_bucket,
  ML.FEATURE_CROSS(STRUCT(plan_type, region)) AS plan_region,
  churned
)
OPTIONS (
  model_type = 'BOOSTED_TREE_CLASSIFIER',
  input_label_cols = ['churned'],
  enable_global_explain = TRUE
) AS
SELECT tenure_months, monthly_spend, plan_type, region, churned
FROM retail.customer_features;

Feature selection

Fewer, better features train faster and generalize better. BigQuery ML gives several ways to find weak or harmful features:

Remove any feature that would not be known at prediction time, such as a "cancellation date" in a churn model. That is data leakage: evaluation looks excellent and production fails.

Evaluating and generating predictions

SELECT customer_id, predicted_churned, predicted_churned_probs
FROM ML.PREDICT(MODEL `retail.churn_model`,
  (SELECT * FROM retail.customers_today));

Batch scoring is usually a scheduled query that writes ML.PREDICT output to a table. For low-latency online predictions, register the model in Model Registry and deploy it to an endpoint, or export it with EXPORT MODEL.

A worked example

A retailer wants a 30-day sales forecast for each of 400 stores. The data is a daily sales table in BigQuery and the team knows SQL. One ARIMA_PLUS model with time_series_id_col trains a separate series per store in a single statement.

CREATE OR REPLACE MODEL `retail.store_forecast`
OPTIONS (
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'sale_date',
  time_series_data_col = 'units',
  time_series_id_col = 'store_id'
) AS
SELECT sale_date, store_id, units FROM retail.daily_sales;

SELECT * FROM ML.FORECAST(MODEL `retail.store_forecast`,
  STRUCT(30 AS horizon, 0.9 AS confidence_level));

How the exam asks about this

• "Analysts want to segment customers by behaviour; there is no label column." Train a KMEANS model in BigQuery ML.
• "Predictions differ because the app scales features differently from training." Move the preprocessing into the TRANSFORM clause so ML.PREDICT applies it.
• "Forecast demand for thousands of products from a BigQuery table with minimal effort." ARIMA_PLUS with time_series_id_col, then ML.FORECAST.