2.3 Data Validation, Cleansing, and Quality Assurance with Dataform

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:

1. 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"
}

1. Views (type: "view"): Compiles into standard BigQuery SQL views. Useful for lightweight data filtering, row-level security abstraction, or cost-conscious intermediate transformations.
2. Tables (type: "table"): Drops and recreates the target BigQuery table on every execution. Ideal for dimension tables, lookup mappings, and aggregations that execute quickly.
3. 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()})`)}

1. 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:

1. 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.
2. 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:

---

Data Quality Failure Modes and Triage Matrix

[!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.