BigQuery ML provides automatic preprocessing during model training through the CREATE MODEL statement, which handles missing value imputation and feature transformations without manual intervention. For numeric columns, BigQuery ML replaces NULL values with the mean value calculated from the original input data during both training and prediction. One-hot and multi-hot encoded columns map NULL values to an additional category added to the data, while previously unseen values receive a weight of 0 during prediction. Timestamp columns use a mixture of methods, replacing Unix time values with the mean and assigning extracted features to their respective NULL categories. Struct columns are imputed field-by-field according to their individual types.
Feature transformations in BigQuery ML standardize input features by default: numeric types like INT64, NUMERIC, BIGNUMERIC, and FLOAT64 are standardized (centered at zero) for most model types, though boosted tree and random forest models skip this step, and k-means models use the STANDARDIZE_FEATURES option to control this behavior. Categorical types including BOOL, STRING, BYTES, DATE, DATETIME, and TIME are automatically one-hot encoded, which creates binary columns for each unique category value.
When automatic preprocessing does not meet specific requirements, the TRANSFORM clause in the CREATE MODEL statement combined with manual preprocessing functions enables custom data transformations. These functions operate in three categories: scalar functions that process single rows like ML.BUCKETIZE, table-valued functions that process all rows and output tables like ML.FEATURES_AT_TIME, and analytic functions that collect statistics across all rows before computing results like ML.QUANTILE_BUCKETIZE. The ML.TRANSFORM function increases transparency by returning the preprocessed data from a model’s TRANSFORM clause, allowing you to verify exactly what training and prediction data the model receives.
Numerical preprocessing functions include ML.MIN_MAX_SCALER for scaling values to a defined range, ML.STANDARD_SCALER for z-score normalization, ML.ROBUST_SCALER for outlier-resistant scaling, and ML.QUANTILE_BUCKETIZE for distributing values into quantile-based buckets. Categorical functions include ML.ONE_HOT_ENCODER for creating binary columns, ML.LABEL_ENCODER for converting categories to integer labels, ML.MULTI_HOT_ENCODER for multi-valued categorical columns, and ML.FEATURE_CROSS for combining multiple categorical features into a single feature.
When working with time-sensitive features, point-in-time correctness prevents data leakage between training and serving by ensuring that feature values reflect what was available at the time of prediction. This requires including a timestamp column in feature tables and using functions like ML.FEATURES_AT_TIME and ML.ENTITY_FEATURES_AT_TIME to retrieve feature values as they existed at a specific point in time. These functions are used in the query_statement clause of the CREATE MODEL statement for training or in inference functions like ML.PREDICT for serving.
Vertex AI Feature Store provides an additional option for serving features to BigQuery ML models registered in Vertex AI, managing and serving features with low latency while working on top of feature tables in BigQuery. Online serving retrieves features in real time for online prediction, while offline serving retrieves historical features for model training. The Agent Platform Feature Store specifically lets you maintain feature data in BigQuery while Vertex AI Feature Store acts as a metadata layer providing online serving capabilities through Bigtable, eliminating the need to copy data to a separate offline store.
For complex feature engineering workflows, Vertex AI Pipelines structures ML pipelines as directed acyclic graphs (DAG) of containerized pipeline tasks interconnected using input-output dependencies, enabling automation of the entire ML lifecycle from data preparation through model deployment. The BigQuery ML components in the Google Cloud Pipeline Components SDK let you compose ML pipelines within Vertex AI Pipelines for tasks like data transformation, model training, and batch prediction.
For simpler SQL-based pipelines, GoogleSQL procedural language executes multiple statements in sequence with shared state, automating tasks like creating or dropping tables and implementing complex logic using IF and WHILE constructs. Multi-statement queries can be saved and scheduled to automate model training, inference, and monitoring. Dataform develops, tests, versions, and schedules complex SQL workflows for data transformation in BigQuery, making it suitable for more complex SQL-based ML pipelines where version control is required.
Materialized views significantly improve feature pipeline performance by precomputing and storing query results that can be refreshed incrementally, reducing the computational cost of repeated transformations on large datasets. For maximum speed, store materialized data rather than using views or subqueries for training data. BigQuery BI Engine accelerates SQL queries by intelligently caching frequently accessed data in memory, and can be combined with materialized views for additional performance gains when the views join and flatten data into BI Engine-optimized structures.
The trade-off between computational cost and model accuracy from engineered features should guide pipeline design decisions: more complex transformations may improve model accuracy but increase query costs and latency. Use the TRANSFORM clause to capture preprocessing logic within the model itself, ensuring that the same transformations applied during training are automatically applied during prediction without requiring separate serving code. For transform-only use cases, create a transform-only model that only performs data transformations, decoupling preprocessing from model training and enabling reuse of transformation logic across multiple models.
BigQuery ML provides automated methods for feature selection and evaluation, primarily through the model training process itself. When you create certain model types, the service automatically calculates and outputs feature importance metrics. For example, models like XGBoost and AutoML Tables generate feature importance scores as part of their evaluation output. These scores indicate the relative contribution of each input feature to the model’s predictions, allowing you to identify and retain the most impactful variables. This automation eliminates the need for manual, pre-training feature selection steps for these supported models.
For interpretable model types, BigQuery ML offers specific SQL functions to retrieve model weights, which are the learned parameters that define the relationship between features and the target variable. You can use the ML.WEIGHTS function for linear and logistic regression models to get feature coefficients and the intercept. A positive coefficient indicates a feature’s positive correlation with the target, while a negative coefficient indicates an inverse relationship. The magnitude of the coefficient reflects the feature’s impact strength. This direct access to model internals is crucial for evaluating which features the model has deemed significant.
You can engineer features for implicit selection by applying regularization techniques within the CREATE MODEL statement. By specifying L1_REG or L2_REG parameters for linear models, you introduce penalties on the size of the coefficients. L1 regularization can drive some feature coefficients to exactly zero, effectively performing automatic feature selection by excluding those variables from the final model. L2 regularization shrinks all coefficients but does not set them to zero, helping to reduce overfitting and stabilize the model by de-emphasizing less important features. Choosing between L1 and L2 involves a tradeoff between aggressive feature selection (L1) and handling correlated features more gracefully (L2).
BigQuery ML does not support direct weight retrieval for all model types, including Boosted Tree, Random Forest, DNN, Wide-and-Deep, and AutoML Tables models. To evaluate features for these models, you must export the model artifact to Cloud Storage. Once exported, you can use external libraries like the XGBoost library to visualize tree structures for tree-based models, which reveals the splits and features used at each node, indicating their importance. For AutoML Tables models, there is no method to extract direct feature importance information from the exported artifact, representing a limitation in the automated evaluation workflow for that specific model type.
The TRANSFORM clause within a BigQuery ML CREATE MODEL statement encapsulates manual data preprocessing logic directly inside the model object. When functions are defined within the TRANSFORM clause, the statistical parameters calculated during training are recorded and automatically applied to input features during inference. To decouple feature engineering logic from specific training algorithms, practitioners can build a transform-only model to manage and reuse consistent transformation pipelines across projects. The ML.TRANSFORM function enables transparency by returning the exact transformed feature values produced by a model’s TRANSFORM clause during both training and evaluation phases.
Numerical feature engineering functions in BigQuery ML modify continuous values through scaling, non-linear expansion, and discretization. The ML.POLYNOMIAL_EXPAND function generates non-linear feature interactions by computing all polynomial combinations of input numerical features up to a specified degree. For discretization, the scalar function ML.BUCKETIZE maps numeric columns into user-defined numerical ranges row by row. In contrast, the analytic function ML.QUANTILE_BUCKETIZE evaluates continuous data across the entire dataset to partition values into quantiles and requires an empty OVER() clause. Additional numerical scalers—including ML.STANDARD_SCALER, ML.MIN_MAX_SCALER, ML.ROBUST_SCALER, and ML.NORMALIZER—regularize and scale numerical distributions to prevent dominant feature ranges.
Categorical preprocessing functions in BigQuery ML convert discrete categories and string columns into structured representations suitable for machine learning models. The ML.FEATURE_CROSS function generates synthetic feature combinations by pairing categorical inputs, allowing linear and tree models to capture non-linear category interactions. For encoding discrete levels, BigQuery ML offers ML.ONE_HOT_ENCODER, ML.MULTI_HOT_ENCODER, and ML.LABEL_ENCODER, alongside ML.HASH_BUCKETIZE for reducing high-cardinality categories into a fixed number of buckets. General missing data cleanup is handled by ML.IMPUTER, which replaces missing values in string or numerical features using designated replacement strategies.
Point-in-time correctness in BigQuery ML prevents target leakage by extracting feature values precisely as they existed at a specific historical moment. The table-valued functions ML.FEATURES_AT_TIME and ML.ENTITY_FEATURES_AT_TIME enforce temporal cutoff boundaries when querying time-sensitive feature tables stored in BigQuery. Practitioners pass these functions into the query statement of a CREATE MODEL definition for training or inside ML.PREDICT during serving. By aligning the historical timestamp of the features with the corresponding entity event, these functions ensure that future data does not inadvertently bias model evaluation or deployment.
StudyCerts
StudyCertsCREATE MODEL statement, with different strategies per column type (mean for numerics, extra category for encoded columns).TRANSFORM clause in CREATE MODEL captures manual preprocessing logic within the model object, ensuring the same transformations apply during both training and prediction without separate serving code.L1_REG) drives some feature coefficients to zero, performing implicit feature selection, while L2 regularization (L2_REG) shrinks all coefficients to reduce overfitting.Use automatic preprocessing when the default transformations (mean imputation, standardization, one-hot encoding) meet your needs and you want the simplest code. Use the TRANSFORM clause when you need custom scalers, feature crosses, or bucketization that automatic preprocessing does not provide.
ML.BUCKETIZE is a scalar function that assigns each row to a user-defined bucket based on numeric thresholds. ML.QUANTILE_BUCKETIZE is an analytic function that computes quantiles across the entire dataset first, then assigns rows to quantile-based buckets; it requires an empty OVER() clause.
Include a timestamp column in your feature tables and use ML.FEATURES_AT_TIME or ML.ENTITY_FEATURES_AT_TIME in the query_statement of CREATE MODEL for training or in ML.PREDICT for serving. These functions retrieve feature values as they existed at the specified time, preventing data leakage.
Models like XGBoost and AutoML Tables output feature importance scores automatically during training. For linear and logistic regression, you can use ML.WEIGHTS to get coefficients. Boosted Tree, Random Forest, DNN, Wide-and-Deep, and AutoML Tables models do not support direct weight retrieval via SQL; you must export the model artifact for tree-based models.
Professional Machine Learning Engineer
Prepare and test your skills
Prepare and test your skills