ExamFinch.com My Study

Study guide

1.3 Data Quality, Cleaning, and Validation

Practice this exam

Google Cloud Associate Data Practitioner Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the Associate Data Practitioner Exam

2 Chapter 1: Data Processing Paradigms & Ingestion Strategies

1.1 ETL, ELT, and ETLT Transformation Paradigms 1.2 Data Transfer and Migration Tools 1.3 Data Quality, Cleaning, and Validation

3 Chapter 2: Data Formats & Storage Solution Selection

2.1 File Formats & Serialization Characteristics 2.2 Cloud Storage Buckets, Location Types & Storage Classes 2.3 Selecting Google Cloud Databases and Storage Services

4 Chapter 3: Loading Data & Modern Ingestion Architectures

3.1 Batch Loading Data into BigQuery 3.2 Streaming Ingestion & Deduplication Patterns 3.3 BigQuery External Tables, BigLake & Federated Queries 3.4 Schema Definition & Evolution for Loaded Data

5 Chapter 4: BigQuery SQL Analytics & Query Foundations

4.1 BigQuery SQL Syntax & Analytical Functions 4.2 Complex Data Types: Arrays, Structs, and JSON 4.3 Answering Business Questions with BigQuery: Worked Analyses

6 Chapter 5: BigQuery Performance Optimization & Cost Governance

5.1 Table Partitioning Strategies 5.2 Table Clustering & Data Skipping 5.3 Query Optimization, Execution Plans, and Cost Controls

7 Chapter 6: Interactive Analysis, Notebooks & Visual Presentation

6.1 Exploratory Analysis with Jupyter & Colab Enterprise 6.2 Looker Studio (Data Studio) Reporting & Dashboard Design 6.3 Enterprise BI with Looker & LookML Foundations 6.4 Building, Modifying & Sharing Looker Dashboards

8 Chapter 7: Machine Learning with BigQuery ML & Vertex AI

7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models? 7.2 Creating, Training & Evaluating Models with BigQuery ML 7.3 Running Inference and Organizing Models in Model Registry 7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

9 Chapter 8: Pipeline Engines & Transformation Services

8.1 Transformation Engine Selection Matrix 8.2 Apache Beam & Cloud Dataflow Core Concepts 8.3 Stream Processing: Windowing, Watermarks, and Triggers

10 Chapter 9: Workflow Orchestration, Automation & Monitoring

9.1 SQL Pipelines with Dataform & Scheduled Queries 9.2 Workflow Orchestration with Cloud Composer & Workflows 9.3 Event-Driven Ingestion: Pub/Sub to BigQuery & Eventarc Triggers 9.4 Monitoring Pipelines: Dataflow Job UI, Cloud Logging & Cloud Monitoring

11 Chapter 10: Access Control, Governance & Data Sharing

10.1 IAM Principles & Role Hierarchies for Data Services 10.2 Cloud Storage Security & Access Controls 10.3 Fine-Grained Security: Column-Level & Row-Level Controls 10.4 Secure Data Sharing with Analytics Hub (BigQuery Sharing)

12 Chapter 11: Lifecycle Management, Reliability & Compliance

11.1 Object Lifecycle Management & Storage Tiering 11.2 High Availability & Disaster Recovery Architectures 11.3 Data Retention, Time Travel, and Point-in-Time Recovery 11.4 Encryption Key Management & Privacy Compliance

1.3 Data Quality, Cleaning, and Validation

Key Takeaways Explain This Topic AI

The six fundamental dimensions of data quality are accuracy, completeness, consistency, timeliness, validity, and uniqueness.

BigQuery provides native SQL cleansing capabilities, including deduplication via QUALIFY ROW_NUMBER() OVER (...), null handling with COALESCE() and IFNULL(), and pattern matching using REGEXP_REPLACE() and SAFE_CAST().

Dataplex (now Knowledge Catalog) runs serverless data profiling scans and rule-based data quality scans, with results you can export to BigQuery and alert on.

Robust ingestion architectures implement Dead-Letter Queues (DLQs) in Cloud Dataflow or Pub/Sub to isolate corrupted records and schema drift without terminating real-time pipelines.

Last updated: October 2026

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:

Quality Dimension Definition Real-World Failure Example Google Cloud Remediation Technique

Accuracy Data values correctly represent real-world entities or events. An IoT sensor reports an ambient temperature of 950°C due to a firmware glitch. Range validation rules in Dataplex; anomaly detection queries in BigQuery.

Completeness All mandatory data attributes are populated; no unexpected nulls or missing rows. Customer registration records are ingested with missing postal_code or email values. COUNTIF(column IS NULL) checks; Dataplex non-null rule assertions.

Consistency Data values across disparate systems, tables, or representations do not conflict. An order status shows as SHIPPED in the fulfillment mart but PENDING in the customer CRM. BigQuery multi-table validation queries; primary key/foreign key referential integrity checks.

Timeliness Data is fresh, up-to-date, and available within the expected operational SLA window. Marketing analytics dashboards display clickstream events with a 24-hour lag instead of real-time. Transitioning from daily batch jobs to Cloud Dataflow streaming pipelines from Pub/Sub.

Validity Data conforms to defined syntactic formats, data types, domains, and business rules. An order_date column contains the string "invalid-date" instead of an ISO 8601 timestamp. SAFE_CAST() and REGEXP_CONTAINS() validation in BigQuery SQL.

Uniqueness Each distinct entity, event, or transaction is represented exactly once; zero duplicates. A mobile client re-submits a payment request three times due to network timeouts, generating 3 identical order rows. Deduplication using QUALIFY ROW_NUMBER() OVER(PARTITION BY id ORDER BY timestamp DESC) = 1 .

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:

It must not crash the continuous pipeline.

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:

Multi-Output PCollection: The parsing transform defines two output tags: a SuccessTag for valid records and a DeadLetterTag for failures.

Defensive Parsing: Inside a try/except block, the transform attempts to parse the payload and validate required fields.

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 .

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 .

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

Cleaning Mechanism Primary Interface Compute Paradigm Real-Time Streaming Support Best Fit Use Case

BigQuery SQL Declarative SQL In-Warehouse (Dremel) Micro-batch / Streaming buffer Deduplication ( QUALIFY ), null handling, text formatting, and dimensional modeling.

Cloud Dataplex Declarative YAML / SQL Serverless Governance Fabric Scheduled Batch Scans Automated enterprise data profiling, metadata cataloging, and SLA compliance monitoring.

Cloud Data Fusion Visual Drag-and-Drop Managed Spark on Dataproc Micro-batch & Batch Visual data wrangling for analysts; multi-cloud ETL without code.

Cloud Dataflow Code (Java / Python Beam) Distributed Streaming & Batch Native Low-Latency Streaming Dead-letter queue routing, complex sessionization, in-flight PII masking, and multi-stream enrichment.

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.