BigQuery ML: Training Models Without Leaving SQL

The Problem: Why Should You Have to Leave Your Data to Train a Model?

You’ve been there. You have a massive table in BigQuery — millions of rows, terabytes of data — and you want to train a simple logistic regression. The old way? Export a CSV, download it to your laptop, load it into a Python notebook, clean it, train a model, then figure out how to get predictions back into your data warehouse. It’s slow, expensive, and error-prone. The export alone can take hours. The download can crash your internet. The notebook environment might not have enough memory.

What if you could just write a SQL query instead?

That’s the promise of BigQuery ML (BQML). Instead of moving your data to your ML tool, you bring ML to your data. You write CREATE MODEL instead of calling sklearn.fit(). You evaluate with ML.EVALUATE() instead of sklearn.metrics. You predict with ML.PREDICT() instead of model.predict(). All inside BigQuery, without ever leaving SQL.

This isn’t just about convenience — it’s about speed of iteration. Google’s official docs say BQML “increases the speed of model development and innovation by removing the need to move data from the data warehouse” (source: Introduction to ML in BigQuery). An InfoWorld article frames it as “predictive AI without ML training” — you don’t need to be a machine learning engineer to train a model (source: InfoWorld).

By the end of this tutorial, you’ll have trained a real model with about 10 lines of SQL. No Python. No data export. Just you, your data, and a query editor.

What Is BigQuery ML? (Intuition First, Formalism Later)

Think of BigQuery ML as a SQL extension that lets you train machine learning models the same way you compute a moving average. You already know how to write AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) to get a 7-day moving average. Now imagine writing CREATE MODEL to train a regression model. Same environment. Same syntax. Same familiar workflow.

Here’s what BQML actually is: Google’s managed ML service inside BigQuery. You write SQL; it handles the training infrastructure — spinning up compute, distributing the work, tuning hyperparameters (if you ask it to), and storing the model as a first-class BigQuery object.

What models can you train? The list is impressive:

• Linear and logistic regression
• K-means clustering
• Matrix factorization (for recommendation systems)
• ARIMA time series forecasting
• Boosted trees (XGBoost under the hood)
• Deep neural networks (via Vertex AI integration)
• Custom imported models (TensorFlow, ONNX, scikit-learn via Vertex AI)

(source: Introduction to ML in BigQuery)

What about pricing? You pay for the BigQuery slots used during training — either on-demand (per TB processed) or flat-rate (reserved slots). Training a small model on a few MB of data costs pennies. A large model on terabytes can cost hundreds of dollars. (source: OWOX blog)

This is the hardest part: letting go of the idea that ML requires a separate codebase. You’re used to Python, scikit-learn, and Jupyter notebooks. BQML asks you to trust SQL for ML. It feels weird at first. But once you see how fast you can iterate — changing a feature and re-running a query in seconds — you’ll start to see the appeal.

The Five-Step Process: From Data to Predictions in SQL

Every ML workflow follows the same pattern: load data, preprocess, train, evaluate, predict. BQML maps each step to a SQL statement. Here’s the mental model:

Step	SQL	What it does
1. Load data	SELECT ... FROM	Get your training data from a table or view
2. Preprocess	TRANSFORM clause or CTE	Feature engineering — scaling, encoding, creating new features
3. Create model	CREATE OR REPLACE MODEL	Train the model with OPTIONS(model_type='...')
4. Evaluate	ML.EVALUATE()	Get metrics like R², precision, recall, ROC AUC
5. Predict	ML.PREDICT()	Score new data

(source: Analytics Vidhya)

Here’s the skeleton — just enough to see the pattern:

-- Step 1 & 2: Load and preprocess (combined in a CTE)
WITH training_data AS (
  SELECT
    feature1,
    feature2,
    label
  FROM `bigquery-public-data.some_dataset.table`
  WHERE _PARTITIONTIME BETWEEN '2023-01-01' AND '2023-12-31'
)

-- Step 3: Create model
CREATE OR REPLACE MODEL `my_dataset.my_model`
OPTIONS(
  model_type='linear_reg',
  input_label_cols=['label']
) AS
SELECT * FROM training_data;

-- Step 4: Evaluate
SELECT *
FROM ML.EVALUATE(MODEL `my_dataset.my_model`);

-- Step 5: Predict
SELECT *
FROM ML.PREDICT(MODEL `my_dataset.my_model`,
  (
    SELECT *
    FROM `bigquery-public-data.some_dataset.new_data`
  )
);

This is the hardest part: choosing the right model_type and hyperparameters. BQML’s defaults are sensible — you can get a working model with zero tuning. But if you want better performance, you can specify MAX_ITERATIONS, LEARN_RATE, L1_REG, L2_REG, and more. Start with defaults, then tune only if needed.

Walkthrough Part 1: Logistic Regression — Predicting Purchases from Web Analytics

Let’s make this real. We’ll use the official Google Analytics sample dataset to predict whether a website visitor will make a purchase. This is the canonical BQML tutorial — you’ll see it referenced everywhere.

Dataset: bigquery-public-data.google_analytics_sample.ga_sessions_* (August 2016 through June 2017). This table contains web session data: page views, traffic source, device info, and whether a transaction occurred.

Label: IF(totals.transactions IS NULL, 0, 1) AS label — binary classification: 1 if the session had at least one transaction, 0 otherwise.

Features: operating system, whether the visitor is on mobile, country, and number of page views.

Let’s train the model:

-- Step 1 & 2: Load and preprocess training data
-- We use sessions from August 2016 through June 2017 for training
-- and July 2017 for evaluation (a simple time-based split)

-- Step 3: Create the logistic regression model
CREATE OR REPLACE MODEL `bqml_tutorial.purchase_model`
OPTIONS(
  model_type='logistic_reg',
  input_label_cols=['label'],
  auto_class_weights=TRUE
) AS
SELECT
  IF(totals.transactions IS NULL, 0, 1) AS label,
  device.operatingSystem AS os,
  device.isMobile AS is_mobile,
  geoNetwork.country AS country,
  totals.pageviews AS pageviews
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20160801' AND '20170630'
  AND totals.visits = 1;

What’s happening here?

• model_type='logistic_reg' tells BQML to train a logistic regression (binary classification)
• input_label_cols=['label'] specifies which column is the target
• auto_class_weights=TRUE handles class imbalance — purchases are rare, so this helps the model not just predict “no purchase” for everyone
• The WHERE clause filters to only include visits (not page views within a session) and uses a time-based split

Now let’s evaluate:

-- Step 4: Evaluate the model
SELECT *
FROM ML.EVALUATE(MODEL `bqml_tutorial.purchase_model`);

You’ll see a table with these metrics:

• precision: Of all sessions the model predicted would purchase, what fraction actually did?
• recall: Of all sessions that actually purchased, what fraction did the model catch?
• accuracy: Overall fraction of correct predictions
• f1_score: Harmonic mean of precision and recall
• log_loss: How confident the model is in its wrong predictions (lower is better)
• roc_auc: The probability that the model ranks a random purchase session higher than a random non-purchase session

(source: Hevo Data)

Interpreting the numbers:

Let’s say your output shows roc_auc = 0.72. What does that mean in plain English? Imagine you have two sessions: one that resulted in a purchase and one that didn’t. If you randomly pick one of each, the model is 72% likely to give a higher predicted probability to the purchase session. Random guessing would be 50%. Perfect would be 100%. So 0.72 is decent — better than random, but not great. It means the model has learned some signal, but there’s plenty of room for improvement.

Now let’s use the model to predict on new data:

-- Step 5: Predict on July 2017 data
SELECT
  country,
  SUM(predicted_label) AS predicted_purchases,
  COUNT(*) AS total_sessions,
  ROUND(SUM(predicted_label) / COUNT(*) * 100, 2) AS predicted_purchase_rate_pct
FROM ML.PREDICT(MODEL `bqml_tutorial.purchase_model`,
  (
    SELECT
      device.operatingSystem AS os,
      device.isMobile AS is_mobile,
      geoNetwork.country AS country,
      totals.pageviews AS pageviews
    FROM
      `bigquery-public-data.google_analytics_sample.ga_sessions_*`
    WHERE
      _TABLE_SUFFIX = '20170701'
      AND totals.visits = 1
  )
)
GROUP BY country
ORDER BY predicted_purchase_rate_pct DESC
LIMIT 10;

This query scores every session from July 1, 2017, then groups by country to see which regions have the highest predicted purchase rates. You might find that certain countries have much higher predicted rates — that’s actionable insight for your marketing team.

(source: Create Machine Learning Model tutorial)

Walkthrough Part 2: Linear Regression — Predicting Penguin Weight

Let’s switch to a regression problem to show BQML handles both classification and regression. We’ll use the penguins dataset from bigquery-public-data.ml_datasets.penguins — it’s more intuitive than web analytics.

Target: body_mass_g (continuous — penguin weight in grams)

Features: species, island, bill length, bill depth, flipper length, sex

-- Step 1 & 2: Load and preprocess training data
-- Step 3: Create the linear regression model
CREATE OR REPLACE MODEL `bqml_tutorial.penguin_weight_model`
OPTIONS(
  model_type='linear_reg',
  input_label_cols=['body_mass_g']
) AS
SELECT
  body_mass_g,
  species,
  island,
  bill_length_mm,
  bill_depth_mm,
  flipper_length_mm,
  sex
FROM
  `bigquery-public-data.ml_datasets.penguins`
WHERE
  body_mass_g IS NOT NULL;

What’s happening here?

• model_type='linear_reg' tells BQML to train a linear regression (continuous target)
• input_label_cols=['body_mass_g'] specifies the target column
• We filter out rows where the target is null

Now evaluate:

-- Step 4: Evaluate the model
SELECT *
FROM ML.EVALUATE(MODEL `bqml_tutorial.penguin_weight_model`);

Your output will include:

• mean_absolute_error (MAE): On average, how many grams off is the prediction? Lower is better.
• mean_squared_error (MSE): Squared error (penalizes large errors more). Lower is better.
• r2_score: The proportion of variance in penguin weight explained by the model. Ranges from 0 to 1 (or negative if the model is worse than guessing the mean).

Interpreting the numbers:

Let’s say your output shows r2_score = 0.76 and mean_absolute_error = 250. In plain English: the model explains 76% of the variation in penguin weight — not bad for a few features. On average, predictions are off by about 250 grams. A typical penguin weighs 4,000–5,000 grams, so 250 grams is about 5–6% error. That’s reasonable.

Now let’s use ML.EXPLAIN_PREDICT to see which features matter most for a specific prediction:

-- Step 5: Explain a specific prediction
SELECT *
FROM ML.EXPLAIN_PREDICT(MODEL `bqml_tutorial.penguin_weight_model`,
  (
    SELECT
      'Adelie' AS species,
      'Torgersen' AS island,
      40.0 AS bill_length_mm,
      18.0 AS bill_depth_mm,
      190.0 AS flipper_length_mm,
      'MALE' AS sex
  ),
  STRUCT(3 AS top_k_features)
);

This returns the predicted weight plus the contribution of each feature. You might see that flipper_length_mm has the largest positive contribution — longer flippers predict heavier penguins. That’s intuitive: bigger birds have longer flippers.

And ML.GLOBAL_EXPLAIN gives you overall feature importance across the entire dataset:

-- Global feature importance
SELECT *
FROM ML.GLOBAL_EXPLAIN(MODEL `bqml_tutorial.penguin_weight_model`);

Here’s a surprise you might find: flipper length is the strongest predictor of weight, not species. You might expect that “Gentoo” (the largest species) would be the most important feature, but the model learns that flipper length is a better continuous predictor. That’s the kind of insight you get from model interpretability.

(source: Linear Regression Tutorial)

Beyond Linear Models: Boosted Trees, Time Series, and AutoML

BQML isn’t just for simple linear models. It supports powerful algorithms that can handle complex patterns.

Boosted Tree Classifier

For classification problems with non-linear relationships, use BOOSTED_TREE_CLASSIFIER. This is XGBoost under the hood. Here’s an example using the census income dataset:

CREATE OR REPLACE MODEL `bqml_tutorial.income_model`
OPTIONS(
  model_type='BOOSTED_TREE_CLASSIFIER',
  input_label_cols=['income_bracket'],
  MAX_ITERATIONS=50,
  SUBSAMPLE=0.8,
  TREE_METHOD='HIST'
) AS
SELECT
  IF(income > 50000, '>50K', '<=50K') AS income_bracket,
  age,
  workclass,
  education_num,
  marital_status,
  occupation,
  relationship,
  race,
  sex,
  capital_gain,
  capital_loss,
  hours_per_week,
  native_country
FROM
  `bigquery-public-data.ml_datasets.census_adult_income`;

Key hyperparameters:

• MAX_ITERATIONS: Number of trees (default 50)
• SUBSAMPLE: Fraction of data used per tree (default 0.8)
• TREE_METHOD: Algorithm for building trees (‘HIST’ for histogram-based, faster on large data)

(source: Boosted Tree Classifier Tutorial)

Time Series Forecasting (ARIMA_PLUS)

For forecasting, use ARIMA_PLUS. This is incredibly powerful — you can forecast without any ML expertise:

CREATE OR REPLACE MODEL `bqml_tutorial.sales_forecast`
OPTIONS(
  model_type='ARIMA_PLUS',
  time_series_timestamp_col='date',
  time_series_data_col='sales',
  time_series_id_col='product_id'
) AS
SELECT
  date,
  product_id,
  sales
FROM
  `my_dataset.daily_sales`;

Then forecast:

SELECT *
FROM ML.FORECAST(MODEL `bqml_tutorial.sales_forecast`,
  STRUCT(30 AS horizon)  -- forecast 30 days ahead
);

AutoML via Vertex AI

For the most complex problems, BQML can delegate to Vertex AI AutoML. You use model_type='AUTOML_TABLES' and Vertex AI searches over architectures automatically. This is the most expensive option but often gives the best performance.

This is the hardest part: knowing when to use which model. Start with linear models — they’re fast, interpretable, and often good enough. If performance is insufficient, escalate to boosted trees. If you need forecasting, use ARIMA_PLUS. Only use AutoML if you have a large budget and need every last percentage point of accuracy.

(source: Predictive Marketing Analytics blog)

Deploying and Using Your Model in Production

Training is only half the story. How do you actually use the model in production?

Scheduled Queries

You can schedule ML.PREDICT queries to run daily, scoring new data automatically. In the BigQuery console, click “Schedule” and set a recurring query. This is the simplest deployment — no infrastructure to manage.

Vertex AI Model Registry

BQML models are automatically registered in Vertex AI Model Registry. From there, you can deploy them to a Vertex AI endpoint for low-latency serving. This is useful if your application needs predictions in milliseconds rather than seconds.

Looker Integration

You can wrap ML.PREDICT in a LookML sql_create parameter to make predictions available in Looker dashboards. Here’s a snippet:

view: purchase_prediction {
  sql_create: {% 
    SELECT *
    FROM ML.PREDICT(MODEL `bqml_tutorial.purchase_model`,
      (
        SELECT *
        FROM ${current_session_table.SQL_TABLE_NAME}
      )
    )
  %} ;;

  dimension: predicted_label {}
  dimension: country {}
  measure: predicted_purchase_rate {
    type: average
    sql: ${predicted_label} ;;
  }
}

(source: Datatonic blog)

Cost Considerations

• Training costs: On-demand pricing charges per TB processed. A small model on a few MB costs pennies. A large model on terabytes can cost hundreds.
• Prediction costs: ML.PREDICT is billed like any other query — per TB processed.
• Flat-rate pricing: If you have reserved slots, training and prediction use those slots with no additional per-query cost.

This is the hardest part: operationalizing ML without a dedicated MLOps team. BQML reduces the barrier, but you still need to monitor data drift (when the patterns in new data differ from training data) and retrain periodically. Set up alerts for when model performance drops below a threshold.

(source: OWOX blog)

When Should You (and Shouldn’t You) Use BigQuery ML?

Let’s be honest: BQML is great for many use cases, but not all.

Good Fit ✅

• Tabular data already in BigQuery — this is the sweet spot. If your data lives in BigQuery, BQML is the fastest path to a model.
• Simple to moderate complexity models — linear regression, logistic regression, boosted trees. If a basic model works, BQML is perfect.
• Quick prototyping — you can go from idea to predictions in minutes. Great for exploration and proof-of-concept.
• Teams without Python expertise — analysts who know SQL can now train models. This democratizes ML.

Bad Fit ❌

• Deep learning on images, text, or audio — BQML doesn’t support these natively. Use Vertex AI or custom TensorFlow models.
• Custom loss functions — you can’t define your own loss function in SQL. If you need a custom objective, use Python.
• Complex feature engineering pipelines — BQML’s TRANSFORM clause handles basic preprocessing, but complex pipelines (e.g., custom embeddings, text vectorization) are better done in Python.
• Models requiring GPU training — BQML runs on CPUs. For GPU-accelerated training (deep learning), use Vertex AI or custom infrastructure.

Hybrid Approach

Use BQML for initial exploration and simple models. If you need more power, export the data to Vertex AI for more complex models. This gives you the best of both worlds: fast iteration for simple problems, full flexibility for hard ones.

(source: InfoWorld)

What You Learned

Let’s recap the key takeaways:

1. BigQuery ML lets you train, evaluate, and predict with SQL — no data export, no Python environment, no separate training cluster.
2. The five-step process is: load data → preprocess → create model → evaluate → predict. Each step maps to a SQL statement.
3. Supported model types include linear/logistic regression, boosted trees, ARIMA time series, k-means, matrix factorization, and AutoML via Vertex AI.
4. Models are deployable via scheduled queries, Looker dashboards, or Vertex AI endpoints.
5. BQML is best for tabular data already in BigQuery — for deep learning or custom pipelines, use Python/Vertex AI.

Next in this series, we’ll explore Vertex AI AutoML — for when you need more power than BQML provides, especially for image, text, and video data.

Check Your Understanding

Remember: What SQL keyword replaces sklearn.fit() in BigQuery ML?

Understand: In plain English, what does an ROC AUC of 0.72 mean?

Apply: Write a CREATE MODEL statement for a linear regression predicting house price from square footage and number of bedrooms. Assume your training data is in my_dataset.house_data with columns price, sqft, bedrooms.

Analyze: Given ML.EVALUATE output showing r2_score = 0.76 and mean_absolute_error = 250 for the penguin model, is the model good enough to deploy? What would you check next?

Evaluate: Compare the BQML workflow to a traditional Python-based workflow. When would you choose one over the other?

Create: Design a BQML pipeline for a real dataset you work with. What model type would you use, what features, and how would you deploy predictions?

Related Articles

• Part 1: What AutoML Actually Automates (and What It Still Can’t) — sets the stage for why automated ML matters.
• Part 2: Google Cloud AutoML vs. Azure AutoML vs. AWS SageMaker Autopilot: Choosing Your AutoML Platform — compares cloud AutoML platforms, including Vertex AI which integrates with BQML.
• Part 3: Auto-Sklearn and H2O AutoML: Open-Source AutoML You Can Run Locally — covers open-source alternatives for when you can’t use cloud services.
• Part 5 (upcoming): Deploying AutoML Models to Production — what to do after training.