Lesson 2: BigQuery ML: matching model types to business problems

Why BigQuery ML is where Section 1 starts

A quick reminder from Level 1: the exam guide is Google's document. It is free on the Professional Machine Learning Engineer certification page, and this course is independent of Google and not endorsed by it. Check the official exam page for length, price and languages.

Item 1.1 of the guide opens with "Building models in BigQuery ML or Agent Platform AutoML (e.g., classification, regression, forecasting, and clustering) based on the business problem." This level covers the BigQuery ML half of that sentence. (Agent Platform is short for Gemini Enterprise Agent Platform, which Google announced in April 2026 as the next version of what used to be Vertex AI. Older study material still says Vertex AI.)

Here is the idea. Your data already lives in BigQuery, and your team already writes SQL. BigQuery ML lets you train a model with one CREATE MODEL statement where the data sits. You don't export it, build a Python environment or manage a training cluster. Think of the warehouse as a kitchen that comes with its own oven: you cook where the ingredients are. Your job on the exam is to pick the right model_type for the business question.

The decision table

A simple rule of thumb: if the answer is a category, it's classification. If it's a number, it's regression. If it's a number in the future, ordered by time, it's forecasting. If there is no answer column at all, it's clustering.

Walkthrough 1: churn classifier

CREATE OR REPLACE MODEL `shop.churn_logreg`
OPTIONS (
  model_type = 'LOGISTIC_REG',        -- binary yes/no outcome
  input_label_cols = ['churned'],     -- the column we want to predict
  auto_class_weights = TRUE           -- churners are rare; rebalance classes
) AS
SELECT tenure_months, monthly_spend, support_tickets, plan_type, churned
FROM `shop.customers`;

Every column except the label becomes a feature. Start with LOGISTIC_REG: it's fast, cheap and easy to explain to a stakeholder. If it underfits, swap in BOOSTED_TREE_CLASSIFIER, which is XGBoost under the hood and handles non-linear interactions well. You change one line of SQL.

SELECT * FROM ML.EVALUATE(MODEL `shop.churn_logreg`);
-- precision | recall | accuracy | f1_score | log_loss | roc_auc
--   0.61    |  0.48  |   0.91   |   0.54   |   0.27   |  0.86

Gotcha: 91% accuracy looks great until you learn that 90% of customers never churn. A model that always says "stays" would score 90%. For rare-event problems, read recall (how many real churners we caught), precision (how many flagged customers really churn) and roc_auc, which ignores the threshold. The precision and recall figures depend on the decision threshold, which defaults to 0.5.

Walkthrough 2: price regressor

CREATE OR REPLACE MODEL `realty.price_linreg`
OPTIONS (model_type = 'LINEAR_REG', input_label_cols = ['sale_price']) AS
SELECT sq_metres, bedrooms, postcode_area, year_built, sale_price
FROM `realty.sales`;

SELECT mean_absolute_error, r2_score
FROM ML.EVALUATE(MODEL `realty.price_linreg`);
-- mean_absolute_error = 18450.0   r2_score = 0.79

Regression output also includes mean_squared_error, mean_squared_log_error, median_absolute_error and explained_variance. For a business audience, mean absolute error is the easiest to explain: "on average we're off by about 18k." r2_score is the share of price variation the model explains. Mean squared error penalises large misses heavily, so use it when one big miss hurts more than several small ones.

Walkthrough 3: sales forecast

CREATE OR REPLACE MODEL `retail.sales_forecast`
OPTIONS (
  model_type = 'ARIMA_PLUS',
  time_series_timestamp_col = 'sale_date',  -- required: when
  time_series_data_col = 'units_sold',      -- required: what to forecast
  time_series_id_col = 'store_id',          -- one model per store, in one statement
  horizon = 30,                             -- periods ahead (default 3)
  holiday_region = 'GB'                     -- add UK holiday effects
) AS
SELECT sale_date, store_id, units_sold FROM `retail.daily_sales`;

ARIMA_PLUS searches over ARIMA candidate settings for you and models seasonality, holidays, and spikes and dips. With time_series_id_col, a single statement fits thousands of series, one per store.

There are two ways to evaluate it:

-- No new data needed: fit diagnostics for the candidate models
SELECT * FROM ML.ARIMA_EVALUATE(MODEL `retail.sales_forecast`);
-- non_seasonal_p | ... | has_drift | log_likelihood | AIC | variance | seasonal_periods

-- Needs held-out actuals: forecast accuracy
SELECT * FROM ML.EVALUATE(MODEL `retail.sales_forecast`,
  (SELECT * FROM `retail.daily_sales_holdout`));
-- mean_absolute_error | mean_squared_error | mean_absolute_percentage_error | symmetric_mean_absolute_percentage_error

Gotcha: Forecasting has no input_label_cols. The timestamp and data columns take their place. MAPE behaves badly when actuals are near zero, such as slow-selling stores, so check SMAPE or MAE as well.

Walkthrough 4: customer segmentation

CREATE OR REPLACE MODEL `shop.segments`
OPTIONS (
  model_type = 'KMEANS',
  num_clusters = 5               -- set explicitly; it's a business choice too
) AS
SELECT recency_days, frequency, monetary_value   -- note: no customer_id, no label
FROM `shop.rfm`;

SELECT * FROM ML.EVALUATE(MODEL `shop.segments`);
-- davies_bouldin_index = 1.12   mean_squared_distance = 2.87

A lower Davies-Bouldin index means the clusters are tighter and better separated. Train with 3, 4, 5 and 6 clusters and compare the scores. Then ask marketing whether the segments are actually usable. If you have five segments and only three campaigns, five clusters may be the wrong answer.

Pitfall: Leave IDs out of the feature list. A customer ID would be treated as a feature and produce nonsense clusters.

Stay in BigQuery ML, or move the data out?

Stay in BigQuery ML when:

• the data is already in BigQuery, especially at large volume, so moving it costs time and adds governance risk;
• the team is strong in SQL but light on Python and ML ops;
• predictions run in batch (nightly churn scores, weekly forecasts), or the model can be registered in Agent Platform Model Registry when it needs serving later;
• a standard model family (linear, boosted trees, ARIMA, k-means) fits the problem.

Move the data out (custom training, Spark, notebooks) when:

• you need an architecture or framework BigQuery ML doesn't offer, such as a custom PyTorch model;
• the core data is unstructured (images, audio) and needs heavy custom preprocessing;
• you need fine control over training loops, distributed GPUs or TPUs, or very low-latency online serving with custom logic.

The trade-off: BigQuery ML gives up flexibility to gain speed, simplicity and governance. Production experience says to start there, set a baseline, and move out only when a measured gap justifies the extra work.

What the exam asks about this

Expect short scenarios like "the data is in BigQuery, the analysts know SQL, they need X." You'll be asked to pick the product (BigQuery ML) and the model_type. Watch for these cues: yes/no outcome means a classifier; a dollar amount means a regressor; dates plus "next quarter" means ARIMA_PLUS; "no labels" or "group similar" means KMEANS. Wrong options often use the right product with the wrong task, or recommend exporting data with no reason to. You may also be asked which metric to trust. On imbalanced classes, accuracy is the trap.

Key takeaways

• One CREATE MODEL ... OPTIONS(model_type=...) statement trains in the warehouse. Choose model_type from the shape of the answer: category, number, future number, or no label.
• Classifiers and regressors need input_label_cols. ARIMA_PLUS needs time_series_timestamp_col and time_series_data_col, with optional time_series_id_col. KMEANS takes no label.
• ML.EVALUATE returns different metrics for each family. Use recall, precision and ROC AUC for rare-event classification, MAE for regression, MAE/MAPE/SMAPE for forecasts, and Davies-Bouldin for clusters.
• ML.ARIMA_EVALUATE returns fit diagnostics (AIC, log likelihood, seasonality) without new data.
• Default to BigQuery ML when the data is in BigQuery, the team writes SQL and the needs are standard. Move out only for custom architectures, unstructured data or specialised serving.