Cleaning Data with Dataform, Dataflow and Fusion

Leveraging large language models (LLMs) for data preparation automation is the use of foundation models to accelerate pipeline development and maintenance. On Google Cloud, Gemini Enterprise Agent Platform (Agent Platform) provides the model and pipeline foundation, while BigQuery (opens in a new tab), Dataflow, and Dataform execute the SQL and Python transformations that LLMs help create. Prompts, generated code, and validation checks flow through managed pipelines where outputs are filtered, evaluated, and reviewed before deployment.

1.1Developing and Managing Prompts for Data Tasks

Prompt development for data tasks is the process of creating and refining inputs that direct an LLM to generate SQL, Python, documentation, or data quality rules. Agent Studio on Gemini Enterprise Agent Platform is the environment for developing and managing prompts for generative AI applications. Prompt work extends into operations through specialized pipeline steps for prompt engineering, response filtering, and human-in-the-loop evaluation. Generated outputs are evaluated against quality metrics and safety guidelines, and automated filtering plus human review forms the control boundary between generation and trusted pipeline use.

1.2Generating SQL and Python Transformations with Foundation Models

Foundation models accessed through Model Garden on Gemini Enterprise Agent Platform generate text, images, and code, which lets engineers draft, debug, and optimize transformation logic from natural-language intent. The generated code targets three execution environments for preprocessing and transformation:

Dataflow and Dataform automate transformations such as feature scaling, encoding categorical variables, and creating new features in batch, streaming, or real time. LLM assistance does not remove validation: Python-based checks for data types, formats, and ranges can run inside Agent Platform Pipelines or BigQuery. Coding-assistant agents such as CodeMender can improve code security only with a human in the loop to validate proposed changes.

1.3Embedding LLM Steps in Automated Data Pipelines

Agent Platform Pipelines is the managed orchestration system that automates every step of the machine learning (ML) lifecycle, from data preparation to model training, evaluation, and deployment. An Agent Platform Pipelines workflow depends on reusable pipeline templates, scheduled pipeline runs, and execution metrics to keep recurring preparation work consistent across projects. For generative AI work, the same pipeline automates data augmentation, prompt-related steps, and evaluation of generated outputs, so prompt diversity improvements and robustness checks become repeatable tasks.

BigQuery ML pipelines (opens in a new tab) add two SQL-native options that can host LLM calls. GoogleSQL procedural language supports multi-statement queries with shared state and constructs such as IF and WHILE, which can be saved and scheduled to automate training, inference, and monitoring. Dataform supports more complex SQL workflows with development, testing, version control, and scheduling for extraction, loading, and transformation (ELT) in BigQuery. When either path uses the ML.GENERATE_TEXT function, quota-related retryable errors are handled by calling the function iteratively, including adapting the structured_table_ml.js example library in Dataform.

1.4Improving Data Quality and Robustness with LLM-Driven Techniques

Data quality for generative AI covers accuracy, relevance, diversity, and alignment with required output characteristics. TensorFlow Data Validation (TFDV) validates schemas and detects anomalies, and automated data quality scans in BigQuery apply checks where applicable. To screen LLM interactions themselves, Model Armor or fine-tuned LLMs can automatically screen prompts and responses for common risks. This screening prevents manipulation or system exploits from flowing from ingestion into preprocessing and application code.

When real-world data is insufficient or imbalanced, synthetic data improves robustness and generalization:

1. Generate: Create synthetic datasets based on existing patterns with BigQuery DataFrames or the synthetic data generation capabilities in Agent Platform.
2. Augment: Add augmented examples to training data to improve prompt diversity and model robustness.
3. Label and validate: Ensure labels for fine-tuning or reinforcement learning from human feedback (RLHF) accurately reflect quality, relevance, and safety before retraining.

This sequence makes synthetic data a dependency of reliable fine-tuning: generation expands coverage, augmentation balances distribution, and accurate labeling preserves safety and relevance in the resulting model.

Selecting and implementing data transformation services means matching the shape of cleaning work to the Google Cloud service that executes it. Dataflow (opens in a new tab) handles unified batch and streaming processing with code, Dataform handles SQL-based transformations inside BigQuery, and Cloud Data Fusion handles managed data integration with reusable plugins. The choice depends on data volume, structure, and latency requirements, and on whether transformation happens before loading or after loading into BigQuery.

2.1Dataform for SQL-Based Transformations

Dataform is the service to develop and operationalize scalable data transformation pipelines in BigQuery using SQL. BigQuery data preparations are BigQuery resources powered by Dataform, so SQL logic defined in Dataform runs directly where the warehouse data lives. This design keeps large tabular datasets in place and avoids moving data to an external engine for cleaning, which preserves performance and governance boundaries between storage and compute.

Dataform automates transformations such as feature scaling, encoding categorical variables, and creating new features in batch, streaming, or real time. Data flows from source tables into Dataform SQL definitions and then into destination tables, and the Dataform service account mediates that flow. The service account depends on BigQuery Data Viewer on the source tables and BigQuery Data Editor on the destination tables, while human users depend on BigQuery Studio User and Dataform permissions for sharing. Visibility for code assets is governed by project-level Dataform permissions, so access to transformation logic and access to data are controlled separately.

2.2Dataflow for Stream and Batch Processing

Dataflow is the serverless service that unifies stream and batch data processing for high-volume and low-latency workloads. A Dataflow pipeline reads from sources such as databases, Pub/Sub, Cloud Storage, or external APIs, applies Apache Beam transforms for cleaning and enrichment, and writes the result to sinks such as BigQuery. For event-driven architecture, Dataflow combines with Eventarc to process data for machine learning, and pipelines can use GPU and right-fitting capabilities to improve efficiency for demanding jobs.

Implementation in Dataflow is code-based and template-driven. A common pattern is to clone a provided template such as Java Database Connectivity (JDBC) to BigQuery and modify the template code to add Beam transforms before writing to BigQuery. A passing build from continuous integration produces deployment artifacts that flow from the build system to preproduction and production environments:

1. Package: Package pipeline code into a self-executing JAR file, plus a JSON template file and optional metadata template when using Classic Templates.
2. Stage: Store the JAR and template files in a Cloud Storage bucket in the deployment project.
3. Launch: Launch batch pipelines directly as a new Dataflow job, on a schedule with Cloud Scheduler, or from an orchestrator such as Managed Service for Apache Airflow; update or coordinate streaming pipelines to replace the running job without business disruption.

This sequence makes Dataflow the fit when structure is varied, volume is large, and latency ranges from batch to streaming. Choose Dataflow when cleaning requires custom logic, windowing, joining of unbounded streams, or integration of machine learning components that SQL alone cannot express.

2.3Cloud Data Fusion for Managed Data Integration

Cloud Data Fusion is the fully managed, cloud-first service for data integration at scale. A Cloud Data Fusion pipeline ingests data from source systems, applies transformations through plugins, and loads the cleaned output to destinations such as BigQuery. Transformation logic comes from any of the Cloud Data Access Platform (CDAP) plugins, so engineers compose existing connectors and transforms rather than writing a full processing program.

Cloud Data Fusion depends on its plugin ecosystem and managed runtime to reduce operational work. The service manages the underlying compute while the pipeline definition controls the order of operations from extraction to transformation to loading. Choose Cloud Data Fusion when the requirement is broad connectivity across many source types with repeatable extract, transform, and load (ETL) patterns, especially when teams want to reuse standardized plugins instead of maintaining custom Beam or Spark code.

2.4Matching Service Capabilities to Requirements

Matching service capabilities to requirements means choosing the interface, latency model, and execution location that fit the cleaning task. The three services split by where compute executes and how logic is expressed:

Two related patterns extend these choices. Extract, load, and transform (ELT) loads data with any extract and load option into a BigQuery staging table and then transforms the staging table with SQL queries that write output into the final production table. Managed Service for Apache Spark runs large-scale joins and behavioral aggregations with Lightning Engine, transforming data with Spark SQL or custom code in Scala, Java, Python, or R before consolidating results into BigQuery.

Designing data quality and cleaning pipelines is the practice of building automated pipelines that enforce validation rules, fix missing or erroneous values, standardize formats, and deduplicate records. On Google Cloud, Dataflow (opens in a new tab) and Dataform automate transformations such as feature scaling, encoding categorical variables, and creating new features in batch, streaming, or real time, while Knowledge Catalog and BigQuery provide validation, profiling, and monitoring. A reliable design connects ingestion, transformation, validation, and governance so errors are caught early and trusted data is reusable downstream.

3.1Transformation Engines for Cleaning and Feature Preparation

Transformation engines for cleaning are the services that apply scaling, encoding, standardization, and deduplication logic as data flows from sources to destinations. Data flows from databases, data lakes, streaming platforms, and external APIs through an ingestion tool into a transformation service, which then writes curated tables and features for BigQuery and Agent Platform Feature Store (Feature Store). The workflow depends on the chosen engine, so select the engine by processing model and team skill:

• Dataflow processes batch and streaming data from various sources with Apache Beam and integrates ML components for event-driven processing, including combination with Eventarc and GPU support.
• Dataform automates SQL-managed transformations such as feature scaling, encoding categorical variables, and creating new features for tabular data in BigQuery.
• Cloud Data Fusion transforms data with CDAP plugins in a visual pipeline before writing to BigQuery.
• Managed Service for Apache Spark runs Apache Spark transformations for large-scale joins, windowing, and behavioral aggregations.
• BigQuery handles SQL-based transformation and classical tabular ingestion with frequent access, plus object tables and signed URLs for unstructured data in Cloud Storage.

Centralizing outputs in Feature Store lets multiple models and projects reuse the same cleaned features, which reduces redundant engineering and keeps definitions consistent. Knowledge Catalog then standardizes those definitions across the organization so downstream pipelines consume the same business meaning.

3.2Validation Rules, Profiles, and Anomaly Detection

Validation rules in cleaning pipelines are checks that verify data types, formats, ranges, distributions, and schemas before training or serving. Data validation is implemented inside ingestion and preprocessing scripts with Python for Agent Platform Pipelines or BigQuery, so each batch or micro-batch is checked for type, format, and range conformance as it arrives. For generative AI, validation extends to accuracy, relevance, diversity, and alignment with required output characteristics, and labels for fine-tuning or reinforcement learning from human feedback must accurately reflect quality, relevance, and safety.

The three validation controls differ by where they run and what they catch:

Use BigQuery DataFrames to generate synthetic data when real data is insufficient or imbalanced, which improves robustness and prompt diversity for generative models. Use data augmentation to balance distributions and add adversarial examples, and train multiple models on different subsets with hyperparameter tuning through ensemble methods to reduce the impact of any poisoned points that pass initial checks.

3.3Standardization, Lineage, and Continuous Monitoring for Consistency

Standardization for consistency is the governance layer that keeps data definitions, business vocabulary, and quality signals uniform across pipelines and teams. Knowledge Catalog is the fully managed metadata management service that automatically ingests technical metadata from supported Google Cloud sources, while custom entries cover sources outside Google Cloud. Data teams enrich that technical metadata with business metadata through aspects and aspect types and define a business glossary, so discovery, search, and reuse rely on one standardized vocabulary with appropriate permissions for data scientists and ML practitioners.

Knowledge Catalog centralizes discovery, management, monitoring, and governance of data and AI artifacts across data platforms for trusted users. Its governance scope includes data lineage with column-level lineage in BigQuery, data quality checks and data profiles, discovery and processing across data marts, feature metadata and model artifacts, unified governance across BigLake and open formats such as Iceberg and Delta, and inspection results sent from BigQuery by Sensitive Data Protection. To monitor fidelity over time, capture real-time pipeline logs and metrics with Cloud Logging and Cloud Monitoring, collect API audit trails with Cloud Audit Logs, and schedule monitoring of features and feature groups in Feature Store. Never log personally identifiable information (PII) or confidential data in experiments or log servers.

3.4Protecting Fidelity Against Poisoning, Bias, and Mishandling

Protecting fidelity means guarding training data against poisoned, biased, or leaked values that would corrupt model behavior. Data poisoning is a cyberattack in which attackers inject malicious data into training datasets to manipulate behavior or degrade performance, so pipelines need comprehensive monitoring and alerting for inconsistent behavior, unexpected transformation, or unauthorized access. Sensitive Data Protection with Model Armor helps discover, classify, and protect sensitive data such as intellectual property and prevents unauthorized exposure in large language model (LLM) interactions, while de-identification methods such as pseudonymization, generalization, and bucketing preserve utility while minimizing PII.

Apply data minimization by not collecting, keeping, or using data beyond business goals, and prefer synthetic or fully anonymized data where possible. Control Cloud Storage lifecycles to limit prolonged access, enrich raw data with Agent Platform data labeling jobs that add meaningful tags for structured training data, and incorporate human review of evaluation metrics and domain-specific patterns to catch biases or suspicious points that automated checks miss. This combination of automated validation, anomaly detection, and expert review keeps downstream analytics and ML consistent, unbiased, and reliable.

Key Facts

• Dataform runs SQL-based transformations inside BigQuery, Dataflow runs Apache Beam code for unified batch and streaming, and Cloud Data Fusion runs managed integration pipelines with CDAP plugins.
• The Dataform service account requires BigQuery Data Viewer on source tables and BigQuery Data Editor on destination tables.
• Call ML.GENERATE_TEXT iteratively in GoogleSQL or Dataform when retryable quota errors occur.
• TensorFlow Data Validation (TFDV) validates schemas during preprocessing, while BigQuery anomaly detection finds statistical outliers in stored training data.
• Knowledge Catalog centralizes technical and business metadata, column-level lineage in BigQuery, data quality checks, and governance across BigLake and open formats such as Iceberg and Delta.