Study guide
2.3 Data Validation, Cleansing, and Quality Assurance with Dataform
Google Cloud Professional Data Engineer Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
0.1 About the Google Cloud Professional Data Engineer Exam 0.2 Study Strategy & Cloud Data Engineering Methodology
2 Chapter 1: Designing for Security and Compliance
1.1 IAM Roles, Service Accounts, and Least Privilege for Data 1.2 Data Encryption, Key Management, and Privacy with Cloud DLP 1.3 Compliance, Data Sovereignty, and Geographic Placement 1.4 Multi-Environment Architecture: Development, Staging, and Production
3 Chapter 2: Designing for Reliability and Resilience
2.1 High Availability, Disaster Recovery, and Failover Patterns 2.2 Fault Tolerance and ACID Guarantees in Distributed Systems 2.3 Data Validation, Cleansing, and Quality Assurance with Dataform 2.4 AI-Assisted Data Preparation and LLM Query Generation
4 Chapter 3: Architecture Patterns and Flexibility
3.1 Modern Data Architectures: Data Lake, Warehouse, Lakehouse, and Mesh 3.2 Data Migration Strategies and Hybrid/Multi-Cloud Integration 3.3 Database Migration Service and Heterogeneous Database Migrations
5 Chapter 4: Ingestion Architectures: Pub/Sub and Streaming Sources
4.1 Cloud Pub/Sub Core Architecture and Ingestion 4.2 Pub/Sub Advanced Patterns: Dead-Lettering, Ordering, and Schemas 4.3 Change Data Capture (CDC) with Datastream and Batch Ingestion
6 Chapter 5: Stream and Batch Processing with Apache Beam and Cloud Dataflow
5.1 Apache Beam Core Concepts and Pipeline Design 5.2 Advanced Windowing, Watermarks, and Triggers 5.3 Dataflow Pipeline Optimization, Scaling, and Templates
7 Chapter 6: Data Transformation with Dataproc, Data Fusion, and Workflow Orchestration
6.1 Apache Spark and Hadoop with Cloud Dataproc 6.2 Visual Data Pipelines with Cloud Data Fusion 6.3 Workflow Orchestration with Cloud Composer 6.4 AI Data Enrichment inside Data Pipelines
8 Chapter 7: Storage Service Selection and Cloud Storage Architecture
7.1 Google Cloud Storage Service Selection Decision Matrix 7.2 Cloud Storage Architecture, Classes, and Lifecycle Management
9 Chapter 8: Deep Dive into Google BigQuery Storage
8.1 BigQuery Storage Architecture and Data Organization 8.2 Partitioning and Clustering for Performance and Cost 8.3 BigLake and Hybrid Querying across Clouds 8.4 Data Warehouse Modeling: Star Schemas, Denormalization, and Slowly Changing Dimensions
10 Chapter 9: Operational and NoSQL Databases: Bigtable and Cloud Spanner
9.1 Cloud Bigtable Architecture and Row Key Design 9.2 Cloud Bigtable Schema Management and Performance Tuning 9.3 Cloud Spanner: Globally Scalable Relational Database
11 Chapter 10: BigQuery Query Performance and Optimization
10.1 BigQuery SQL Tuning and Performance Best Practices 10.2 BigQuery BI Engine and Materialized Views 10.3 Data Sharing and Analytics Hub 10.4 Preparing Data for Visualization and BI Tool Connectivity
12 Chapter 11: Machine Learning Readiness and Data Governance
11.1 Machine Learning with BigQuery ML 11.2 Feature Engineering and Data Preparation for AI/ML 11.3 Data Governance, Cataloging, and Discovery with Dataplex 11.4 Unstructured Data, Embeddings, and Retrieval-Augmented Generation
13 Chapter 12: Site Reliability Engineering and Monitoring for Data Systems
12.1 SRE Principles for Data: SLIs, SLOs, and SLAs 12.2 Observability: Cloud Monitoring, Cloud Logging, and Alerting 12.3 Troubleshooting Data Pipelines and Workload Failures
14 Chapter 13: Workload Automation, CI/CD, and Cost Optimization
13.1 CI/CD and Version Control for Data Pipelines 13.2 BigQuery Cost Management and Capacity Planning 13.3 Holistic Cost Optimization across GCP Data Services 13.4 Query Workload Management: Interactive vs. Batch Jobs, Queues, and Quotas
2.3 Data Validation, Cleansing, and Quality Assurance with Dataform
Key Takeaways Explain This Topic AI
Modern ELT paradigms adopt shift-left data quality testing, validating and cleansing datasets directly inside BigQuery using declarative SQL assertions before downstream consumers access the data.
Dataform uses SQLX files to manage transformations, supporting table materialization types including views, full table rebuilds, and incremental tables with conditional merge logic.
Dataform assertions enforce schema and business contracts through built-in constraints (uniqueKey, nonNull, rowConditions) and custom SQL assertions that trigger pipeline failures if any violating rows are returned.
Schema drift in automated ingestion pipelines can be managed in BigQuery using schemaUpdateOptions (ALLOW_FIELD_ADDITION), while breaking type modifications must be gated through staging contracts.
A resilient dead-letter pattern routes valid records to production warehouse tables while isolating corrupted records in quarantine tables enriched with error codes and ingestion metadata for automated triage.
Last updated: September 2026
2.3 Data Validation, Cleansing, and Quality Assurance with Dataform
Quick Answer: Ensuring enterprise data reliability requires shift-left quality engineering—validating and cleansing data directly within the analytical data warehouse rather than relying on brittle upstream ingestion scripts. In Google Cloud, Dataform provides an enterprise framework for developing, testing, versioning, and deploying declarative SQL pipelines natively in BigQuery. Using SQLX files, Dataform manages table materializations (views, tables, incremental tables), defines automated built-in assertions ( uniqueKey , nonNull , rowConditions ) and custom SQL assertions, and orchestrates workflows through Cloud Composer and Workflows. Combined with BigQuery schema evolution options ( ALLOW_FIELD_ADDITION ) and dead-letter quarantine tables, data teams isolate corrupted records without halting production analytical workloads.
Shift-Left Data Quality in Modern ELT Architectures
Traditional data warehousing relied on rigid Extract-Transform-Load (ETL) pipelines where proprietary transformation servers cleansed and validated records prior to loading them into the database. While this prevented malformed data from entering target storage, it introduced severe pipeline bottlenecks, compute redundancy, high licensing overhead, and delayed analytical delivery.
Modern cloud data engineering adopts the Extract-Load-Transform (ELT) paradigm combined with shift-left data quality testing :
Bronze Tier (Raw Staging): Raw events from Pub/Sub, Cloud Storage files, and relational CDC streams land directly in BigQuery staging tables without structural alterations.
Shift-Left Validation: Quality tests, schema contracts, and semantic integrity rules execute directly inside BigQuery at the earliest transformation stage using the scalable BigQuery SQL engine.
Silver Tier (Curated & Conformed): Dataform models transform raw staging records into conformed tables only after passing rigorous assertion suites, stripping duplicates and standardizing datatypes.
Gold Tier (Analytical Marts & Feature Stores): Executive dashboards, BI Engine semantic models, and Vertex AI feature stores query validated Gold tables, completely insulated from upstream operational errors. [ Raw Ingestion Sources ]
- (Pub/Sub, GCS, App DBs)
- |
- v
- +-------------------------------------------------------------------------+
- | BigQuery Bronze Tier: Raw Staging Tables |
- +-------------------------------------------------------------------------+
- |
- v
- +-------------------------------------------------------------------------+
- | Dataform Validation & Transformation Engine |
- | - SQLX Materialization: Incremental merges & table builds |
- | - Assertions Gating: uniqueKey, nonNull, rowConditions |
- | - Custom Assertions: Cross-table referential integrity & business logic|
- +-------------------------------------------------------------------------+
- | |
- [Passes All Assertions] [Fails Assertions / Schema Checks]
- | |
- v v
- +------------------------------+ +------------------------------------+
- | BigQuery Silver/Gold Marts | | Quarantine / Dead-Letter Storage |
- | - Reporting & Analytics | | - Enriched with error metadata |
- | - ML Feature Stores | | - Triggers SRE alerts & triage |
- +------------------------------+ +------------------------------------+
- Dataform Core Architecture and SQLX Files
Dataform is Google Cloud's fully managed, Git-integrated data transformation and orchestration framework natively embedded in BigQuery. It empowers data teams to build robust ELT pipelines using SQL and JavaScript encapsulated in SQLX files .
Repository File System Structure
A production Dataform repository is organized into standardized operational folders:
workflow_settings.yaml (or dataform.json ): Governs global repository configuration, including the default Google Cloud project, default BigQuery dataset location, compilation targets, and the dedicated assertion dataset ( dataform_assertions ).
definitions/ : Houses .sqlx files specifying declarations, views, tables, incremental tables, custom assertions, and operational procedures.
includes/ : Contains reusable JavaScript modules ( .js files) that export functions, constants, and SQL macro templates to enforce standard column logic across the codebase.
Declarations and Table Materialization Types
Dataform establishes dependencies and manages BigQuery lifecycle states across distinct object types:
Declarations ( type: "declaration" ): Inform Dataform about external tables managed outside the repository (such as raw streaming tables populated by Dataflow or Cloud Storage external tables). Declarations integrate external objects into Dataform's dependency graph ( ${ref("raw_orders")} ) without attempting to drop or create them: config {
- type: "declaration",
- database: "prj-analytics-prod",
- schema: "raw_ingestion",
- name: "streaming_events"
- }
Views ( type: "view" ): Compiles into standard BigQuery SQL views. Useful for lightweight data filtering, row-level security abstraction, or cost-conscious intermediate transformations.
Tables ( type: "table" ): Drops and recreates the target BigQuery table on every execution. Ideal for dimension tables, lookup mappings, and aggregations that execute quickly.
Incremental Tables ( type: "incremental" ): Engineered for high-volume streaming telemetry, clickstreams, and transactional event logs. Rather than scanning and rewriting the full historical table on every run, Dataform processes only newly arrived delta records using an automated MERGE statement: config {
- type: "incremental",
- schema: "curated_telemetry",
- uniqueKey: ["device_id", "event_timestamp"],
- bigquery: {
- partitionBy: "DATE(event_timestamp)",
- clusterBy: ["device_id"]
- }
- }
- SELECT
- device_id,
- event_timestamp,
- temperature_celsius,
- battery_level
- FROM ${ref("raw_ingestion", "streaming_events")}
- ${when(incremental(), `WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM ${self()})`)}
Operations ( type: "operations" ): Executes arbitrary DDL or DML SQL scripts that do not produce a managed table model, such as granting IAM roles, managing row-level access policies, or creating user-defined functions (UDFs).
Automated Data Quality with Dataform Assertions
Assertions are automated data quality checks that verify whether table data conforms to schema contracts and operational business invariants.
- Built-in Assertions
- Configured directly inside the SQLX config block of any table or view model: config {
- type: "table",
- schema: "curated_orders",
- assertions: {
- uniqueKey: ["order_id"],
- nonNull: ["order_id", "customer_id", "order_date", "total_amount"],
- rowConditions: [
- "total_amount >= 0",
- "tax_amount >= 0",
- "order_status IN ('PENDING', 'PROCESSING', 'SHIPPED', 'DELIVERED', 'CANCELLED')"
- ]
- }
- }
- SELECT order_id, customer_id, order_date, total_amount, tax_amount, order_status
- FROM ${ref("stg_orders")}
uniqueKey : Generates an underlying assertion query verifying that the specified column (or composite column set) contains no duplicate values.
nonNull : Verifies that no rows contain NULL in the specified columns.
rowConditions : Evaluates arbitrary boolean SQL expressions against every row in the materialized table.
Custom SQL Assertions
For complex validation logic—such as cross-table foreign key referential integrity, aggregate volume checks, or historical trend verification—engineers author dedicated .sqlx files with type: "assertion" .
The Core Rule of Assertions: An assertion query is written to return failing records . If the query returns zero rows , the test passes. If the query returns one or more rows , Dataform flags the assertion as failed: config {
- type: "assertion",
- schema: "data_quality_assertions",
- tags: ["daily_validation"]
- }
- - Fails if any order references a non-existent customer in dim_customers
- SELECT
- o.order_id,
- o.customer_id,
- o.order_date
- FROM ${ref("curated_orders")} o
- LEFT JOIN ${ref("dim_customers")} c ON o.customer_id = c.customer_id
- WHERE c.customer_id IS NULL
Important
By default, if a Dataform assertion fails during execution, Dataform automatically blocks all dependent downstream models from executing . This prevents corrupted or unvalidated records from contaminating downstream Gold marts and executive reporting.
Orchestrating Dataform Pipelines: Cloud Composer and Workflows
Dataform separates code compilation from execution:
Compilation: Dataform compiles the SQLX and JavaScript code against a designated Git commit, resolving ${ref()} dependencies and building a deterministic Directed Acyclic Graph (DAG) of DDL, DML, and assertion actions.
Workflow Invocation: Executes the compiled DAG against the BigQuery SQL engine.
In enterprise platforms, Dataform is orchestrated as part of broader data workflows:
Cloud Composer (Managed Apache Airflow): Cloud Composer uses specialized Google Cloud Airflow operators to trigger and monitor Dataform workflows:
DataformCreateCompilationResultOperator : Compiles the repository against a target Git branch or workspace.
DataformCreateWorkflowInvocationOperator : Invokes the compiled workflow, with support for filtering by action tags (e.g., tags: ["finance_hourly"] ) or compilation overrides.
Google Cloud Workflows: Serverless, HTTP-based orchestration ideal for event-driven pipelines. For example, when a new batch of files lands in a Cloud Storage bucket, an Eventarc trigger invokes a Cloud Workflow, which calls the Dataform REST API to trigger compilation and invocation without maintaining persistent Airflow infrastructure.
Managing Schema Drift in BigQuery and Cloud Storage
Upstream application releases frequently introduce unexpected schema modifications—commonly referred to as schema drift :
Backward-Compatible Schema Drift: New optional columns added by upstream producers. In automated BigQuery load jobs (from Cloud Storage or Dataflow), engineers specify schemaUpdateOptions :
ALLOW_FIELD_ADDITION : Automatically adds newly discovered columns to the destination BigQuery table schema as nullable fields during load or query append operations.
ALLOW_FIELD_RELAXATION : Relaxes REQUIRED field modes to NULLABLE if incoming batches omit previously mandatory fields.
Breaking Schema Drift: Altering a column's data type (e.g., STRING to INTEGER ) or dropping required fields cannot be resolved automatically by BigQuery load jobs. Such events trigger load job failures, necessitating dead-letter isolation.
The Dead-Letter and Quarantine Storage Pattern
To prevent malformed records or breaking schema drift from aborting entire ingestion pipelines, production architectures deploy a dead-letter quarantine pattern : +-------------------------+
- | Incoming Raw Data Batch |
- +-------------------------+
- |
- v
- +-------------------------------+
- | Validation Filter / Assertions |
- +-------------------------------+
- |
- +---------------------+---------------------+
- | |
- [ Valid Records ] [ Corrupted Records ]
- | |
- v v
- +--------------------------+ +----------------------------+
- | Production BigQuery Table| | Dead-Letter Quarantine |
- | (Clean Analytical Mart) | | Table / Cloud Storage |
- +--------------------------+ +----------------------------+
- |
- v
- +----------------------------+
- | Enriched Error Metadata: |
- | - quarantine_id (UUID) |
- | - ingest_timestamp |
- | - source_system |
- | - raw_payload (JSON/STRING)|
- | - error_code |
- | - error_message |
- +----------------------------+
- |
- v
- +----------------------------+
- | Alerting & Triage Workflow |
- | (Pub/Sub -> Cloud Monitor) |
- +----------------------------+
- Quarantine Schema Enrichment
When routing corrupted records to a dead-letter BigQuery table or quarantine Cloud Storage bucket, pipelines enrich each record with standardized auditing fields:
- Audit Field Data Type Description
- quarantine_id STRING Unique UUID identifying the quarantine incident record
- ingest_timestamp TIMESTAMP Exact time the corrupted record was processed
- source_system STRING Originating ingestion pipeline, topic, or file path
- raw_payload STRING / JSON Unparsed, verbatim original record content
- error_code STRING Categorized error identifier (e.g., ERR_INVALID_FOREIGN_KEY , ERR_SCHEMA_MISMATCH )
- error_message STRING Detailed parser exception or assertion condition that failed
- Data Quality Failure Modes and Triage Matrix
Failure Scenario Detection Mechanism Quarantine Destination Remediation Action
Duplicate Primary Keys Dataform uniqueKey built-in assertion failure BigQuery quarantine_duplicate_orders Deduplicate using ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) in cleansing view
Schema Drift (New Columns) BigQuery load job schema comparison Destination table with ALLOW_FIELD_ADDITION Validate new fields against data catalog; update Dataform SQLX declarations
Data Type Incompatibility BigQuery load error / Dataflow parse error Dead-letter Cloud Storage bucket ( gs://bucket/quarantine/ ) Log error metadata; notify upstream team to correct data generator format
Out-of-Bounds Metric (Negative Price) Dataform rowConditions assertion failure Downstream build blocked; flagged in assertion audit log Filter invalid records into quarantine; notify billing operations team
Missing Mandatory Foreign Key Custom SQL assertion ( LEFT JOIN ... IS NULL ) Staging orphan records table Hold records in quarantine pending parent entity ingestion; trigger reprocessing DAG
Tip
In Cloud Dataflow streaming pipelines, use side-outputs ( TupleTag ) to implement the dead-letter pattern without throwing runtime exceptions that crash worker virtual machines.