Train ML Models with SQL in BigQuery

BigQuery ML (BQML) allows you to create and execute machine learning models directly in BigQuery using standard SQL. The data stays in BigQuery — there is no need to export data to a separate training environment, dramatically reducing time-to-insight.

Why BigQuery ML?

BQML supports a wide range of model types, from simple linear models to AutoML and imported TensorFlow models. The exam frequently tests your ability to choose the right model type for a given scenario.

Complete Model Type Reference

The CREATE MODEL statement is the core of BigQuery ML. It combines model specification, hyperparameters, and training data in a single SQL statement.

Basic Syntax

-- General CREATE MODEL syntax
CREATE OR REPLACE MODEL `project.dataset.model_name`
OPTIONS (
  model_type = 'LOGISTIC_REG',           -- Required: model type
  input_label_cols = ['target_column'],   -- Label column (supervised)
  auto_class_weights = TRUE,             -- Handle imbalanced classes
  data_split_method = 'AUTO_SPLIT',      -- AUTO_SPLIT, RANDOM, CUSTOM, SEQ, NO_SPLIT
  max_iterations = 20,                    -- Training iterations
  learn_rate = 0.1,                        -- Learning rate
  l1_reg = 0.001,                          -- L1 regularization
  l2_reg = 0.001,                          -- L2 regularization
  early_stop = TRUE,                      -- Stop when no improvement
  min_rel_progress = 0.01                  -- Minimum relative progress
) AS
SELECT
  feature1,
  feature2,
  feature3,
  target_column
FROM
  `project.dataset.training_table`
WHERE
  feature1 IS NOT NULL

Classification Example (Logistic Regression)

-- Predict penguin species using logistic regression
CREATE OR REPLACE MODEL `my_dataset.penguin_classifier`
OPTIONS (
  model_type = 'LOGISTIC_REG',
  input_label_cols = ['species'],
  auto_class_weights = TRUE,
  max_iterations = 20
) AS
SELECT
  species,
  island,
  culmen_length_mm,
  culmen_depth_mm,
  flipper_length_mm,
  body_mass_g,
  sex
FROM
  `bigquery-public-data.ml_datasets.penguins`
WHERE
  body_mass_g IS NOT NULL
  AND sex IS NOT NULL

Regression Example (Boosted Tree)

-- Predict taxi fare using boosted tree regression
CREATE OR REPLACE MODEL `my_dataset.taxi_fare_model`
OPTIONS (
  model_type = 'BOOSTED_TREE_REGRESSOR',
  input_label_cols = ['fare'],
  num_parallel_tree = 5,
  max_tree_depth = 6,
  subsample = 0.8,
  learn_rate = 0.1,
  early_stop = TRUE,
  data_split_method = 'AUTO_SPLIT'
) AS
SELECT
  fare,
  pickup_latitude, pickup_longitude,
  dropoff_latitude, dropoff_longitude,
  passenger_count,
  EXTRACT(HOUR FROM pickup_datetime) AS pickup_hour,
  EXTRACT(DAYOFWEEK FROM pickup_datetime) AS pickup_day
FROM
  `my_dataset.taxi_trips`
WHERE
  fare > 0 AND fare < 200

Time Series Example (ARIMA_PLUS)

-- Forecast daily sales using ARIMA_PLUS
CREATE OR REPLACE MODEL `my_dataset.sales_forecast`
OPTIONS (
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'date',
  time_series_data_col = 'total_sales',
  time_series_id_col = 'store_id',       -- Multiple time series
  auto_arima = TRUE,                     -- Auto-detect (p,d,q)
  holiday_region = 'US',                 -- Holiday effects
  data_frequency = 'DAILY'               -- AUTO_FREQUENCY also works
) AS
SELECT
  date,
  store_id,
  total_sales
FROM
  `my_dataset.daily_sales`

BQML provides built-in feature engineering functions that can be applied within the TRANSFORM clause. This ensures transformations are consistently applied during both training and prediction.

The TRANSFORM Clause

The TRANSFORM clause defines feature transformations that become part of the model. When you call ML.PREDICT, these transformations are automatically applied — you pass raw data and the model handles preprocessing.

-- Feature engineering with TRANSFORM clause
CREATE OR REPLACE MODEL `my_dataset.engineered_model`
TRANSFORM (
  -- Pass through label and features
  target_col,

  -- Bucketize continuous features
  ML.BUCKETIZE(age, [18, 25, 35, 50, 65]) AS age_bucket,

  -- Quantile bucketize (data-driven boundaries)
  ML.QUANTILE_BUCKETIZE(income, 10) AS income_decile,

  -- Feature cross (interaction between categoricals)
  ML.FEATURE_CROSS(STRUCT(city, product_category)) AS city_product_cross,

  -- Polynomial expansion
  ML.POLYNOMIAL_EXPAND(STRUCT(feature1, feature2), 2) AS poly_features,

  -- Standard SQL transformations
  LOG(amount + 1) AS log_amount,
  EXTRACT(HOUR FROM timestamp_col) AS hour_of_day,
  EXTRACT(DAYOFWEEK FROM timestamp_col) AS day_of_week,
  IF(category IS NULL, 'unknown', category) AS category_clean
)
OPTIONS (
  model_type = 'BOOSTED_TREE_CLASSIFIER',
  input_label_cols = ['target_col']
) AS
SELECT * FROM `my_dataset.training_data`

Built-in Feature Engineering Functions

After training, use ML.EVALUATE to assess model performance. The metrics returned depend on the model type.

-- Evaluate model performance
SELECT *
FROM ML.EVALUATE(
  MODEL `my_dataset.penguin_classifier`
)

-- Evaluate on a specific holdout set
SELECT *
FROM ML.EVALUATE(
  MODEL `my_dataset.penguin_classifier`,
  (
    SELECT * FROM `my_dataset.penguins_test`
    WHERE body_mass_g IS NOT NULL
  )
)

Key Metrics by Model Type

-- View training info (loss curve)
SELECT *
FROM ML.TRAINING_INFO(
  MODEL `my_dataset.penguin_classifier`
)
ORDER BY iteration

-- View confusion matrix for classification
SELECT *
FROM ML.CONFUSION_MATRIX(
  MODEL `my_dataset.penguin_classifier`
)

ML.PREDICT generates batch predictions, while ML.EXPLAIN_PREDICT adds feature attributions to explain why the model made each prediction.

Batch Prediction with ML.PREDICT

-- Generate predictions on new data
SELECT
  predicted_species,
  predicted_species_probs,
  island,
  body_mass_g
FROM ML.PREDICT(
  MODEL `my_dataset.penguin_classifier`,
  (
    SELECT
      island, culmen_length_mm, culmen_depth_mm,
      flipper_length_mm, body_mass_g, sex
    FROM `my_dataset.new_penguins`
  )
)

-- For regression models, output column is: predicted_<label>
SELECT predicted_fare, actual_fare
FROM ML.PREDICT(
  MODEL `my_dataset.taxi_fare_model`,
  (SELECT * FROM `my_dataset.test_trips`)
)

Explainability with ML.EXPLAIN_PREDICT

-- Get predictions with feature attributions
SELECT *
FROM ML.EXPLAIN_PREDICT(
  MODEL `my_dataset.penguin_classifier`,
  (
    SELECT *
    FROM `bigquery-public-data.ml_datasets.penguins`
    WHERE body_mass_g IS NOT NULL
    LIMIT 5
  ),
  STRUCT(3 AS top_k_features)
)

-- Returns: predicted label + Shapley-based feature attributions
-- Each row includes the top_k most important features for that prediction

ARIMA_PLUS Forecasting

-- Generate 30-day forecast
SELECT *
FROM ML.FORECAST(
  MODEL `my_dataset.sales_forecast`,
  STRUCT(30 AS horizon, 0.95 AS confidence_level)
)

-- Returns: forecast_timestamp, forecast_value,
--          standard_error, prediction_interval_lower_bound,
--          prediction_interval_upper_bound

BQML models trained in BigQuery can be exported and deployed to Vertex AI Endpoints for online (real-time) prediction. This is the bridge from BQML prototyping to production serving.

-- Step 1: Export model to Cloud Storage
EXPORT MODEL `my_dataset.penguin_classifier`
OPTIONS (
  URI = 'gs://my-bucket/exported_models/penguin_classifier/'
)

-- Step 2: Register in Vertex AI Model Registry (via gcloud)
gcloud ai models upload \
  --region=us-central1 \
  --display-name=penguin-classifier \
  --artifact-uri=gs://my-bucket/exported_models/penguin_classifier/ \
  --container-image-uri=us-docker.pkg.dev/vertex-ai/prediction/tf2-cpu.2-12:latest

-- Step 3: Deploy to an endpoint
gcloud ai endpoints deploy-model ENDPOINT_ID \
  --region=us-central1 \
  --model=MODEL_ID \
  --display-name=penguin-prod \
  --machine-type=n1-standard-4 \
  --min-replica-count=1 \
  --max-replica-count=3

BQML supports automatic hyperparameter tuning using the num_trials option. BigQuery uses a Bayesian optimization strategy to search the hyperparameter space.

-- Hyperparameter tuning with BQML
CREATE OR REPLACE MODEL `my_dataset.tuned_model`
OPTIONS (
  model_type = 'BOOSTED_TREE_CLASSIFIER',
  input_label_cols = ['species'],

  -- Enable HP tuning
  num_trials = 20,                        -- Number of trials to run
  max_parallel_trials = 5,                -- Parallel trials
  hparam_tuning_objectives = ['roc_auc'], -- Metric to optimize

  -- Hyperparameter search ranges (auto-selected if omitted)
  max_tree_depth = hparam_range(3, 10),
  learn_rate = hparam_range(0.01, 0.3),
  subsample = hparam_range(0.5, 1.0),
  l2_reg = hparam_candidates([0, 0.1, 1.0, 10.0])
) AS
SELECT * FROM `my_dataset.training_data`

-- View tuning results
SELECT *
FROM ML.TRIAL_INFO(
  MODEL `my_dataset.tuned_model`
)
ORDER BY roc_auc DESC

Choosing the right ML approach on GCP depends on your team's skills, data complexity, performance requirements, and time constraints.

Model Type Selection Cheat Sheet

Common Exam Scenarios

Key Exam Takeaways

• BQML trains models using SQL with no data movement from BigQuery
• Know all model types and when to use each (especially ARIMA_PLUS, KMEANS, LOGISTIC_REG)
• TRANSFORM clause prevents training-serving skew by embedding preprocessing in the model
• ML.EVALUATE returns different metrics based on model type (classification vs regression)
• ML.EXPLAIN_PREDICT uses Shapley values for feature attribution
• Export BQML models to Vertex AI for online serving (not all types are exportable)
• Use ML.FORECAST (not ML.PREDICT) for ARIMA_PLUS time series models
• Hyperparameter tuning uses num_trials with Bayesian optimization
• Choose precision for "minimize false positives," recall for "minimize false negatives"