Lab 03 — Warehouse-Native ML (BigQuery-ML style)

Phase 27 · Lab 03 · Phase README · Warmup

The problem

The classic ML workflow exports data out of the warehouse — CSVs to a training box, a Python environment, a serving stack — and every hop is a place for preprocessing to drift, PII to leak, and pipelines to rot. BigQuery ML's counter-move: bring the model to the data. Training is a SQL statement over a table (CREATE MODEL ... AS SELECT ...), scoring is a query (ML.PREDICT), metrics are a query (ML.EVALUATE), and — the underrated part — preprocessing can be declared in a TRANSFORM clause that is stored inside the model, so serving re-applies exactly the preprocessing training used. That last piece is a structural fix for training-serving skew: you cannot forget to scale a feature at predict time, because the scaler travels with the model.

The pieces you must understand mechanically, not just by name:

1. Deterministic training. Logistic regression by full-batch gradient descent with fixed (zero) initialization and fixed hyperparameters: same table in, same weights out — every run, every machine. Reproducibility is a choice you engineer, not luck.
2. Fit-on-train-only preprocessing. The z-score's mean/std and the one-hot vocabulary come from the training rows. Re-fitting on serving data (or the test set) is leakage's quieter sibling; an unseen category at serving time must degrade gracefully (all-zeros), never crash.
3. A hash-based train/test split. MOD(ABS(FARM_FINGERPRINT(key)), 10) < 8 — the warehouse idiom for a split that is stable across runs and across dataset growth, because each row's side depends only on its own key, never on a shuffle order.
4. Metrics from the confusion matrix. Accuracy, precision, recall, F1 — computed on a table where you can hand-check every cell.

What you build

File map

Run it

pytest test_lab.py -v                       # your lab.py (red until you implement)
LAB_MODULE=solution pytest test_lab.py -v   # the reference (green)
python solution.py                          # worked example

Success criteria

• hash_split is deterministic, partitions every row, and a row never switches sides when more rows are added (it's a hash of the key, not a shuffle).
• Transform.fit learns mean/std (population) and a sorted one-hot vocabulary from training rows only; a constant column z-scores to 0.0 instead of dividing by zero.
• An unseen category at predict time is all-zeros, not an error; a missing numeric column is a SchemaError.
• create_model trains byte-identical weights on the same data (zero init, fixed epochs and learning rate) and reaches 100% accuracy on a linearly separable toy set.
• Labels are validated (0/1 only), empty tables and unsupported model types are structured errors.
• ml_predict preserves input columns, adds predicted_<label> and predicted_<label>_prob, and is reproducible.
• ml_evaluate reproduces exact metrics on a hand-checkable confusion matrix (TP/FP/TN/FN = 1/1/1/1 → everything 0.5).
• The anti-skew tests pass: prediction through the model equals a manual apply-the-fitted-transform computation, and predicting on shifted data never re-fits the scaler.
• All 32 tests pass under both lab and solution.

How this maps to the real stack

• CREATE MODEL / ML.PREDICT / ML.EVALUATE are the real BigQuery ML verbs, with the same shapes: CREATE MODEL takes OPTIONS(model_type='logistic_reg', input_label_cols=['y']) and a SELECT; ML.PREDICT returns the input columns plus predicted_<label> and a probability struct; ML.EVALUATE returns a metrics row (precision, recall, accuracy, f1_score, log_loss, roc_auc for logistic regression). Our dict-of-metrics is that row.
• TRANSFORM is the real anti-skew feature: preprocessing declared there (e.g. ML.STANDARD_SCALER(x) OVER ()) is embedded in the model and automatically applied by ML.PREDICT. Snowflake ML and Redshift ML have the same "model owns its preprocessing" design; scikit-learn's Pipeline and Vertex AI's preprocessing-in-SavedModel are the same idea outside the warehouse.
• hash_split is the documented BigQuery pattern for reproducible splits: MOD(ABS(FARM_FINGERPRINT(CAST(key AS STRING))), 10) < 8. We use md5 because it's stdlib; the property that matters — side depends only on the key — is identical.
• Real BigQuery ML training uses closed-form or iterative optimizers ("normal equation" for small linear models, batch gradient descent with line search otherwise), runs distributed, and supports many model types (boosted trees via XGBoost, DNNs via TensorFlow, ARIMA, k-means, matrix factorization) plus imported TensorFlow/ONNX models and remote Vertex endpoints. Ours is one model type with a fixed-step optimizer, because the contract (deterministic train, owned transform, table-in/table-out predict) is the lesson, not the model zoo.
• When warehouse ML fits: the data is already in the warehouse, the model is tabular and moderate-sized, and the team speaks SQL — dashboards' churn scores, LTV, propensity. When it doesn't: deep learning on unstructured data, sub-100ms online serving (pair the warehouse model with Lab 01's online store instead), or custom architectures — that's Vertex AI / Databricks territory, per the phase README's comparison table.

Limits of the miniature. No SQL parser — the API is Python methods shaped like the SQL verbs. Population (not sample) std; fixed learning rate with no convergence check (fixed epochs); binary labels only; probability-vs-label mse/mae rather than BigQuery's log-loss/roc_auc. The one-hot's all-zeros-for-unseen policy matches common warehouse encoders but hides the "new category alert" a production feature-monitoring layer would raise.

Extensions (your own machine)

• Add k-means as a second model_type (fixed initial centroids = first k distinct rows, Lloyd's iterations, ML.EVALUATE returning inertia / Davies-Bouldin) — BigQuery ML supports exactly this for segmentation.
• Add ML.WEIGHTS: expose the trained coefficients as a table, the way analysts inspect a BigQuery ML model's feature attribution.
• Implement early stopping: track mean log-loss per epoch and stop when the improvement drops below min_rel_progress — the real OPTIONS knob.
• Run the real thing on the BigQuery sandbox (free tier): the same churn table as a real CREATE MODEL ... TRANSFORM(...), then compare ML.EVALUATE's metrics with your miniature's.

Interview / resume signal

"Built a BigQuery-ML-style warehouse ML engine: CREATE MODEL as deterministic logistic regression (zero-init full-batch gradient descent — same table, same weights, every run), a TRANSFORM clause whose standardization and one-hot encoding are fit on training data and stored inside the model so ML.PREDICT cannot skew from training, FARM_FINGERPRINT-style hash splits for reproducible evaluation, and ML.EVALUATE metrics verified against a hand-computed confusion matrix."