Stop Cloning Data: Connecting Vertex AI Directly to BigQuery in Practice

A training job goes through CI, and suddenly an SRE is pinging you on Slack—the pipeline swallowed 400GB of storage in an hour. Duplicate data movement is quietly eating your cloud budget.

Many teams treat the Lakehouse as a write-once data warehouse: after ETL and feature transformation, they export the "features" to a new table or GCS bucket for Vertex AI to consume. This habit stems from convenience, but it's a disaster for budgets and data consistency. In regulated industries like healthcare and finance, having two versions of the "truth" in both the production warehouse and the training set is itself a compliance nightmare.

The core idea is straightforward: let the data stay where it is. If the features are already in BigQuery or Delta Lake, don't move them again. Make BigQuery the single source of truth, and let Vertex AI Pipelines connect directly to the raw or processed tables.

How This Pattern Actually Works

The goal is to use your existing data warehouse as a feature store. This is achieved by calling BigQuery DataFrames (BQF) or executing SQL directly within Kubeflow Pipelines (KFP) components.

Instead of writing a Spark job to export features to CSV, write a KFP component that executes CREATE OR REPLACE TABLE, or better yet, CREATE VIEW pointing directly to the already-processed features.

from kfp.dsl import component, Input, Output, Dataset

@component(base_image="python:3.9")
def train_model_op(project_id: str, query: str):
    from google.cloud import bigquery

    # Don't materialize data, query the existing lakehouse table directly
    client = bigquery.Client(project=project_id)
    job = client.query(query)
    df = job.to_dataframe()

    # Proceed with training
    # ...

The real key is moving from to_dataframe() to BigQuery ML (BQML) or Vertex AI's BigqueryTabularDataset. Through aiplatform.TabularDataset.create(display_name=..., bq_source=...), you create a "pointer," not a data copy. It's essentially a URI telling Vertex AI: go read this BigQuery table when training is needed.

The pipeline no longer waits for a data export job to finish—it only needs to verify that the schema exists. If using BigQuery Omni, this approach can even work across clouds. The pipeline triggers, reads metadata, and maps features into the training container, with costs concentrated on compute rather than storage.

BigQuery DataFrames (BQF) is a Python library from Google that translates pandas and scikit-learn style APIs into BigQuery SQL, executed server-side. It relies on the BigQuery Storage Read API and Query API underneath, so it can handle datasets far larger than a single machine's memory. The key is lazy execution: the object returned by bqf.read_gbq() is just a reference to a BigQuery table, and data isn't actually pulled until .to_pandas() or .execute() is called. This means you can use pandas-like syntax for feature engineering in KFP components while the actual computation happens inside BigQuery, without downloading the full dataset into the container.

BigQuery ML (BQML) allows you to train and deploy models directly within BigQuery using SQL statements, e.g., CREATE MODEL ... OPTIONS(model_type='logistic_reg') AS SELECT .... Its value lies in keeping the training phase inside the warehouse, avoiding feature exports. For common algorithms like linear models, boosted trees, and matrix factorization, BQML can directly consume tables in the warehouse and register models as BigQuery resources. When integrated with Vertex AI, you can use ML.PREDICT for batch inference, or export the model to GCS via EXPORT MODEL for use with a Vertex AI Endpoint.

The Trade-offs Nobody Wants to Mention

If this were a silver bullet, everyone would have done it already. But there are plenty of rough edges that vendors tend to gloss over.

Performance is non-deterministic. If the feature engineering logic involves complex joins across massive partitioned tables, running that query inside the training loop is competing with the warehouse for concurrency slots. If a DBA runs a heavy ANALYZE command while your pipeline is materializing features, the training job will hang. In real cases, a 4-hour training window got stretched to 12 hours due to underlying warehouse resource contention.

The data snapshot problem. Not materializing means you're training on live data. That's usually what we want, but it's also dangerous. If an upstream pipeline updates the feature table mid-training, you introduce non-deterministic bias into the model. Strict partitioning is essential—without using _PARTITIONTIME or a specific run_id as the partition key for the BigQuery table, you're essentially training on a moving target.

Vertex AI SDK version volatility. With google-cloud-aiplatform>=1.30.0, the way it handles bq_source in Dataset resources differs from older versions. Someone spent an entire afternoon debugging an InvalidArgument error, only to find that a BigQuery metadata mapping in the SDK had been deprecated. Always pin the SDK version in your container image's requirements.txt, and don't rely on latest.

_PARTITIONTIME and partition pruning: Partitioned tables in BigQuery maintain metadata for each partition, and _PARTITIONTIME is a pseudo-column representing the time data was written to that partition. Using WHERE _PARTITIONTIME = TIMESTAMP('2024-01-01') scans only one partition, avoiding full table scans. In the "no-copy" mode, if the feature table is partitioned daily, the training query must explicitly bound the partition range; otherwise, every training run scans the full history, which is both slow and expensive. A safer practice is to introduce run_id or snapshot_date as a business partition key, so each training run reads a fixed, immutable partition snapshot, completely eliminating bias from mid-training upstream writes.

Concurrency slots and resource contention: BigQuery's on-demand pricing model has dynamic concurrent query limits—by default, each project can run a certain number of queries simultaneously (usually starting at 100 slots, varying with scale). When a training pipeline issues large join queries during peak warehouse hours, it may enter a queue. If the warehouse also has ETL, BI queries, and training jobs, slot contention directly extends training time. Mitigations include: using reservations for training workloads, scheduling off-peak, or breaking heavy queries into smaller ones and leveraging BigQuery's materialized view caching for intermediate results.

When to Use It and When Not To

The "no-copy" approach is suitable when feature engineering is idempotent and the data warehouse is the primary source of truth. If the BigQuery environment is already managed with dbt dependencies, the Vertex AI pipeline should simply be an orchestrator—triggering model builds after dbt run completes. This keeps lineage clean and avoids reinventing the wheel.

Don't use it when you need extremely low-latency feature retrieval. If model serving requires sub-millisecond feature lookups, the overhead of querying the lakehouse—even with BigQuery caching—is still too high. Such scenarios need a dedicated feature store like Feast or Vertex AI Feature Store. The "no-copy" pattern is for training and batch inference, not real-time inference.

Also don't rush to use it when data scientists are still in the exploration phase. If they're frequently dropping columns, renaming fields, and mangling schemas, you don't want them touching production BigQuery tables directly. Let them copy freely in a sandbox environment, and only migrate to the "no-copy" pipeline once the feature logic is solidified into a production dbt model.

Feast is an open-source feature store whose core capability is unifying feature definitions for offline training and online inference, ensuring consistency between the two. On the offline side, it reads historical features from warehouses like BigQuery and Snowflake for training; on the online side, it syncs the latest feature values to low-latency stores like Redis and Bigtable for millisecond-level lookups during model serving. Vertex AI Feature Store is Google's managed version, deeply integrated with Vertex AI training and prediction services, supporting on-demand and online feature serving. Both solve the same problem: when a model needs real-time features, you can't query the lakehouse every time. So the "no-copy" pattern and feature stores are not mutually exclusive—a common architecture is connecting directly to BigQuery during training, using Feast or Vertex AI Feature Store for low-latency features during online inference, while keeping feature definitions and backfill logic from the same source.

Keep the Data, Keep the Budget

Data duplication is a double tax on engineering teams and cloud bills. By treating BigQuery or your Lakehouse as the API for your training pipeline rather than a storage cabinet, you can regain control of lineage. This forces discipline around schemas and partitioning, which is good for long-term maintenance.

Ship the pipeline, let the data stay where it is, and stop paying twice for the same bytes.

One-line Summary

Connect Vertex AI directly to BigQuery, replacing data clones with pointers to save storage costs—but be prepared for performance and snapshot challenges.

Paragraph Summary

The article argues for abandoning the traditional practice of exporting features from BigQuery or a Lakehouse to new tables in Vertex AI training pipelines, instead using BigQuery DataFrames, SQL, or aiplatform.TabularDataset to create "pointers" to original tables, keeping data in place. This eliminates petabyte-scale duplicate storage costs and avoids compliance risks from having two versions of the "truth" in production warehouses and training sets. But direct warehouse connection isn't a silver bullet: query performance becomes non-deterministic due to warehouse concurrency slots, mid-training upstream updates introduce snapshot bias, and Vertex AI SDK version volatility can cause compatibility errors. The article recommends strict partitioning, pinned SDK versions, and idempotent feature engineering, and clarifies that this pattern suits training and batch inference, while real-time inference still requires Feast or Vertex AI Feature Store.