What is BigQuery ML, and when would you train a model with CREATE MODEL instead of exporting data?

## What BigQuery ML is BigQuery ML (BQML) lets you create, evaluate and use machine-learning models with SQL statements executed by BigQuery itself. A model is a first-class object inside a dataset, alongside tables and views. There is no separate cluster to provision, no data export, and no model server: training is a query job, and prediction is a query. This matters mostly because of gravity. In a warehouse-centric organisation the features already exist as columns in BigQuery. Moving them out to train and moving predictions back in is usually the largest, most fragile part of the pipeline. BQML removes that round trip for the class of models it supports. ## The workflow The cycle is three statements. **Train.** `CREATE MODEL` takes an `OPTIONS(...)` list and an `AS SELECT` that produces the training rows. `model_type` picks the algorithm; `input_label_cols` names the target column (a column literally named `label` is picked up by default). Everything else in the SELECT list is treated as a feature. ```sql CREATE OR REPLACE MODEL `analytics.churn` OPTIONS(model_type='logistic_reg', input_label_cols=['churned']) AS SELECT plan, tenure_days, sessions_28d, churned FROM `analytics.features`; ``` **Evaluate.** `ML.EVALUATE(MODEL ...)` returns the standard metrics for the model family — precision, recall, log loss, ROC AUC for a classifier; mean squared error and R² for a regressor. `ML.CONFUSION_MATRIX` and `ML.ROC_CURVE` go further for classification, and `ML.TRAINING_INFO` returns one row per training iteration so you can see whether the loss actually converged. **Predict.** `ML.PREDICT(MODEL ..., TABLE ...)` or `ML.PREDICT(MODEL ..., (SELECT ...))` returns the input rows plus prediction columns — for a logistic regression, `predicted_<label>` and `predicted_<label>_probs`. Because it is a table-valued function, you can join, filter and write the result into a table like any other query. ## What BigQuery does automatically BQML applies standard preprocessing on its own: numeric features are standardised, STRING features are one-hot encoded, and rows with NULL labels are dropped. It also splits the input: `data_split_method` defaults to an automatic split whose held-out fraction depends on how many rows you fed it, with very small inputs used entirely for training. You can override that with a random split, a sequential split on a chosen column, or a custom boolean column — the sequential option is what you want for time-series-shaped data, where a random split leaks the future into the training set. ## TRANSFORM and training–serving skew The subtle failure mode is preprocessing drift: you bucket ages and cross two columns in the training SELECT, then someone writes a slightly different expression in the prediction query. The `TRANSFORM` clause fixes this. Feature expressions declared inside `TRANSFORM` are stored with the model and re-applied automatically by `ML.PREDICT`, so callers pass raw columns and cannot get the feature engineering wrong. Preprocessing helpers such as `ML.STANDARD_SCALER`, `ML.QUANTILE_BUCKETIZE` and `ML.FEATURE_CROSS` are designed to be used there. ## Model families and their shapes The supported types span several execution styles. Linear and logistic regression, k-means and matrix factorization train directly in BigQuery. Boosted-tree and DNN models, and the AutoML options, are trained through Vertex AI on your behalf — same SQL surface, different cost profile and training time. `ARIMA_PLUS` covers time-series forecasting and is consumed with `ML.FORECAST` rather than `ML.PREDICT`. You can also import an already-trained TensorFlow or ONNX model and score with it, or define a remote model that calls a Vertex AI endpoint, which is how large language models are reached from SQL. ## Where BQML stops being the right answer It is a batch scoring engine. `ML.PREDICT` runs as a query job, so per-call latency is query latency — fine for nightly scoring into a table that an application then reads by key, wrong for scoring a single row inside a web request. It offers a fixed menu of algorithms; there is no place to put a custom loss function or a hand-built network. Experiment management, feature stores and model registries are thin or absent compared with a purpose-built platform. And training is not free just because the data is local: model creation consumes bytes or slots like any other job, and the Vertex-backed model types carry their own charges. The honest positioning in an interview: BQML is how an analytics team ships a good-enough model this quarter without building a platform, and how you produce a baseline that a real ML team can later beat.