Preprocessing ML Data with BigQuery and Dataflow

Organizing different data types for machine learning means placing tabular data in BigQuery (opens in a new tab) and unstructured data in Cloud Storage so distributed trainers and serving systems can load each type at high throughput. Source data flows from online service logs, on-device images, or web-scraped documents into a dedicated training store that is separate from production systems. The training store uses columnar tables for tabular data and large binary containers for unstructured data, so repeated experimentation reads do not contend with production writes and latency requirements. Serialization follows the training framework, with sharded TFRecord files for TensorFlow and Avro files for other frameworks.

1.1Store Tabular Data in BigQuery as Materialized Tables

BigQuery storage for training holds structured and semi-structured data as materialized tables instead of views or subqueries. Materialization avoids recomputing query logic on every read, and partitioning large tables by date, timestamp, or ingestion time improves performance and cost by scanning only relevant partitions. Data flows from BigQuery tables to training workers through the BigQuery Storage API, which provides high-throughput parallel reads. Training pipelines can reference the data as a managed tabular dataset in Vertex AI to track artifacts and lineage.

The read path from BigQuery to training depends on the framework:

1.2Store Unstructured Data in Cloud Storage as Sharded Containers

Cloud Storage for machine learning holds image, video, audio, and other unstructured data packaged for high sequential throughput. Dataflow can convert raw unstructured objects into binary container formats such as TFRecord, which improves ingestion because trainers read fewer, larger sequential files. Throughput improves when many small images, videos, or audio clips are combined into large files, with buckets and directories grouping shards for management and parallel discovery. BigLake can present lake data for governed analytics, while BigQuery object tables can reference Cloud Storage objects without copying bytes and signed URLs can grant time-limited access.

To keep distributed reads balanced, unstructured datasets follow three sharding rules: store data in sharded TFRecord for TensorFlow and Avro for other frameworks, size each shard to at least 100 MB, and keep total shard count between 100 and 10,000 shards per dataset. When shards drop below 100 MB or exceed 10,000 shards, workers spend more time opening objects than reading bytes and throughput falls.

1.3Serve Shared Features from the Online Feature Layer

Vertex AI Feature Store is the central location to create, maintain, share, and serve machine learning features for structured data. Feature data itself lives in a BigQuery table or view, which lets BigQuery store all feature data including historical values for offline training and point-in-time correctness. To use Vertex AI Feature Store, first create an online store instance and then define feature views. The service then depends on BigQuery for offline history and on the online store for low-latency serving, so an online prediction request flows from the endpoint to the online store instead of scanning BigQuery.

Because features are defined once and reused across projects, teams avoid redundant data engineering and reduce infrastructure cost. After preprocessing, a managed Vertex AI dataset links data to custom-trained models and provides descriptive statistics with automatic or manual splitting into train, test, and validation sets. Training scale follows data size and schedule, where a Vertex AI Workbench instance fits small datasets or subsets and the managed training service fits larger datasets, distributed training, and production retraining on arrival of new data.

1.4Explore Data and Preserve Reproducibility Across Pipelines

Scalable exploration and feature engineering pipelines automate profiling, transformation, labeling, and versioning for tabular, text, and image data. Raw data flows from logs, devices, or web scrapes into BigQuery for structured data and into Cloud Storage for unstructured data, then through Dataflow, Dataproc, or BigQuery transforms into training-ready datasets and features. BigQuery handles SQL-expressible transforms, Dataflow handles large-volume batch and streaming conversion with Apache Beam, and Managed Service for Apache Spark handles serverless Spark processing when an organization already has Spark code. Vertex AI data labeling jobs create and import training data for text and images, while Gemini capabilities and marketplace solutions support classification, entity extraction, and assisted review.

Reproducibility links every model to the exact data, features, code, and parameters that produced it. Managed Vertex AI Datasets link pre-processed data to custom-trained models, Vertex ML Metadata logs data sources, transformations, parameters, metrics, and artifacts, and Vertex AI Pipelines lineage tracks each pipeline artifact for debugging. Model Registry versions each stored model, and Vertex AI TensorBoard and Experiments visualize runs by hyperparameters, loss, accuracy, model graph, and embeddings.

Choosing the right preprocessing tool on Google Cloud means matching dataset scale, latency, and transformation complexity to the engine that controls cost and operations. Single-node Python fits in-memory prototyping, BigQuery (opens in a new tab) SQL fits in-warehouse transformation where data already lives, Cloud Dataflow fits distributed batch and streaming with custom logic, and Managed Service for Apache Spark fits existing Spark and Hadoop code. Storage and compute stay separated, with Cloud Storage, BigQuery storage, Cloud SQL, Spanner, or Bigtable holding bytes and the chosen engine loading and processing them.

2.1Run Unified Batch and Streaming Preprocessing with Dataflow and Apache Beam

Cloud Dataflow is the fully managed service for running Apache Beam pipelines that perform distributed batch and streaming preprocessing. Data flows from sources such as Pub/Sub, Apache Kafka, Cloud Storage, or databases through Beam transforms that run in parallel across Dataflow workers to sinks such as BigQuery. A Beam pipeline is built from a pipeline definition, PCollections, transforms, and a runner, where PCollections carry distributed data and transforms apply parallel processing that the runner executes on Dataflow workers. This unification lets one feature pipeline process historical backfill and live events with identical logic, so training features stay consistent with serving features.

Teams can start from predefined Dataflow templates or build custom logic. Built-in templates include exporting Kafka data and the Java Database Connectivity (JDBC) to BigQuery template, deployable through a code-free job wizard. Choose a custom Apache Beam pipeline when templates do not meet requirements, such as integrating an unsupported source or sink or performing cleaning, enrichment, joining, and aggregation. For reuse, create libraries of reusable transforms shared between batch and streaming paths, and check the Beam Capability Matrix before running a Dataflow pipeline on another runner such as the Apache Spark Runner for backup.

Dataflow differs from Kafka Connect in transformation power. Kafka Connect, including MirrorMaker 2.0, transfers data between Kafka clusters with record-at-a-time, per-message modifications for migration, backup, or disaster recovery. Choose Dataflow when migration requires joining multiple streams with windowing, alignment, cleaning, enrichment, or aggregation before data reaches the target Managed Service for Apache Kafka cluster. Dataflow also supports change data capture inputs with DebeziumIO for Cloud SQL, MySQL, Postgres, and SQL Server, plus Spanner Change Streams and Bigtable Change Streams.

2.2Ensure Consistency with Windowing, TensorFlow Transform, and Validation

Custom windowing and stream correlation is the grouping and alignment logic that joins multiple streaming inputs into features. Windowing groups unbounded streaming elements by event time into bounded collections, and alignment logic correlates elements from different streams that arrive at different times. Pub/Sub provides scalable event ingestion to and from streaming Dataflow pipelines, while Dataflow combined with Eventarc supports event-driven triggering. Because windows and triggers define when results are emitted, the same Beam pipeline can produce early speculative results and later refined results as late data arrives.

Consistent preprocessing means training and serving apply identical transformations. Dataflow supports this pattern by running the same Beam transforms in offline batch jobs that generate training data and in streaming jobs that generate serving features. TensorFlow Transform (tf.Transform) extends this pattern by embedding the full-pass analysis and per-example transformation graph into both the training pipeline and the serving graph, so training-serving skew does not occur when vocabularies, means, or scaling factors change. Validation uses TensorFlow Data Validation (TFDV) to check schemas and detect anomalies, plus automated data quality scans in BigQuery for staged and production tables.

2.3Prototype In Memory with Python Versus Transform in BigQuery SQL

In-memory Python prototyping uses single-node libraries such as pandas that load an entire dataset into memory on one machine. Data flows from BigQuery storage or Cloud Storage to a Vertex AI Workbench notebook, where one-off Python scripts apply cleaning, scaling, encoding, and new-feature logic. This pattern fits smaller datasets that fit into memory and supports fast iteration on samples, but large tables increase memory pressure, transfer time, and outbound transfer cost, especially when computation runs in a different zone than storage.

Scalable in-warehouse transformation with BigQuery SQL keeps structured and semi-structured data in BigQuery tables and transforms it where it is stored. BigQuery is a serverless SQL data warehouse that combines managed storage with on-demand computation, so no computation layer has to be deployed. When the model uses BigQuery ML or AutoML, built-in preprocessing handles transformation, and for custom models BigQuery transformations are often the most cost-effective path because data does not move to an external cluster. Partitioning divides a table by TIMESTAMP, DATE, or ingestion time so queries scan only a subset, while clustering sorts by frequently filtered columns to skip unnecessary scans.

2.4Run Existing Spark Code on Managed Service for Apache Spark

Managed Service for Apache Spark is a managed Apache Hadoop environment for distributed preprocessing that runs Spark, Flink, and Hive workloads. Managed Service for Apache Spark is the direct landing zone for existing PySpark and Hadoop code that must run with minimal rewrite and specialized libraries. Migration works best incrementally, where each job is examined in isolation to evaluate needs, test dependencies against supported versions, and build a proof of concept without affecting production. Inputs, outputs, and environment provisioning must not be hardcoded; jobs should receive Cloud Storage paths, table names, and connection strings as arguments so the same definition recreates clusters on demand in a new region.

Managed Service for Apache Spark favors many short-lived ephemeral clusters over one monolithic long-lived cluster. An ephemeral cluster is built to run one job or group of jobs and then destroyed, which also destroys its Hadoop Distributed File System (HDFS) layer, so storage must live outside the cluster in Cloud Storage or BigQuery. Ephemeral clusters allow per-job configuration and scaling, payment only while jobs run, and fresh configuration on every use. Long-living clusters fit continuous job arrivals, but clusters that keep data inside complicate migration and should be refactored to external storage.

Movement of processed data from Spark to BigQuery uses the Spark-BigQuery connector, which uses abstracted Hadoop InputFormat and OutputFormat classes and is pre-installed on image version 2.1 or later. The connector uses an indirect write method in fixed order: first the Spark job writes the final DataFrame to a temporary Cloud Storage location, then the connector triggers a BigQuery Load Job, then BigQuery ingests the data from Cloud Storage into the target table. The job is submitted with gcloud dataproc jobs submit pyspark, where workers fetch the script from Cloud Storage and execute it on the cluster.

Creating and consolidating features in Gemini Enterprise Agent Platform Feature Store (opens in a new tab) means keeping one consistent definition of each feature while using different storage for training and inference. BigQuery and Cloud Storage hold materialized feature data for offline training, and Vertex AI Feature Store serves the same features online for low-latency inference. BigQuery remains the source of truth for all structured feature data, including historical rows, while the online store holds only the latest values for fast lookup.

3.1Materialize Offline Features and Serve Online Features

BigQuery materialization for features stores structured and semi-structured feature data as materialized tables instead of views or subqueries. Feature pipelines extract data from operational sources into storage separate from the operational source, then materialize the result so training jobs do not recompute a view on every read. Partitioning large datasets further improves query performance and cost efficiency. Data flows from BigQuery tables into training through the BigQuery Storage API, with framework-specific connectors including the tf.data.dataset reader for TensorFlow with Keras, BigQuery client for TFX, Google BigQuery I/O Connector for Dataflow with Apache Beam, and BigQuery import paths for PyTorch, XGBoost, or scikit-learn.

Cloud Storage materialization for features holds image, video, audio, and other unstructured feature data in large container formats. Unstructured objects use sharded TFRecord files for TensorFlow workflows and Avro files for other frameworks, and Dataflow with Apache Beam can convert raw objects into TFRecord to improve ingestion. Throughput depends on combining many small objects into large files with a target of at least 100 MB per file and 100 to 10,000 shards per dataset, with buckets and directories grouping shards for management and parallel discovery.

Serving from Vertex AI Feature Store requires first creating a FeatureOnlineStore instance for optimized online serving and then defining feature views that map BigQuery tables or views to servable features. At serving time, traffic flows from the inference application to the feature view in the online store instance, which returns the latest materialized values without running a BigQuery query in the request path. For FeatureOnlineStore instances with optimized online serving, the online store also supports vector similarity search for approximate nearest neighbors, which lets RAG Engine use Vertex AI Feature Store as a vector database for retrieval-augmented generation.

3.2Track Lineage and Version Code, Data, Features, and Models

Feature lineage and versioning keeps one traceable record for every feature from source data through transformation to training and serving versions. Gemini Enterprise Agent Platform Feature Store is the central repository for creating, maintaining, sharing, and serving features, with BigQuery storing underlying historical data and Vertex ML Metadata linking datasets, parameters, metrics, and model artifacts. Reuse flows from the same Feature Store to both training and serving, so multiple teams consume the same versioned features instead of rebuilding divergent copies.

Source provenance and transformation lineage tracks where feature data originated, how it changed, and where the result is stored. Data flows from source systems into ML-optimized storage separate from operational sources, then through Dataflow for Beam, Managed Service for Apache Spark for Spark, or BigQuery for SQL processing, with validation from TensorFlow Data Validation and automated BigQuery quality scans. The lineage record centers on source data provenance, transformation logic, and version history plus pointers to data locations held in version control or a data catalog.

The versioning strategy assigns each artifact type to its managed service:

Metadata and pointers flow into GitHub, GitLab, or a data catalog, while managed tabular datasets and Experiments integrate with Vertex ML Metadata to link a specific model version back to its feature version and dataset. Pipelines automate preparation through deployment with scheduled runs and reusable templates, Experiments and TensorBoard compare runs by hyperparameters, loss, accuracy, graphs, and embeddings, and Model Monitoring detects training-serving skew and inference drift to decide whether to retrain.

3.3Build Scalable Feature Pipelines in Gemini Enterprise with Apps and Agents

Scalable feature pipelines in Gemini Enterprise transform raw enterprise data into curated, reusable features that serve search, answers, and agentic workflows. Raw data flows from connected systems into dedicated stores, consolidated stores serve applications through a shared serving layer, and assistants, actions, and agents transform and serve the data under governed observability. Storage, serving, and transformation stay separated so each layer can scale without rewriting the others.

Ingestion separates connection from storage. Data sources include Google and third-party systems such as Confluence, Jira, Microsoft SharePoint, and ServiceNow, while data stores hold the ingested entities from those systems. Data moves in order: first the pipeline connects a source, then ingestion creates a collection of data stores with a separate store per entity type, then each store changes from Creating to Active. Only after reaching Active can the store serve an app, so an app must connect to at least one Active store to serve results.

A Gemini Enterprise app is the consolidation point where curated features from multiple stores become one serving interface. Apps have a many-to-many relationship with data stores, so one app can serve many stores and one store can serve many apps. When multiple stores connect to a single app, the pattern is blended search, where Gemini Enterprise sends the query to the connected stores, blends results, and displays a comprehensive result. Transformation and serving are performed by the assistant, actions, and agents: the assistant generates grounded answers and summaries, actions create or update records such as Calendar events or Jira issues, and agents automate multi-step processes with prebuilt agents from the Agent Gallery or custom agents from Workflow Builder. Governance combines Looker-powered Analytics, real-time Observability with traces and audit logs, and control through Agent Registry, Agent Gateway, IAM, Model Armor, and Security Command Center integration.

Ensuring data privacy for machine learning means combining data transforms, encryption, perimeters, access controls, and governance so teams can preprocess, train, and share models without exposing personally identifiable information (PII). Sensitive Data Protection (opens in a new tab) discovers and transforms PII, BigQuery enforces column and row controls, VPC Service Controls blocks exfiltration, Identity and Access Management (IAM) limits who can see what, and catalogs plus logs prove what happened. Together these controls reduce membership inference, model inversion, reconstruction of training inputs, and poisoning of training data.

4.1Enforce Least-Privilege Access with IAM, Column and Row Controls

Least-privilege role-based access means every user, group, and service account receives only the minimum permissions needed for its role, such as no-access, read-only, or write. Least privilege protects data lakes, feature stores, and training hyperparameters from theft, tampering, and accidental change. For ML pipelines this means dedicated service accounts per stage, where a training service account can read from only one Cloud Storage bucket and write artifacts only to Model Registry. Vertex AI access control combines predefined and custom IAM roles for data scientists, annotators, and MLOps operators, and table-level IAM grants access to specific tables without granting access to the entire dataset.

BigQuery column-level security restricts access to specific columns through policy tags managed in Data Catalog. A column tagged with a policy tag is invisible unless the caller holds both BigQuery data access and the Fine-Grained Reader role on that tag. The production taxonomy is hierarchical: 3_Confidential for very sensitive data such as credit card numbers grants 2_Private and 1_Sensitive, 2_Private for sensitive PII such as first name grants 1_Sensitive but not 3_Confidential, and 1_Sensitive for non-public data such as credit limit grants nothing higher. BigQuery row-level security filters rows by a policy condition evaluated at query time, such as restricting a salesperson to assigned accounts, while authorized views let users query a view without direct access to base tables.

Column protections differ in what they hide and what the reader needs:

IAM Conditions grant conditional access such as allowing a pipeline trigger only from a trusted Cloud Build trigger, principal access boundary policies cap maximum permissions, Privileged Access Manager grants just-in-time temporary elevation with audit logging, and Identity-Aware Proxy plus Access Context Manager add IP, device, and per-authentication context. VPC Service Controls creates a service perimeter around BigQuery datasets, storage buckets, and training services where data flows only inside by default and cross-boundary movement requires explicit ingress and egress rules. Firewall policies deny all inbound and outbound traffic by default and allow only matching traffic, while Dedicated Interconnect, Cloud VPN, private endpoints, and private IPs protect movement to and from the perimeter.

4.2Discover and De-identify PII with Sensitive Data Protection

Cloud Sensitive Data Protection is the Google Cloud service for automatically discovering, classifying, and transforming PII before training. Discovery uses data profiler to scan all BigQuery tables and columns across organization, folders, and projects and output predicted infoTypes, data risk, sensitivity levels, and metadata, with more than 200 built-in infoTypes. Profiles are stored in BigQuery and can be sent to Knowledge Catalog as aspects for lineage and governance. Discovery also extends to Vertex AI datasets, while pipeline logs must exclude PII to avoid a second exposure path.

De-identification replaces raw identifiers with privacy-safe values that remain useful for joins and analytics. Data minimization applies first: do not collect, keep, or use data beyond business goals, and prefer synthetic or fully anonymized data where possible. For structured data, record transformations check defined columns such as credit card numbers, while for unstructured data infoType detectors find free-text PII. Transformation options include cryptographic tokenization with a wrapped key for authorized re-identification, pseudonymization with artificial identifiers, generalization with bucketing into broader ranges, masking to hide values, and encryption to preserve join or decrypt capability.

Ingestion-time tokenization de-identifies data before it lands in the warehouse in fixed order: first data arrives from Cloud Storage or Pub/Sub into an ingestion project, then a Dataflow pipeline sends values to Sensitive Data Protection for tokenization, then transformed data flows to BigQuery with confidential data in a confidential-data project and de-identified data in a non-confidential project. Query-time de-identification instead sanitizes results during the query through BigQuery remote functions such as fns.dlp_freetext_encrypt hosted on Cloud Run, where BigQuery invokes Cloud Run, Cloud Run calls the Data Loss Prevention API, and BigQuery returns transformed values. To control cost and quota, pass only final filtered results, keep dataset, Cloud Run, and templates in the same region, batch requests, and limit items to 10,000 or fewer per call within the 50,000 table-value and 0.5 MB default request limits.

4.3Protect Data with Encryption, Perimeters, and Attack Mitigation

Customer-Managed Encryption Keys (CMEK) in Cloud Key Management Service give the data owner control over key encryption keys that protect data at rest. BigQuery encrypts all data at rest and in transit by default and this default cannot be disabled, but CMEK replaces Google-managed keys for ingestion, Dataflow workers, Pub/Sub topics, and BigQuery warehouses. CMEK-integrated services include Agent Platform, Cloud Storage, Cloud SQL, Spanner, and BigQuery. The CMEK location must match the protected resource location or requests fail, rotation occurs every 30 days by default, and separate keys isolate confidential and non-confidential warehouses. Cloud External Key Manager holds keys outside Google Cloud when required, but the customer then owns rotation and availability.

Column-level encryption encrypts selected columns with Authenticated Encryption with Associated Data (AEAD) functions using keysets that support rotation, enforcing dual-access control where the reader needs both table and key permission. Client-side encryption with the Tink library extends protection before upload: a locally generated data encryption key (DEK) is wrapped by a key encryption key (KEK) held in Cloud Hardware Security Module, both wrapped DEK and encrypted data land in separate BigQuery tables, and AEAD decryption first unwraps the DEK with the KEK then decrypts the column. Confidential Computing and Shielded VMs protect data in use during training, HTTPS protects API calls, and BigQuery differential privacy adds noise to aggregate query results so individual rows cannot be identified.

Governance and attack mitigation close the loop from discovery to monitoring. Knowledge Catalog provides discovery, column-level lineage, quality checks, and data mesh ownership, Vertex ML Metadata logs sources and transformations, Model Registry links model versions to dataset and code versions, and audit logs exported to BigQuery or a SIEM record access. Model Monitoring and Feature Store monitoring detect drift, skew, and anomalies, while poisoning mitigation uses quality checks, anomaly detection, Sensitive Data Protection with Model Armor, robust ensemble training on different subsets, data augmentation, and human review through model evaluation.

Key Facts

• BigQuery stores structured training data as materialized and partitioned tables read through the BigQuery Storage API, while Cloud Storage stores unstructured data in large sharded containers.
• TensorFlow uses sharded TFRecord files and all other frameworks use Avro files, with unstructured shards sized to at least 100 MB and 100 to 10,000 shards per dataset.
• Vertex AI Feature Store keeps feature history in BigQuery and serves current values from a FeatureOnlineStore instance through feature views for low-latency online prediction.
• BigQuery column-level security uses a hierarchy where 3_Confidential grants 2_Private and 1_Sensitive, while column-level encryption requires permission to both the table and the encryption key.
• CMEK keys must reside in the same location as the protected resource and rotate every 30 days by default, and VPC Service Controls permits cross-perimeter movement only through explicit ingress and egress rules.