2.2 Feature Engineering & Feature Selection in BigQuery ML

Google Cloud Professional Machine Learning Engineer PMLE

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Exam Overview & the Agent Platform Map

1.1 PMLE Exam Facts, Policies & the June 2026 Blueprint 1.2 From Vertex AI to Gemini Enterprise Agent Platform: The Product Name Map

2 Chapter 2: Low-Code ML with BigQuery ML

2.1 Choosing, Training & Predicting with BigQuery ML Models 2.2 Feature Engineering & Feature Selection in BigQuery ML 2.3 Fine-Tuning & Evaluating Gemini Models from BigQuery

3 Chapter 3: AutoML & Google Cloud AI APIs

3.1 Training Models with Agent Platform AutoML 3.2 Building with Document AI, Vision, Translation & Other AI APIs

4 Chapter 4: Foundation Models in Model Garden

4.1 Evaluating & Selecting Models in Model Garden 4.2 Building & Tuning Solutions with Gemini, Imagen, Veo & MaaS 4.3 Optimizing Gemini Applications for Cost, Latency & Availability

5 Chapter 5: Exploring, Preprocessing & Protecting Data

5.1 Organizing & Exploring Tabular, Text & Image Data 5.2 Choosing a Preprocessing Tool: BigQuery, Dataflow, Spark or Python 5.3 Creating & Consolidating Features in Agent Platform Feature Store 5.4 Protecting PII & Sensitive Data in ML Workflows

6 Chapter 6: Notebooks, Prototyping & Experiment Tracking

6.1 Workbench vs. Colab Enterprise: Secure, Collaborative Notebooks 6.2 Prototyping with PyTorch, scikit-learn, JAX & Model Garden in Notebooks 6.3 Experiment Environments, Tracking & Lineage with Experiments and ML Metadata

7 Chapter 7: Evaluating Predictive & Generative AI Solutions

7.1 Evaluation Metrics for Predictive Models 7.2 Evaluating Gen AI: Computation Metrics, Rubrics & LLM-as-a-Judge

8 Chapter 8: Choosing the Modeling Approach

8.1 Choosing the Model Type: ARIMA, Trees, DNNs & LLMs 8.2 Choosing the Product & Deployment Strategy 8.3 Modeling Techniques for Interpretability Requirements

9 Chapter 9: Training Data & Training SDKs

9.1 Organizing & Ingesting Training Data from Cloud Storage & BigQuery 9.2 Custom Training on Agent Platform: Containers, Jobs & Worker Pools 9.3 Kubeflow on GKE, AutoML & Tabular Workflows as Training Choices

10 Chapter 10: Troubleshooting, Hyperparameter Tuning & Fine-Tuning

10.1 Troubleshooting ML Training Failures 10.2 Hyperparameter Tuning on Agent Platform 10.3 Fine-Tuning Foundation Models & When to Tune

11 Chapter 11: Training Hardware & Distributed Training

11.1 Choosing CPUs, GPUs & TPUs for Training 11.2 Distributed Training: Data & Model Parallelism

12 Chapter 12: Deploying Models for Inference

12.1 Batch vs. Online Inference on Agent Platform 12.2 Serving Beyond Agent Platform Endpoints: Model Garden, Cloud Run & GKE 12.3 Prebuilt & Custom Serving Containers

13 Chapter 13: Model Versions, Rollouts & Inference Logic

13.1 Organizing & Versioning Models in Model Registry 13.2 Rollout Strategies: A/B Tests, Canaries & Traffic Splits 13.3 Inference Preprocessing & Postprocessing

14 Chapter 14: Scaling Online Serving

14.1 Serving Features Online with Feature Store 14.2 Public, Dedicated & Private Endpoints 14.3 Serving Hardware & Autoscaling for Throughput 14.4 Tuning Models for Production Training & Serving

15 Chapter 15: End-to-End ML Pipelines

15.1 Validating Data & Models in Pipelines 15.2 Agent Platform Pipelines with KFP: Components, Templates & Caching 15.3 Ensuring Consistent Preprocessing Between Training & Serving

16 Chapter 16: Orchestration with Managed Airflow & Ray

16.1 Orchestrating ML Workflows with Managed Service for Apache Airflow 16.2 Ray on Agent Platform for Distributed ML Workloads

17 Chapter 17: Automating Retraining & CI/CD/CT

17.1 Choosing a Retraining Policy 17.2 CI/CD/CT for ML with Cloud Build

18 Chapter 18: Securing AI Systems, Responsible AI & Explainability

18.1 Protecting AI Systems from Data & Model Exfiltration 18.2 Defending Gen AI Apps: Safety Filters, Model Armor & Regex Guardrails 18.3 Responsible AI: Detecting & Monitoring Bias 18.4 Model Explainability on Agent Platform

19 Chapter 19: Monitoring AI Solutions

19.1 Configuring Model Monitoring on Agent Platform 19.2 Training-Serving Skew, Data Drift, Concept Drift & Attribution Drift 19.3 Monitoring, Testing & Evaluating Gen AI Solutions in Production

2.2 Feature Engineering & Feature Selection in BigQuery ML

Key Takeaways Explain This Topic AI

BigQuery ML automatically standardizes numeric features for most model types but not for boosted tree or random forest models.

Preprocessing in the TRANSFORM clause is saved with the model and reapplied automatically by ML.EVALUATE and ML.PREDICT, which prevents training-serving skew.

ML analytic preprocessing functions such as ML.QUANTILE_BUCKETIZE and ML.STANDARD_SCALER require an empty OVER() clause.

ML.FEATURE_IMPORTANCE reports importance_weight, importance_gain, and importance_cover, and it works only for boosted tree and random forest models.

ML.TRANSFORM returns the preprocessed data from a model's TRANSFORM clause, so you can inspect exactly what the model received.

Last updated: September 2026

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:

Input type Default transformation

Numeric ( INT64 , NUMERIC , BIGNUMERIC , FLOAT64 ) Standardization for most models. Exceptions: boosted tree and random forest models (no standardization) and k-means (controlled by STANDARDIZE_FEATURES )

• BOOL , STRING , BYTES , DATE , DATETIME , TIME One-hot encoding . Tree models use label encoding instead
• Non-numeric ARRAY Multi-hot encoding

TIMESTAMP For linear and logistic regression, split into Unix time (standardized) plus day of month, day of week, month, hour, minute, week, and year (one-hot)

STRUCT Expanded into struct_field columns

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

• Family Functions Use when
• General ML.IMPUTER Replace missing values with mean, median, or most frequent

Numeric ML.STANDARD_SCALER , ML.MIN_MAX_SCALER , ML.MAX_ABS_SCALER , ML.ROBUST_SCALER , ML.NORMALIZER , ML.BUCKETIZE , ML.QUANTILE_BUCKETIZE , ML.POLYNOMIAL_EXPAND Scale skewed values, handle outliers (robust scaler), create non-linear features

Categorical ML.ONE_HOT_ENCODER , ML.MULTI_HOT_ENCODER , ML.LABEL_ENCODER , ML.HASH_BUCKETIZE , ML.FEATURE_CROSS Encode high-cardinality IDs (hash), capture interactions (cross)

Text ML.NGRAMS , ML.BAG_OF_WORDS , ML.TF_IDF Turn short text into sparse features

Image ML.DECODE_IMAGE , ML.RESIZE_IMAGE , ML.CONVERT_COLOR_SPACE , ML.CONVERT_IMAGE_TYPE Prepare images from object tables for imported vision models

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:

Technique How Works with

Tree feature importance ML.FEATURE_IMPORTANCE returns importance_weight (split count), importance_gain (average gain), importance_cover (average coverage) Boosted tree and random forest only

Global explanations Train with ENABLE_GLOBAL_EXPLAIN = TRUE , then query ML.GLOBAL_EXPLAIN Supervised models that support explainability

Per-row attributions ML.EXPLAIN_PREDICT with TOP_K_FEATURES Many supervised models

Sparse weights L1_REG pushes small weights to exactly zero Linear, logistic, and tree models (tunable with HPARAM_RANGE )

• Weight inspection ML.WEIGHTS / ML.ADVANCED_WEIGHTS Linear and logistic regression
• Dimensionality reduction PCA model outputs principal components High-dimensional numeric inputs

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:

Retrain as a boosted tree with ENABLE_GLOBAL_EXPLAIN = TRUE .

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.

Check the top features for leakage. actual_delivery_date is recorded after the outcome, so remove it.

Move the kept features into a TRANSFORM clause with bucketized distance and a carrier × region cross.

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.