BigQuery ML: Building Machine Learning Models with SQL

BigQuery ML lets analysts build, train, and deploy machine learning models using SQL — no Python environment, no infrastructure management. This guide covers every supported model type with real SQL examples and production deployment patterns.

The traditional machine learning workflow has a lot of moving parts: export data from your warehouse, load it into a notebook, run preprocessing in pandas, train a scikit-learn or TensorFlow model, evaluate it, save the artifact, set up a serving endpoint, and build an ETL pipeline to score new data back into your warehouse. That's six infrastructure pieces before you've answered a single business question.

BigQuery ML (BQML) is Google's answer to this complexity. It lets you train, evaluate, and deploy machine learning models entirely in SQL, directly on the data that's already in BigQuery. For many analytics ML use cases — customer churn prediction, recommendation systems, demand forecasting, anomaly detection — BQML cuts the time from idea to production insight from days to hours.

This guide is for data analysts and analytics engineers who are comfortable with SQL and want to add ML capabilities without becoming a Python ML engineer. We'll cover every major model type BQML supports, with real SQL examples for each, plus how to connect BQML models to Vertex AI for production serving.

Why BigQuery ML Exists and When It Makes Sense

BQML isn't trying to replace TensorFlow or PyTorch for cutting-edge deep learning research. It's designed for the enormous category of ML problems that are solved by well-understood algorithms on tabular data — the kinds of problems that live in every company's analytics stack.

The core advantages:

Data stays in BigQuery: No ETL pipeline to maintain, no data serialization overhead, no data governance headaches from exporting PII to notebooks
SQL as the interface: Your data team can participate in ML work without Python expertise
Automatic preprocessing: BQML handles missing value imputation, feature normalization, and categorical encoding automatically
Versioned models: Models are BigQuery objects you can query, inspect, and roll back like any other table
Built-in evaluation: Standard evaluation metrics are computed automatically after training

When BQML is the right choice:

Tabular data (structured rows and columns)
Classic ML problems: binary classification, regression, clustering, ranking, time series
Teams where SQL skills are more common than Python ML skills
Prototyping before investing in a full MLOps pipeline

When to reach for Vertex AI or custom Python instead:

Computer vision or NLP tasks requiring deep learning
Custom architectures not covered by BQML's supported model types
Very large datasets where distributed training with GPU clusters is necessary
Production serving that requires sub-10ms latency

Supervised Learning: Linear Regression

Let's start with the simplest model type — predicting a continuous numeric value. Suppose you want to predict a customer's lifetime value (LTV) based on their first-week behavior:

-- Step 1: Create the model
CREATE OR REPLACE MODEL marketing.customer_ltv_model
OPTIONS (
  model_type = 'linear_reg',
  input_label_cols = ['ltv_90_days'],
  data_split_method = 'auto_split',  -- Automatically splits train/eval
  l2_reg = 0.1,                      -- L2 regularization to prevent overfitting
  max_iterations = 100,
  learn_rate_strategy = 'line_search'
) AS
SELECT
  -- Label: what we're predicting
  ltv_90_days,

  -- Features: first-week behavior signals
  sessions_week_1,
  pages_viewed_week_1,
  items_added_to_cart_week_1,
  first_purchase_value,
  traffic_source,
  device_category,
  country,

  -- Derived features
  SAFE_DIVIDE(items_added_to_cart_week_1, sessions_week_1) AS cart_rate,
  CASE WHEN first_purchase_value > 0 THEN 1 ELSE 0 END AS converted_week_1
FROM marketing.customer_features
WHERE cohort_date BETWEEN '2024-01-01' AND '2024-09-30'
  AND ltv_90_days IS NOT NULL;

After the model trains (this may take a few minutes for large datasets), evaluate it:

-- Step 2: Evaluate the model
SELECT
  mean_absolute_error,
  mean_squared_error,
  mean_squared_log_error,
  median_absolute_error,
  r2_score,
  explained_variance
FROM ML.EVALUATE(
  MODEL marketing.customer_ltv_model,
  (
    SELECT *
    FROM marketing.customer_features
    WHERE cohort_date BETWEEN '2024-10-01' AND '2024-12-31'
      AND ltv_90_days IS NOT NULL
  )
);

Inspect which features matter most:

-- Step 3: Feature importance
SELECT *
FROM ML.FEATURE_IMPORTANCE(MODEL marketing.customer_ltv_model)
ORDER BY importance_gain DESC;

Apply the model to score new customers:

-- Step 4: Predict on new data
SELECT
  customer_id,
  predicted_ltv_90_days AS expected_ltv,
  -- Confidence interval for the prediction
  predicted_ltv_90_days_lower,
  predicted_ltv_90_days_upper
FROM ML.PREDICT(
  MODEL marketing.customer_ltv_model,
  (
    SELECT
      customer_id,
      sessions_week_1,
      pages_viewed_week_1,
      items_added_to_cart_week_1,
      first_purchase_value,
      traffic_source,
      device_category,
      country,
      SAFE_DIVIDE(items_added_to_cart_week_1, sessions_week_1) AS cart_rate,
      CASE WHEN first_purchase_value > 0 THEN 1 ELSE 0 END AS converted_week_1
    FROM marketing.customer_features
    WHERE cohort_date >= '2025-01-01'
      AND ltv_90_days IS NULL  -- Customers whose LTV we don't know yet
  )
);

Binary Classification: Predicting Customer Churn

Churn prediction is the most common ML use case in B2C analytics. Let's build a logistic regression model:

CREATE OR REPLACE MODEL product.churn_model
OPTIONS (
  model_type = 'logistic_reg',
  input_label_cols = ['churned'],
  data_split_method = 'auto_split',
  auto_class_weights = TRUE,  -- Handles class imbalance automatically
  l1_reg = 0.01,
  max_iterations = 50
) AS
SELECT
  churned,  -- 1 if churned within 30 days, 0 if still active

  -- Engagement features
  sessions_last_30d,
  avg_session_duration_seconds,
  feature_adoption_score,  -- 0-100 score of features used
  support_tickets_last_90d,

  -- Billing features
  current_plan,
  months_since_last_upgrade,
  overdue_invoices,

  -- Success milestone features
  onboarded,
  integrations_connected,
  team_members_invited
FROM product.customer_churn_features
WHERE snapshot_date BETWEEN '2024-01-01' AND '2024-09-30';

For classification models, evaluate with ROC curve and precision/recall metrics:

-- Evaluate classification performance
SELECT *
FROM ML.EVALUATE(
  MODEL product.churn_model,
  (SELECT * FROM product.customer_churn_features WHERE snapshot_date >= '2024-10-01')
);
-- Returns: precision, recall, accuracy, f1_score, log_loss, roc_auc

-- Inspect the ROC curve across thresholds
SELECT *
FROM ML.ROC_CURVE(MODEL product.churn_model,
  (SELECT * FROM product.customer_churn_features WHERE snapshot_date >= '2024-10-01')
)
ORDER BY threshold;

Scoring customers daily:

INSERT INTO product.churn_scores
SELECT
  customer_id,
  CURRENT_DATE() AS score_date,
  predicted_churned_probs[OFFSET(1)].prob AS churn_probability,
  CASE
    WHEN predicted_churned_probs[OFFSET(1)].prob > 0.7 THEN 'high'
    WHEN predicted_churned_probs[OFFSET(1)].prob > 0.4 THEN 'medium'
    ELSE 'low'
  END AS churn_risk_tier
FROM ML.PREDICT(
  MODEL product.churn_model,
  (SELECT * FROM product.customer_churn_features WHERE snapshot_date = CURRENT_DATE())
);

Boosted Trees for High-Accuracy Classification

Logistic regression is interpretable and fast to train. For higher accuracy on complex feature interactions, use XGBoost-based boosted trees:

CREATE OR REPLACE MODEL product.churn_xgb_model
OPTIONS (
  model_type = 'boosted_tree_classifier',
  input_label_cols = ['churned'],
  num_parallel_tree = 4,
  max_tree_depth = 6,
  subsample = 0.8,
  colsample_bytree = 0.8,
  learn_rate = 0.1,
  num_trials = 5,  -- Hyperparameter tuning with 5 trials
  hparam_tuning_objectives = ['roc_auc']
) AS
SELECT * FROM product.customer_churn_features
WHERE snapshot_date BETWEEN '2024-01-01' AND '2024-09-30';

The num_trials parameter triggers automatic hyperparameter tuning — BQML tries different combinations of tree depth, learning rate, and subsampling and selects the best configuration automatically.

K-Means Clustering for Customer Segmentation

Clustering is an unsupervised technique that groups customers by behavioral similarity. It's useful for defining audience segments for marketing, personalization, or product roadmap prioritization.

CREATE OR REPLACE MODEL marketing.customer_segments
OPTIONS (
  model_type = 'kmeans',
  num_clusters = 5,  -- Or use ELBOW method to pick this
  kmeans_init_method = 'KMEANS++',  -- Better initialization than random
  standardize_features = TRUE
) AS
SELECT
  -- Features that define meaningful customer differences
  avg_monthly_spend,
  product_categories_purchased,
  purchase_frequency_30d,
  avg_order_value,
  days_since_first_purchase,
  days_since_last_purchase,
  channel_direct_pct,
  channel_email_pct,
  channel_paid_pct
FROM marketing.customer_rfm_features
WHERE snapshot_date = '2025-01-01';

After training, assign customers to segments and name them based on their centroid characteristics:

SELECT
  customer_id,
  centroid_id AS segment_id,
  -- Inspect what each segment looks like
  nearest_centroids_distance[OFFSET(0)].distance AS segment_distance
FROM ML.PREDICT(
  MODEL marketing.customer_segments,
  (SELECT * FROM marketing.customer_rfm_features WHERE snapshot_date = CURRENT_DATE())
);

-- Inspect centroid values to name segments
SELECT *
FROM ML.CENTROIDS(MODEL marketing.customer_segments)
ORDER BY centroid_id;

The centroids table shows you the average feature values for each cluster. Cluster 1 might have high recency and high value (your VIPs); Cluster 3 might have low recency and declining spend (churn risk). Give them meaningful names in a mapping table.

Time Series Forecasting with ARIMA_PLUS

BQML's ARIMA_PLUS model automatically detects trend, seasonality, and holiday effects. It's designed for cases where you want to forecast multiple time series simultaneously — like forecasting demand for 10,000 SKUs at once.

CREATE OR REPLACE MODEL sales.demand_forecast
OPTIONS (
  model_type = 'arima_plus',
  time_series_timestamp_col = 'sales_date',
  time_series_data_col = 'units_sold',
  time_series_id_col = 'product_sku',  -- One model per SKU
  horizon = 30,                          -- Forecast 30 days ahead
  auto_arima = TRUE,                     -- Automatic parameter detection
  data_frequency = 'DAILY',
  holiday_region = 'NL'                  -- Netherlands holiday calendar
) AS
SELECT
  sales_date,
  product_sku,
  SUM(units_sold) AS units_sold
FROM sales.daily_sales
WHERE sales_date >= '2023-01-01'
GROUP BY 1, 2;

Generate forecasts:

SELECT
  forecast_timestamp,
  product_sku,
  forecast_value AS predicted_units,
  prediction_interval_lower_bound,
  prediction_interval_upper_bound,
  confidence_level
FROM ML.FORECAST(
  MODEL sales.demand_forecast,
  STRUCT(30 AS horizon, 0.9 AS confidence_level)
)
ORDER BY product_sku, forecast_timestamp;

Matrix Factorization for Recommendations

If you have user-item interaction data (purchases, ratings, clicks), BQML's matrix factorization model can generate product recommendations:

CREATE OR REPLACE MODEL product.item_recommendations
OPTIONS (
  model_type = 'matrix_factorization',
  user_col = 'user_id',
  item_col = 'product_id',
  rating_col = 'implicit_rating',  -- Or explicit rating
  feedback_type = 'implicit',      -- No explicit star ratings
  num_factors = 50,
  l2_reg = 0.1,
  wals_alpha = 40  -- Weight for implicit feedback
) AS
SELECT
  user_id,
  product_id,
  -- Implicit rating: more interactions = stronger signal
  LOG(1 + view_count + 3 * add_to_cart_count + 10 * purchase_count) AS implicit_rating
FROM product.user_item_interactions
WHERE interaction_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);

Generate top-5 recommendations per user:

SELECT
  user_id,
  ARRAY_AGG(product_id ORDER BY predicted_rating_implicit DESC LIMIT 5) AS recommended_products
FROM ML.RECOMMEND(MODEL product.item_recommendations)
GROUP BY user_id;

Using AutoML Tables for Complex Tabular Problems

For problems where you're not sure which algorithm will work best, BQML's AutoML Tables model runs automatic hyperparameter tuning across multiple algorithm types:

CREATE OR REPLACE MODEL fraud.transaction_classifier
OPTIONS (
  model_type = 'automl_classifier',
  input_label_cols = ['is_fraud'],
  budget_hours = 1.0  -- Training budget in hours (higher = more exploration)
) AS
SELECT *
FROM fraud.transaction_features
WHERE transaction_date BETWEEN '2024-01-01' AND '2024-10-01';

AutoML Tables tries many models internally and returns the best one. Budget 1-3 hours for a thorough search; 0.5 hours for quick prototyping.

Model Explainability

Understanding why a model makes a specific prediction is critical for regulated industries and trust-building with stakeholders:

-- Global feature importance
SELECT feature, importance_gain, importance_weight, importance_cover
FROM ML.GLOBAL_EXPLAIN(MODEL product.churn_xgb_model)
ORDER BY importance_gain DESC;

-- Local explanation: why did THIS customer get this score?
SELECT
  customer_id,
  predicted_churned_probs[OFFSET(1)].prob AS churn_probability,
  top_feature_attributions
FROM ML.EXPLAIN_PREDICT(
  MODEL product.churn_xgb_model,
  (SELECT * FROM product.customer_churn_features WHERE customer_id = 'cust_12345'),
  STRUCT(3 AS top_k_features)  -- Show top 3 contributing features
);

Exporting BQML Models to Vertex AI

For production serving with low-latency REST API access, export your BQML model to Vertex AI:

# Export model artifacts to Cloud Storage
bq extract   --model   --destination_format=ML_TF_SAVED_MODEL   my-project:product.churn_xgb_model   gs://my-project-models/churn-xgb/

# Deploy to Vertex AI Endpoint
gcloud ai models upload   --region=europe-west4   --display-name=churn-xgb-v1   --artifact-uri=gs://my-project-models/churn-xgb/   --container-image-uri=europe-docker.pkg.dev/vertex-ai/prediction/tf2-cpu.2-11:latest

gcloud ai endpoints deploy-model ENDPOINT_ID   --region=europe-west4   --model=MODEL_ID   --display-name=churn-xgb-v1   --traffic-split=0=100

Once deployed, you can call the Vertex AI endpoint from your application for real-time scoring, while still using BQML directly in BigQuery for batch scoring of the full customer base.

Operationalizing BQML with Scheduled Queries

The real power of BQML in production is combining models with BigQuery's scheduled query feature:

-- This scheduled query runs daily to score all customers
DECLARE score_date DATE DEFAULT CURRENT_DATE();

INSERT INTO product.churn_scores (customer_id, score_date, churn_probability, churn_risk_tier)
SELECT
  customer_id,
  score_date,
  predicted_churned_probs[OFFSET(1)].prob AS churn_probability,
  CASE
    WHEN predicted_churned_probs[OFFSET(1)].prob > 0.7 THEN 'high'
    WHEN predicted_churned_probs[OFFSET(1)].prob > 0.4 THEN 'medium'
    ELSE 'low'
  END AS churn_risk_tier
FROM ML.PREDICT(
  MODEL product.churn_model,
  (
    SELECT * FROM product.customer_churn_features
    WHERE snapshot_date = score_date
  )
);

Schedule this to run every morning at 6am, and your CRM team can start their day with fresh churn scores ready in Salesforce (via a Sheets connector or direct API push).

Cost Considerations for BQML

BQML training cost varies dramatically by model type:

Model Type	Typical Training Cost
Linear/Logistic Regression	$0.10 - $2
XGBoost Boosted Trees	$0.50 - $5
K-Means Clustering	$0.10 - $1
ARIMA_PLUS (time series)	$0.25 per 1000 time series
AutoML Tables (1 hour)	~$19.12 per hour
Matrix Factorization	$0.50 - $3

Prediction (scoring) costs are the same as regular BigQuery queries — you pay for bytes processed. A daily scoring job for 1 million customers on a 20-feature model typically processes under 500 MB, costing pennies.

The most expensive option by far is AutoML Tables. Use it when nothing else is working, or when you have a complex feature space and need maximum accuracy. For most problems, boosted trees with hyperparameter tuning gives 90% of AutoML's performance at 5% of the cost.

A Practical Workflow for New BQML Projects

Start with your analytics table: Make sure the training data is already in BigQuery with clean, well-defined feature columns.
Prototype with linear models: Logistic regression and linear regression train fast and are easy to explain to stakeholders.
Move to boosted trees if needed: They consistently outperform linear models on tabular data.
Use data_split_method = 'auto_split': BQML will automatically hold out 20% of your data for evaluation.
Check feature importance: It often reveals data quality issues (e.g., a target leakage column with suspiciously high importance).
Schedule scoring in BigQuery: Daily batch scoring is cheap and keeps predictions fresh.
Export to Vertex AI if you need real-time serving.

BigQuery ML removes the barrier between your data and your ML capabilities. For organizations with strong SQL talent and tabular data problems, it's often the most practical path to production ML.

For broader GCP ML capabilities, see our Vertex AI MLOps guide for production pipelines, and our streaming data guide for applying these models on streaming inputs.