11.2 Feature Engineering and Data Preparation for AI/ML

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:

1. 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 = \frac{x - \mu}{\sigma}$), 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.
2. 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 = \frac{x - \mu}{\sigma}$. 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_{norm} = \frac{x - x_{min}}{x_{max} - x_{min}}$. Particularly effective when feature distributions are bounded or when neural network activation functions (such as Sigmoid) operate optimally on $[0, 1]$ inputs.
• ML.MAX_ABS_SCALER(numeric_expr) OVER(): Scales features by dividing by the maximum absolute value, mapping data to $[-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$ norm ($p=1$ for Manhattan norm, $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$ or $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]$ 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$) 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$ 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

---

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:

1. 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.
2. 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).
3. 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.
4. 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.
5. 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.