1.2 Feature Engineering and Feature Selection in BigQuery ML

1.2 Feature Engineering and Feature Selection in BigQuery ML

Blueprint reference: Section 1.1, "Performing feature engineering or selection using BigQuery ML."

Most candidates arrive at this topic knowing how to write CREATE MODEL. Far fewer can answer the question the exam actually asks, which is almost always some variation of: "the model scores well offline but produces garbage in production — what is wrong?" On BigQuery ML the answer is very often that the engineer preprocessed features in a separate query, trained on the result, and then forgot to reproduce that same query at prediction time. BigQuery ML has a purpose-built cure for this, and knowing it is the difference between two plausible-looking answer options.

Why the TRANSFORM Clause Is the Whole Topic

Training-serving skew is any difference between the transformations applied during training and those applied during inference. If you bucketize age into deciles in a training query and then send raw ages to ML.PREDICT, the model receives values it has never seen and silently degrades.

The TRANSFORM clause solves this by storing the preprocessing logic inside the model artifact. Expressions written inside TRANSFORM are executed on the training data and then re-executed automatically on every row passed to ML.PREDICT, ML.EVALUATE, and ML.EXPLAIN_PREDICT. The caller passes raw columns; BigQuery replays the transformations.

CREATE OR REPLACE MODEL `analytics.churn_model`
TRANSFORM (
  ML.STANDARD_SCALER(monthly_spend) OVER () AS monthly_spend_z,
  ML.QUANTILE_BUCKETIZE(tenure_days, 10) OVER () AS tenure_decile,
  ML.FEATURE_CROSS(STRUCT(plan_tier, region)) AS plan_region,
  churned
)
OPTIONS (
  model_type = 'LOGISTIC_REG',
  input_label_cols = ['churned']
) AS
SELECT monthly_spend, tenure_days, plan_tier, region, churned
FROM `analytics.customers`;

Two details are exam-relevant. First, the label column must be listed inside TRANSFORM or it is dropped and training fails. Second, analytic preprocessing functions such as ML.STANDARD_SCALER and ML.QUANTILE_BUCKETIZE require an OVER () clause because they compute statistics across the whole training set — and those statistics are frozen into the model, which is precisely what makes the replay correct.

Automatic Preprocessing You Get for Free

Even with no TRANSFORM clause, BigQuery ML preprocesses inputs based on column type. Knowing these defaults prevents a whole family of wrong answers where a candidate assumes manual encoding is mandatory.

Two of those defaults are worth reading twice. First, tree-based models take label encoding rather than one-hot, which is why a boosted tree tolerates high-cardinality categoricals that would blow up a linear model's feature space. Second, a NULL in a categorical column becomes its own category rather than being filled with the most common value — so "missing" is itself a signal the model can learn from, which is usually what you want and occasionally a leak (a field that is only ever null for one class).

The practical consequence: to train a churn model on a table containing a plan_tier STRING column, you do not need to write a pivot query. Passing the raw column is both sufficient and safer.

Manual Transformation Functions Worth Memorizing

ML.FEATURE_CROSS deserves special attention. A logistic regression on plan_tier and region learns one weight per tier and one per region; it structurally cannot represent "enterprise customers in EMEA behave differently." A crossed feature gives it that capacity without moving to a boosted tree.

Inspecting Features Before You Train

ML.FEATURE_INFO is the fastest sanity check in the entire BigQuery ML surface. It returns, per input column: minimum, maximum, mean, median, standard deviation, category count, and null count.

SELECT * FROM ML.FEATURE_INFO(MODEL `analytics.churn_model`);

Read it for three failure signatures. A column whose category_count roughly equals the row count is an identifier masquerading as a feature. A column with a near-zero standard deviation carries no signal and wastes capacity. A column with a very high null fraction will be dominated by imputed values, so the model is largely learning the imputation constant.

Feature Selection: L1 Regularization and ML.WEIGHTS

BigQuery ML has no separate "feature selector" product. Selection is done through regularization and inspection.

For LINEAR_REG and LOGISTIC_REG, set l1_reg. L1 (lasso) regularization drives uninformative coefficients to exactly zero, unlike L2 (l2_reg), which only shrinks them toward zero. That distinction is a favourite exam discriminator: if a scenario asks for a sparse model or explicitly for feature elimination, L1 is the answer; if it asks only to control overfitting from correlated features, L2 is fine.

CREATE OR REPLACE MODEL `analytics.churn_sparse`
OPTIONS (model_type = 'LOGISTIC_REG', l1_reg = 0.1, input_label_cols = ['churned'])
AS SELECT * EXCEPT (customer_id) FROM `analytics.customers`;

SELECT * FROM ML.WEIGHTS(MODEL `analytics.churn_sparse`)
ORDER BY ABS(weight) DESC;

Features whose weight is zero have been eliminated. For tree-based models (BOOSTED_TREE_CLASSIFIER, RANDOM_FOREST_CLASSIFIER), weights do not exist; use ML.FEATURE_IMPORTANCE instead, which reports gain, cover, and weight-based importance. ML.GLOBAL_EXPLAIN provides model-agnostic Shapley-based attributions when the model was created with enable_global_explain = TRUE.

Exam Traps

• Leakage through a post-outcome column. A feature such as cancellation_reason is only populated for churned customers. It will produce a near-perfect AUC offline and a useless model in production. ML.FEATURE_INFO null counts expose it.
• Preprocessing outside TRANSFORM. Any option that preprocesses in a view or a scheduled query, then predicts on raw data, is wrong.
• Confusing ML.BUCKETIZE with ML.QUANTILE_BUCKETIZE. Explicit boundaries versus equal-population boundaries.
• Assuming you must one-hot encode manually. BigQuery ML already does it for STRING columns.