1.2 Feature Engineering and Feature Selection in BigQuery ML

Google Cloud Professional Machine Learning Engineer Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

0.1 Exam Overview, Blueprint & Certification Logistics 0.2 Study Strategy, Scenario Mechanics & 8-Week Roadmap

2 Section 1: Architecting Low-Code AI Solutions (~13%)

1.1 BigQuery ML: Model Types, SQL Syntax and In-Database Evaluation 1.2 Feature Engineering and Feature Selection in BigQuery ML 1.3 Generating Predictions with BigQuery ML 1.4 AutoML on Agent Platform: Tabular, Image, Text and Video Training 1.5 Fine-Tuning Gemini Models from BigQuery 1.6 Model Garden: Evaluating and Selecting the Right Model 1.7 Industry-Specific AI APIs: Document AI, Vision, Translate and Speech 1.8 Generative Media and Models as a Service: Gemini, Imagen and Veo 1.9 Optimizing Gemini Applications for Cost, Latency and Availability

3 Section 2: Collaborating Within and Across Teams to Manage Data and Models (~16%)

2.1 Organizing and Exploring Tabular, Text, Image and Video Data 2.2 Choosing the Preprocessing Tool: BigQuery, Dataflow, Spark and In-Memory Python 2.3 Creating and Consolidating Features in Agent Platform Feature Store 2.4 Data Privacy, PII Handling and Sensitive Information 2.5 Notebook Environments: Collaboration and Security Best Practices 2.6 Prototyping in Workbench and Colab Enterprise with PyTorch, scikit-learn and JAX 2.7 Prototyping with Model Garden Foundational and Open Models in Notebooks 2.8 Choosing the Development and Experimentation Environment 2.9 Evaluating Generative Solutions: LLM-as-a-Judge and RAG Evaluation 2.10 Experiment Tracking, Artifact Lineage and Model Registry Governance

4 Section 3: Scaling Prototypes into ML Models (~21%)

3.1 Choosing the Model Type: ARIMA, DNN, LLM and Classical Baselines 3.2 Choosing the Product: AutoML, BigQuery ML, Custom Training and Pipelines 3.3 Choosing the Deployment Strategy at Design Time 3.4 Modeling Techniques for Interpretability Requirements 3.5 Organizing Training Data on Cloud Storage and BigQuery 3.6 Ingesting Structured and Unstructured Data into Training Pipelines 3.7 Training SDKs and Services: Custom Training, Kubeflow on GKE, AutoML and Tabular Workflows 3.8 Troubleshooting ML Model Training Failures 3.9 Hyperparameter Tuning, Bayesian Optimization and Vizier 3.10 Model Evaluation Metrics, Transfer Learning and Fine-Tuning 3.11 Evaluating Compute and Accelerator Options: CPU, GPU and TPU 3.12 Distributed Training Strategies, Hardware Acceleration and Performance Optimization

5 Section 4: Serving and Scaling Models (~20%)

4.1 Batch and Online Inference: Agent Platform, Model Garden, Cloud Run and GKE 4.2 Rollout Strategies: Canary, Traffic Splitting, Shadow and Blue-Green 4.3 Tuning Models for Production: Vector Search and Model Compression 4.4 Deploying Models to Public and Private Endpoints 4.5 Choosing Serving Hardware: CPU, GPU, TPU and Edge 4.6 Scaling the Serving Backend for Throughput

6 Section 5: Automating and Orchestrating ML Pipelines (~18%)

5.1 Validating Data and Models Inside Pipelines 5.2 Agent Platform Pipelines: Kubeflow Pipelines (KFP) and TFX Architecture 5.3 Reusable Pipeline Components, Artifacts and ML Metadata Lineage 5.4 Ray on Agent Platform for Distributed Python Workloads 5.5 MLOps CI/CD Automation, Retraining Triggers and Skew Prevention

7 Section 6: Monitoring AI Solutions (~13%)

6.1 Securing AI Systems: Exfiltration, Malicious Prompting and Model Armor 6.2 Responsible AI, Explainable AI, Fairness Metrics and Model Governance 6.3 Model Monitoring: Feature Skew and Drift Detection 6.4 Operational Troubleshooting, Performance Profiling and Cost Optimization 6.5 Monitoring, Testing and Evaluating Generative AI in Production

1.2 Feature Engineering and Feature Selection in BigQuery ML

Key Takeaways Explain This Topic AI

The TRANSFORM clause stores preprocessing inside the model, so ML.PREDICT applies the identical transformations at inference and training-serving skew becomes structurally impossible.

BigQuery ML applies automatic preprocessing even with no TRANSFORM clause — standardization for numeric columns, one-hot encoding for STRING columns (label encoding for tree models), multi-hot for non-numeric ARRAY columns, mean imputation for numeric NULLs, and a separate category for categorical NULLs.

ML.FEATURE_INFO reports per-column min, max, mean, median, standard deviation, null count, and category count, making it the fastest way to spot a leaking or degenerate feature before training.

Manual preprocessing functions such as ML.BUCKETIZE, ML.QUANTILE_BUCKETIZE, ML.STANDARD_SCALER, ML.FEATURE_CROSS, and ML.NGRAMS must be written inside TRANSFORM to be replayed at prediction time.

For linear and logistic models, L1 regularization drives coefficients to exactly zero and therefore doubles as automated feature selection; ML.WEIGHTS shows which features survived.

Last updated: September 2026

• 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.

Input type Automatic treatment

INT64 , NUMERIC , BIGNUMERIC , FLOAT64 Standardized (centred at zero) — except for boosted tree, random forest, and k-means models. NULLs are replaced with the column's mean

STRING , BOOL , BYTES , DATE , DATETIME , TIME One-hot encoded — except boosted tree and random forest models, which use label encoding . NULLs become an additional category , not the mode

• ARRAY of non-numeric values Multi-hot encoded — the natural representation for tag or basket columns
• TIMESTAMP Unix time standardized; the extracted calendar components one-hot encoded
• STRUCT Expanded into its constituent fields, each preprocessed by its own type
• ARRAY<NUMERIC> , ARRAY<STRUCT> No transformation

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

• Function Purpose Typical use
• ML.BUCKETIZE Split a numeric column at explicit boundaries Regulatory age bands, price tiers
• ML.QUANTILE_BUCKETIZE Split into equal-population buckets Skewed distributions such as income or spend
• ML.STANDARD_SCALER Zero mean, unit variance Linear and DNN models with mixed scales
• ML.MIN_MAX_SCALER Rescale to [0, 1] Bounded inputs, image-like features
• ML.FEATURE_CROSS Cartesian product of categoricals Capturing interactions a linear model cannot learn
• ML.NGRAMS Token n-grams from arrays of words Text classification without a full NLP stack
• ML.POLYNOMIAL_EXPAND Polynomial and interaction terms Non-linear structure in a linear model
• ML.LABEL_ENCODER Categorical to integer index Tree models where ordinality is harmless
• ML.HASH_BUCKETIZE Hash strings into a fixed number of buckets Very high cardinality identifiers

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.