2.2 Feature Engineering & Feature Selection in BigQuery ML

Feature engineering often matters more for model quality than which algorithm you pick. The exam tests two BigQuery ML skills: knowing what BQML does automatically, and applying custom transformations so the same logic runs at training and prediction.

Automatic Preprocessing

When you run CREATE MODEL without custom preprocessing, BQML handles missing values and applies default transformations:

Missing values: NULL numeric values are replaced with the column mean from training data. NULL categorical values become their own extra category. Categories that appear for the first time at prediction get zero weight.

The TRANSFORM Clause

The TRANSFORM clause defines preprocessing inside the model. The same logic, including statistics such as means and quantile boundaries computed during training, is reapplied automatically in ML.EVALUATE and ML.PREDICT. Clients send raw columns, so preprocessing done in client code can't drift from what the model learned.

CREATE OR REPLACE MODEL `ops.late_delivery`
TRANSFORM (
  ML.IMPUTER(distance_km, 'median') OVER () AS distance_km,
  ML.QUANTILE_BUCKETIZE(order_value, 10) OVER () AS value_bucket,
  ML.FEATURE_CROSS(STRUCT(carrier, origin_region)) AS carrier_region,
  ML.STANDARD_SCALER(items) OVER () AS items_scaled,
  EXTRACT(DAYOFWEEK FROM order_ts) AS order_dow,
  late
)
OPTIONS (model_type = 'LOGISTIC_REG', input_label_cols = ['late'])
AS SELECT distance_km, order_value, carrier, origin_region, items, order_ts, late
FROM `ops.orders_2025`;

Rules to remember:

• Analytic functions (such as ML.QUANTILE_BUCKETIZE, ML.STANDARD_SCALER, ML.MIN_MAX_SCALER, ML.IMPUTER) compute statistics across all rows and must use an empty OVER() clause.
• Scalar functions (such as ML.BUCKETIZE with fixed boundaries, or ML.FEATURE_CROSS) work one row at a time.
• Columns you select but don't output from TRANSFORM aren't used as features.
• ML.TRANSFORM returns the transformed rows so you can debug what the model actually sees.
• A transform-only model (MODEL_TYPE = 'TRANSFORM_ONLY') stores preprocessing without training a predictor. Use it to share one feature pipeline across several models or to materialize features for other tools.

Manual Preprocessing Function Families

Practical feature patterns

• Bucketize a skewed numeric like income or order value so a linear model can learn step effects.
• Cross features like carrier × region when their combination drives the outcome, for example one carrier being late only in mountain regions.
• Hash-bucketize very high-cardinality IDs to cap feature count, accepting some collisions.
• Extract calendar parts (day of week, hour) from timestamps when seasonality matters.
• Point-in-time features: ML.FEATURES_AT_TIME returns feature values as of a cutoff time for each entity. This stops training rows from seeing information recorded after the label event, which is a classic source of leakage.

Feature Selection in SQL

Too many weak features slow training, raise overfitting risk, and make models harder to explain. BQML gives you several selection tools:

A practical loop: train a boosted tree with ENABLE_GLOBAL_EXPLAIN = TRUE, rank features by importance_gain and global attribution, drop features that add little or that leak the label (for example, "refund_issued" when predicting returns), then retrain and compare ML.EVALUATE metrics.

Worked Example: Tightening a Delivery Model

An operations team's late-delivery classifier has 120 input columns, trains slowly, and is hard to explain to carrier managers. A disciplined BQML workflow:

1. Retrain as a boosted tree with ENABLE_GLOBAL_EXPLAIN = TRUE.
2. Query ML.FEATURE_IMPORTANCE and ML.GLOBAL_EXPLAIN. Twelve features carry almost all of the gain. Several others turn out to be IDs that the model uses to memorize individual rows.
3. Check the top features for leakage. actual_delivery_date is recorded after the outcome, so remove it.
4. Move the kept features into a TRANSFORM clause with bucketized distance and a carrier × region cross.
5. Compare ML.EVALUATE on the same holdout. A smaller model with similar AUC is easier to explain, cheaper to run, and less likely to overfit.

Exam Traps

• Skew from duplicated logic: computing a z-score in a SQL view for training and again in application code for serving. Put it in TRANSFORM or a transform-only model.
• Unnecessary scaling for trees: standardizing inputs doesn't improve boosted trees, which is why BQML skips it for them.
• Leaky features: a feature that is only known after the outcome makes offline metrics look great and fails in production.
• Wrong importance tool: ML.FEATURE_IMPORTANCE returns an error for linear or DNN models. Use global explanations or weights instead.