Prepare and test your skills
Prepare and test your skills
Optimizing feature pipelines for performance and scalability in BigQuery ML (opens in a new tab) means keeping transformation logic inside the warehouse, reusing curated features, and bundling preprocessing with training. Data flows directly from BigQuery storage to the training job without export to external clusters. Performance comes from minimizing repeated scans and choosing only transformations where accuracy gains justify compute cost.
Automatic preprocessing (opens in a new tab) in BigQuery ML is the imputation and transformation that runs inside the CREATE MODEL (opens in a new tab) statement during training and is reapplied during prediction. Because the logic lives with the model, raw tables flow from storage to training without an external preprocessing cluster. This dependency of the model on in-warehouse logic reduces pipeline stages, repeated scans, and drift between training-time and serving-time features. Disable or override defaults only when a custom transformation produces measurable accuracy gain that outweighs extra query and storage cost.
Missing-data handling in BigQuery ML defines how NULL values and unseen categories are replaced in training and prediction. The behavior is deterministic by column type, so pipelines do not need separate fill logic before training:
NULL values in both training and prediction with the mean value of that feature column as calculated from the original input data.NULL values in both training and prediction to an additional category, while unseen values in prediction are assigned a weight of 0.TIMESTAMP columns use a mixture where the generated Unix time column replaces values with the mean Unix time, and other extracted features assign values to the respective NULL category.STRUCT columns impute each field in both training and prediction according to the type-specific rule for that field.Because unseen categories contribute zero weight instead of failing, prediction stays available when new categories appear, at the cost of no signal from those values until retraining.
Default feature transformation in BigQuery ML converts raw BigQuery types into model-ready numeric inputs before training. Numeric types including INT64, NUMERIC, BIGNUMERIC, and FLOAT64 are standardized and centered at zero for most models, which helps gradient-based linear models converge. Categorical and temporal types including BOOL, STRING, BYTES, DATE, DATETIME, and TIME are one-hot encoded into sparse indicator columns, which preserves category signal but increases feature width and training cost.
| Input group | Transformation | Cost and accuracy behavior |
|---|---|---|
| Numeric features | Standardization | Applied for most models; skipped for boosted tree and random forest models where splits do not need scaling; controlled for k-means by STANDARDIZE_FEATURES |
| Categorical and date-time features | One-hot encoding | Expands each distinct value into its own column, improving linear accuracy but raising dimensionality and compute for high-cardinality columns |
Exam tip: Boosted tree and random forest models skip standardization, while k-means models standardize only when STANDARDIZE_FEATURES is enabled — do not pay for scaling where tree splits do not use it.
Choose or disable each default by weighing its computational cost (opens in a new tab) against its accuracy contribution, especially for wide categorical features.
Reusable feature supply is the practice of publishing curated tables once and letting multiple training jobs read them in place. Knowledge Catalog automatically ingests metadata from supported Google Cloud sources and makes assets discoverable without copying data. BigQuery sharing exchanges curated assets as listings in data exchanges and governs sensitive joins with data clean rooms, so consumers query shared tables rather than duplicating them.
For tabular, unstructured, and vector reuse across projects:
Reusing a feature table trades a one-time curation and governance cost for lower per-model ingestion, storage, and compute cost across the lifecycle.
The Pipeline (opens in a new tab) class in the bigframes.ml.pipeline module is the container that assembles multiple preprocessing steps with a final estimator so they are cross-validated and deployed together. Data flows from source DataFrames through transformers from bigframes.ml.preprocessing and bigframes.ml.compose, such as the KBinsDiscretizer class that bins continuous data into intervals, and then into the estimator for training. Because the pipeline object carries both transformers and estimator, prediction applies identical binning, encoding, and scaling without a separate preprocessing service.
Model selection in the bigframes.ml.model_selection module controls how training data is split and how validation cost is spent:
train_test_split function splits data into training and testing sets, for example with test_size=0.2, for a single low-cost validation pass.KFold class and KFold.split method create multi-fold training and testing sets, which is valuable for small datasets where a single split is unstable.cross_validate function automatically creates folds, trains and evaluates on each fold, and returns per-fold scores, for example with cv=5.Choose a single split when volume makes repeated training expensive, and choose 5-fold validation when stability of the accuracy estimate justifies training five times. Standardized ingestion with Dataflow for batch and stream sources, combined with Eventarc for event-driven processing, keeps feature inputs fresh without custom movement code.
BigQuery ML (opens in a new tab) is the in-warehouse machine learning service that creates, trains, and serves models with SQL in BigQuery. Feature engineering in BigQuery ML combines plain SQL expressions in the TRANSFORM clause of CREATE MODEL with built-in manual preprocessing functions. The TRANSFORM clause defines custom preprocessing during training, and the same logic and learned statistics are automatically applied to prediction input.
The TRANSFORM execution model defines how custom SQL and preprocessing functions move from training to prediction in BigQuery ML. Manual preprocessing functions (opens in a new tab) can be used inside the TRANSFORM clause of CREATE MODEL (opens in a new tab) and can also be used outside TRANSFORM for ad-hoc preparation. When analytic functions are used inside TRANSFORM during training, statistics computed over training rows are saved in the model and automatically applied to prediction input, so training and serving stay consistent.
Manual preprocessing functions divide into three execution types by the rows they observe:
| Function type | Input scope | Prediction behavior |
|---|---|---|
| Scalar function | Operates on a single row, for example ML.BUCKETIZE | Applies the same row-level rule in training and prediction |
| Analytic function | Operates on all rows and returns a result per row, for example ML.QUANTILE_BUCKETIZE | Reuses training-time statistics for prediction input |
| Table-valued function | Operates on all rows and outputs a table, for example ML.FEATURES_AT_TIME | Performs point-in-time lookup over feature tables for training or serving |
Every ML analytic function must be written with an empty OVER() clause, which signals that statistics are collected across all rows. A transform-only model decouples this work from fitting by using only the TRANSFORM clause without training a predictor. The ML.TRANSFORM function returns the preprocessed data from a model's TRANSFORM clause, so you can see the actual training data and prediction data that enter the model.
SQL-based feature creation in BigQuery ML is the practice of writing Standard SQL expressions inside the TRANSFORM clause to create transformed features before fitting. Temporal decomposition splits a timestamp or date into model-usable parts, such as extracting hour, day-of-week, or Unix time from a TIMESTAMP, DATE, DATETIME, or TIME column for seasonality and trend signals. Polynomial expansion creates higher-order and interaction terms from a numeric column to let linear models learn curves, while bucketization converts continuous numerics into discrete intervals to handle non-linearity and outliers. Because these expressions live in TRANSFORM, the feature definition travels with the model from training data flowing from the query_statement clause to prediction data flowing into ML.PREDICT.
Exam tip: ML analytic functions require an empty OVER() clause, and statistics learned in training are automatically reused in prediction — you do not recompute quantiles or scalers at serving time.
Numerical preprocessing functions in BigQuery ML regularize numeric expressions and reshape their distributions for modeling. ML.POLYNOMIAL_EXPAND generates polynomial features from a numeric input to capture curved relationships without leaving SQL. ML.BUCKETIZE assigns each row to a fixed interval using caller-supplied boundaries, while ML.QUANTILE_BUCKETIZE as an analytic function learns quantile cut points across all training rows and assigns each row to a bucket with balanced counts. Choose ML.BUCKETIZE when domain cut points are known in advance, and choose ML.QUANTILE_BUCKETIZE when boundaries should adapt to skewed distributions.
Additional numerical functions standardize scale and clean values before training:
Categorical preprocessing functions in BigQuery ML convert strings and discrete values into model-ready encodings and interactions. ML.FEATURE_CROSS combines two or more categorical inputs into a single crossed feature, so the model learns the joint effect of the combination rather than each column alone. The crossed output flows from source columns in TRANSFORM to the training algorithm as one feature, which is useful for pairs like device and hour-bucket or product and region where co-occurrence carries the signal. Use ML.FEATURE_CROSS after bucketization or encoding when the predictive pattern depends on co-occurrence.
Other categorical functions control dimensionality and representation:
Automatic preprocessing (opens in a new tab) in BigQuery ML is the default imputation and transformation that CREATE MODEL applies when no manual override is specified. Numeric NULL values in both training and prediction are replaced with the mean value of the given column as calculated from the feature column in the original input data. NULL values in one-hot or multi-hot encoded columns are mapped to an additional category, while previously unseen categories in prediction are assigned a weight of 0. Each field of a STRUCT is imputed according to its type, so nested data follows the same rules as top-level equivalents.
Default type transformations set the starting point that manual features must complement. Most models standardize and center numeric INT64, NUMERIC, BIGNUMERIC, and FLOAT64 columns at zero, with no standardization for boosted tree and random forest models and with STANDARDIZE_FEATURES controlling k-means. BOOL, STRING, BYTES, DATE, DATETIME, and TIME inputs are one-hot encoded by default, while TIMESTAMP columns use a mixture where the generated Unix time column uses mean Unix time and other extracted features use their NULL category. Design manual TRANSFORM logic with BigQuery DataFrames (opens in a new tab) exploration to override this baseline only where scale, cardinality, skew, or temporal structure requires explicit control.
Automated feature selection and evaluation in BigQuery ML (opens in a new tab) is the practice of using model artifacts and built-in functions to identify which input columns most influence predictions. A trained model saves its learned structures as model weights, and BigQuery ML exposes those weights through SQL functions or through model export so importance can be inspected. Preprocessing in BigQuery DataFrames shapes raw columns into scaled, encoded, or binned features before training, which makes later weight signals more reliable for selection.
Model weights (opens in a new tab) in BigQuery ML are the learned components that a model saves after training on BigQuery data. A linear regression model saves a vector of feature coefficients, a decision tree model saves trees of if-then statements with specific split values, and a deep neural network model saves a graph structure with matrices of weights. Inspecting these weights shows how strongly each feature contributes to output, so features with near-zero coefficients or that never appear in splits are candidates for removal while high-magnitude coefficients and frequent split features are retained.
Direct weight retrieval in BigQuery ML returns weights through SQL functions for linear, unsupervised, and time-series models without export. The ML.WEIGHTS function applied to a linear or logistic regression model returns one row per feature coefficient plus the intercept, which lets an engineer rank features by magnitude and sign. For unsupervised models, weights flow from the model artifact to the analyst through specialized functions that expose cluster and latent structure rather than prediction coefficients.
| Model category | Model types | Weight function | What the function returns for selection |
|---|---|---|---|
| Supervised models | Linear and logistic regression | ML.WEIGHTS | Feature coefficients and intercept for ranking impact |
| Unsupervised models | K-means | ML.CENTROIDS | Feature coefficients for all centroids |
| Unsupervised models | Matrix factorization | ML.WEIGHTS | Latent-factor weights for user matrix and item matrix |
| Unsupervised models | Principal component analysis (PCA) | ML.PRINCIPAL_COMPONENTS, ML.PRINCIPAL_COMPONENT_INFO | Eigenvectors per component and per-component statistics such as eigenvalue |
| Time-series models | ARIMA_PLUS | ML.ARIMA_COEFFICIENTS | Coefficients for trend component; use ML.ARIMA_EVALUATE for seasonal patterns |
The PCA outputs support dimensionality-based selection because ML.PRINCIPAL_COMPONENTS shows how original features load onto each eigenvector and ML.PRINCIPAL_COMPONENT_INFO shows how much variance each eigenvalue explains. The matrix factorization output supports interaction-based selection because user and item matrices together reveal which latent factors carry recommendation signal. The ARIMA output supports temporal selection because only trend coefficients come from ML.ARIMA_COEFFICIENTS, while seasonal behavior must be checked separately.
Export-based evaluation in BigQuery ML is required because boosted tree, random forest, deep neural network (DNN), wide-and-deep, and AutoML Tables models have no in-warehouse weight functions. Feature importance for these models must be obtained by running an EXPORT MODEL (opens in a new tab) statement that writes the model artifact from BigQuery to a Cloud Storage bucket, then inspecting the artifact with an external library. Exported boosted tree and random forest models depend on the XGBoost library for tree visualization, while exported DNN and wide-and-deep models depend on the TensorFlow library for graph inspection, and there is no method for getting weight information for AutoML Tables models.
Visualization of an exported XGBoost model follows an ordered workflow where data flows from BigQuery to Cloud Storage to a notebook:
model.bst file from the bucket to a local environment.model.bst file to the Files area of a Colab notebook.xgb.Booster(model_file="model.bst") and call xgb.plot_tree for each iteration number to render one tree per boosting iteration.The exported tree uses encoded identifiers rather than original column names, so interpretation depends on two sidecar files in the export. Categorical splits such as f0 < 2.95 refer to label-encoded integers, and the corresponding category is found by position in the vocabulary file inside the assets/ directory. Generic identifiers such as f0 and f1 map to real column names through assets/model_metadata.json, where each fN key is an index into the original feature list.
Exam tip: Use ML.WEIGHTS for linear and logistic regression, but export to Cloud Storage and use XGBoost or TensorFlow for boosted tree, random forest, DNN, and wide-and-deep models; AutoML Tables has no weight retrieval method.
DataFrames preprocessing in BigQuery DataFrames creates transformed features through the bigframes.ml.preprocessing module and the bigframes.ml.compose module, and those transformers feed estimators during training. A transformer reads a BigQuery DataFrame column and writes a normalized, scaled, or encoded column, so the downstream model receives comparable numeric inputs and its importance signals reflect predictive value rather than raw scale. Because the same preprocessing logic applies at training and prediction time, a feature retained after evaluation keeps the same transformation when the model serves.
The available transformers cover numeric scaling, discretization, and categorical encoding:
[-1, 1] by its maximum absolute value.[0, 1].A TRANSFORM_ONLY model preserves this feature-engineering logic by returning the same columns specified in the model's TRANSFORM clause at prediction time. Prediction outputs for PCA models separately return PRINCIPAL_COMPONENT_IDS as INT64 values and PRINCIPAL_COMPONENT_PROJECTIONS as FLOAT values, which lets an engineer verify how a reduced feature set projects new serving rows before finalizing selection. Ingestion for these pipelines can use Dataflow (opens in a new tab) for batch and stream sources without custom movement code.
Numeric NULL values in both training and prediction are replaced with the mean value of that feature column as calculated from the original input data. NULL values in one-hot and multi-hot encoded columns are mapped to an additional category, while unseen categories in prediction are assigned a weight of 0 so prediction stays available without signal from those values until retraining.
ML.BUCKETIZE assigns each row to a fixed interval using caller-supplied boundaries, while ML.QUANTILE_BUCKETIZE learns quantile cut points across all training rows to create buckets with balanced counts for skewed distributions. ML.QUANTILE_BUCKETIZE is an analytic function that requires an empty OVER clause, and statistics learned during training are automatically reused for prediction input.
ML.WEIGHTS applied to a linear or logistic regression model returns one row per feature coefficient plus the intercept for ranking impact, and it also returns latent-factor weights for matrix factorization models. Boosted tree, random forest, deep neural network and wide-and-deep models have no in-warehouse weight functions and require an EXPORT MODEL statement to a Cloud Storage bucket for inspection with the XGBoost library or the TensorFlow library, while there is no method for getting weight information for AutoML Tables models.
Last updated