Articles

BigQuery ML Interview Questions: Training Splits, Transformations, and Model Evaluation

Review BigQuery ML interview questions on training splits, stored transformations, raw prediction inputs, leakage, and trustworthy evaluation metrics.

A BigQuery ML interview can turn on one misleading line of SQL: the model trains successfully, yet the reported score answers the wrong question. A strong answer traces four contracts—when features were available, which rows trained the model, how transformations are reused, and which population produced the evaluation metric.

The questions below use current official Google Cloud documentation and an original churn-model review exercise. They are preparation recommendations, not candidate reports of Google's interview process or evidence of question frequency.

What is wrong with a successful training query?

Consider this deliberately unsafe exercise:

CREATE OR REPLACE MODEL `practice.churn_bad`
OPTIONS(model_type='LOGISTIC_REG',
        input_label_cols=['churn_label']) AS
SELECT * FROM `practice.customer_snapshots`;

Assume the source contains one row per customer snapshot, a future cancellation timestamp, recent sessions, and the label. The query does not define a prediction time or an approved feature list. A column added later can silently alter the model's inputs. The fact that SQL executes would not establish that its features were available when the business needed the prediction.

Our repair starts outside CREATE MODEL: define the decision as predicting next-month churn at the snapshot time. Each feature must use only information available by that time, while the label must have a completed observation window. A later cancellation field can define the label but cannot become a predictor.

Also define the deployment population. Predicting future snapshots for existing customers and predicting entirely new customers are different tasks. A temporal holdout may suit the former; the latter also needs customer separation. Say which claim your evaluation supports before discussing a high score.

This is the central interview answer: a training success proves that the service accepted a model-building job, not that the experiment estimates future performance. The feature contract, membership query, and evaluation population supply that missing evidence.

How should you choose a training split?

For the original exercise, use January–March snapshots for training, April for model selection, and May as an untouched final test. Assume labels for all three periods have matured before evaluation. The dates are examples, not recommended universal window sizes.

A random row split can mix earlier and later customer snapshots. It may expose the model to future patterns and place closely related records in both sets. Our recommendation is to choose membership according to the intended deployment, then check customer overlap and label maturity explicitly.

Official BigQuery contract: For generalized linear models, CUSTOM without hyperparameter tuning uses a Boolean split column: FALSE means training, while TRUE or NULL means evaluation. With tuning, it uses a string column containing TRAIN, EVAL, or TEST. The split column is automatically excluded from features. Generalized linear model options.

Split design	What to explain in the interview	Check before trusting it
Explicit external periods	Training, selection, and final-test tables have distinct purposes	Time boundaries, matured labels, overlapping identities
`CUSTOM`, no tuning	Boolean membership follows the documented contract	Count `FALSE`, `TRUE`, and `NULL` separately
`CUSTOM`, with tuning	String membership allocates three purposes	Confirm only the intended membership values occur
`SEQ`	Ordering uses the selected split column	Tied times, repeated customers, and intended cutoff

Do not describe NULL as “ignored” in the Boolean case. It belongs to evaluation under this contract. If upstream logic uses null to mean an unknown assignment, make that discrepancy visible before model creation.

For explicit external periods, inspect a membership summary before training: rows, distinct customers, positives, and minimum/maximum snapshot times. Then test for duplicate customer-time keys. Those checks distinguish a well-defined experiment from three tables whose names merely say train, validation, and test.

What belongs in the TRANSFORM clause?

Official fact: BigQuery's TRANSFORM clause stores preprocessing with the model. At prediction, the model applies those transformations to the required original inputs. The general syntax also requires labels and a split column, when present, to pass through the clause without transformation. CREATE MODEL reference.

Our repaired example deliberately trains on a separately bounded training population. It disables automatic splitting and early stopping so the example's model-selection step remains explicit. This is a review pattern, not a claim that these options are optimal for every model.

CREATE OR REPLACE MODEL `practice.churn_model`
TRANSFORM(
  churn_label,
  tenure_days,
  COALESCE(plan_name, 'unknown') AS plan_name,
  ML.STANDARD_SCALER(sessions_7d) OVER () AS sessions_z
)
OPTIONS(
  model_type='LOGISTIC_REG',
  input_label_cols=['churn_label'],
  data_split_method='NO_SPLIT',
  early_stop=FALSE,
  max_iterations=20
) AS
SELECT churn_label, tenure_days, plan_name, sessions_7d
FROM `practice.customer_snapshots`
WHERE snapshot_date >= DATE '2026-01-01'
  AND snapshot_date < DATE '2026-04-01';

The source must already enforce point-in-time feature availability and mature labels. A date filter alone establishes neither. A retrospectively updated sessions_7d column could still leak future information into a historical row.

The explicit projection keeps cancellation timestamps and identifiers out of the feature list. The transformation normalizes the plan's missing-value representation and stores a scaling rule. Twenty iterations is an exercise parameter to inspect and vary using validation evidence, not a convergence guarantee.

If you switch this design to an internal custom split, carry its split column through TRANSFORM unchanged and verify the model-type-specific options. Do not copy the Boolean version into a tuning job merely because both use the word CUSTOM.

Why should prediction reuse training statistics?

Official fact: ML.STANDARD_SCALER standardizes numerical values; when used within TRANSFORM, its calculated mean and standard deviation are reused during prediction. ML.STANDARD_SCALER reference.

Use an original arithmetic oracle to explain the consequence. Suppose training values are 10, 20, and 30 sessions. Their mean is 20 and population standard deviation is approximately 8.165. A raw value of 30 therefore maps to about +1.225 under that fitted scale.

Now consider a prediction batch containing 30 and 40. Recomputing its mean and population standard deviation would produce 35 and 5, mapping 30 to −1. The same customer input would receive different features depending on who else appeared in the batch. That breaks the stored model's input meaning.

The useful debugging question is not “Did I standardize?” It is “Which population supplied the statistics, and are those exact statistics reused?” A transformation copied into a separate prediction query may look identical while fitting itself to a different population.

Google also provides ML.TRANSFORM for applying the preprocessing captured by an existing model. Use it to inspect the fitted transformation when diagnosing a discrepancy, rather than independently rebuilding the scale. ML.TRANSFORM reference.

What columns should ML.PREDICT receive?

Official fact: For a model trained with TRANSFORM, prediction needs the original input columns used by that clause. Extra input columns can pass through the result without becoming predictors. ML.PREDICT reference.

For our model, supply raw sessions_7d, tenure_days, and plan_name; do not substitute a manually computed sessions_z. Include customer_id for joining the output back to the intended customer. Its presence in the prediction query does not turn it into a feature of this already-trained model.

SELECT * FROM ML.PREDICT(
  MODEL `practice.churn_model`,
  (SELECT customer_id, tenure_days, plan_name, sessions_7d
   FROM `practice.scoring_snapshots`)
);

Before presenting this as a deployment contract, check column names, compatible types, missing-value behavior, and one-row-per-customer assumptions. The example leaves those checks visible rather than burying them inside a long feature query.

Ask a follow-up: what if sessions_7d changes from a count to a ratio while retaining its name? Compatible SQL types would not detect the semantic change. Preserve feature definitions and model version together, and compare the scoring distribution with the training contract.

What does ML.EVALUATE actually evaluate?

Official fact: Calling ML.EVALUATE without a table or query returns metrics generated during training. Supplying input evaluates the supplied population, subject to the model's input contract. Neither form automatically proves that the rows are an untouched future holdout. ML.EVALUATE reference.

For the exercise, use April to compare proposed configurations. After choosing the model and decision threshold, evaluate May once. Keep that test result out of the selection loop; repeatedly selecting changes against May would make it another validation set.

SELECT * FROM ML.EVALUATE(
  MODEL `practice.churn_model`,
  (SELECT churn_label, tenure_days, plan_name, sessions_7d
   FROM `practice.customer_snapshots`
   WHERE snapshot_date >= DATE '2026-05-01'
     AND snapshot_date < DATE '2026-06-01')
);

If your interview answer proposes hyperparameter tuning, separately explain which internal set selects a trial and which external set supports the final deployment claim. The word “test” in a service option is not permission to repeatedly inspect every held-out result.

How do you diagnose an impressive metric on the wrong population?

Use this synthetic confusion matrix, rather than inventing a cloud result: 1,000 evaluated customers include 50 who churn. The classifier finds 30 of them, flags 70 non-churners, misses 20 churners, and correctly clears 880 non-churners.

Accuracy is 910/1,000, or 91%. Precision is 30/100, or 30%; recall is 30/50, or 60%. A classifier that predicts no churn achieves 95% accuracy on this same population while finding none of the churners. Accuracy alone therefore does not justify the retention action.

Explain the business denominator: if the team contacts all 100 flagged customers, 30 are actual churners in this exercise. Whether that is useful depends on intervention cost, benefit, and the effect of contacting someone. Predictive quality does not prove the intervention itself reduces churn.

Then audit population integrity. Were missing features filtered away? Did an inner join remove customers without activity? Did label maturity exclude recent snapshots? Report exclusions and class prevalence beside the metric, because a score over easy surviving rows can conceal weak coverage.

Finally, compare slices relevant to the decision: plan, tenure, and new versus established customers. A single aggregate can hide a segment where false positives consume the intervention budget. Choose thresholds on validation data and document that choice before opening the final test.

What should you inspect when training and scoring disagree?

Start with one customer snapshot whose raw features you can write down. Compare the source row, the stored transformation's output, and the prediction query's inputs. This small inspection localizes a mismatch more effectively than changing several model options at once.

Suppose the training view uses an integer count of sessions, while a scoring view counts joined event rows. Duplicate events can inflate the scoring feature even though the SQL column retains the same name and type. Group both inputs at the customer-snapshot grain, inspect one duplicated key, and reconcile the count before investigating model coefficients.

Next compare missingness. A null plan becomes the exercise's explicit unknown category, while a missing numerical field needs its own documented handling. A join that removes customers with missing values changes coverage; an imputation rule changes feature values. Describe which behavior you intended and check both row counts and transformed values.

Finally, freeze the inputs used for comparison. If a scheduled query refreshes a source view between training and your inspection, differences may come from a new data snapshot rather than inconsistent preprocessing. Record source version or snapshot time, feature definitions, model identity, and the population filter together. This diagnostic record supports a precise answer to “Which contract changed?” without inventing a model-quality explanation.

Sources and Further Reading

Research checked October 4, 2026. SQL is an original review example and was not executed in a billed BigQuery project. Scaling and confusion-matrix arithmetic were checked locally; no model performance is claimed.