Gowtham Potureddi

Posted on Jun 29

BigQuery ML: Train, Predict, Forecast & Vector Search Entirely in SQL

#python #sql #dataengineering #interview

bigquery ml is the senior-DE move that quietly killed half the "stand up a Python pipeline for that model" tickets in 2026 — and the one most data teams still under-use because they think of bqml as a toy. It is not. The surface has grown from linear and logistic regression to deep neural nets, boosted trees, AutoML tables, ARIMA_PLUS time-series, k-means anomaly detection, imported TensorFlow / ONNX / XGBoost models, and remote models that bridge from a CREATE MODEL statement to Gemini, embedding endpoints, and arbitrary Vertex AI deployments — all addressed with the same ML.PREDICT, ML.FORECAST, ML.EVALUATE, ML.GENERATE_TEXT_EMBEDDING, and VECTOR_SEARCH functions you call from any SQL client.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "when do you pick bigquery machine learning over Vertex AI for a tabular classifier?" or "how does bigquery vector search change the RAG architecture once your corpus lives in BigQuery already?" It walks through the full BQML model surface (built-in trainable, imported, and remote), the prediction surface (ml.predict bigquery, ML.FORECAST, ML.EVALUATE, ML.EXPLAIN_PREDICT), the embedding + vector search stack (ML.GENERATE_TEXT_EMBEDDING, VECTOR_SEARCH, CREATE VECTOR INDEX, bqml gemini remote models for end-to-end RAG-in-SQL), bigquery forecasting with ARIMA_PLUS plus exogenous regressors, and the 5-question decision framework senior engineers use to pick between BQML, Vertex AI, and Snowflake Cortex for new pipelines. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on medium-difficulty ETL problems →, and sharpen the aggregation axis with the aggregation drills →.

On this page

Why BQML is the senior-DE ML play in 2026
BQML model surface — what trains in SQL
ML.PREDICT, ML.FORECAST, ML.EVALUATE
Embeddings + vector search in SQL
BQML vs Vertex vs Snowflake Cortex
Cheat sheet — BQML recipes
Frequently asked questions
Practice on PipeCode

1. Why BQML is the senior-DE ML play in 2026

`bigquery ml` moves compute to the data — no separate Python pipeline, no GPU ops, no second governance perimeter

The one-sentence invariant: BQML lets you train, evaluate, predict, forecast, embed, and vector-search inside the same BigQuery project that already holds your data — using pure SQL — and the cost model is the same slot-seconds + bytes-processed you already budget for your warehouse. Every other comparison — model surface, latency, retraining cadence, MLOps story — follows from that one structural choice: the model lives where the data lives, not the other way around. Once you internalise "move compute to data," the entire bqml interview surface collapses to a sequence of consequences from that one decision.

Four axes interviewers actually probe.

Model surface. BQML now covers linear / logistic regression, k-means, matrix factorisation, DNN, boosted trees (XGBoost-style), AutoML Tables, ARIMA_PLUS for time-series, TimesFM for zero-shot forecasting, and imported TensorFlow / ONNX / XGBoost models. Remote models bridge to Vertex AI endpoints, Gemini, and embedding-001 — the surface is wider than most teams realise.
Training cost. Training a BQML model is just a SQL query that scans your training table and computes gradients in the query engine. You pay for slots and bytes scanned, not for managed GPUs sitting idle. For tabular models up to ~100M rows, this is dramatically cheaper than spinning up Vertex Custom Training.
Inference cost. ML.PREDICT runs as a SQL function over a table — no model server, no autoscaling endpoint, no cold-start. For batch scoring, this is essentially free compared to a Vertex endpoint that bills per-second-uptime. For online (sub-100ms) scoring, BQML is the wrong fit and Vertex AI Online Prediction is the answer.
Ops shape. No model registry, no Docker image, no Kubernetes — the model is a first-class BigQuery object with CREATE MODEL, DROP MODEL, ALTER MODEL, and IAM. For analytics ML, this is the single biggest reason BQML wins: zero net-new ops surface.

The DSL split — same SQL, different mental model.

CREATE MODEL. The verb that does everything — CREATE OR REPLACE MODEL dataset.my_model OPTIONS(model_type='boosted_tree_classifier', ...) AS SELECT ... FROM training_table. Hyperparameters live in OPTIONS; the training set is just a SELECT.
ML.PREDICT / ML.FORECAST / ML.EVALUATE. The verbs that consume the model. ML.PREDICT(MODEL dataset.my_model, (SELECT * FROM new_data)) returns the input columns plus predicted labels and probabilities.
Remote models. CREATE MODEL dataset.my_model REMOTE WITH CONNECTION ... OPTIONS(endpoint = 'gemini-1.5-pro') registers a remote inference endpoint as a BQML model. Subsequent ML.GENERATE_TEXT(...) calls round-trip to Vertex AI / Gemini transparently.

The 2026 reality — what changed since 2023.

bqml gemini integration is GA. ML.GENERATE_TEXT, ML.GENERATE_TEXT_EMBEDDING, ML.GENERATE_EMBEDDING (multimodal), and ML.UNDERSTAND_TEXT are all backed by Gemini / PaLM 2 / embedding-001 via remote models — no Python wrapper, no API key plumbing.
bigquery vector search is GA. VECTOR_SEARCH(table, embedding_column, query_vector, top_k => N) plus CREATE VECTOR INDEX (HNSW / IVF) means you can run sub-second ANN over multi-million-row embedded corpora without standing up a separate vector database.
ARIMA_PLUS_XREG. Time-series with exogenous regressors (e.g. forecast sales while passing in marketing spend as a covariate) — the missing capability that pushed teams to Prophet / Vertex Forecast.
Continuous training. Schedule a query that re-runs CREATE OR REPLACE MODEL nightly; the model is versioned by the next CREATE OR REPLACE (BQML keeps the last 4 versions). No separate retraining service required.

What interviewers listen for.

Do you say "BQML moves compute to the data" in the first sentence? — senior signal.
Do you mention "CREATE MODEL is just a SQL statement; the training set is a SELECT" unprompted? — senior signal.
Do you contrast "BQML batch scoring is essentially free; Vertex endpoints bill per-second-uptime"? — required answer.
Do you push back on "BQML is only for toy models" with the boosted-tree / DNN / ARIMA_PLUS / remote-Gemini surface? — senior signal.

Worked example — same logistic regression, BQML vs Python

Detailed explanation. The classic tabular ML Hello World — predict whether a customer churns from a customers table — looks deceptively similar in both worlds. The SQL reads almost identically to a scikit-learn snippet. The runtime is wildly different: BQML compiles the CREATE MODEL into a BigQuery job that scans the table and computes gradients in the query engine. The Python path needs a data extract, a feature pipeline, a training script, a serialised model, and a deployment target.

Question. Write a logistic regression that predicts churned from (tenure_months, monthly_spend, support_tickets) in both BQML SQL and scikit-learn Python. Highlight the runtime difference (where does training actually run?), the deployment shape, and the cost model.

Input.

customer_id	tenure_months	monthly_spend	support_tickets	churned
c1	12	49.99	2	0
c2	3	19.99	5	1
c3	24	99.99	0	0
c4	1	9.99	7	1
c5	18	79.99	1	0

Code.

-- BQML — train + deploy in one SQL statement, runs inside BigQuery
CREATE OR REPLACE MODEL `analytics.churn_lr`
OPTIONS(
  model_type = 'logistic_reg',
  input_label_cols = ['churned'],
  auto_class_weights = TRUE,
  data_split_method = 'auto_split',
  l2_reg = 0.01
) AS
SELECT
  tenure_months,
  monthly_spend,
  support_tickets,
  churned
FROM `analytics.customers`
WHERE training_cutoff_date < '2026-01-01';

-- Score new customers — same SQL surface, no endpoint to call
SELECT
  customer_id,
  predicted_churned,
  predicted_churned_probs[OFFSET(0)].prob AS prob_no_churn,
  predicted_churned_probs[OFFSET(1)].prob AS prob_churn
FROM ML.PREDICT(
  MODEL `analytics.churn_lr`,
  (SELECT * FROM `analytics.customers_today`)
);

# Python (scikit-learn) — needs extract, pipeline, registry, endpoint
from google.cloud import bigquery
from sklearn.linear_model import LogisticRegression
import joblib

# 1) Pull data out of BigQuery
df = bigquery.Client().query("""
    SELECT tenure_months, monthly_spend, support_tickets, churned
    FROM `analytics.customers`
    WHERE training_cutoff_date < '2026-01-01'
""").to_dataframe()

# 2) Train locally
X = df[['tenure_months', 'monthly_spend', 'support_tickets']]
y = df['churned']
model = LogisticRegression(C=1/0.01, class_weight='balanced')
model.fit(X, y)

# 3) Serialise + push to Vertex Model Registry + create endpoint
joblib.dump(model, 'churn_lr.joblib')
# ... gcloud ai models upload ... gcloud ai endpoints deploy-model ...
# ... and now you have an endpoint billing per-second-uptime ...

Step-by-step explanation.

The BQML DSL looks similar to scikit-learn's API — both call out a model type, a label, and hyperparameters. The difference is the call site: CREATE MODEL runs inside BigQuery; model.fit() runs on a laptop or a Vertex Custom Training job.
BQML executes the training as a single BigQuery job that scans the training table once, computes the gradient + Hessian in the query engine using vectorised SIMD, and persists the coefficients as a BigQuery-native model object. No data leaves the warehouse.
The Python path requires four extra steps: pull data out, run training in a separate environment, serialise the model, deploy it to a Vertex endpoint. Each of those is a separate ops surface (auth, IAM, networking, monitoring).
Scoring with ML.PREDICT is just another BigQuery query — same auth, same IAM, same WHERE clause filters. Scoring with the Python model requires hitting the Vertex endpoint over HTTPS, paying for endpoint uptime, and managing connection pools.
The key operational difference: BQML scales by adding BigQuery slots (which you already do for analytics); Python scales by adding Vertex endpoint replicas + Python workers + a CI/CD pipeline.

Output.

customer_id	predicted_churned	prob_no_churn	prob_churn
c1	0	0.87	0.13
c2	1	0.18	0.82
c3	0	0.93	0.07
c4	1	0.09	0.91
c5	0	0.91	0.09

Rule of thumb. If the training data already lives in BigQuery and the team writes more SQL than Python, BQML piggybacks on infra you already operate. If you need GPU training, custom loss functions, or sub-100ms online scoring, Vertex Custom Training + Online Prediction is the right fit.

Worked example — batch scoring cost, BQML vs Vertex endpoint

Detailed explanation. A common interview question — "you score 50M customers nightly with the churn model. What does that cost on BQML vs a Vertex endpoint?" The answer is the cleanest demonstration of the warehouse-native cost model.

Question. Compute the rough monthly cost of scoring 50M rows × 30 nights on BQML vs Vertex AI Online Prediction. Use BigQuery on-demand pricing ($6.25 per TB scanned) and a small Vertex endpoint at $0.20/hour with 50ms latency.

Input.

Workload	Rows/night	Nights/month	Model type
Churn scoring	50,000,000	30	logistic_reg

Code.

-- BQML batch scoring — 50M rows once per night
INSERT INTO `analytics.churn_scores`
SELECT
  customer_id,
  CURRENT_DATE() AS scored_at,
  predicted_churned,
  predicted_churned_probs[OFFSET(1)].prob AS prob_churn
FROM ML.PREDICT(
  MODEL `analytics.churn_lr`,
  (SELECT customer_id, tenure_months, monthly_spend, support_tickets
   FROM `analytics.customers`)
);
-- ~50M rows × ~50 bytes/row = ~2.5 GB scanned per run
-- 2.5 GB × $6.25/TB = ~$0.016 per night, ~$0.48/month

# Vertex endpoint scoring — 50M predict calls per night
# Endpoint at $0.20/hour running 24x7 = $144/month minimum
# 50M predictions × 50ms = 2,500,000 seconds = ~700 hours of compute
# If served by 1 replica → too slow; need ~30 replicas to finish overnight
# 30 replicas × $0.20/hour × 24 × 30 = $4,320/month for the endpoint
# Plus egress, plus the Python orchestration cost.

Step-by-step explanation.

BQML batch scoring is a SQL query that scans the input table once. For 50M rows × ~50 bytes/row, you scan ~2.5 GB per run — that's ~$0.016 on on-demand pricing, or essentially zero on a flat-rate slot reservation.
The Vertex endpoint bills per-second-uptime regardless of QPS. Even at 0 QPS the endpoint costs money; at 50M predictions/night you must run multiple replicas just to finish overnight.
The latency picture inverts. BQML batch scoring finishes in ~30 seconds for 50M rows; the Vertex endpoint serves each prediction in ~50ms but must serialise 50M HTTPS round-trips somehow (the orchestration cost is real).
For batch scoring with overnight latency budgets, BQML is 3-4 orders of magnitude cheaper. For online scoring with sub-100ms p99, BQML is the wrong tool — Vertex Online Prediction or a custom serving container is correct.
End result: 80% of analytics ML workloads (churn scores, propensity scores, segmentation, fraud flags refreshed hourly) belong on BQML; 20% (real-time recommender, fraud at swipe time) belong on Vertex.

Output (cost comparison).

Aspect	BQML	Vertex Online Prediction
Monthly cost (50M × 30)	~$0.48	~$4,320
Latency per row	batch (~30s for 50M)	~50ms per call
Ops surface	zero (just SQL)	endpoint + autoscaling + monitoring
Best fit	nightly / hourly batch	sub-100ms online

Rule of thumb. If your latency budget is "by 8am" or "within the hour", BQML wins on cost by orders of magnitude. If your latency budget is "before the user clicks next", Vertex Online Prediction is the right tool.

Worked example — BQML governance + IAM

Detailed explanation. A common interview probe — "how do you control who can train models and who can predict?" — is where BQML's first-class warehouse object pays off. Models are governed by the same IAM and dataset-level permissions as tables and views; no separate ML platform IAM to learn.

Question. Set up dataset-level governance so that data scientists can train models, analysts can call ML.PREDICT, but only an MLOps service account can DROP MODEL. Show the SQL grants.

Input.

Role	Should	Should NOT
Data Scientist	CREATE MODEL, ML.EVALUATE	DROP MODEL
Analyst	ML.PREDICT, ML.FORECAST	CREATE MODEL
MLOps SA	CREATE / DROP / ALTER MODEL	(full control)

Code.

-- BQML governance — pure SQL, no separate ML IAM
-- Dataset-level grants apply to every model inside the dataset.

GRANT `roles/bigquery.dataEditor`
  ON SCHEMA `analytics`
  TO 'group:data-scientists@example.com';

GRANT `roles/bigquery.dataViewer`
  ON SCHEMA `analytics`
  TO 'group:analysts@example.com';

GRANT `roles/bigquery.admin`
  ON SCHEMA `analytics`
  TO 'serviceAccount:mlops-sa@example.iam.gserviceaccount.com';

-- A custom role that allows CREATE MODEL but not DROP MODEL
-- (defined in IAM with bigquery.models.create + bigquery.models.updateData
--  but NOT bigquery.models.delete).

Step-by-step explanation.

BQML models are stored as objects under a BigQuery dataset (analytics.churn_lr). Permissions on the dataset cascade to every model inside it.
Data scientists get dataEditor on the analytics dataset, which includes bigquery.models.create and bigquery.models.updateData — they can train and overwrite models.
Analysts get dataViewer, which includes bigquery.models.getData (needed for ML.PREDICT) but not bigquery.models.create or bigquery.models.delete.
The MLOps service account gets admin for cleanup and lifecycle automation. A custom role can split CREATE from DROP if you need a stricter separation.
No separate ML platform IAM, no model registry permissions, no endpoint deployment grants — the same audit log that tracks who queried customers also tracks who trained churn_lr and who called ML.PREDICT on it.

Output.

Identity	Can train?	Can predict?	Can drop?
Data Scientist	yes	yes	no (custom role)
Analyst	no	yes	no
MLOps SA	yes	yes	yes

Rule of thumb. Treat BQML governance as a dataset-level concern, not a model-level one. Put production models in a separate dataset (analytics_models) with stricter grants than the raw data dataset, and let dataset IAM do the rest.

Senior interview question on BQML positioning

A senior interviewer often opens with: "Walk me through how you would decide whether a new ML workload belongs on BigQuery ML or on Vertex AI. What are the four or five questions you ask, in order, and what answer to each one pushes you to one platform over the other?"

Solution Using a 4-question decision framework

Decision framework — BigQuery ML vs Vertex AI

1. Where does the training data live?
   - Already in BigQuery (or easily landed)  → BQML eligible
   - Streaming raw bytes / images / video    → Vertex (custom training)

2. What is the latency budget for inference?
   - Batch / nightly / hourly  → BQML wins on cost
   - Sub-100ms online          → Vertex Online Prediction

3. What is the model class?
   - Tabular (linear, tree, DNN, ARIMA_PLUS, k-means)  → BQML built-in
   - Vision, audio, custom architectures                → Vertex Custom Training
   - LLM / embeddings                                   → BQML remote model to Gemini

4. What is the MLOps shape?
   - SQL-first team, no MLOps platform → BQML
   - MLOps platform with registry + pipelines → Vertex (or BQML + Vertex hybrid)

Step-by-step trace.

Pipeline	Q1 data	Q2 latency	Q3 model	Q4 ops	Picked
Nightly churn scoring	BigQuery	overnight	logistic_reg	SQL team	BQML
Real-time fraud at swipe	Streaming	< 50ms	DNN	MLOps team	Vertex
28-day sales forecast	BigQuery	daily	ARIMA_PLUS	SQL team	BQML
Image classification on uploads	GCS images	minutes	Custom CNN	MLOps team	Vertex
RAG over internal docs	BigQuery	seconds	Gemini + embeddings	SQL team	BQML (remote model)

After the 4-question pass, the platform choice is usually unambiguous. The remaining 5% — where both platforms work — defaults to whatever the team already operates.

Output:

Platform	When it wins
BQML	Warehouse-resident data, batch / sub-second-not-required, tabular or LLM-via-remote, SQL-first team
Vertex AI	Sub-100ms online, custom architectures, vision / audio, full MLOps lifecycle needed

Why this works — concept by concept:

Data gravity is the structural axis — every other consequence (cost, latency, ops shape) follows from where the data already lives. Asking the data question first short-circuits a lot of false dichotomies between "ML platforms".
Batch vs online latency — BQML is a batch query engine wearing an ML hat. For overnight / hourly workloads it is 3-4 orders of magnitude cheaper than a Vertex endpoint. For real-time scoring it is the wrong tool.
Model surface coverage — BQML covers the 80% of tabular ML that analytics teams actually ship. The remaining 20% (vision, audio, custom losses, fine-tuned LLMs) still belongs on Vertex.
Ops cost is real — running a Vertex MLOps stack requires registry, pipelines, endpoints, monitoring. BQML piggybacks on whatever already runs your warehouse.
Cost — BQML cost scales with bytes-scanned (O(rows)); Vertex endpoint cost scales with uptime (O(hours)). For batch workloads with idle gaps, BQML wins by orders of magnitude.

SQL
Topic — sql
BigQuery ML platform-decision problems

Practice →

ETL Topic — etl · medium Warehouse-native ML pipeline drills

Practice →

2. BQML model surface — what trains in SQL

`create model bigquery` covers linear, tree, DNN, AutoML, ARIMA_PLUS, k-means, imports, and remote — one verb, the whole surface

The mental model in one line: every model in BQML is created with a single CREATE MODEL ... OPTIONS(model_type = '...') AS SELECT ... statement, and that statement covers built-in trainable models, imported TensorFlow / ONNX / XGBoost models, and remote models that wrap Vertex AI endpoints or Gemini. Once you say that out loud, every bigquery machine learning interview question becomes a deduction from "the model type is a string in OPTIONS; the training set is a SELECT."

The three model families.

Built-in trainable. Linear / logistic regression, k-means, matrix factorisation, DNN classifier / regressor, boosted tree classifier / regressor, AutoML Tables, ARIMA_PLUS, ARIMA_PLUS_XREG, autoencoder, TimesFM. BQML trains the model from scratch inside BigQuery using slot-seconds you already pay for.
Imported. Bring your own TensorFlow SavedModel, ONNX model, or XGBoost model. Load it into BQML with MODEL_TYPE='TENSORFLOW' | 'ONNX' | 'XGBOOST' plus a MODEL_PATH pointing at GCS. BQML serves predictions but does not train these.
Remote. Wrap a Vertex AI endpoint, a Gemini model, or an embedding endpoint behind a BQML model object. Subsequent ML.PREDICT / ML.GENERATE_TEXT / ML.GENERATE_TEXT_EMBEDDING calls round-trip to the remote endpoint. No training; pure inference proxy.

The model-type catalogue (the cheat-sheet interviewers love).

Tabular regression / classification. linear_reg, logistic_reg, boosted_tree_regressor, boosted_tree_classifier, dnn_regressor, dnn_classifier, dnn_linear_combined_regressor, dnn_linear_combined_classifier, automl_regressor, automl_classifier.
Unsupervised. kmeans (clustering + anomaly detection), matrix_factorization (collaborative filtering / recsys), pca, autoencoder (anomaly detection on tabular).
Time-series. arima_plus (univariate forecasting with auto seasonality), arima_plus_xreg (with exogenous regressors), times_fm (zero-shot foundation model for forecasting, 2026 addition).
LLM / embedding via remote. remote_model pointing at gemini-1.5-pro, gemini-1.5-flash, text-embedding-004, multimodal-embedding-001, plus arbitrary Vertex endpoints.

Training data — just a SELECT.

The AS SELECT ... clause is the training set. Filter, join, window, aggregate — anything BigQuery SQL allows.
The input_label_cols option names the target column(s). For unsupervised models, no label is needed.
The data_split_method option (auto_split, random, seq, custom, no_split) controls train/eval split. Use seq with a timestamp column for time-aware splits.

Hyperparameters live in OPTIONS.

Every model type accepts a model-specific bag of options. For boosted trees: num_parallel_tree, max_tree_depth, learn_rate, early_stop. For DNN: hidden_units, dropout, activation_fn, optimizer. For ARIMA_PLUS: time_series_timestamp_col, time_series_data_col, horizon, auto_arima.
Hyperparameter tuning is built in: set num_trials = 20, hparam_tuning_objectives = ['roc_auc'], and BQML will run Vizier-style hyperparameter search inside the same CREATE MODEL statement.

Imported models — bring your own.

Load a TensorFlow SavedModel from GCS: CREATE MODEL ... OPTIONS(model_type='tensorflow', model_path='gs://bucket/savedmodel/*').
The model must take a record with column names matching the input feature names. BQML auto-binds column names to model inputs.
Imported models inherit BQML's IAM and slot-based billing for inference but cannot be retrained via BQML.

Remote models — wrap an endpoint.

CREATE MODEL ... REMOTE WITH CONNECTION 'us.my-vertex-connection' OPTIONS(endpoint = 'gemini-1.5-pro').
The CONNECTION object is created once via bq mk --connection --connection_type=CLOUD_RESOURCE ... and grants BigQuery's service identity access to the Vertex endpoint.
Subsequent ML.GENERATE_TEXT(MODEL ..., (SELECT prompt FROM prompts), STRUCT(0.2 AS temperature, 1024 AS max_output_tokens)) round-trips each row to the remote model.

Common interview probes on the model surface.

"What model types ship in BQML?" — name 6+ across regression, classification, unsupervised, and time-series.
"How do you bring an XGBoost model trained in Python into BQML?" — export to XGBoost JSON, upload to GCS, CREATE MODEL ... OPTIONS(model_type='xgboost', model_path='gs://...').
"What is a remote model for?" — to call Gemini / Vertex endpoints from SQL without leaving BigQuery.
"Can BQML train a deep learning model?" — yes, dnn_classifier / dnn_regressor, and dnn_linear_combined_* for wide-and-deep. Also imported TensorFlow / ONNX.

Worked example — boosted tree classifier in 8 lines

Detailed explanation. A boosted-tree classifier is the workhorse model for tabular ML — it dominates linear regression for non-linear interactions, handles categorical features without one-hot encoding, and ships with sensible defaults in BQML. The interview pattern is: "write a BQML statement that trains a boosted tree on customer data with auto class weights and L2 regularisation, then score new customers."

Question. Train a boosted_tree_classifier to predict churned from (tenure_months, monthly_spend, support_tickets, plan_tier). Set auto_class_weights = TRUE and l2_reg = 0.1. Then run ML.PREDICT on the next day's customers.

Input.

customer_id	tenure_months	monthly_spend	support_tickets	plan_tier	churned
c1	12	49.99	2	basic	0
c2	3	19.99	5	basic	1
c3	24	99.99	0	gold	0
c4	1	9.99	7	basic	1
c5	18	79.99	1	silver	0

Code.

CREATE OR REPLACE MODEL `analytics.churn_bt`
OPTIONS(
  model_type = 'boosted_tree_classifier',
  input_label_cols = ['churned'],
  auto_class_weights = TRUE,
  l2_reg = 0.1,
  max_tree_depth = 6,
  num_parallel_tree = 1,
  data_split_method = 'auto_split',
  early_stop = TRUE,
  min_rel_progress = 0.01
) AS
SELECT
  tenure_months,
  monthly_spend,
  support_tickets,
  plan_tier,
  churned
FROM `analytics.customers`
WHERE training_cutoff_date < '2026-01-01';

-- Score new customers
SELECT
  customer_id,
  predicted_churned,
  predicted_churned_probs[OFFSET(1)].prob AS prob_churn
FROM ML.PREDICT(
  MODEL `analytics.churn_bt`,
  (SELECT * FROM `analytics.customers_today`)
);

Step-by-step explanation.

The CREATE OR REPLACE MODEL verb registers a new versioned model under analytics.churn_bt. The previous version (if any) is kept for 4 generations so you can roll back via ALTER MODEL.
model_type = 'boosted_tree_classifier' selects XGBoost-style gradient boosting. max_tree_depth = 6 and num_parallel_tree = 1 are XGBoost defaults; BQML supports random forest-style ensembles via num_parallel_tree > 1.
auto_class_weights = TRUE rebalances the loss against the class distribution — essential for churn (the positive class is usually 5-15%). l2_reg = 0.1 adds L2 regularisation on leaf weights.
data_split_method = 'auto_split' reserves a holdout for evaluation. early_stop = TRUE plus min_rel_progress = 0.01 stops training when the validation loss plateaus.
Categorical features like plan_tier are auto-encoded — BQML handles the one-hot or target encoding internally based on cardinality. No OneHotEncoder glue needed.
ML.PREDICT returns the input row plus predicted_churned (the class label) and predicted_churned_probs (an array of {label, prob} structs).

Output.

customer_id	predicted_churned	prob_churn
c1	0	0.11
c2	1	0.84
c3	0	0.05
c4	1	0.92
c5	0	0.07

Rule of thumb. For tabular classification on warehouse data, default to boosted_tree_classifier with auto_class_weights = TRUE and early_stop = TRUE. Tune max_tree_depth (4-8) and l2_reg (0.01-1.0) before reaching for DNN.

Worked example — k-means anomaly detection in SQL

Detailed explanation. k-means in BQML doubles as an unsupervised anomaly detector — train on the bulk of the data, then any point with a large distance to its assigned centroid is flagged as anomalous. The interview pattern is: "detect anomalous user sessions without labels."

Question. Train kmeans on session features (duration_sec, pages_viewed, bytes_downloaded), then flag sessions whose distance to the nearest centroid exceeds the 99th percentile.

Input.

session_id	duration_sec	pages_viewed	bytes_downloaded
s1	120	5	250000
s2	30	1	10000
s3	7200	1	980000000
s4	180	8	400000
s5	60	3	100000

Code.

CREATE OR REPLACE MODEL `analytics.sessions_km`
OPTIONS(
  model_type = 'kmeans',
  num_clusters = 8,
  standardize_features = TRUE,
  kmeans_init_method = 'kmeans++'
) AS
SELECT duration_sec, pages_viewed, bytes_downloaded
FROM `analytics.sessions`
WHERE event_date BETWEEN '2026-01-01' AND '2026-03-31';

-- Flag anomalies — sessions with distance > 99th percentile
WITH scored AS (
  SELECT
    session_id,
    centroid_id,
    nearest_centroids_distance[OFFSET(0)].distance AS dist
  FROM ML.PREDICT(
    MODEL `analytics.sessions_km`,
    (SELECT * FROM `analytics.sessions_today`)
  )
),
threshold AS (
  SELECT APPROX_QUANTILES(dist, 100)[OFFSET(99)] AS p99
  FROM scored
)
SELECT s.session_id, s.dist, s.dist > t.p99 AS is_anomaly
FROM scored s, threshold t;

Step-by-step explanation.

model_type = 'kmeans' with num_clusters = 8 trains 8 centroids over the 3-month session feature space. standardize_features = TRUE z-scores each column so high-magnitude features (bytes) do not dominate.
kmeans_init_method = 'kmeans++' uses the k-means++ initialisation (random sample biased away from existing centroids). This is the standard for production k-means.
ML.PREDICT on a k-means model returns the cluster assignment (centroid_id) plus nearest_centroids_distance — an array of {centroid_id, distance} structs sorted ascending.
The first entry (OFFSET(0)) is the assigned centroid and its distance. Large distances indicate a point that does not fit any cluster well — the anomaly signal.
The CTE pattern uses APPROX_QUANTILES to compute the 99th-percentile distance on the held-out scoring set, then flags everything above it as anomalous.

Output.

session_id	dist	is_anomaly
s1	0.34	false
s2	0.51	false
s3	4.78	true
s4	0.42	false
s5	0.38	false

Rule of thumb. k-means anomaly detection works well when the bulk of the data clusters tightly and anomalies are isolated. For high-dimensional sparse data, prefer autoencoder (reconstruction error as the anomaly score) over k-means.

Worked example — importing an XGBoost model trained in Python

Detailed explanation. Sometimes the team has a model that was trained in Python (with custom feature engineering or a research-grade hyperparameter sweep) and they want to serve it from BigQuery without rewriting. BQML's MODEL_TYPE='xgboost' import handles this — export the trained booster to JSON or UBJ, upload to GCS, register as a BQML model.

Question. A data scientist trained an XGBoost classifier in Python and exported it as gs://models/churn-xgb/model.bst. Wrap it as a BQML model so analysts can call ML.PREDICT on it.

Input.

Artifact	Location
XGBoost booster	gs://models/churn-xgb/model.bst
Feature names	tenure_months, monthly_spend, support_tickets
Output	probability of churn

Code.

-- Wrap an externally-trained XGBoost model as a BQML model
CREATE OR REPLACE MODEL `analytics.churn_xgb_imported`
OPTIONS(
  model_type = 'xgboost',
  model_path = 'gs://models/churn-xgb/*'
);

-- Predict — column names must match what XGBoost expects
SELECT
  customer_id,
  predicted_label
FROM ML.PREDICT(
  MODEL `analytics.churn_xgb_imported`,
  (
    SELECT
      customer_id,
      tenure_months,
      monthly_spend,
      support_tickets
    FROM `analytics.customers_today`
  )
);

Step-by-step explanation.

model_type = 'xgboost' plus model_path pointing at a GCS prefix tells BQML to load the model files from GCS instead of training in-engine.
The model files include the serialised booster (.bst or .ubj) and an optional feature spec. BQML reads the feature names from the booster and matches them against the column names in the SELECT passed to ML.PREDICT.
The prediction path runs inside BigQuery slots — no Python runtime, no Vertex endpoint. Latency is on the order of milliseconds per row for batch scoring.
The trade-off vs CREATE MODEL ... boosted_tree_classifier: imported models cannot be retrained from SQL. You have to retrain in Python and re-import. For models that retrain rarely (quarterly), this is fine. For nightly retraining, train natively in BQML.

Output.

customer_id	predicted_label
c1	0
c2	1
c3	0

Rule of thumb. Use imported models when the training pipeline is Python-heavy and the serving path benefits from BigQuery's IAM and SQL surface. Use native BQML training when retraining cadence is daily or weekly and you want zero Python in the pipeline.

Senior interview question on BQML model selection

A senior interviewer might ask: "You've inherited a BQML codebase with 12 models — some logistic_reg, some boosted_tree, some imported TF SavedModels. The team wants to standardise. How do you decide which model class to default to for tabular classification, and when to deviate?"

Solution Using a model-selection rubric anchored on data shape and ops tolerance

-- The default tabular classifier rubric
-- Default:           boosted_tree_classifier (covers 80% of tabular)
-- Switch to DNN:     when feature interactions are high-order AND > 10M rows
-- Switch to AutoML:  when team has no ML expertise AND can wait 1-3 hours
-- Switch to imported: when feature engineering is research-grade in Python
-- Switch to logistic_reg: when explainability is the primary requirement

CREATE OR REPLACE MODEL `analytics.standard_default`
OPTIONS(
  model_type = 'boosted_tree_classifier',
  input_label_cols = ['label'],
  auto_class_weights = TRUE,
  l2_reg = 0.1,
  max_tree_depth = 6,
  early_stop = TRUE,
  num_trials = 10,                    -- hparam tuning built-in
  hparam_tuning_objectives = ['roc_auc']
) AS
SELECT * FROM `analytics.training_set`;

Step-by-step trace.

Question	Answer	Routes to
What is the model class?	Tabular classification	tree / DNN / logistic_reg
Are feature interactions critical?	yes	boosted_tree or DNN
Is explainability mandatory?	no	boosted_tree wins
Training set size?	5M rows	boosted_tree (DNN overkill)
Hyperparameter tuning needed?	yes	num_trials = 10 inside CREATE MODEL

The default lands on boosted_tree_classifier with auto-class-weights and hyperparameter tuning baked into the CREATE MODEL. Deviations are explicit and justified per-model.

Output:

Model class	When to pick
boosted_tree_classifier	default for tabular classification
dnn_classifier	high-order interactions + > 10M rows
automl_classifier	no in-house ML expertise + > 1-hour training budget
logistic_reg	explainability is the primary KPI
imported xgboost / tensorflow	research-grade Python feature pipeline
remote (Gemini)	text / unstructured inputs

Why this works — concept by concept:

Boosted tree as the default — covers the bulk of tabular ML and ships with sensible defaults in BQML. Auto-class-weights, early stop, and built-in hyperparameter tuning mean you rarely need to deviate.
DNN only above 10M rows — DNN's variance penalty is high on small data. Below ~10M rows, boosted trees match or beat DNN on AUC for less compute.
AutoML for no-ML teams — AutoML Tables runs a model-selection sweep across architectures (linear + tree + DNN + ensemble) inside one CREATE MODEL. Slow but bulletproof.
Imported for Python-heavy feature engineering — if the feature pipeline lives in scikit-learn or PyTorch, importing the trained model into BQML serves it without rewriting the features.
Cost — boosted_tree_classifier training is O(rows × depth × trees). For a 10M-row table with depth 6 and 100 trees, this is ~10 minutes on a 1000-slot reservation. DNN training is 5-10x more expensive for the same data.

SQL
Topic — sql
BQML CREATE MODEL practice

Practice →

Aggregation Topic — aggregation Feature engineering in SQL

Practice →

3. ML.PREDICT, ML.FORECAST, ML.EVALUATE

`ml.predict bigquery` is the universal inference verb; `ML.FORECAST` is the time-series specialist; `ML.EVALUATE` is the metric reporter

The mental model in one line: once a model exists, every interaction with it is one of three SQL functions — ML.PREDICT for generic inference, ML.FORECAST for time-series horizons, and ML.EVALUATE for held-out metrics — and each takes the model name plus a sub-query. Once you say "model in, sub-query in, scored rows out," every ml.predict bigquery interview question reduces to "what does the sub-query look like and what columns come back?"

ML.PREDICT — the universal inference verb.

Signature: ML.PREDICT(MODEL model_name, (SELECT ... ), STRUCT(threshold_options)).
Returns every column of the input sub-query plus model-specific output columns. For classification: predicted_<label> and predicted_<label>_probs (array of {label, prob} structs). For regression: predicted_<label>.
Works for linear_reg, logistic_reg, boosted_tree_*, dnn_*, kmeans, matrix_factorization, imported models, AutoML Tables.
The threshold_options STRUCT (optional) lets you override the default 0.5 decision threshold for binary classification or change return shapes.

ML.FORECAST — the time-series specialist.

Signature: ML.FORECAST(MODEL model_name, STRUCT(horizon AS INT64, confidence_level AS FLOAT64)).
Works only on arima_plus, arima_plus_xreg, times_fm models.
Returns one row per future timestamp with forecast_value, prediction_interval_lower_bound, prediction_interval_upper_bound, standard_error, confidence_level, confidence_interval_lower_bound, confidence_interval_upper_bound.
Default horizon is 1000 steps; default confidence level is 0.95.

ML.EVALUATE — the metric reporter.

Signature: ML.EVALUATE(MODEL model_name, (SELECT ... )) — optional sub-query (defaults to the auto-split evaluation set used at training time).
Returns one row of metrics: precision, recall, accuracy, f1_score, log_loss, roc_auc for classification; mean_absolute_error, mean_squared_error, r2_score, explained_variance for regression; mean_absolute_percentage_error, mean_absolute_error for time-series.
Pass an explicit held-out set to evaluate on a custom slice (e.g. last week's data, or a specific cohort).

Supporting verbs.

ML.EXPLAIN_PREDICT. SHAP-style feature importance per row. Adds top_feature_attributions column with {feature, attribution} structs. Slower than ML.PREDICT (typically 2-5x). Use for audit and debugging, not bulk inference.
ML.GLOBAL_EXPLAIN. Global feature importance — one row per feature with mean absolute SHAP value across the training set.
ML.TRAINING_INFO. Per-iteration training loss and evaluation metrics — useful for plotting learning curves and diagnosing under/over-fitting.
ML.WEIGHTS. Model coefficients for linear / logistic regression — the cheapest interpretability tool when the model is linear.

Batch vs online inference patterns.

Batch (nightly / hourly). Materialise predictions into a predictions table: INSERT INTO ... SELECT ... FROM ML.PREDICT(...). Downstream BI dashboards read from the table. This is the 80% pattern.
On-query (ad-hoc). Call ML.PREDICT inline in a query — useful for one-off analyses but not for production dashboards (re-runs the model on every query).
Pseudo-online (BI Engine). Materialise predictions + serve via BI Engine for sub-second dashboard reads. Not real online serving but good enough for "yesterday's scored cohort" exploration.

Common interview probes on the prediction surface.

"What does ML.PREDICT return for a binary classifier?" — input columns + predicted_<label> + predicted_<label>_probs array.
"How do you forecast 30 steps ahead with 95% confidence intervals?" — ML.FORECAST(MODEL ..., STRUCT(30 AS horizon, 0.95 AS confidence_level)).
"How do you change the decision threshold for a binary classifier?" — pass STRUCT(0.7 AS threshold) as the third argument to ML.PREDICT, or compute the predicted label yourself from predicted_<label>_probs[OFFSET(1)].prob.
"What's the difference between ML.PREDICT and ML.EXPLAIN_PREDICT?" — ML.EXPLAIN_PREDICT adds per-row SHAP attributions; ~2-5x slower; used for audit, not bulk.

Worked example — ML.PREDICT with custom threshold + feature attributions

Detailed explanation. A common interview question — "score yesterday's customers with a 0.7 decision threshold and return the top-3 contributing features for each prediction." This stitches ML.PREDICT, ML.EXPLAIN_PREDICT, and the threshold option into one query.

Question. Score analytics.customers_today with the churn_bt model at threshold 0.7. Also return the top-3 SHAP attributions per row.

Input.

customer_id	tenure_months	monthly_spend	support_tickets	plan_tier
c101	4	19.99	3	basic
c102	36	99.99	0	gold
c103	8	29.99	6	basic

Code.

-- Predict with custom threshold + SHAP attributions in one shot
SELECT
  customer_id,
  predicted_churned,
  predicted_churned_probs[OFFSET(1)].prob AS prob_churn,
  -- Top-3 contributing features per row
  ARRAY(
    SELECT AS STRUCT feature, attribution
    FROM UNNEST(top_feature_attributions)
    ORDER BY ABS(attribution) DESC
    LIMIT 3
  ) AS top3_features
FROM ML.EXPLAIN_PREDICT(
  MODEL `analytics.churn_bt`,
  (SELECT * FROM `analytics.customers_today`),
  STRUCT(0.7 AS threshold, 5 AS top_k_features)
);

Step-by-step explanation.

ML.EXPLAIN_PREDICT is a superset of ML.PREDICT — it returns everything ML.PREDICT returns plus a top_feature_attributions column (array of {feature, attribution} structs, sorted by absolute attribution by default).
The STRUCT(0.7 AS threshold, ...) overrides the default 0.5 binary-classification cutoff. Any row with prob_churn >= 0.7 is labelled 1; everything else is 0. This is how you tune the precision/recall trade-off without retraining.
top_k_features = 5 tells BQML to compute SHAP attributions for the top-5 features only — cheaper than full-feature SHAP for high-dimensional inputs.
The outer SELECT projects the predicted columns and a derived top3_features array, taking the top-3 by absolute attribution. Negative attributions push the prediction towards class 0; positive push towards class 1.
The same query handles 1 row or 100M rows — BQML pushes the inference into the query engine, no Python orchestration.

Output.

customer_id	predicted_churned	prob_churn	top3_features
c101	1	0.78	[{tenure_months, +0.31}, {support_tickets, +0.22}, {plan_tier, +0.08}]
c102	0	0.04	[{tenure_months, -0.42}, {monthly_spend, -0.18}, {plan_tier, -0.11}]
c103	1	0.82	[{support_tickets, +0.36}, {tenure_months, +0.27}, {monthly_spend, +0.05}]

Rule of thumb. Use ML.EXPLAIN_PREDICT only when you need per-row attributions — it is meaningfully slower than ML.PREDICT. For bulk scoring, run ML.PREDICT first and re-run ML.EXPLAIN_PREDICT on the flagged subset.

Worked example — ML.FORECAST with ARIMA_PLUS and confidence intervals

Detailed explanation. Forecasting is the killer BQML use case for analytics teams — train an ARIMA_PLUS model on 3 years of daily store sales, then call ML.FORECAST for the next 28 days with 95% intervals. The model handles seasonality, trend, holiday effects, and outliers automatically.

Question. Train arima_plus on store_sales (3 years of daily revenue per store), then forecast the next 28 days with 95% confidence intervals.

Input.

sale_date	store_id	revenue
2023-01-01	s1	12000
2023-01-02	s1	13500
...	...	...
2025-12-31	s1	14800

Code.

-- Train ARIMA_PLUS — auto-detects seasonality, holidays, trend
CREATE OR REPLACE MODEL `analytics.store_sales_arima`
OPTIONS(
  model_type = 'arima_plus',
  time_series_timestamp_col = 'sale_date',
  time_series_data_col      = 'revenue',
  time_series_id_col        = 'store_id',
  auto_arima                = TRUE,
  data_frequency            = 'DAILY',
  holiday_region            = 'US',
  decompose_time_series     = TRUE
) AS
SELECT sale_date, store_id, revenue
FROM `analytics.store_sales`
WHERE sale_date BETWEEN '2023-01-01' AND '2025-12-31';

-- Forecast 28 days, 95% confidence intervals
SELECT
  store_id,
  forecast_timestamp,
  forecast_value,
  prediction_interval_lower_bound AS lo_95,
  prediction_interval_upper_bound AS hi_95,
  standard_error
FROM ML.FORECAST(
  MODEL `analytics.store_sales_arima`,
  STRUCT(28 AS horizon, 0.95 AS confidence_level)
)
ORDER BY store_id, forecast_timestamp;

Step-by-step explanation.

model_type = 'arima_plus' with auto_arima = TRUE runs an automatic order-selection algorithm over (p, d, q) (autoregressive, differencing, moving-average orders) and seasonality detection. The training picks the best order via AIC.
time_series_id_col = 'store_id' trains one ARIMA model per store inside a single CREATE MODEL statement — BQML handles the multi-series fan-out automatically. This is the killer feature vs raw Python ARIMA.
holiday_region = 'US' injects US federal holidays as known events that the model decomposes out of the seasonal signal. Other regions: 'GB', 'IN', 'JP', etc.
decompose_time_series = TRUE makes the trained model emit a decomposed view (trend + seasonal + holiday + residual) accessible via ML.EXPLAIN_FORECAST. Useful for "why did revenue drop last Tuesday?" diagnostics.
ML.FORECAST takes horizon = 28 (days, matching data_frequency = 'DAILY') and confidence_level = 0.95. It returns one row per (store_id, forecast_timestamp) with the point forecast and 95% prediction intervals.
The intervals are prediction intervals, not confidence intervals on the mean — they reflect the uncertainty in the actual value, including residual noise. Wider than CIs you might compute manually.

Output.

store_id	forecast_timestamp	forecast_value	lo_95	hi_95	standard_error
s1	2026-01-01	14820	13520	16120	650
s1	2026-01-02	15010	13690	16330	660
s1	2026-01-03	15240	13900	16580	670
...	...	...	...	...	...
s1	2026-01-28	16100	14400	17800	850

Rule of thumb. For multi-series forecasting (per-store, per-SKU, per-region), always use time_series_id_col so BQML trains one model per series in a single statement. The fan-out is parallelised by the query engine and runs in minutes, not the hours a Python loop would take.

Worked example — ML.EVALUATE on a custom holdout

Detailed explanation. A common interview probe — "how do you evaluate model drift on this month's data without retraining?" ML.EVALUATE accepts an optional sub-query, so you can point it at a held-out slice (last month, a specific cohort, an A/B test arm) and get fresh metrics.

Question. Evaluate churn_bt on customers who churned in March 2026 (post-training-cutoff) and compare ROC-AUC to the training-time AUC.

Input.

Slice	Description
Training	customers with `training_cutoff_date < '2026-01-01'`
March holdout	customers labelled in March 2026

Code.

-- Evaluate on a custom held-out slice
SELECT
  'march_holdout' AS slice,
  precision,
  recall,
  roc_auc,
  log_loss,
  accuracy
FROM ML.EVALUATE(
  MODEL `analytics.churn_bt`,
  (
    SELECT
      tenure_months,
      monthly_spend,
      support_tickets,
      plan_tier,
      churned
    FROM `analytics.customers`
    WHERE event_date BETWEEN '2026-03-01' AND '2026-03-31'
  ),
  STRUCT(0.5 AS threshold)
)

UNION ALL

-- Re-emit training-time metrics for comparison
SELECT
  'training' AS slice,
  precision,
  recall,
  roc_auc,
  log_loss,
  accuracy
FROM ML.EVALUATE(MODEL `analytics.churn_bt`);

Step-by-step explanation.

ML.EVALUATE without a sub-query returns the training-time metrics on the auto-split evaluation set. With a sub-query, it returns the same metric panel computed against the provided rows.
The STRUCT(0.5 AS threshold) argument controls the decision threshold for precision/recall/F1. Use the same threshold here as the production decision threshold to evaluate apples-to-apples.
The UNION ALL pattern produces a side-by-side comparison — training vs holdout — in a single query. Drop in additional slices (e.g. by plan_tier or country) to detect cohort-level drift.
A meaningful drop in ROC-AUC between training and March holdout indicates concept drift — the relationship between features and label has shifted. Trigger a retrain or investigate which features moved.
ROC-AUC is threshold-independent; precision and recall are threshold-dependent. A model with stable AUC but shifting precision/recall is a calibration problem (retrain the threshold), not a model problem.

Output.

slice	precision	recall	roc_auc	log_loss	accuracy
march_holdout	0.72	0.68	0.87	0.34	0.86
training	0.78	0.74	0.91	0.29	0.89

Rule of thumb. Schedule ML.EVALUATE against the last 7 / 30 days of labelled data on a recurring query (BigQuery Scheduled Queries or Dataform). Alert when ROC-AUC drops by more than 5% versus training. This is the cheapest drift monitor you can ship.

Senior interview question on production inference

A senior interviewer might ask: "Your BQML churn model serves nightly batch scores via ML.PREDICT. The business now wants a real-time dashboard that shows churn probability for a single user looked up by ID. What's the minimum change to support sub-second lookup without breaking the existing batch pipeline?"

Solution Using a materialised predictions table + BI Engine cache

-- 1) Existing nightly batch job — write to a partitioned table
CREATE OR REPLACE TABLE `analytics.churn_scores_today`
PARTITION BY scored_date
CLUSTER BY customer_id AS
SELECT
  CURRENT_DATE() AS scored_date,
  customer_id,
  predicted_churned,
  predicted_churned_probs[OFFSET(1)].prob AS prob_churn
FROM ML.PREDICT(
  MODEL `analytics.churn_bt`,
  (SELECT * FROM `analytics.customers_today`)
);

-- 2) Enable BI Engine reservation on the table for sub-second lookups
-- (one-time, via gcloud or console)
--   gcloud bigquery reservations capacity-commitments create \
--     --plan=FLEX --slots=100 --location=US

-- 3) The dashboard query — sub-second response with BI Engine cache
SELECT customer_id, prob_churn, predicted_churned
FROM `analytics.churn_scores_today`
WHERE scored_date = CURRENT_DATE()
  AND customer_id = @target_customer;

Step-by-step trace.

Step	Action	Latency
1	Nightly batch INSERT via ML.PREDICT	~30s for 50M rows
2	Partition by scored_date	filter-pruned
3	Cluster by customer_id	scan reduced to ~1MB
4	BI Engine cache	hot rows in RAM
5	Single-customer lookup	< 200ms p99

After the change, the nightly batch pipeline is unchanged; the dashboard query reads from a partitioned + clustered + BI-Engine-cached table. No model server, no new infra.

Output:

Metric	Before	After
Batch job duration	30s	30s (unchanged)
Dashboard query latency	n/a	< 200ms p99
Net-new infra	n/a	BI Engine reservation only
Cost overhead	$0	~$30/month for 100 BI Engine slots
Ops surface	n/a	none (still pure BigQuery)

Why this works — concept by concept:

Materialise then serve — for "freshness within a day" use cases, batch-score into a table and serve from the table. No model in the request path means no model latency, no warmup, no cold start.
Partition + cluster matter — PARTITION BY scored_date prunes 99.7% of the table on a one-day filter; CLUSTER BY customer_id further narrows to the single block that owns the row.
BI Engine for sub-second — BI Engine is BigQuery's in-memory caching layer for hot tables. Sub-second responses without standing up a separate KV store.
No new ops — the architecture is still 100% BigQuery: same IAM, same monitoring, same audit log. The dashboard team gets sub-second lookups for ~$30/month of BI Engine slots.
Cost — ML.PREDICT cost is O(rows × model size); table storage is O(rows × columns); BI Engine cost is O(GB × hours reserved). For 50M scored customers, total monthly cost is ~$50, vs ~$4000+ for a Vertex Online endpoint.

SQL
Topic — sql
ML.PREDICT inference problems

Practice →

ETL Topic — etl · medium Batch inference pipelines

Practice →

4. Embeddings + vector search in SQL

`bigquery embeddings` + `bigquery vector search` collapse the entire RAG stack into a single SQL query

The mental model in one line: ML.GENERATE_TEXT_EMBEDDING turns text into a 768-dim vector via a remote embedding model; VECTOR_SEARCH runs an ANN over a column of vectors with a query vector and returns the top-k; CREATE VECTOR INDEX builds an HNSW / IVF index that takes the search from O(N) to O(log N); together they replace Pinecone + LangChain for any corpus that already lives in BigQuery. Once you say that, every bigquery vector search and bqml gemini interview question collapses to "embed once, index once, query in SQL forever."

The embedding surface.

ML.GENERATE_TEXT_EMBEDDING — takes a text column and a remote embedding model (typically text-embedding-004 from Google). Returns an ARRAY<FLOAT64> of length 768 (or whatever the model produces).
ML.GENERATE_EMBEDDING — multimodal variant. Accepts text or images via STRUCT(content STRING, mime_type STRING) or OBJECTREF to GCS images. Useful for product-image search and mixed-modality RAG.
ML.GENERATE_TEXT — the LLM call. Takes a text prompt column and a remote Gemini / PaLM model. Returns the model's generated text, plus optional grounding metadata.
ML.UNDERSTAND_TEXT — task-specific NLP (entity extraction, classification, sentiment) backed by remote endpoints. Lighter than full LLM calls.

VECTOR_SEARCH — the ANN verb.

Signature: VECTOR_SEARCH(TABLE table_or_subquery, 'embedding_column_name', (SELECT query_vector), top_k => N, distance_type => 'COSINE' | 'EUCLIDEAN' | 'DOT_PRODUCT', options => JSON '{...}').
Returns one row per match with base (the matched row), distance, and an internal score column. Joins the matched row's full schema back so you can SELECT base.doc_id, base.content, distance directly.
Without an index, it scans the entire table — O(N) but parallelised across slots, fine for < ~100K rows.
With a vector index, it is O(log N) — sub-second on multi-million-row corpora.

CREATE VECTOR INDEX — the ANN accelerator.

Signature: CREATE VECTOR INDEX index_name ON table(embedding_column) OPTIONS(distance_type, index_type, ivf_options).
Index types: IVF (inverted file with k-means cluster centroids) and TREE_AH (Google's ScaNN-style asymmetric hashing tree). IVF is the older default; TREE_AH is the 2026 default for new indexes.
Indexes are incremental — new rows added to the base table are auto-indexed by a background process. No reindex required for streaming inserts.
Indexes have a coverage_ratio (rows indexed vs rows in table). When below 1.0, VECTOR_SEARCH falls back to brute force for the un-indexed remainder.

RAG-in-SQL — the end-to-end recipe.

Step 1. Embed your corpus once: INSERT INTO docs_with_emb SELECT doc_id, content, ML.GENERATE_TEXT_EMBEDDING(...) AS emb FROM docs.
Step 2. Create a vector index: CREATE VECTOR INDEX docs_idx ON docs_with_emb(emb) OPTIONS(distance_type='COSINE', index_type='TREE_AH').
Step 3. Query: embed the user query → VECTOR_SEARCH for top-k → pass top-k content as context to ML.GENERATE_TEXT against Gemini → return the answer. All in one CTE.

Cost model.

Embedding generation bills per 1K tokens via the remote embedding endpoint (typically ~$0.000025 / 1K tokens for text-embedding-004). For a 1M-doc corpus at 500 tokens/doc → ~$12.50 one-time.
Vector index storage is metered as BigQuery storage (~$0.02/GB/month for the index file). An IVF or TREE_AH index on 1M × 768-float vectors is ~3 GB → ~$0.06/month.
VECTOR_SEARCH queries bill on slot usage; a single ANN query is ~$0.0001 with the index. Without the index, scales linearly with table size.
ML.GENERATE_TEXT (for the LLM reranker / answerer) bills per output token via the remote Gemini endpoint — this dominates the cost in production RAG.

Common interview probes on the vector stack.

"Why VECTOR_SEARCH in BigQuery instead of Pinecone?" — same data plane, same IAM, no cross-cloud egress, no separate vector DB to operate.
"What's the difference between IVF and TREE_AH?" — IVF buckets by k-means centroids; TREE_AH is Google's ScaNN — generally faster recall@k for the same memory footprint in 2026.
"How do you handle a streaming corpus?" — indexes auto-update incrementally; queries hit the indexed portion with the index and the un-indexed tail with brute force.
"How is multimodal embedding different from text?" — ML.GENERATE_EMBEDDING (note: no _TEXT_) accepts image or text inputs; the resulting vector lives in the same space, so you can match text queries against image documents.

Worked example — embed a docs corpus once

Detailed explanation. The foundational step in RAG-in-SQL — embed an entire corpus once, into a table that pairs (doc_id, content, embedding). Subsequent queries embed only the user query and match against this table.

Question. Embed a 100K-row docs table (each row has doc_id and content) into a new docs_with_emb table using text-embedding-004. Set up the remote model first.

Input.

doc_id	content
d1	BigQuery ML supports linear and logistic regression models trained in SQL.
d2	ML.PREDICT runs inference inside the query engine, no separate endpoint required.
d3	VECTOR_SEARCH performs approximate nearest neighbour search over an embedding column.
...	...

Code.

-- 1) Register the remote embedding model (one-time)
CREATE OR REPLACE MODEL `analytics.embedding_004`
REMOTE WITH CONNECTION `us.vertex-connection`
OPTIONS(endpoint = 'text-embedding-004');

-- 2) Embed the corpus once — INSERT new column
CREATE OR REPLACE TABLE `analytics.docs_with_emb` AS
SELECT
  doc_id,
  content,
  ml_generate_embedding_result AS embedding
FROM ML.GENERATE_TEXT_EMBEDDING(
  MODEL `analytics.embedding_004`,
  (SELECT doc_id, content AS content FROM `analytics.docs`),
  STRUCT(TRUE AS flatten_json_output, 'RETRIEVAL_DOCUMENT' AS task_type)
);

Step-by-step explanation.

The CREATE MODEL ... REMOTE WITH CONNECTION step registers the Vertex embedding endpoint as a BQML model object. The connection (us.vertex-connection) was created previously via bq mk --connection.
ML.GENERATE_TEXT_EMBEDDING takes the model, a sub-query that emits a content column (the column name is fixed by the function), and an options STRUCT.
task_type = 'RETRIEVAL_DOCUMENT' tells the embedding model that these are documents (not queries) — important because text-embedding-004 produces asymmetric embeddings (different vectors for queries vs docs even for identical text).
flatten_json_output = TRUE returns the embedding as ml_generate_embedding_result ARRAY<FLOAT64> instead of a nested JSON struct. Easier to query downstream.
The whole corpus is embedded in one statement — BQML parallelises the calls across slots, batching ~100 rows per outbound API call. For 100K docs, this takes ~5-10 minutes and costs ~$1.25 at text-embedding-004 pricing.

Output.

doc_id	content	embedding
d1	BigQuery ML supports linear ...	0.012, -0.045, 0.083, ..., 0.011
d2	ML.PREDICT runs inference ...	0.024, -0.018, 0.061, ..., -0.007
d3	VECTOR_SEARCH performs ...	0.005, -0.071, 0.094, ..., 0.029

Rule of thumb. Embed once on the bulk corpus, then keep a Scheduled Query that embeds the daily diff. For corpora that update continuously, the diff embed is cheap (~$0.02 for 10K new docs/day) and keeps the vector store fresh.

Worked example — VECTOR_SEARCH end-to-end with an index

Detailed explanation. The full RAG round-trip — user query in, embed it, VECTOR_SEARCH against the indexed corpus, pass top-3 to Gemini for an answer. All in one query.

Question. Build a vector index on docs_with_emb, then implement end-to-end retrieval: given a user query string, return the top-3 docs by cosine similarity and a synthesised Gemini answer that cites them.

Input.

doc_id	content	embedding
(100K rows)

User query: "How do I forecast sales in BigQuery without standing up a Python service?"

Code.

-- 1) Create the vector index (one-time)
CREATE VECTOR INDEX `analytics.docs_idx`
ON `analytics.docs_with_emb`(embedding)
OPTIONS(
  distance_type = 'COSINE',
  index_type    = 'TREE_AH'
);

-- 2) Register a remote Gemini model for the answer step
CREATE OR REPLACE MODEL `analytics.gemini_pro`
REMOTE WITH CONNECTION `us.vertex-connection`
OPTIONS(endpoint = 'gemini-1.5-pro');

-- 3) Query — full RAG round-trip in one CTE chain
WITH user_query AS (
  SELECT 'How do I forecast sales in BigQuery without standing up a Python service?' AS content
),
query_emb AS (
  SELECT ml_generate_embedding_result AS qvec
  FROM ML.GENERATE_TEXT_EMBEDDING(
    MODEL `analytics.embedding_004`,
    TABLE user_query,
    STRUCT(TRUE AS flatten_json_output, 'RETRIEVAL_QUERY' AS task_type)
  )
),
top_docs AS (
  SELECT base.doc_id, base.content, distance
  FROM VECTOR_SEARCH(
    TABLE `analytics.docs_with_emb`,
    'embedding',
    (SELECT qvec FROM query_emb),
    top_k => 3,
    distance_type => 'COSINE',
    options => '{"use_brute_force": false}'
  )
  ORDER BY distance ASC
),
context AS (
  SELECT
    STRING_AGG(CONCAT('[', doc_id, '] ', content), '\n\n') AS ctx
  FROM top_docs
)
SELECT
  ml_generate_text_result AS answer,
  (SELECT ARRAY_AGG(doc_id ORDER BY distance) FROM top_docs) AS citations
FROM ML.GENERATE_TEXT(
  MODEL `analytics.gemini_pro`,
  (
    SELECT
      CONCAT(
        'Answer the user question using only the provided documents. ',
        'Cite each doc by its bracketed id.\n\n',
        'Documents:\n', (SELECT ctx FROM context),
        '\n\nQuestion: How do I forecast sales in BigQuery without standing up a Python service?'
      ) AS prompt
  ),
  STRUCT(0.2 AS temperature, 1024 AS max_output_tokens, TRUE AS flatten_json_output)
);

Step-by-step explanation.

CREATE VECTOR INDEX builds a TREE_AH index on the embedding column with cosine distance. For 100K rows × 768-float vectors, this takes ~2 minutes and ~3 GB of storage.
The query CTE chain: user_query is the literal text → query_emb runs ML.GENERATE_TEXT_EMBEDDING with task_type = 'RETRIEVAL_QUERY' (note: different from documents).
top_docs calls VECTOR_SEARCH with the query vector against the indexed table, top_k => 3, cosine distance, and use_brute_force = false to force index use. Returns the 3 nearest docs with their distance.
context concatenates the top docs into a single context string with each doc tagged by [doc_id]. This is the standard RAG prompt-engineering pattern — let the model cite the docs by ID.
The final ML.GENERATE_TEXT call passes the assembled prompt to Gemini-1.5-pro with temperature = 0.2 (low randomness for factual answers) and a 1024-token cap. Returns the synthesised answer plus the citations array from top_docs.
The entire round-trip — embed query, ANN search, LLM answer — happens in one SQL statement. No Python, no LangChain, no separate vector DB.

Output.

answer	citations
To forecast sales in BigQuery without standing up a Python service, use the built-in `arima_plus` model via `CREATE MODEL`. It auto-detects seasonality and trend [d27]. Forecast horizons are produced via `ML.FORECAST` with a confidence_level [d12]. The whole flow runs inside BigQuery slots, no separate endpoint required [d2].	[d27, d12, d2]

Rule of thumb. Run RAG-in-SQL when the corpus is already in BigQuery and the team writes more SQL than Python. For corpora outside BigQuery, the LangChain + Pinecone path may still be cheaper. The break-even is roughly "is the corpus > 100K docs and does it live in BigQuery?"

Worked example — multimodal embedding for image search

Detailed explanation. Product-image search is the canonical multimodal RAG case — embed each product image and each text query into a shared vector space, then match. BQML's ML.GENERATE_EMBEDDING (no _TEXT_) handles both modalities through the multimodal-embedding-001 endpoint.

Question. A products table has product_id and image_uri (GCS paths). Embed every image, then let a user search via text query.

Input.

product_id	image_uri
p1	gs://catalog/p1.jpg
p2	gs://catalog/p2.jpg
p3	gs://catalog/p3.jpg

User query: "red running shoes"

Code.

-- 1) Register the multimodal embedding model
CREATE OR REPLACE MODEL `analytics.mm_emb`
REMOTE WITH CONNECTION `us.vertex-connection`
OPTIONS(endpoint = 'multimodal-embedding-001');

-- 2) Embed product images via OBJECTREF
CREATE OR REPLACE TABLE `analytics.products_with_emb` AS
SELECT
  product_id,
  ml_generate_embedding_result AS embedding
FROM ML.GENERATE_EMBEDDING(
  MODEL `analytics.mm_emb`,
  (
    SELECT
      product_id,
      OBJ.MAKE_REF(image_uri, 'us.vertex-connection') AS content
    FROM `analytics.products`
  ),
  STRUCT(1408 AS output_dimensionality, TRUE AS flatten_json_output)
);

-- 3) Build a vector index
CREATE VECTOR INDEX `analytics.products_idx`
ON `analytics.products_with_emb`(embedding)
OPTIONS(distance_type = 'COSINE', index_type = 'TREE_AH');

-- 4) Text query → match against product images
WITH q AS (
  SELECT ml_generate_embedding_result AS qvec
  FROM ML.GENERATE_EMBEDDING(
    MODEL `analytics.mm_emb`,
    (SELECT 'red running shoes' AS content),
    STRUCT(1408 AS output_dimensionality, TRUE AS flatten_json_output)
  )
)
SELECT base.product_id, distance
FROM VECTOR_SEARCH(
  TABLE `analytics.products_with_emb`,
  'embedding',
  (SELECT qvec FROM q),
  top_k => 5,
  distance_type => 'COSINE'
)
ORDER BY distance ASC;

Step-by-step explanation.

multimodal-embedding-001 produces 1408-dim vectors in a shared text-image space. The output_dimensionality = 1408 is the model's native dimension.
For images, BQML uses OBJ.MAKE_REF(image_uri, connection) to pass the GCS image to the embedding endpoint without copying the bytes through BigQuery. The connection grants the embedding endpoint read access to the GCS object.
For text queries, the same model accepts a STRING content directly. The resulting vector lives in the same 1408-dim space as the image embeddings.
The VECTOR_SEARCH then matches text query vector against image embeddings — cosine distance is small when the image is semantically close to the text (red running shoes → an image of a red running shoe).
The whole thing fits in one schema: one multimodal-embedding-001 model registration, one embedding column per table, one shared index. No bespoke image-feature pipeline.

Output.

product_id	distance
p17	0.18
p84	0.21
p3	0.24
p219	0.27
p52	0.29

Rule of thumb. For image search where the catalog already lives behind GCS URIs, multimodal embeddings + VECTOR_SEARCH beat a separate vision pipeline on ops cost by an order of magnitude. The embedding model handles all the feature extraction; you only deal with vectors.

Senior interview question on RAG architecture choice

A senior interviewer might ask: "The team wants to ship a question-answering bot over 5 million internal documents. They're debating Pinecone + LangChain vs BigQuery vector search. Walk me through your recommendation and where the trade-offs flip."

Solution Using a corpus-locality + ops-tolerance framework

Decision framework — BQ Vector Search vs Pinecone + LangChain

1. Where does the corpus already live?
   - BigQuery / GCS  → BQ Vector Search is the lower-friction path
   - Outside GCP     → Pinecone path is more natural

2. What's the query volume?
   - < 10 QPS sustained          → BQ VS is fine
   - > 100 QPS sustained         → consider dedicated vector DB

3. What's the latency SLO?
   - p99 < 2s on a single search → both work
   - p99 < 100ms                 → Pinecone wins, BQ slot warmup hurts

4. What's the ops capacity?
   - SQL-first team, no MLOps platform → BQ VS
   - Existing MLOps + Python services  → Pinecone is fine

5. What's the corpus size?
   - < 1M docs    → BQ VS (cheaper, simpler)
   - 1-100M docs  → BQ VS with TREE_AH index
   - > 100M docs  → either works, ops shape decides

Step-by-step trace.

RAG requirement	Answer	Pushes to
Q1 — Corpus location	already in BigQuery	BQ VS (corpus locality)
Q2 — Query volume	~5 QPS sustained	BQ VS works
Q3 — Latency SLO	p99 < 1s	BQ VS (fine for batch + RAG)
Q4 — Ops capacity	SQL team, no MLOps	BQ VS (no new infra)
Q5 — Corpus size	5M docs	BQ VS with TREE_AH

Five BQ VS answers → BigQuery vector search is the obvious pick. If Q3 had been "p99 < 100ms" and Q2 "1K QPS", the same framework would have pushed to Pinecone unambiguously.

Output:

Stack	When it wins
BQ Vector Search	Corpus in BigQuery, < 100 QPS, p99 < 2s, SQL team
Pinecone + LangChain	Corpus elsewhere, > 100 QPS, p99 < 100ms, MLOps team

Why this works — concept by concept:

Corpus locality is decisive — moving 5M docs out of BigQuery into Pinecone is a real engineering project (export, embed, sync). If the docs are already in BigQuery, vector search in-place is the no-brainer.
QPS vs index size matter independently — BQ Vector Search is amazing for batch RAG and moderate-QPS interactive RAG. For high-QPS sustained traffic (sub-100ms p99 at 1K QPS), dedicated vector DBs still win.
Latency SLO ties to slot warmup — BigQuery slots can cold-start; a first query after idle can take 500ms-2s. Pinecone serves from an always-on cluster. For chatbots that need sub-second first responses, Pinecone has the edge.
Ops cost compounds — Pinecone is yet another vendor, yet another billing relationship, yet another SDK, yet another set of credentials. For a SQL-first team, BQ VS adds zero ops surface.
Cost — BQ VS storage scales with vectors (~3 GB / 1M vectors); query cost is slot-based per query. Pinecone scales with pod count + sustained QPS. The cost crossover is roughly at 100+ sustained QPS — below that, BQ VS is cheaper; above it, Pinecone's dedicated infra amortises better.

SQL
Topic — sql
VECTOR_SEARCH RAG problems

Practice →

ETL Topic — etl Embedding pipeline problems

Practice →

5. BQML vs Vertex vs Snowflake Cortex

`bigquery ml` competes with Vertex AI on the same cloud and Snowflake Cortex on the other warehouse — the 2026 decision is "where does the data live and where does the team write code"

The mental model in one line: BQML, Vertex AI, and Snowflake Cortex occupy three corners of the warehouse-ML triangle — BQML is GCP's warehouse-native ML, Vertex AI is GCP's full MLOps platform, Snowflake Cortex is Snowflake's BQML-equivalent — and the right pick collapses to the data warehouse you already own plus the latency and lifecycle requirements. Once you say that, every "BQML vs X" interview question reduces to a deduction from those two axes.

BQML — strengths and constraints.

Strengths. Warehouse-native, SQL-first, zero new ops, IAM via BigQuery, cost = slots + bytes, remote models bridge to Gemini, batch inference is essentially free, time-series and vector search are GA, embeddings and LLM via remote model are SQL-callable.
Constraints. No GPU training (only via remote / Vertex), limited model architectures (no custom losses, no full PyTorch / TF training), no built-in online serving (batch + BI Engine substitute), no model registry / lineage that matches Vertex Model Registry's depth.
Sweet spot. Analytics ML on BigQuery-resident data, batch inference, time-series forecasting, embedding + vector search, LLM calls via Gemini remote, SQL-first teams.

Vertex AI — strengths and constraints.

Strengths. Custom training (PyTorch / TF / JAX with GPU + TPU), online serving with autoscaling endpoints, Model Registry with lineage and versioning, Pipelines (Kubeflow-based) for MLOps, AutoML for vision / video / NLP / forecasting, Feature Store, Experiments, Model Monitoring.
Constraints. Higher ops surface (endpoints, pipelines, registry), per-second-uptime billing on endpoints, requires Python skills + MLOps platform competency, separate IAM and audit trail from BigQuery.
Sweet spot. Custom architectures, sub-100ms online serving, full MLOps lifecycle, multi-team model governance, vision / audio / video / fine-tuned LLM training.

Snowflake Cortex — the BQML equivalent on Snowflake.

Cortex Functions. SNOWFLAKE.CORTEX.COMPLETE(model, prompt), SNOWFLAKE.CORTEX.EMBED_TEXT_768, SNOWFLAKE.CORTEX.SUMMARIZE, SNOWFLAKE.CORTEX.SENTIMENT — direct SQL wrappers around Snowflake-hosted LLMs (Llama 3, Mistral, Gemma).
Cortex Analyst. Natural-language-to-SQL over a Snowflake semantic model.
Cortex Search. Vector + keyword hybrid search over a Snowflake table (analogous to BigQuery's VECTOR_SEARCH).
Cortex Fine-Tuning. Fine-tune Cortex-provided models on Snowflake data via SQL.
Gap. No first-class trainable tabular ML equivalent to BQML's boosted_tree_classifier etc — Snowflake's tabular ML story relies on Snowpark + scikit-learn / XGBoost, not Cortex.

The 2026 decision matrix.

You already use BigQuery. Default to BQML for analytics ML; reach for Vertex when you need custom training, online serving, or full MLOps lineage. Use BQML remote models to bridge to Gemini without leaving SQL.
You already use Snowflake. Default to Cortex Functions for LLM / embedding / vector search; reach for Snowpark + scikit-learn for tabular ML; reach for Cortex Fine-Tuning for narrow LLM customisation. There is no in-warehouse tabular ML equivalent to BQML.
You use both warehouses. Pick the ML stack on the warehouse where the training data lives. Cross-warehouse pipelines (BigQuery training → Snowflake serving) are real ops cost and should be avoided.

The hybrid pattern — BQML inference + Vertex training.

Train a research-grade model in Vertex Custom Training (PyTorch + GPU + custom loss).
Export the trained model to TensorFlow SavedModel or ONNX.
Import into BQML via MODEL_TYPE='tensorflow' | 'onnx'.
Serve batch inference from BQML at warehouse-cost; serve online inference from a Vertex endpoint where sub-100ms latency is required.

Common interview probes on the cross-platform comparison.

"When does Vertex beat BQML?" — sub-100ms online serving, custom architectures, full MLOps lifecycle.
"When does BQML beat Vertex?" — batch inference cost, SQL-first teams, warehouse-resident data, time-series forecasting, embedding + vector search in-place.
"How does Snowflake Cortex compare?" — Cortex covers LLM / embedding / vector search but lacks BQML's tabular ML surface.
"Can you mix BQML and Vertex?" — yes, via Vertex Model Registry import → BQML import, or BQML remote models that call Vertex endpoints.

Worked example — BQML vs Vertex cost on 50M-row nightly scoring

Detailed explanation. A senior interview probe — "show me the dollar math for serving a tabular classifier at 50M rows/night on BQML vs Vertex." The number isn't just a magnitude — it's a structural argument.

Question. Compute the steady-state monthly cost of scoring 50M rows × 30 nights on BQML vs Vertex Online Prediction (small instance) vs Vertex Batch Prediction (the closer apples-to-apples comparison).

Input.

Path	Pricing model
BQML on-demand	$6.25 / TB scanned
Vertex Online Prediction	$0.20 / hour per endpoint replica
Vertex Batch Prediction	$0.08 / hour per worker × hours used

Code.

-- BQML batch — INSERT one-shot
INSERT INTO `analytics.churn_scores`
SELECT CURRENT_DATE() AS scored_at, customer_id, predicted_churned, prob
FROM ML.PREDICT(MODEL `analytics.churn_bt`, (SELECT * FROM `analytics.customers`));
-- ~2.5 GB scanned per night × 30 nights × $6.25/TB = ~$0.48/month

# Vertex Online Prediction — 1 small replica running 24x7
# $0.20/hour × 24 × 30 = $144/month (per replica, idle or not)
# To finish 50M/night you need ~30 replicas → ~$4,320/month

# Vertex Batch Prediction — only billed during the actual job
# 50M rows at 1K rows/sec = 50,000 seconds = ~14 hours per night
# 14 hours × 30 nights × $0.08/hour × (assume 4 workers) = ~$135/month

Step-by-step explanation.

BQML batch scoring scans ~2.5 GB per run (50M rows × ~50 bytes/row). At on-demand pricing this is $0.016 per night, ~$0.48/month. On a slot-based reservation, the cost is amortised in the reservation.
Vertex Online Prediction bills per-second-uptime — even at 0 QPS, the endpoint costs $0.20/hour. To finish 50M/night within an overnight window you need many replicas (~30 small ones), driving cost to ~$4,300/month.
Vertex Batch Prediction is the apples-to-apples comparison — only billed during the job. 14 hours × 30 nights × $0.08/hour × 4 workers ≈ $135/month. Closer to BQML but still ~280x more expensive.
The structural reason BQML wins: it amortises inference cost over the same slot pool you already pay for analytics queries. There is no second compute pool to provision, scale, idle, or monitor.
The crossover where Vertex wins is real-time. If you need sub-second p99 on a per-row predict, BQML's batch model doesn't apply; the Vertex Online endpoint cost is justified by the latency SLO that BQML cannot meet.

Output.

Path	Monthly cost	Latency	Net-new ops
BQML batch	$0.48	overnight	none
Vertex Batch Prediction	~$135	14h/night	endpoint + job orchestration
Vertex Online Prediction	~$4,320	< 100ms	endpoint + autoscaling + monitoring

Rule of thumb. For overnight batch scoring of warehouse-resident data, BQML is 2-3 orders of magnitude cheaper than Vertex. The structural reason is "no idle endpoint cost", not just "cheap compute".

Worked example — Snowflake Cortex equivalent translation

Detailed explanation. When the team is bi-warehouse (BigQuery for analytics, Snowflake for product), interviewers love asking you to translate a BQML pipeline to Snowflake Cortex and back. The translation reveals which capabilities are 1:1, which are missing, and where to bridge.

Question. Translate this BQML pipeline (embed docs, vector search, LLM answer) into Snowflake Cortex SQL.

Input.

BQML primitive	Purpose
ML.GENERATE_TEXT_EMBEDDING + remote text-embedding-004	Embed docs
CREATE VECTOR INDEX + VECTOR_SEARCH	ANN
ML.GENERATE_TEXT + remote gemini-1.5-pro	Answer

Code.

-- BQML version (recap)
WITH q_emb AS (
  SELECT ml_generate_embedding_result AS qvec
  FROM ML.GENERATE_TEXT_EMBEDDING(MODEL `analytics.embedding_004`,
                                  (SELECT 'how to forecast in SQL?' AS content),
                                  STRUCT(TRUE AS flatten_json_output))
)
SELECT base.doc_id, base.content, distance
FROM VECTOR_SEARCH(TABLE `analytics.docs_with_emb`, 'embedding',
                   (SELECT qvec FROM q_emb), top_k => 3);

-- Snowflake Cortex equivalent
WITH q_emb AS (
  SELECT SNOWFLAKE.CORTEX.EMBED_TEXT_768('e5-base-v2',
                                          'how to forecast in SQL?') AS qvec
),
docs_scored AS (
  SELECT doc_id,
         content,
         VECTOR_COSINE_SIMILARITY(embedding, (SELECT qvec FROM q_emb)) AS sim
  FROM docs_with_emb
)
SELECT doc_id, content, 1 - sim AS distance
FROM docs_scored
ORDER BY sim DESC
LIMIT 3;

-- Or with Cortex Search (the BigQuery VECTOR_SEARCH equivalent)
-- (Service must be created with CREATE CORTEX SEARCH SERVICE.)
SELECT *
FROM TABLE(CORTEX_SEARCH_SERVICE!('docs_search', 'how to forecast in SQL?', 3));

Step-by-step explanation.

The embedding step maps cleanly: BQML's ML.GENERATE_TEXT_EMBEDDING with a remote model becomes Snowflake's SNOWFLAKE.CORTEX.EMBED_TEXT_768 with a model name argument.
The ANN step has two flavours on Snowflake. Manual: store embeddings as VECTOR(FLOAT, 768) and use VECTOR_COSINE_SIMILARITY in an ORDER BY — works for ~< 1M rows. Indexed: create a Cortex Search Service, which builds a hybrid keyword + vector index under the hood.
BQML's CREATE VECTOR INDEX and Snowflake's CREATE CORTEX SEARCH SERVICE are roughly equivalent — both turn O(N) scans into O(log N) lookups, both auto-incrementally update.
The LLM answer step uses SNOWFLAKE.CORTEX.COMPLETE('llama3-70b', prompt) on Snowflake — analogous to BQML's ML.GENERATE_TEXT with a Gemini remote model.
The gap: Snowflake Cortex does not ship a trainable tabular ML surface (no boosted_tree_classifier equivalent). For that, you fall back to Snowpark + scikit-learn or import a model. BQML's tabular trainable surface remains a real differentiator.

Output.

Capability	BQML	Snowflake Cortex
Embedding	ML.GENERATE_TEXT_EMBEDDING	CORTEX.EMBED_TEXT_768
ANN	CREATE VECTOR INDEX + VECTOR_SEARCH	Cortex Search Service
LLM completion	ML.GENERATE_TEXT (remote Gemini)	CORTEX.COMPLETE (Llama/Mistral)
Trainable tabular ML	yes (boosted_tree, dnn, arima_plus)	no (Snowpark only)
Forecasting	arima_plus, times_fm	Snowflake-native forecast functions

Rule of thumb. For LLM / embedding / vector workloads, BQML and Cortex are roughly equivalent — pick the warehouse you already own. For trainable tabular ML, BQML has a meaningful advantage; Snowflake teams typically fall back to Snowpark + scikit-learn.

Worked example — hybrid BQML + Vertex pipeline

Detailed explanation. The most common 2026 production pattern — train novel models in Vertex (custom architecture, GPUs, full MLOps lifecycle), serve them from BQML for batch + warehouse-resident inference. The trick is the export/import handoff.

Question. A Vertex Custom Training job produces a fine-tuned TensorFlow model that classifies support tickets. Wrap it as a BQML model so analysts can score nightly in SQL.

Input.

Artifact	Where
TensorFlow SavedModel	gs://models/ticket-classifier/v3/
Vertex endpoint (online)	projects/.../endpoints/abc123
Batch scoring need	nightly on `support_tickets` table

Code.

-- Wrap the Vertex-trained TF model as a BQML model for batch scoring
CREATE OR REPLACE MODEL `analytics.ticket_clf_imported`
OPTIONS(
  model_type = 'tensorflow',
  model_path = 'gs://models/ticket-classifier/v3/*'
);

-- Nightly batch scoring inside BQ — no Vertex endpoint required
INSERT INTO `analytics.ticket_scores`
SELECT
  CURRENT_DATE() AS scored_at,
  ticket_id,
  predicted_category,
  predicted_category_probs
FROM ML.PREDICT(
  MODEL `analytics.ticket_clf_imported`,
  (SELECT ticket_id, ticket_text, customer_tier
   FROM `analytics.support_tickets`
   WHERE created_date = CURRENT_DATE() - 1)
);

-- ALSO keep the Vertex endpoint for online single-ticket lookup
-- (Bridged from BigQuery via BQ.AI_PREDICT or via the app layer.)

Step-by-step explanation.

The Vertex Custom Training job (Python + GPU + custom architecture) exports a TensorFlow SavedModel to GCS. This is the standard checkpoint format.
CREATE MODEL ... model_type = 'tensorflow', model_path = 'gs://.../*' registers the SavedModel as a BQML model. The input feature names in the SavedModel signature must match the column names passed to ML.PREDICT.
Batch scoring runs entirely inside BigQuery — no Vertex endpoint cost, no Python orchestration. For 1M tickets/day this is a few cents per night.
The Vertex endpoint stays alive for online single-ticket lookups where a customer-support agent needs sub-second classification. Best-of-both-worlds: BQML for batch, Vertex for online.
Retraining flow: Vertex Custom Training produces v4, exports to gs://models/ticket-classifier/v4/. BQML re-imports via CREATE OR REPLACE MODEL ... model_path = 'gs://models/ticket-classifier/v4/*'. The model registry lineage stays in Vertex; BQML just consumes the latest pointer.

Output.

Layer	Tool	Cost	Latency
Training (custom arch + GPU)	Vertex Custom Training	$X per job	hours
Batch scoring (nightly)	BQML imported model	~$0.5 / month	overnight
Online scoring (agent UI)	Vertex Online Prediction	~$144 / month / replica	< 100ms
Model versioning	Vertex Model Registry	$0 (free tier)	n/a

Rule of thumb. The hybrid pattern (Vertex training + BQML batch serving + Vertex online serving) is the production default for medium-to-large teams. BQML alone for analytics ML; Vertex alone for vision / custom; hybrid when you need both batch and online on the same model.

Senior interview question on platform strategy

A senior interviewer might frame this as: "You join a team running 12 ML models in production. Some live in Vertex Online endpoints; some in Cloud Run with FastAPI; some run as Spark jobs. The CFO wants to consolidate. What's your platform strategy and how do you justify it?"

Solution Using a workload classification + 5-bucket consolidation plan

Consolidation plan — 12 production models

Bucket A: SQL-batch analytics ML (churn, propensity, segmentation)
  → Move to BQML. Native CREATE MODEL + ML.PREDICT.
  → Wins: zero new ops, batch cost near zero, SQL-team-owned.

Bucket B: Time-series forecasts (sales, inventory, demand)
  → Move to BQML ARIMA_PLUS / ARIMA_PLUS_XREG.
  → Wins: multi-series fan-out in one CREATE MODEL, no Python.

Bucket C: Embeddings + RAG over warehouse data
  → Move to BQML remote (Gemini) + VECTOR_SEARCH.
  → Wins: one stack for embed + search + answer, IAM via BQ.

Bucket D: Online real-time scoring (fraud, recommender)
  → Keep on Vertex Online Prediction; standardise via Model Registry.
  → Wins: sub-100ms SLO, autoscaling, single online platform.

Bucket E: Custom architectures (vision, audio, fine-tuned LLM)
  → Train on Vertex; if batch serving needed, import to BQML.
  → Wins: GPU training where needed, batch serving cost-amortised.

Step-by-step trace.

Model	Current home	Move to	Reason
Churn classifier	Cloud Run + scikit-learn	BQML boosted_tree	warehouse-resident, batch
Propensity score	Vertex Online	BQML boosted_tree	overnight refresh suffices
Daily sales forecast	Spark + Prophet	BQML ARIMA_PLUS	multi-series, SQL
Support ticket RAG	Cloud Run + LangChain	BQML + Gemini remote	corpus in BQ
Fraud at-swipe	Vertex Online	stay	< 50ms SLO
Personalised recommender	Vertex Online	stay	< 100ms SLO
Vision quality check	Cloud Run + ONNX	Vertex (train) + BQML (batch)	hybrid
LLM summariser	LangChain	BQML + Gemini remote	SQL-callable
Anomaly detection	Spark + kmeans	BQML kmeans	SQL refresh
Inventory forecast	Spark + Prophet	BQML ARIMA_PLUS	warehouse data
Segmentation	Spark + kmeans	BQML kmeans	warehouse data
Sentiment analysis	Cloud Run + HuggingFace	BQML ML.UNDERSTAND_TEXT	SQL-native

10 of 12 models consolidate to BQML; 2 stay on Vertex for online SLO. Spark and Cloud Run ML-serving workloads drop to near-zero.

Output:

Bucket	Models	Platform	Approx monthly cost (steady state)
A — SQL-batch ML	4	BQML	< $50
B — Forecasting	2	BQML ARIMA_PLUS	< $20
C — Embeddings + RAG	2	BQML + Gemini remote	~$200 (LLM tokens)
D — Online	2	Vertex Online	~$600
E — Custom	2	Vertex train + BQML batch	~$300
Total	12	(consolidated)	~$1,170

Why this works — concept by concept:

Workload classification first — bucketing by SLO + data location + model class is the discipline that drives platform consolidation. Without it you end up with ad-hoc per-model decisions that compound the very fragmentation you wanted to remove.
BQML for the analytics 80% — batch / warehouse-resident / SQL-team-owned ML belongs in BQML. This is where the largest cost wins live (no idle endpoints, no orchestrator overhead).
Vertex Online for the latency 20% — sub-100ms SLOs require always-on dedicated serving. Vertex Online is the standard; consolidate online models onto it.
Hybrid for the custom corner — vision and fine-tuned LLM models need Vertex for training and either Vertex (online) or BQML (batch) for serving. The hybrid pattern is mature and standard.
Cost — consolidation usually saves 50-80% versus the per-model ad-hoc setup. Bigger wins come from killing always-on Cloud Run + endpoint replicas for batch workloads — replacing N idle endpoints with ML.PREDICT in BQML.

SQL
Topic — sql
BQML platform-strategy problems

Practice →

ETL
Topic — etl · medium
Cross-platform ML pipelines

Practice →

Cheat sheet — BQML recipes

8-line CREATE MODEL boosted tree. CREATE OR REPLACE MODEL ds.m OPTIONS(model_type='boosted_tree_classifier', input_label_cols=['y'], auto_class_weights=TRUE, l2_reg=0.1, max_tree_depth=6, early_stop=TRUE) AS SELECT * FROM training. Default tabular classifier for 80% of analytics ML.
Logistic regression default. model_type='logistic_reg' with auto_class_weights=TRUE and l2_reg=0.01. Reach for it when explainability via ML.WEIGHTS is the primary KPI.
Time-series forecasting. model_type='arima_plus' with time_series_timestamp_col, time_series_data_col, optional time_series_id_col for multi-series fan-out, auto_arima=TRUE, holiday_region='US'. Forecast via ML.FORECAST(MODEL ..., STRUCT(28 AS horizon, 0.95 AS confidence_level)).
ML.PREDICT with custom threshold. ML.PREDICT(MODEL ..., (SELECT ... ), STRUCT(0.7 AS threshold)) for binary classifiers — tune precision/recall without retraining.
ML.EXPLAIN_PREDICT. Returns top_feature_attributions per row. Use for audit and per-row debugging, not bulk inference — ~2-5x slower than ML.PREDICT.
ML.EVALUATE on custom holdout. ML.EVALUATE(MODEL ..., (SELECT ... WHERE event_date > '...')) for drift monitoring; schedule via Scheduled Queries; alert when ROC-AUC drops > 5%.
Hyperparameter tuning built-in. Add num_trials=20, hparam_tuning_objectives=['roc_auc'] to OPTIONS — BQML runs Vizier-style search inside the same CREATE MODEL. No separate sweep job.
Embedding pipeline. CREATE MODEL ds.embedder REMOTE WITH CONNECTION 'region.conn' OPTIONS(endpoint='text-embedding-004') → INSERT INTO docs_with_emb SELECT *, ml_generate_embedding_result AS emb FROM ML.GENERATE_TEXT_EMBEDDING(MODEL ds.embedder, (SELECT * FROM docs), STRUCT(TRUE AS flatten_json_output, 'RETRIEVAL_DOCUMENT' AS task_type)).
VECTOR_SEARCH with TREE_AH index. CREATE VECTOR INDEX idx ON docs_with_emb(embedding) OPTIONS(distance_type='COSINE', index_type='TREE_AH') then VECTOR_SEARCH(TABLE docs_with_emb, 'embedding', (SELECT qvec FROM ...), top_k => 5). Sub-second on multi-million-row corpora.
Gemini remote model for RAG. CREATE MODEL ds.gemini REMOTE WITH CONNECTION ... OPTIONS(endpoint='gemini-1.5-pro') → ML.GENERATE_TEXT(MODEL ds.gemini, (SELECT prompt FROM ...), STRUCT(0.2 AS temperature, 1024 AS max_output_tokens, TRUE AS flatten_json_output)).
Imported XGBoost. Train in Python, export booster to GCS, register via CREATE MODEL ds.m OPTIONS(model_type='xgboost', model_path='gs://bucket/model/*'). BQML serves predictions; you retrain in Python.
k-means anomaly detection. Train kmeans with standardize_features=TRUE, score via ML.PREDICT, threshold on nearest_centroids_distance[OFFSET(0)].distance at the 99th percentile.
BI Engine for sub-second dashboards. Materialise ML.PREDICT output to a PARTITION BY scored_date CLUSTER BY customer_id table; reserve BI Engine capacity; lookups drop to < 200ms p99 without standing up a KV store.
Cost back-of-envelope. BQML batch inference: ~$6.25 / TB scanned. 50M-row classifier ≈ 2.5 GB scan ≈ $0.016 / run. Versus Vertex Online endpoint at $0.20 / hour / replica = $144 / month / replica idle. Batch wins by 3-4 orders of magnitude.
Retraining cadence pattern. Wrap CREATE OR REPLACE MODEL in a BigQuery Scheduled Query running nightly / weekly. Add ML.EVALUATE against the last week of labels; alert on AUC regression.

Frequently asked questions

What is BigQuery ML?

BigQuery ML (BQML) is Google Cloud's in-warehouse machine learning surface — train, evaluate, predict, forecast, embed, and vector-search models entirely from SQL inside the same BigQuery project that holds your data. The verb is CREATE MODEL; the training set is a SELECT; inference is ML.PREDICT, ML.FORECAST, ML.EVALUATE, ML.GENERATE_TEXT_EMBEDDING, or VECTOR_SEARCH depending on the workload. The 2026 surface covers linear / logistic regression, k-means, matrix factorisation, DNN, boosted trees, AutoML Tables, ARIMA_PLUS time-series, TimesFM, autoencoder, imported TensorFlow / ONNX / XGBoost models, plus remote models that bridge to Gemini, embedding-001, and arbitrary Vertex AI endpoints. The unifying idea — "move compute to the data, not the other way around" — is what makes bqml the senior-DE default for analytics ML on BigQuery-resident data.

Can BQML run deep learning models?

Yes — and in two ways. Native: model_type='dnn_classifier', 'dnn_regressor', 'dnn_linear_combined_classifier', 'dnn_linear_combined_regressor' (wide-and-deep), and 'autoencoder' train deep neural nets directly inside BigQuery using slot-seconds. You set hidden_units, dropout, activation_fn, optimizer, and BQML handles the rest. Imported: any TensorFlow SavedModel or ONNX model can be registered as a BQML model via MODEL_TYPE='tensorflow' | 'onnx' and a GCS path — useful when the model was trained on GPUs in Vertex Custom Training and now needs to be served from ml.predict bigquery for batch workloads. The trade-off is that BQML native deep models don't get GPU training (only CPU + slot-based parallelism), so for billion-row training sets or custom architectures, train in Vertex and import. For 1-100M-row tabular training, native BQML DNN is fine.

BQML vs Vertex AI — which one do I pick?

Use BQML when training data lives in BigQuery, batch or hourly inference is acceptable, the model class fits the BQML surface (tabular, time-series, embeddings, remote LLM), and the team writes more SQL than Python. Use Vertex AI when you need sub-100ms online serving, custom architectures (vision, audio, fine-tuned LLM), GPU/TPU training, or a full MLOps lifecycle with Model Registry, Pipelines, and Model Monitoring. The cost gap is huge in BQML's favour for batch — bigquery machine learning scales with bytes-scanned per query (no idle endpoint cost), whereas Vertex Online Prediction bills per-second-uptime regardless of QPS. The standard 2026 pattern is hybrid: train novel models on Vertex (with GPUs), import to BQML for batch serving, keep a Vertex endpoint alive only for the online subset that needs sub-100ms latency.

How does VECTOR_SEARCH work in BigQuery?

bigquery vector search runs an approximate nearest neighbour (ANN) over a column of embeddings. The function signature is VECTOR_SEARCH(TABLE corpus, 'embedding_column', (SELECT query_vector), top_k => N, distance_type => 'COSINE'|'EUCLIDEAN'|'DOT_PRODUCT'). Without an index, it scans the entire embedding column — O(N) but parallelised across slots, fine for up to ~100K rows. With CREATE VECTOR INDEX ... OPTIONS(index_type='TREE_AH') (Google's ScaNN-style accelerator) or 'IVF' (inverted-file with k-means centroids), the search becomes O(log N) and runs sub-second on multi-million-row corpora. Indexes update incrementally — new rows are auto-indexed in the background. Combined with ML.GENERATE_TEXT_EMBEDDING for the embedding step and a remote Gemini model for the answer step, you get a full RAG-in-SQL pipeline that replaces Pinecone + LangChain for any corpus already in BigQuery.

Is BQML cheaper than custom MLOps?

For batch / warehouse-resident workloads, dramatically so. A 50M-row nightly scoring job costs ~$0.48/month on bigquery ml (~2.5 GB scanned per run on on-demand pricing). The same job on a Vertex Online endpoint sized to finish overnight costs ~$4,000/month (replica uptime). Even Vertex Batch Prediction, the apples-to-apples comparison, runs ~$135/month for the same workload. The structural reason is that BQML amortises inference into the same slot pool you already pay for analytics — no idle compute, no second autoscaler. The crossover point where Vertex wins is sub-100ms online serving; below that latency floor, BQML cannot compete, but for everything overnight / hourly, BQML is 2-3 orders of magnitude cheaper.

How do I retrain a BQML model on a schedule?

Wrap CREATE OR REPLACE MODEL in a BigQuery Scheduled Query — point it at a date-windowed training set (WHERE event_date BETWEEN ... AND CURRENT_DATE() - 1) and schedule nightly or weekly. BQML keeps the last 4 versions of the model automatically, so rollback is ALTER MODEL ... SET OPTIONS(...) against an earlier version. Pair the scheduled retrain with a scheduled ML.EVALUATE against the most recent labelled holdout — alert if ROC-AUC drops by more than 5% from the prior week. For production rigour, layer Dataform or dbt on top to manage the SQL files in git and run them as a DAG; this gives you versioned bqml gemini / create model bigquery pipelines without standing up a separate MLOps orchestrator like Kubeflow or Airflow MLflow.

Practice on PipeCode

Drill the SQL practice library → for the CREATE MODEL / ML.PREDICT / VECTOR_SEARCH family of probes.
Rehearse on medium-difficulty ETL problems → when the interviewer wants warehouse-native pipeline depth.
Sharpen the ETL practice library → for the batch inference + scheduled retrain patterns.
Layer the aggregation library → for the feature-engineering-in-SQL family of probes.
For the broader surface, read top data engineering interview questions →.
Stack the prerequisites with the only 5 skills you need to become a data engineer →.

Lock in BQML muscle memory

BQ docs explain the functions. PipeCode drills explain the decision — when BQML beats Vertex, when VECTOR_SEARCH replaces Pinecone, when ML.FORECAST is enough vs ARIMA-from-scratch. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice ETL problems →

1. Why BQML is the senior-DE ML play in 2026

bigquery ml moves compute to the data — no separate Python pipeline, no GPU ops, no second governance perimeter

Worked example — same logistic regression, BQML vs Python

Worked example — batch scoring cost, BQML vs Vertex endpoint

Worked example — BQML governance + IAM

Senior interview question on BQML positioning

Solution Using a 4-question decision framework

2. BQML model surface — what trains in SQL

create model bigquery covers linear, tree, DNN, AutoML, ARIMA_PLUS, k-means, imports, and remote — one verb, the whole surface

Worked example — boosted tree classifier in 8 lines

Worked example — k-means anomaly detection in SQL

Worked example — importing an XGBoost model trained in Python

Senior interview question on BQML model selection

Solution Using a model-selection rubric anchored on data shape and ops tolerance

3. ML.PREDICT, ML.FORECAST, ML.EVALUATE

ml.predict bigquery is the universal inference verb; ML.FORECAST is the time-series specialist; ML.EVALUATE is the metric reporter

Worked example — ML.PREDICT with custom threshold + feature attributions

Worked example — ML.FORECAST with ARIMA_PLUS and confidence intervals

Worked example — ML.EVALUATE on a custom holdout

Senior interview question on production inference

Solution Using a materialised predictions table + BI Engine cache

4. Embeddings + vector search in SQL

bigquery embeddings + bigquery vector search collapse the entire RAG stack into a single SQL query

Worked example — embed a docs corpus once

Worked example — VECTOR_SEARCH end-to-end with an index

Worked example — multimodal embedding for image search

Senior interview question on RAG architecture choice

Solution Using a corpus-locality + ops-tolerance framework

5. BQML vs Vertex vs Snowflake Cortex

bigquery ml competes with Vertex AI on the same cloud and Snowflake Cortex on the other warehouse — the 2026 decision is "where does the data live and where does the team write code"

Worked example — BQML vs Vertex cost on 50M-row nightly scoring

Worked example — Snowflake Cortex equivalent translation

Worked example — hybrid BQML + Vertex pipeline

Senior interview question on platform strategy

Solution Using a workload classification + 5-bucket consolidation plan

Cheat sheet — BQML recipes

Frequently asked questions

What is BigQuery ML?

Can BQML run deep learning models?

BQML vs Vertex AI — which one do I pick?

How does VECTOR_SEARCH work in BigQuery?

Is BQML cheaper than custom MLOps?

How do I retrain a BQML model on a schedule?

Practice on PipeCode

Lock in BQML muscle memory

`bigquery ml` moves compute to the data — no separate Python pipeline, no GPU ops, no second governance perimeter

`create model bigquery` covers linear, tree, DNN, AutoML, ARIMA_PLUS, k-means, imports, and remote — one verb, the whole surface

`ml.predict bigquery` is the universal inference verb; `ML.FORECAST` is the time-series specialist; `ML.EVALUATE` is the metric reporter

`bigquery embeddings` + `bigquery vector search` collapse the entire RAG stack into a single SQL query

`bigquery ml` competes with Vertex AI on the same cloud and Snowflake Cortex on the other warehouse — the 2026 decision is "where does the data live and where does the team write code"