Building the appropriate BigQuery ML model based on the business problem

A machine learning engineer working inside a Google Cloud data platform inherits a hard constraint: the training data already sits in BigQuery, and the team that owns the warehouse expects the predictions to land back in BigQuery for downstream BI tools. Exporting that data into a Vertex AI custom training container, training a scikit-learn model, then writing the predictions back into BigQuery is a multi-hop pipeline. BigQuery ML compresses the whole loop into SQL: the same warehouse hosts the features, runs the training, stores the model artifact, and serves the predictions. The exam tests whether you reach for that compression instinctively when the problem maps onto a model_type BigQuery ML already implements.

The model selection question is mechanical once you know the catalog. Binary classification problems (will a customer churn, will an invoice be paid late) map to LOGISTIC_REG. Multiclass problems with structured outcomes map to either LOGISTIC_REG again with multi-class labels or to BOOSTED_TREE_CLASSIFIER. Continuous numeric targets (basket size, response time, expected lifetime value) map to LINEAR_REG for an interpretable baseline or BOOSTED_TREE_REGRESSOR for non-linear lift. Time-series forecasting maps to ARIMA_PLUS for univariate series and ARIMA_PLUS_XREG when external regressors are present. Customer-item recommendation maps to MATRIX_FACTORIZATION. Unsupervised clustering for segmentation maps to KMEANS. Anomaly detection on tabular vectors maps to AUTOENCODER. Random forests are available via RANDOM_FOREST_CLASSIFIER and RANDOM_FOREST_REGRESSOR. Deep neural networks are reachable through DNN_CLASSIFIER, DNN_REGRESSOR, DNN_LINEAR_COMBINED_CLASSIFIER, and DNN_LINEAR_COMBINED_REGRESSOR (the wide-and-deep variant).

The interesting selection trap is the boundary between BigQuery ML and AutoML Tabular. Both can solve a regression or classification problem on warehouse data. BigQuery ML trains one model family per CREATE MODEL statement; AutoML Tabular runs a neural-architecture-search pipeline that ensembles several model families and picks the best per metric. The decision turns on three signals: How much engineering time is the team willing to invest, how important is interpretability, and how big is the dataset. A logistic regression in BigQuery ML trains in minutes, returns weight coefficients, and can be tuned by hand. AutoML Tabular trains for hours, returns no weights, but typically lifts AUC by several points on the same data because it explores XGBoost, deep networks, and shallow networks together.

Time-series modelling is the second high-frequency exam topic. ARIMA_PLUS extends classical ARIMA with automatic seasonality detection (daily, weekly, monthly, yearly), holiday effects (configurable per region via HOLIDAY_REGION), and outlier handling. When the input series contains gaps, ARIMA_PLUS interpolates inside the configured window; when the series ends mid-period, the model forecasts forward by horizon steps. The auto_arima flag (default TRUE) sweeps (p, d, q) combinations and picks the AIC-optimal model. For demand forecasting across thousands of SKUs, the TIME_SERIES_ID_COL parameter trains one ARIMA model per ID in a single statement and exposes them through a single ML.FORECAST call.

Matrix factorization in BigQuery ML implements the classic collaborative-filtering decomposition: users and items each map to latent factor vectors, and the dot product approximates the rating. Two flavors exist: explicit (the user rated the item on a numeric scale; the loss is squared error) and implicit (the user merely interacted; the loss is weighted alternating least squares with a confidence weight). The feedback_type option toggles between them. The output is reachable through ML.RECOMMEND, which returns top-k items per user without you ever writing a join. Boosted trees follow XGBoost semantics and accept the standard hyperparameters (MAX_TREE_DEPTH, L1_REG, L2_REG, LEARN_RATE, SUBSAMPLE, COLSAMPLE_BYTREE) plus the BigQuery-specific TREE_METHOD (HIST for histogram-based splitting on very large datasets). Autoencoders are the only unsupervised reconstruction-loss model in BigQuery ML and exist explicitly for anomaly detection: train on the in-distribution slice, score new rows with ML.RECONSTRUCTION_LOSS, alert above a threshold.

💡 Exam Trap: LOGISTIC_REG covers BOTH binary and multiclass classification in BigQuery ML. There is no separate MULTICLASS_LOGISTIC_REG model type. The exam often presents a multiclass problem and lists MULTINOMIAL_LOGISTIC_REG as a distractor; that string is not a valid model type.

💡 Exam Trap: ARIMA_PLUS requires a single timestamp column and a single value column when TIME_SERIES_ID_COL is unset. Feeding a wide table with one column per metric returns a syntax error; either pivot the data first or specify the ID column with the long-format table.

📊 Limit: A MATRIX_FACTORIZATION model with feedback_type='IMPLICIT' requires the flat-rate or autoscaling reservation pricing model. On the on-demand pricing model the statement returns the error MATRIX_FACTORIZATION requires reservation.

⚠️ Anti-Pattern: Training a deep neural network with DNN_CLASSIFIER on a 50,000-row tabular dataset to "see if it beats logistic regression." The data volume is below the practical threshold for DNN lift, the training cost is much higher, and the resulting model is harder to explain. Start with LOGISTIC_REG, measure, then escalate.

🎯 Scenario: A retail analytics team needs to forecast next-quarter SKU demand across 12,000 products. The data sits in BigQuery as one row per SKU per day. The team is comfortable in SQL and does not want to manage VMs. The right pattern is one ARIMA_PLUS model with TIME_SERIES_ID_COL='sku_id'. BigQuery ML trains 12,000 sub-models in one statement and ML.FORECAST returns the per-SKU forecast in one call.

Using AutoML for tabular data

AutoML Tabular is reached from two surfaces: the Vertex AI Console's Tabular workflow, and BigQuery ML via model_type='AUTOML_CLASSIFIER' / AUTOML_REGRESSOR. The BigQuery ML wrapper is the lower-friction path because it accepts the same CREATE MODEL syntax and reads training data from a BigQuery table without an export step. Behind the scenes, the wrapper provisions a Vertex AI Tabular Workflows pipeline, runs neural architecture search across logistic regression, deep networks, deep-and-wide, and gradient-boosted tree families, ensembles the top candidates, and materializes the resulting model into a BigQuery ML model handle. The handle behaves like any other BigQuery ML model: ML.PREDICT, ML.EVALUATE, and ML.EXPLAIN_PREDICT all work, and the model can be exported to a Vertex AI Model Registry entry for online serving.

CREATE OR REPLACE MODEL `acme.models.churn_automl`
OPTIONS(
 model_type = 'AUTOML_CLASSIFIER',
 input_label_cols = ['churned'],
 budget_hours = 2.0,
 optimization_objective = 'maximize-au-roc'
) AS
SELECT
 customer_tenure_days,
 monthly_spend_usd,
 support_tickets_30d,
 plan_tier,
 churned
FROM `acme.retail.customer_features`
WHERE feature_date < '2024-09-01';

The budget_hours option caps wall-clock training spend. The minimum allowed value is 1.0 hour for AUTOML_CLASSIFIER and AUTOML_REGRESSOR. The optimization_objective accepts metrics such as maximize-au-roc, maximize-au-prc, minimize-log-loss for classification, and minimize-rmse, minimize-mae, minimize-rmsle for regression. The objective drives the leaderboard during architecture search; choose to match the business cost of error rather than defaulting to AU-ROC for every classifier.

💡 Exam Trap: AutoML Tabular through BigQuery ML respects the excluded_columns and input_label_cols options, but it does NOT respect a manually configured TRANSFORM clause. AutoML applies its own preprocessing internally. Wrapping the training query in a CTE that pre-computes features is the supported workaround.

Decision Anchor. Choose BigQuery ML native model types (LOGISTIC_REG, LINEAR_REG, BOOSTED_TREE_*, DNN_*, ARIMA_PLUS, MATRIX_FACTORIZATION, KMEANS, AUTOENCODER, RANDOM_FOREST_*) when you want a single SQL artifact, interpretable coefficients (for linear and logistic), or a known model family. Choose AUTOML_CLASSIFIER / AUTOML_REGRESSOR when the team is willing to spend hours of training time for a few extra points of AUC and does not need to inspect the model architecture. Choose Vertex AI custom training when the problem requires an architecture BigQuery ML does not host (transformers, custom loss functions, multi-input image-plus-tabular fusion).