1.3 Data Quality, Cleaning, and Validation

Data Quality, Cleaning, and Validation

Core Focus: High-throughput ingestion pipelines are useless if downstream analytics are poisoned by corrupt, duplicate, or malformed data. Google Cloud provides a comprehensive quality toolkit spanning declarative BigQuery SQL sanitization, automated Dataplex governance, visual Cloud Data Fusion wrangling, and resilient Cloud Dataflow dead-letter queue architectures.

In enterprise data engineering, raw data is inherently dirty. Source systems produce duplicate transaction IDs due to network retries, upstream microservices alter JSON schemas without notification, mobile clients send malformed timestamps, and legacy databases contain corrupted text fields. Ensuring that data is fit for analytical consumption requires understanding the six dimensions of data quality, applying automated profiling, and building resilient pipelines that isolate errors without crashing.

---

The 6 Core Dimensions of Data Quality

Data quality is evaluated across six standardized dimensions established by data governance frameworks. The Google Cloud Associate Data Practitioner exam frequently tests your ability to identify which dimension is compromised and how to remediate it:

---

Data Profiling and Governance with Google Cloud Dataplex

Before cleaning data, data practitioners must profile it to discover structural anomalies, distributions, and missing value ratios.

Dataplex Auto-Data Profiling

Dataplex (the name used in the exam guide; Google renamed the catalog Knowledge Catalog in April 2026, while APIs and gcloud dataplex commands kept the Dataplex name) governs data across Cloud Storage and BigQuery. It provides serverless data profiling scans:

• Automated Metric Computation: Dataplex scans designated tables and computes statistical profiles without requiring users to write custom queries:
  • Percentage of null, blank, and distinct values per column.
  • Statistical distributions: minimum, maximum, mean, standard deviation, and median quartiles for numeric fields.
  • Top frequent values and pattern frequency for string columns.
• Zero Compute Management: Profiling runs on fully managed, ephemeral serverless compute, publishing profile history directly to the Google Cloud Console.

Dataplex Data Quality Scans

In addition to profiling, Dataplex lets you define data quality scans with declarative rules (in the console or a YAML specification) or custom SQL expressions:

# Sample Dataplex Data Quality Rule Specification
rules:
  - column: customer_id
    dimension: COMPLETENESS
    nonNullExpectation: {}
  - column: transaction_amount
    dimension: VALIDITY
    rangeExpectation:
      minValue: 0.01
      maxValue: 1000000.00
  - column: email_address
    dimension: VALIDITY
    regexExpectation:
      regex: '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

Scans run on demand or on a schedule. Results appear in the console, can be exported to a BigQuery table for trend reporting, and are written to Cloud Logging, so you can alert on failures with Cloud Monitoring or the built-in email notifications.

---

In-Warehouse Data Cleansing with BigQuery SQL

BigQuery provides powerful, built-in SQL functions specifically tailored for data hygiene and defensive data transformation.

1. Robust Deduplication with the QUALIFY Clause

In traditional SQL, deduplicating data while retaining the most recent record required verbose subqueries or Common Table Expressions (CTEs) combined with INNER JOIN clauses. BigQuery simplifies this through the QUALIFY clause, which filters the results of window functions directly:

-- Deduplicate customer order events, keeping only the latest updated record
CREATE OR REPLACE TABLE `analytics.curated_orders` AS
SELECT
  order_id,
  customer_id,
  order_status,
  order_total,
  updated_at
FROM
  `raw_staging.ingested_orders`
QUALIFY
  ROW_NUMBER() OVER (
    PARTITION BY order_id 
    ORDER BY updated_at DESC
  ) = 1;

How it works: The PARTITION BY order_id groups rows sharing the same order identifier. ORDER BY updated_at DESC sorts each group so the newest record receives row number 1. The QUALIFY clause filters out all subsequent rows (> 1), executing the deduplication in a single parallelized scan.

2. Defensive Type Conversion with SAFE_CAST()

A common cause of batch pipeline failure is dirty string data in numeric or date fields. When using standard CAST(column AS INT64), if a single row among millions contains the string "N/A" or "UNKNOWN", BigQuery aborts the entire query with a runtime execution error.

To prevent pipeline crashes, BigQuery provides SAFE_CAST():

SELECT
  order_id,
  -- If total_amount cannot be converted to NUMERIC, it gracefully returns NULL instead of failing
  SAFE_CAST(total_amount_str AS NUMERIC) AS clean_total_amount,
  -- Safe parsing of date strings
  SAFE.PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', event_time_str) AS clean_event_timestamp
FROM
  `raw_staging.raw_transactions`;

3. Handling Missing Values: COALESCE() and IFNULL()

• IFNULL(expr, default_value): Evaluates an expression; if it is NULL, it returns the specified fallback value (both arguments must share compatible types).
• COALESCE(expr1, expr2, ..., exprN): Evaluates arguments sequentially from left to right, returning the first non-null value encountered. This is indispensable when cascading across multiple fallback contact methods (e.g., mobile phone, work phone, home phone).

4. Text Cleansing with Regular Expressions

BigQuery includes optimized regular expression functions:

• REGEXP_REPLACE(value, pattern, replacement): Strips special characters, formatting noise, or non-numeric symbols:

  -- Strips all non-digit characters from phone numbers: "(555) 123-4567" -> "5551234567"
  REGEXP_REPLACE(phone_number_raw, r'[^0-9]', '') AS standardized_phone
  

• REGEXP_EXTRACT(value, pattern): Extracts specific substrings matching capturing groups from unstructured logs or headers.

---

Visual and Programmatic Cleansing Tools

When transformations require visual interactive data preparation or complex streaming event logic, Google Cloud offers specialized alternatives to raw SQL.

Cloud Data Fusion (Visual Wrangler)

Cloud Data Fusion is a fully managed, visual data integration service built on the open-source CDAP framework:

• Wrangler UI: Provides an interactive data wrangling workspace where business analysts and data engineers can visually explore data samples, profile column distributions, and apply transformation directives without writing code.
• Pre-built directives: Enables one-click operations such as splitting delimited strings, masking PII, parsing JSON objects, standardizing date formats, and filtering rows.
• Execution Under the Hood: When deployed, Data Fusion compiles the visual pipeline into distributed Apache Spark jobs running automatically on an underlying ephemeral Cloud Dataproc cluster.

Cloud Dataflow (Programmatic Apache Beam Transforms)

For programmatic stream and batch processing, Cloud Dataflow provides deep validation capabilities via Apache Beam:

• Custom DoFn Validation: Data engineers write imperative validation algorithms in Python, Java, or Go.
• Side Inputs: Pipelines can validate incoming transaction streams against external dynamic reference datasets (such as a fraud blacklist table stored in BigQuery) using Beam side inputs.
• Windowed Aggregations: Computes rolling statistics (e.g., detecting if a user has attempted 10 logins in 60 seconds) using tumbling or sliding event-time windows.

---

Handling Corrupt Records, Schema Drift, and Dead-Letter Queues (DLQs)

In production streaming architectures (such as Cloud Pub/Sub feeding Cloud Dataflow into BigQuery), malformed records will inevitably arrive. A resilient architecture must satisfy two requirements:

1. It must not crash the continuous pipeline.
2. It must not silently discard corrupt data, which violates financial and regulatory auditability.

The Dead-Letter Queue (DLQ) Pattern

The Dead-Letter Queue (DLQ) pattern is the enterprise standard for error isolation in Google Cloud ingestion pipelines.

                    +------------------------------------+
                    |        Cloud Pub/Sub Topic         |
                    +-----------------+------------------+
                                      |
                                      v
                    +------------------------------------+
                    |       Cloud Dataflow Pipeline      |
                    +--------+------------------+--------+
                             |                  |
            (Valid Records)  |                  |  (Malformed / Unparseable)
                             v                  v
                 +-------------------+  +-------------------+
                 | BigQuery Primary  |  |  Dead-Letter Sink |
                 | Analytics Tables  |  | (Cloud Storage /  |
                 +-------------------+  | Pub/Sub DLQ Topic)|
                                        +---------+---------+
                                                  |
                                                  v
                                        +-------------------+
                                        | Cloud Monitoring  |
                                        | Alert & Reprocess |
                                        +-------------------+

How the DLQ Operates in Apache Beam / Dataflow:

1. Multi-Output PCollection: The parsing transform defines two output tags: a SuccessTag for valid records and a DeadLetterTag for failures.
2. Defensive Parsing: Inside a try/except block, the transform attempts to parse the payload and validate required fields.
3. Routing Failures: If JSON parsing fails, schema validation fails, or type casting throws an exception:
   • The original, un-mutated raw payload is packaged into an error object.
   • Diagnostic metadata is appended: timestamp, source system, exception stack trace, and pipeline stage identifier.
   • The error object is emitted to the DeadLetterTag.
4. Isolated Storage:
   • Valid records are written to production BigQuery tables.
   • Dead-letter records are routed to an inexpensive, durable sink: a Cloud Storage bucket (e.g., gs://dead-letter-archive/) or a dedicated Pub/Sub DLQ topic.
5. Alerting and Reprocessing: An alert is triggered via Cloud Monitoring. Once upstream bugs are fixed or schema mappings are updated, engineers can run an offline batch Dataflow replay job to reprocess records from the dead-letter bucket back into the main pipeline.

Managing Schema Drift

Upstream source applications frequently add new attributes to data payloads without notifying data teams. In Google Cloud:

• Schema update options: When a load or query job appends to an existing table, the ALLOW_FIELD_ADDITION option (with autodetect or a supplied schema) lets BigQuery add the new columns instead of failing the job.
• BigQuery JSON Column Flexibility: By storing evolving payload sub-trees in a native JSON column, newly introduced attributes are stored immediately without altering the table DDL.
• Avro and Protocol Buffers: In high-governance environments, organizations utilize schema registries with Avro or Protobuf schemas that support backwards and forwards compatibility rules.

---

Architecture Comparison: Data Cleansing Mechanisms

---

Common Exam Traps and Scenarios

Exam Tip: Whenever an exam scenario describes a streaming ingestion pipeline that fails due to occasional malformed JSON records, look for solutions implementing side outputs or Dead-Letter Queues (DLQs). Never choose options that drop corrupted records silently or let exceptions bubble up to crash the worker instances.

Trap 1: Using CAST() Instead of SAFE_CAST() in Production Batch Jobs

• The Trap: Writing a scheduled BigQuery query using CAST(sale_price AS FLOAT64).
• The Reality: A single non-numeric record among a billion rows will cause the entire scheduled query to fail, missing SLA delivery deadlines. In production pipelines, always use SAFE_CAST() and flag resulting NULL values for audit.

Trap 2: Implementing Complex Dataflow Pipelines for Simple Deduplication

• The Trap: Spinning up a Cloud Dataflow cluster simply to eliminate duplicate rows from a BigQuery daily load.
• The Reality: BigQuery's native QUALIFY ROW_NUMBER() OVER(PARTITION BY id ORDER BY timestamp DESC) = 1 accomplishes this in seconds using standard SQL without provisioning pipeline runners, writing Beam code, or paying for worker VM instances.

Trap 3: Silent Data Dropping

• The Trap: Handling unparseable records in a pipeline by wrapping them in an empty catch block and proceeding.
• The Reality: Silently dropping malformed data leads to reconciliation discrepancies in financial systems, missing regulatory reporting, and un-diagnosable data drift. Corrupted data must always be preserved in a dead-letter sink with full audit context.