BigQuery ML Feature Engineering and Serving

Design and execution of data transformation pipelines for feature creation is the process of converting raw source data into normalized, encoded, and complete features that models can consume at scale. Raw data flows from operational sources into ingestion tools, then through transformation logic, and finally into training tables or a central feature store for serving. On Google Cloud, BigQuery (opens in a new tab) handles tabular SQL transformation, Dataflow (opens in a new tab) handles batch and streaming transformation, and BigQuery ML (opens in a new tab) applies automatic preprocessing during CREATE MODEL.

1.1Scalable Ingestion and Transformation Architecture

Scalable ingestion and transformation architecture separates source selection from data movement so batch, streaming, and event-driven features can share one pipeline pattern. Data sources include operational databases such as Cloud SQL, Spanner, AlloyDB for PostgreSQL, and Firestore, data lakes such as Cloud Storage with BigLake, streaming platforms such as Pub/Sub and Dataflow, and external APIs. For each source, data flows from the source system into an ingestion tool and then into BigQuery or Cloud Storage storage optimized for machine learning (ML) training, separate from the operational source.

Transformation tool choice depends on latency, volume, and reuse requirements for feature consumption. Dataflow processes data from various sources in batch and stream with ML-component integration, and it combines with Eventarc for event-driven architectures where changes trigger processing. Dataform automates SQL-based transformations for extraction, loading, and transformation (ELT) workflows after raw data lands in BigQuery staging tables. BigQuery itself performs classical tabular ingestion and in-warehouse SQL transformation for frequently accessed structured data, where materialized tables provide faster training reads than views or subqueries.

Feature transformation logic in these pipelines covers four core tasks for model consumption. Feature scaling standardizes numeric ranges, categorical encoding converts strings and booleans to numeric vectors, missing-value handling replaces gaps, and derived-feature creation builds new signals such as bucketed ranges or crossed categories. Dataflow or Dataform automates these operations in batch, streaming, or real time, while Vertex AI Feature Store (opens in a new tab) centralizes the resulting features in BigQuery tables or views for reuse across models and projects. Because BigQuery stores both online serving data and historical offline data, the same feature definitions support training and serving without duplicate engineering.

1.2Automatic Preprocessing in BigQuery ML

Automatic preprocessing in BigQuery ML is the default transformation that runs during training through the CREATE MODEL statement without custom code. Automatic preprocessing consists of missing-value imputation followed by feature transformation, and the same statistics learned during training are automatically applied during prediction. This behavior ensures that training and serving see consistent feature values even when prediction rows contain nulls or previously unseen categories.

Missing-data handling in BigQuery ML treats NULL values during training as missing data, while prediction treats both NULL values and unseen values as missing. Numeric columns replace nulls with the column mean calculated from the original input data in both training and prediction. One-hot and multi-hot encoded columns map nulls to an added category, while unseen values receive a weight of 0 during prediction. TIMESTAMP columns use a mixed approach where the generated Unix time column uses mean imputation and other extracted fields use a null category, and each field of a STRUCT column is imputed according to its own type.

The following table compares imputation behavior by column type for training and prediction:

Default feature transformations in BigQuery ML depend on input data type and model type. Numeric types including INT64, NUMERIC, BIGNUMERIC, and FLOAT64 are standardized and centered at zero for most models, which regularizes scale differences before training. Non-numerical, non-array columns such as BOOL, STRING, BYTES, DATE, DATETIME, and TIME receive one-hot encoding for most models, which creates a separate feature for each unique value. Non-numerical ARRAY columns receive multi-hot encoding, which creates a separate feature for each unique element in the array.

1.3Manual Feature Engineering with the TRANSFORM Clause

Manual feature engineering with the TRANSFORM clause is custom preprocessing defined inside CREATE MODEL that runs in addition to automatic preprocessing. The clause takes raw input columns, applies manual preprocessing functions, and passes only the transformed outputs to training, so the model never sees the untransformed values. The ML.TRANSFORM function then returns those preprocessed rows for inspection, which makes training data and prediction-time serving data transparent and reproducible.

Manual preprocessing functions divide by how they read rows and preserve training-to-serving consistency. Scalar functions operate on a single row, such as bucketizing a value. Table-valued functions operate on all rows and output a table, such as retrieving features at a point in time. Analytic functions operate on all rows but return a result per row based on statistics collected across the full dataset, and they always require an empty OVER() clause so BigQuery ML can capture the statistics during training and reapply the same statistics during prediction.

Common manual functions for normalization, encoding, and cleanup include:

• ML.IMPUTER for data cleanup on string or numerical expressions with custom replacement strategies.
• ML.BUCKETIZE, ML.QUANTILE_BUCKETIZE, and ML.HASH_BUCKETIZE for binning continuous or categorical values into intervals.
• ML.STANDARD_SCALER, ML.MIN_MAX_SCALER, ML.MAX_ABS_SCALER, ML.ROBUST_SCALER, and ML.NORMALIZER for regularizing numeric scale and distribution.
• ML.ONE_HOT_ENCODER, ML.LABEL_ENCODER, and ML.MULTI_HOT_ENCODER for encoding categorical variables into model-ready vectors.
• ML.FEATURE_CROSS for creating derived features by combining two categorical signals, ML.POLYNOMIAL_EXPAND for creating polynomial derived features, and ML.NGRAMS for creating text features from string expressions.

A transform-only model decouples preprocessing from model fitting when teams need to version and reuse transformation logic independently. The transform-only model contains only the TRANSFORM clause and outputs transformed tables without training a predictor. Downstream training jobs then depend on the transform-only model for consistent features, which avoids maintaining multiple custom implementations across projects.

1.4Reusable Python and SQL Pipelines for Training and Serving

Reusable Python and SQL pipelines assemble preprocessing steps and estimators into versioned workflows that automate training, evaluation, and deployment. BigQuery DataFrames provides the bigframes.ml library for Python-based preprocessing, where transformers from bigframes.ml.preprocessing and bigframes.ml.compose prepare data for estimators. The bigframes.ml.pipeline.Pipeline class chains multiple transforms with a final estimator so preprocessing and modeling deploy together, while bigframes.ml.model_selection splits data with train_test_split, KFold, or cross_validate for robust evaluation.

Production orchestration on Google Cloud routes pipeline tasks through dependencies so repeatable steps can be scheduled and monitored. A typical BigQuery ML development workflow follows an ordered sequence:

1. Create: Build the model with the CREATE MODEL statement.
2. Preprocess: Apply automatic preprocessing plus manual functions in TRANSFORM.
3. Tune: Refine fit with hyperparameter tuning.
4. Evaluate: Assess performance on held-out data and compare models.
5. Predict: Perform inference on new data.
6. Explain: Provide feature attributions for predictions and overall behavior.
7. Inspect: Review model weights to understand learned components.

Three orchestration options support different complexity and governance needs for ML pipelines. Vertex AI Pipelines structures work as a directed acyclic graph (DAG) of containerized tasks where outputs from one task flow as inputs to the next task, which suits portable multi-step MLOps with BigQuery ML components. GoogleSQL procedural language runs multi-statement queries with shared state and IF and WHILE logic, which suits less complex SQL pipelines that can be saved and scheduled for retraining and monitoring. Dataform develops, tests, versions, and schedules complex SQL transformation suites, which suits ELT-heavy feature pipelines that require version control and documentation before model training.

Optimizing data storage and partitioning for ML workloads means choosing where data lives, what file format it uses, and how it is divided so iterative training and batch prediction scan less data, run faster, and cost less. BigQuery (opens in a new tab) separates storage, compute, and memory into independently scaled services, while Cloud Storage (opens in a new tab) holds data-lake files that feed training jobs directly or through BigQuery tables. The right combination of columnar formats, Hive-style partitioning, BigQuery native storage, and read APIs determines whether repeated feature queries and scoring jobs stay fast and affordable.

2.1Separated Storage Compute and Memory in BigQuery

Separated storage, compute, and memory in BigQuery is a design where compute, memory, and storage scale independently instead of as fixed nodes. Compute is measured in slots, which are units of query-processing capacity managed by Google in reservations assigned to projects, folders, or organizations. Memory comes from a remote distributed service connected to slots by a petabit network, and table data lives in Google's distributed file system where it is automatically compressed, encrypted, replicated, and distributed without consuming query slots. Because storage growth to dozens of petabytes does not require adding compute, teams can keep large training tables and prediction outputs online while sizing slots only for query and BigQuery ML workload concurrency.

2.2Columnar Formats and File Layout in Cloud Storage

Columnar file formats in Cloud Storage store processed data for efficient analytical access during feature engineering and model training. Parquet and Optimized Row Columnar (ORC) organize data by column rather than by row, which allows efficient storage and faster access for analytical workloads compared to row-oriented JavaScript Object Notation (JSON) or comma-separated values (CSV) files. A Hive partitioned layout places files in directory prefixes keyed by partitioning columns, so queries that filter on those columns can prune partitions and skip entire directories. File design must also balance the number of files against file size, because too many tiny files increases metadata and listing overhead while very large files can limit parallelism for distributed readers.

The following table compares storage and query behavior for ML scans:

2.3Partitioning Clustering and BigQuery Native Storage

Partitioning, clustering, and BigQuery native storage add controls that external files alone cannot match for repeated training queries. When queries against BigLake tables that read external Parquet, ORC, JSON, or CSV files cannot meet service-level agreement (SLA) requirements and cannot be tuned further, the supported path is to convert the exposed data to BigQuery storage and redefine authorized views to point at the new BigQuery tables. This migration generally does not disrupt consumers or require query changes, but it unlocks optimizations unavailable to BigLake tables, including materialized views with different partitioning and clustering than the base tables and acceleration with the Business Intelligence (BI) Engine. In practice, data flows from Cloud Storage ingestion buckets through processing into curated BigQuery tables, and the serving layer depends on those tables for consistent, pruned, and clustered access during batch prediction.

2.4Read Paths for Training and Batch Prediction

Read paths for training and batch prediction determine how much filtering happens before data reaches training code and how access is controlled. The BigQuery Storage Read API can read data from both BigQuery tables and BigLake tables, supports filtering and fine-grained access control, and is a good option for stable analytics or ML consumers that repeatedly scan large feature sets. The Cloud Storage JSON or XML APIs read bucket objects directly, which is useful only when consumers cannot use the SQL interface or when the bucket holds formats not supported by the Storage Read API. Direct bucket access does not allow filtering or fine-grained access control, so it is viable mainly for stable, small-sized datasets measured in gigabytes, and copying bucket data into consumer projects is discouraged because multiple copies increase storage cost plus maintenance and lineage overhead.

2.5Query Execution Monitoring and Cost Control for Repeated Jobs

Query execution, monitoring, and cost control for repeated jobs reward early filtering and partition pruning for iterative training queries. First, BigQuery builds an execution tree that breaks the query into stages with steps that can run in parallel. Then stages exchange intermediate data through a fast distributed shuffle tier that uses network and memory to move data to workers, while additional workers monitor progress and dynamically adjust the query plan as results arrive. Only after planning completes does BigQuery write final results to persistent storage and return them, which lets the next identical training-data query serve cached results. Teams control cost and performance for this repeated pattern with on-demand pricing that charges for bytes scanned versus capacity-based pricing that uses reserved slots, plus query-plan inspection in the console, INFORMATION_SCHEMA.JOBS views, custom quotas, and billing alerts — for example, a stage that writes far more output than other stages signals that a filter should move earlier to reduce scanned and shuffled data.

Data versioning and reproducibility for ML experiments is the practice of linking every training run to the exact code, dataset snapshot, transformations, parameters, and metrics that produced it. On Google Cloud, this linkage is built from Vertex ML Metadata (opens in a new tab) for experiment-level lineage, Knowledge Catalog (opens in a new tab) for organization-wide data governance, and Model Registry for model versions, combined with reproducible storage and pipeline practices.

3.1Track Lineage Across Data Code and Artifacts

Lineage tracking across data, code, and artifacts connects data sources, transformations, parameters, metrics, and dataset and model artifacts so any experiment can be audited, debugged, and rerun. Vertex ML Metadata logs metadata about data sources, transformations, model parameters, and experiment results, and Experiments on Vertex AI integrates with it to build linkage across those objects. Vertex AI TensorBoard visualizes and compares runs by metrics such as loss and accuracy, model graph, and embeddings, while audit logs record who accessed or modified data and models and when.

Knowledge Catalog provides the governance layer around pipeline lineage and discovery. Metadata from supported Google Cloud sources is automatically ingested into the universal catalog, while sources outside Google Cloud require custom entries. Technical metadata is enriched with business metadata through aspects, and BigQuery adds column-level lineage for fine-grained tracking. Lineage from Vertex AI Pipelines artifacts can be searched in Knowledge Catalog for debugging, troubleshooting, and root-cause analysis, with access controlled so data scientists can search and use trusted assets.

The following table compares what each governance tool tracks and when to use it:

Version control for code and documents uses Git, and pipeline definitions use pipeline versioning in Vertex AI Pipelines or Kubeflow Pipelines. Together, Git plus metadata plus registry versions mean code changes, data changes, and model changes each have an independent, queryable history.

3.2Create Repeatable Training Inputs with Managed Datasets and Materialized Storage

Repeatable training inputs with managed datasets and materialized storage require separating ML training storage from operational sources and freezing the data actually used for training. Data flows from source systems such as logs, devices, or scraped documents into storage optimized for ML training, separate from the operational source. For structured and semi-structured data, that destination is BigQuery, and for maximum speed and reproducibility the training input should be materialized data rather than views or subqueries.

Vertex AI managed datasets create a clear link between that frozen data and custom-trained models. A managed dataset provides descriptive statistics and automatic or manual splitting into train, test, and validation sets, and data is read out through the BigQuery Storage API for artifact tracking. Managed datasets are not required when a team wants full control over splitting logic inside training code or when lineage between data and model is not critical to the application.

Unstructured data follows the same freeze-and-group principle in Cloud Storage. Images, video, and audio are combined into large container formats such as sharded TFRecord files for TensorFlow or Avro files for other frameworks, because large files improve read and write throughput. Aim for files of at least 100 MB with between 100 and 10,000 shards, and use buckets and directories to group shards for management.

3.3Control Training-Serving Skew with Centralized Features

Training-serving skew happens when features computed for offline training differ from features served for online prediction across model iterations. Vertex AI Feature Store (opens in a new tab) reduces this risk by creating, maintaining, sharing, and serving ML features in a central location that is optimized for low-latency workloads. Feature data lives in a BigQuery table or view, and BigQuery retains historical feature data so offline training can reuse the same definitions used online.

Use of the Feature Store depends on two objects: the team first creates an online store instance, then defines feature views on top of BigQuery data. Data flows from BigQuery historical storage to offline training jobs and from the online store to serving paths, so both paths resolve the same feature logic. Scheduled monitoring of features and feature groups detects drift after deployment, and centralized features can be reused across different models and projects instead of re-engineering the same transformations.

Reusable data products extend the same idea beyond a single project. Curated datasets published as data products are discovered through Knowledge Catalog and securely exchanged through BigQuery sharing, including data exchanges, listings, and data clean rooms for sensitive data. This reuse accelerates model development and keeps iterations anchored to governed, versioned inputs.

3.4Standardize Environments and Automate Pipelines for Reproducibility

Standardized environments and automated pipelines ensure the same code and data produce the same result regardless of who runs the experiment. Vertex AI Workbench instances provide a secure, reproducible way to access data and AI services for writing code, starting jobs, running queries, and checking status. Best practice is to create one instance per team member, and multiple instances per person when projects have different dependencies, treating each instance as a virtual workspace that can be stopped when not in use.

Automated training pipelines enforce standardization and remove manual variation between iterations. Vertex AI Pipelines orchestrates complex ML workflows as separate containerized tasks, where each step manages data flow to the next step only after its dependencies complete. The pipeline definition controls order of operations for transformation, training, and deployment, and pipeline versioning preserves each workflow revision. Combined with Git for source tracking, the Vertex AI SDK for Python for consistent programmatic runs, and automated data quality scans plus anomaly detection in BigQuery for input validation, the full path from raw data to versioned model remains consistent, observable, and repeatable.

Key Facts

• BigQuery ML automatic preprocessing applies missing-value imputation followed by feature transformation during CREATE MODEL, and the same training statistics are reapplied during prediction.
• The TRANSFORM clause passes only transformed outputs to training, and analytic preprocessing functions require an empty OVER() clause so training statistics can be reused at prediction time.
• Parquet and ORC are columnar formats that allow only needed columns to be read and partitions to be pruned, while JSON and CSV are row-oriented and typically require full scans.
• The BigQuery Storage Read API supports filtering and fine-grained access control for BigQuery and BigLake tables, while direct Cloud Storage API access does not.
• Vertex AI Feature Store centralizes features in BigQuery tables or views so offline training and online serving resolve the same feature logic and reduce training-serving skew.