ExamFinch.com My Study

Study guide

11.2 Feature Engineering and Data Preparation for AI/ML

Practice this exam

Google Cloud Professional Data Engineer Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

0.1 About the Google Cloud Professional Data Engineer Exam 0.2 Study Strategy & Cloud Data Engineering Methodology

2 Chapter 1: Designing for Security and Compliance

1.1 IAM Roles, Service Accounts, and Least Privilege for Data 1.2 Data Encryption, Key Management, and Privacy with Cloud DLP 1.3 Compliance, Data Sovereignty, and Geographic Placement 1.4 Multi-Environment Architecture: Development, Staging, and Production

3 Chapter 2: Designing for Reliability and Resilience

2.1 High Availability, Disaster Recovery, and Failover Patterns 2.2 Fault Tolerance and ACID Guarantees in Distributed Systems 2.3 Data Validation, Cleansing, and Quality Assurance with Dataform 2.4 AI-Assisted Data Preparation and LLM Query Generation

4 Chapter 3: Architecture Patterns and Flexibility

3.1 Modern Data Architectures: Data Lake, Warehouse, Lakehouse, and Mesh 3.2 Data Migration Strategies and Hybrid/Multi-Cloud Integration 3.3 Database Migration Service and Heterogeneous Database Migrations

5 Chapter 4: Ingestion Architectures: Pub/Sub and Streaming Sources

4.1 Cloud Pub/Sub Core Architecture and Ingestion 4.2 Pub/Sub Advanced Patterns: Dead-Lettering, Ordering, and Schemas 4.3 Change Data Capture (CDC) with Datastream and Batch Ingestion

6 Chapter 5: Stream and Batch Processing with Apache Beam and Cloud Dataflow

5.1 Apache Beam Core Concepts and Pipeline Design 5.2 Advanced Windowing, Watermarks, and Triggers 5.3 Dataflow Pipeline Optimization, Scaling, and Templates

7 Chapter 6: Data Transformation with Dataproc, Data Fusion, and Workflow Orchestration

6.1 Apache Spark and Hadoop with Cloud Dataproc 6.2 Visual Data Pipelines with Cloud Data Fusion 6.3 Workflow Orchestration with Cloud Composer 6.4 AI Data Enrichment inside Data Pipelines

8 Chapter 7: Storage Service Selection and Cloud Storage Architecture

7.1 Google Cloud Storage Service Selection Decision Matrix 7.2 Cloud Storage Architecture, Classes, and Lifecycle Management

9 Chapter 8: Deep Dive into Google BigQuery Storage

8.1 BigQuery Storage Architecture and Data Organization 8.2 Partitioning and Clustering for Performance and Cost 8.3 BigLake and Hybrid Querying across Clouds 8.4 Data Warehouse Modeling: Star Schemas, Denormalization, and Slowly Changing Dimensions

10 Chapter 9: Operational and NoSQL Databases: Bigtable and Cloud Spanner

9.1 Cloud Bigtable Architecture and Row Key Design 9.2 Cloud Bigtable Schema Management and Performance Tuning 9.3 Cloud Spanner: Globally Scalable Relational Database

11 Chapter 10: BigQuery Query Performance and Optimization

10.1 BigQuery SQL Tuning and Performance Best Practices 10.2 BigQuery BI Engine and Materialized Views 10.3 Data Sharing and Analytics Hub 10.4 Preparing Data for Visualization and BI Tool Connectivity

12 Chapter 11: Machine Learning Readiness and Data Governance

11.1 Machine Learning with BigQuery ML 11.2 Feature Engineering and Data Preparation for AI/ML 11.3 Data Governance, Cataloging, and Discovery with Dataplex 11.4 Unstructured Data, Embeddings, and Retrieval-Augmented Generation

13 Chapter 12: Site Reliability Engineering and Monitoring for Data Systems

12.1 SRE Principles for Data: SLIs, SLOs, and SLAs 12.2 Observability: Cloud Monitoring, Cloud Logging, and Alerting 12.3 Troubleshooting Data Pipelines and Workload Failures

14 Chapter 13: Workload Automation, CI/CD, and Cost Optimization

13.1 CI/CD and Version Control for Data Pipelines 13.2 BigQuery Cost Management and Capacity Planning 13.3 Holistic Cost Optimization across GCP Data Services 13.4 Query Workload Management: Interactive vs. Batch Jobs, Queues, and Quotas

11.2 Feature Engineering and Data Preparation for AI/ML

Key Takeaways Explain This Topic AI

Train-serve skew occurs when mathematical feature transformations applied during training diverge from transformations applied during inference; BigQuery ML eliminates this skew by embedding preprocessing logic directly into the model artifact using the TRANSFORM clause.

The TRANSFORM clause encapsulates scaling (ML.STANDARD_SCALER, ML.MIN_MAX_SCALER), categorical encoding (ML.ONE_HOT_ENCODER, ML.LABEL_ENCODER), and binning (ML.BUCKETIZE), calculating summary statistics once during training and automatically applying them to raw input features during ML.PREDICT.

Missing numerical and categorical values can be reliably handled during model training using ML.IMPUTER, which dynamically replaces nulls with mean, median, or mode statistics computed strictly over the training dataset partition to prevent data leakage.

Selecting the correct data_split_method is vital to prevent data leakage: while RANDOM or CUSTOM splits work for independent tabular records, time-dependent datasets require sequential chronological splitting (data_split_method = 'SEQ') to ensure models are never evaluated on historical data that predates training observations.

Vertex AI Feature Store provides a centralized repository for enterprise feature management, enabling feature reusability across data science teams, time-travel point-in-time joins to eliminate target leakage, and dual-tier serving for both batch training and sub-10ms online prediction.

Last updated: September 2026

11.2 Feature Engineering and Data Preparation for AI/ML

Tip

A recurring architectural challenge on the Professional Data Engineer exam is train-serve skew . When preprocessing transformations (such as scaling or encoding) are performed in external SQL views or ad-hoc data scripts prior to training, any downstream application calling the model must manually duplicate those exact transformations on new incoming data. If the logic diverges even slightly, model performance degrades catastrophically. The exam tests your ability to eliminate this failure mode using BigQuery ML's native TRANSFORM clause.

High-performing machine learning models require clean, standardized, and well-represented numerical and categorical features. However, raw data in enterprise warehouses rarely matches the mathematical requirements of machine learning algorithms: continuous features have disparate scales, categorical strings must be vectorized, and missing values disrupt matrix calculations.

While traditional data pipelines address these requirements using multi-stage ETL scripts, this decouples data transformation from the resulting model artifact, introducing significant operational risks.

The Architecture of Train-Serve Skew

Train-serve skew is a discrepancy between the performance of a model during training and its performance in production serving. It is primarily driven by two phenomena:

Transformation Discrepancy: A difference in how input data is handled between the training pipeline and the serving pipeline. For example, if a data engineer standardizes a user_age feature during training by computing the mean and variance across the training set ( z = x − μ σ z = \frac{x - \mu}{\sigma} z = σ x − μ ​ ), the serving pipeline must use the exact same training μ \mu μ and σ \sigma σ to scale incoming live records. If the serving application uses a freshly computed daily average, or if a software engineer re-implements the scaling logic in Java with slightly different rounding, the model receives shifted inputs and generates erroneous predictions.

Data Leakage (Target Leakage): When information from the future or from the validation/test partition inadvertently contaminates the training dataset. For instance, calculating the global mean of a feature across an entire dataset before splitting into train and test folds leaks validation data statistics into the training process, producing overly optimistic evaluation metrics that fail to generalize in production. Traditional Pipeline (High Risk of Train-Serve Skew):

  • [Raw Data] -> [Manual ETL / View] -> (Saves Scaled Data) -> [Train Model]
  • |
  • [New Raw Data] -> [Application Code / Java Service] -------------> [Inference]
  • (Risk: Divergent scaling logic or stale stats)

BigQuery ML TRANSFORM Clause (Zero Skew Architecture):

  • [Raw Data] -----------------------------------------------------> [Train Model]
  • +-------------------------------------------------+ |
  • | TRANSFORM( ML.STANDARD_SCALER(x) OVER() ... ) | |
  • | * Statistics (mean, stddev) saved inside model | |
  • +-------------------------------------------------+ |
  • v
  • [New Raw Data] -------------------------------------------------> [ML.PREDICT]
  • (BigQuery ML automatically applies identical transform using embedded stats)
  • The BigQuery ML TRANSFORM Clause

BigQuery ML resolves train-serve skew at the architectural level through the TRANSFORM clause. The TRANSFORM clause is defined directly within the CREATE MODEL statement, positioned immediately before OPTIONS() .

When TRANSFORM is used:

BigQuery computes all necessary summary statistics (such as the mean and standard deviation for scalers, minimum and maximum values, or distinct category dictionaries) strictly across the training data partition .

These statistics and transformation formulas are permanently embedded into the resulting model metadata artifact.

During inference ( ML.PREDICT ) or after exporting the model as a TensorFlow SavedModel, the caller passes unmodified, raw data columns . BigQuery ML automatically executes the embedded transformation graph using the saved training parameters before feeding inputs to the underlying algorithm. CREATE OR REPLACE MODEL `ecommerce_ml.customer_ltv_model`

  • TRANSFORM (
  • - Categorical features
  • ML.LABEL_ENCODER(country_code) OVER() AS encoded_country,
  • ML.ONE_HOT_ENCODER(device_category, 'DROP_FIRST', 10) OVER() AS encoded_device,
  • - Numerical scalers
  • ML.STANDARD_SCALER(session_duration_sec) OVER() AS scaled_session_duration,
  • ML.MIN_MAX_SCALER(pageviews) OVER() AS normalized_pageviews,
  • - Bucketizing continuous values
  • ML.BUCKETIZE(user_age, [18, 25, 35, 50, 65]) AS age_bracket,
  • - Missing value imputation
  • ML.IMPUTER(prior_spend, 'median') OVER() AS imputed_prior_spend,
  • - Target label
  • total_ltv_revenue
  • )
  • OPTIONS (
  • model_type = 'BOOSTED_TREE_REGRESSOR',
  • input_label_cols = ['total_ltv_revenue'],
  • data_split_method = 'AUTO_SPLIT'
  • ) AS
  • SELECT
  • country_code,
  • device_category,
  • session_duration_sec,
  • pageviews,
  • user_age,
  • prior_spend,
  • total_ltv_revenue
  • FROM `ecommerce_ml.raw_web_conversions`;
  • Native BigQuery ML Preprocessing Functions

BigQuery ML provides a comprehensive suite of built-in scalar and analytic functions designed specifically for the TRANSFORM clause:

1. Numerical Normalization and Scaling

ML.STANDARD_SCALER(numeric_expr) OVER() : Computes the Z-score normalization: z = x − μ σ z = \frac{x - \mu}{\sigma} z = σ x − μ ​ . It transforms numerical distributions to have a mean of 0 and a standard deviation of 1. Crucial for distance-based models (such as KMEANS ) and gradient descent algorithms (such as LINEAR_REG , LOGISTIC_REG , and deep neural networks), where unscaled features with large numerical magnitudes dominate model updates.

ML.MIN_MAX_SCALER(numeric_expr) OVER() : Scales numerical values into a bounded range between 0 and 1: x n o r m = x − x m i n x m a x − x m i n x_{norm} = \frac{x - x_{min}}{x_{max} - x_{min}} x n or m ​ = x ma x ​ − x min ​ x − x min ​ ​ . Particularly effective when feature distributions are bounded or when neural network activation functions (such as Sigmoid) operate optimally on [ 0 , 1 ] [0, 1] [ 0 , 1 ] inputs.

ML.MAX_ABS_SCALER(numeric_expr) OVER() : Scales features by dividing by the maximum absolute value, mapping data to [ − 1 , 1 ] [-1, 1] [ − 1 , 1 ] without shifting the center, thereby preserving sparsity in sparse datasets.

ML.ROBUST_SCALER(numeric_expr) OVER() : Standardizes features using the median and Interquartile Range (IQR = 75th percentile - 25th percentile). Highly recommended when numerical features contain severe outliers that would distort standard mean and variance calculations.

ML.NORMALIZER(numeric_array, p) : Normalizes an array of numerical values using an L p L_p L p ​ norm ( p = 1 p=1 p = 1 for Manhattan norm, p = 2 p=2 p = 2 for Euclidean norm), transforming feature vectors into unit length.

2. Discretization and Binning

ML.BUCKETIZE(numeric_expr, array_split_points) : Bins continuous numeric values into discrete intervals based on user-defined boundary points. For example, ML.BUCKETIZE(age, [20, 40, 60]) produces integer buckets 0 through 3. This enables linear models to learn non-linear step-function responses to continuous variables.

ML.QUANTILE_BUCKETIZE(numeric_expr, num_buckets) OVER() : Automatically computes continuous quantile cut points to partition data into bins with approximately equal numbers of observations, neutralizing heavy tail distributions.

3. Categorical Encoding and Feature Synthesis

ML.ONE_HOT_ENCODER(string_expr [, drop_first] [, top_k] [, frequency_threshold]) OVER() : Converts categorical string values into a sparse array of binary indicator features ( 0 0 0 or 1 1 1 ). Options allow dropping the first category to avoid multicollinearity (the "dummy variable trap" in linear models), and setting top_k or frequency_threshold to group rare low-frequency categories into an '__OTHER__' bucket, preventing feature explosion.

ML.LABEL_ENCODER(string_expr) OVER() : Assigns a deterministic integer index [ 0 , N − 1 ] [0, N-1] [ 0 , N − 1 ] to each unique categorical string. Optimal for tree-based models ( BOOSTED_TREE_CLASSIFIER ), which can natively split on integer-encoded categorical features without expanding dimensionality.

ML.POLYNOMIAL_EXPAND(struct_expr, degree) : Generates polynomial and cross-product interaction terms (e.g., degree 2 creates x 1 2 , x 1 x 2 , x 2 2 x_1^2, x_1 x_2, x_2^2 x 1 2 ​ , x 1 ​ x 2 ​ , x 2 2 ​ ) across input numerical fields, allowing linear regression models to capture non-linear relationships without shifting to complex tree or neural architectures.

ML.FEATURE_CROSS(struct_features, degree) : Creates cross-categorical features combining multiple distinct discrete attributes into unified interaction features.

4. Text Feature Engineering

ML.NGRAMS(array_tokens, range) : Generates contiguous sequences of n n n items from an array of string tokens, capturing multi-word contextual phrases.

ML.BAG_OF_WORDS(array_tokens) : Computes token frequency dictionaries for document classification.

ML.GENERATE_EMBEDDING : Employs pre-trained Google text embedding models to convert unstructured customer feedback, product titles, or search queries into dense numerical vectors for downstream classification or clustering.

5. Missing Value Handling ( ML.IMPUTER )

Real-world data pipelines frequently encounter missing data. BigQuery ML provides ML.IMPUTER(feature, strategy) OVER() , where strategy can be:

'mean' : Substitutes nulls with the arithmetic mean of the column (numerical features only).

'median' : Substitutes nulls with the 50th percentile median (numerical features only; robust against extreme outliers).

'mode' : Substitutes nulls with the most frequently occurring value (supported for both numerical and categorical string columns).

When executed inside TRANSFORM , the replacement statistics are computed strictly over the training slice, completely preventing data leakage.

Feature Engineering Transformation Functions Reference

Function Name Category Mathematical / Transformation Logic Supported Input Types Primary Algorithmic Use Cases

ML.STANDARD_SCALER Scaling z = x − μ σ z = \frac{x - \mu}{\sigma} z = σ x − μ ​ (Zero mean, unit variance) FLOAT64 , INT64 LINEAR_REG , LOGISTIC_REG , DNN_CLASSIFIER , KMEANS

ML.MIN_MAX_SCALER Normalization x ′ = x − x m i n x m a x − x m i n x' = \frac{x - x_{min}}{x_{max} - x_{min}} x ′ = x ma x ​ − x min ​ x − x min ​ ​ (Bounded [ 0 , 1 ] [0, 1] [ 0 , 1 ] ) FLOAT64 , INT64 Neural networks, algorithms sensitive to bounded input ranges

ML.ROBUST_SCALER Scaling x ′ = x − median IQR x' = \frac{x - \text{median}}{\text{IQR}} x ′ = IQR x − median ​ (Outlier resilient) FLOAT64 , INT64 Highly skewed data with extreme outliers in regression / clustering

ML.BUCKETIZE Discretization Maps continuous numbers into discrete bins based on split boundaries FLOAT64 , INT64 Converting continuous ages, incomes, or scores into discrete step features

ML.QUANTILE_BUCKETIZE Discretization Bins continuous numbers into equal-frequency quantiles FLOAT64 , INT64 Mitigating the impact of severe numerical skew or power-law distributions

ML.ONE_HOT_ENCODER Encoding Generates binary indicator columns for unique category values STRING Low-cardinality categorical variables in linear and neural models

ML.LABEL_ENCODER Encoding Maps unique string categories to ordinal integers [ 0 , N − 1 ] [0, N-1] [ 0 , N − 1 ] STRING Tree-based models ( BOOSTED_TREE_CLASSIFIER , BOOSTED_TREE_REGRESSOR )

ML.POLYNOMIAL_EXPAND Feature Synthesis Generates feature powers and interaction cross-products STRUCT of numerics Expanding linear models to fit non-linear feature interactions

ML.IMPUTER Imputation Replaces NULL with 'mean' , 'median' , or 'mode' FLOAT64 , INT64 , STRING Handling sparse tables without dropping rows or introducing leakage

Dataset Splitting Strategies and Preventing Data Leakage

To rigorously evaluate generalizability, BigQuery ML splits input data into training, evaluation (validation), and testing partitions. This is configured via the data_split_method option in the CREATE MODEL statement:

AUTO_SPLIT (Default): For datasets with fewer than 50,000 rows, BigQuery ML automatically partitions data into 80% training and 20% evaluation using a randomized split. For datasets exceeding 50,000 rows, it allocates 80% to train, 10% to evaluate, and 10% to a final test set.

RANDOM : Randomly assigns rows using a deterministic hash of the entire row content. The fraction allocated to evaluation is controlled by data_split_eval_fraction (e.g., 0.2 for an 80/20 split).

CUSTOM : Uses a user-provided boolean column specified via data_split_col . Rows where the column evaluates to TRUE are used for evaluation, while FALSE rows are used for training. This provides full control when implementing stratified sampling or cross-organization splits.

SEQ (Sequential Chronological Split): Essential for time-series, financial transactions, and sequential customer telemetry. By supplying a timestamp column to data_split_col , BigQuery ML sorts the data chronologically and allocates the oldest records to training and the most recent records to evaluation.

Why this prevents leakage: In time-dependent processes, random splitting allows the model to train on future records to predict past events, causing catastrophic look-ahead bias. Sequential splitting guarantees that evaluation replicates true forward-looking production conditions.

NO_SPLIT : Uses the entire input dataset for training. Used only when evaluation is performed manually via a completely separate holdout table passed to ML.EVALUATE .

Handling Highly Imbalanced Datasets

When training classification models on heavily skewed datasets (e.g., credit card fraud where fraudulent transactions comprise only 0.05% of all records), naive models often predict the majority class 100% of the time to achieve deceptively high accuracy (e.g., 99.95%). Data engineers must employ specific mitigation strategies:

auto_class_weights = TRUE : In LOGISTIC_REG and BOOSTED_TREE_CLASSIFIER , BigQuery ML dynamically weights each class inversely proportional to its frequency in the training data, penalizing misclassifications on minority classes heavily.

Downsampling / Stratified Splitting: Selecting a representative balanced ratio of positive to negative samples during training while maintaining true distribution in the evaluation holdout.

Metric Selection: Evaluating models based on Precision-Recall AUC (PR AUC) and F1-score rather than raw classification accuracy or ROC AUC.

Centralized Feature Management: Vertex AI Feature Store

While BigQuery ML streamlines feature transformations within single data warehouse projects, large enterprise organizations face broader feature governance challenges:

Feature Redundancy: Multiple data science teams independently recreate similar features (e.g., 30_day_avg_user_spend ) with slightly varying definitions and compute costs.

Dual-Speed Serving Bottlenecks: Training pipelines require high-throughput batch reads across petabytes of historical data, whereas real-time recommendation or fraud services require single-digit millisecond point lookups by user_id or entity_id .

Point-in-Time Correctness: Calculating features for historical training examples requires "time-travel" queries to extract feature values exactly as they existed at the time the event occurred, avoiding future data leakage.

Vertex AI Feature Store acts as Google Cloud's centralized, managed architectural solution to these challenges. Vertex AI Feature Store Architecture

  • [Streaming Ingestion] [Batch Ingestion]
  • (Pub/Sub -> Dataflow) (BigQuery / Cloud Storage)
  • | |
  • +------------------->+<------------------+
  • |
  • +-------------------+-------------------+
  • | Feature Registry |
  • | * Feature Search & Discovery (IAM) |
  • | * Centralized Versioning & Metadata |
  • +-------------------+-------------------+
  • |
  • +-------------------------+-------------------------+
  • | |
  • v v
  • [Online Serving Layer] [Offline Serving Layer]
  • Ultra-low latency (<10ms) • High-throughput batch export
  • Backed by Cloud Bigtable / Cache • Point-in-time time-travel joins
  • Real-time inference (Vertex AI Endpoints) • Model training in BQML / Vertex AI
  • BigQuery-Backed Feature Store

Modern Vertex AI Feature Store integrates directly with BigQuery storage. Organizations register BigQuery tables or views as Feature Views without duplicating data into an external store:

Offline Serving for Training: When training a model, Vertex AI Feature Store executes an exact point-in-time lookup join (time-travel join). It joins historical event timestamps with feature value change logs, ensuring that a transaction from June 2026 is paired with customer feature values as of June 2026, never leaking July 2026 updates.

Online Serving for Real-Time Inference: Feature values are synchronized continuously into an optimized key-value caching layer (such as Cloud Bigtable). Online microservices or Vertex AI Endpoints query the Feature Store using an entity ID (e.g., customer_id = 94821 ) and retrieve the latest precomputed feature vector with sub-10 millisecond latency.

BigQuery ML TRANSFORM vs. Cloud Dataflow with tf.Transform

A common design question on the exam involves choosing between BigQuery ML TRANSFORM and Cloud Dataflow with tf.Transform (Apache Beam):

Choose BigQuery ML TRANSFORM : When the training data and inference datasets reside inside BigQuery, the modeling team utilizes GoogleSQL, and models are trained directly in BigQuery ML. It provides zero-infrastructure overhead and turnkey skew elimination.

Choose Cloud Dataflow with tf.Transform : When feature engineering requires complex streaming windowed aggregations on unstructured data (e.g., calculating rolling session metrics over Pub/Sub streams), when models are built using custom TensorFlow or PyTorch architectures on custom GPU clusters, and when pipelines must execute identically across hybrid or non-GCP cloud environments.

Loading diagram... Elimination of Train-Serve Skew via BigQuery ML TRANSFORM Clause: Freezing Statistics inside the Model Artifact