Data Cleansing with Dataplex, Dataflow and BigQuery

Visual profiling and governance in Google Cloud is the practice of inspecting data before it is trusted and then enforcing rules that keep it clean over time. Knowledge Catalog, the Dataplex-based governance platform, centralizes this work by letting teams discover, manage, monitor, and govern data and AI artifacts across data platforms. Data flows from collection through storage and transformation into analytical consumption, and the governance layer sits alongside that flow to monitor data flows, transformations, and access. This approach protects validity and quality and supports consistency, reliability, and integrity of downstream models and analytics.

1.1Visual Data Profiling for Cleansing

Visual data profiling with Knowledge Catalog is the generation of data profiles that describe formats, ranges, distributions, and completeness before analytical use. A data profile summarizes what the data looks like, so missing values, outliers, skewed distributions, and unexpected formats become visible before training or reporting. Knowledge Catalog manages both data profiles and data quality checks, and BigQuery (opens in a new tab) provides column-level lineage to show which source columns contributed to each profiled field. Because profiling happens before consumption, teams can verify formats, ranges, and distributions and remove or correct defects early.

Profiling for cleansing also defends against inconsistent or malicious change in pipelines. Comprehensive monitoring and alerting is configured for data pipelines and machine learning (ML) pipelines to track inconsistent behavior, unexpected transformation, or unexpected access to data. To detect anomalies in training data that might indicate data poisoning, BigQuery anomaly detection uses statistical methods or ML models. Human review complements automation by analyzing evaluation metrics and applying domain expertise to identify suspicious patterns that automated methods might miss.

1.2Automated Data Quality Rules in Dataplex

Automated data quality in Knowledge Catalog is the use of built-in checks to validate integrity continuously rather than by one-time inspection. Validation checks verify data formats, ranges, and distributions before data is used for training, which prevents invalid or poisoned records from moving downstream. The governance platform manages those data quality checks centrally, so the same rule definition applies wherever the governed table or artifact is discovered and used. This central control plane manages policy while the underlying platforms in BigQuery, data lakes, and lakehouses store and process the data.

Knowledge Catalog extends quality governance with rich metadata controls:

• Data lineage tracks source-to-target transformations, with column-level lineage in BigQuery, to support debugging and root-cause analysis.
• Business glossary establishes a standardized vocabulary for metadata across organization-wide or department-wide domains.
• Aspects and aspect types enrich entries with business and technical context.
• Unified governance covers BigLake and open-format tables like Iceberg and Delta Lake.
• Data mesh support decentralizes ownership among domain teams while retaining central discovery and monitoring.
• Sensitive-data integration inspects BigQuery results and sends findings to Knowledge Catalog for classification and trust decisions.

1.3Continuous Monitoring for Compliance and Trust

Continuous monitoring for compliance is the capture of logs, metrics, and audit trails about data access and manipulation with alerting on deviation. Cloud Logging (opens in a new tab) captures real-time logs from data pipelines, Cloud Monitoring collects metrics and alerting signals, and Cloud Audit Logs collect audit trails of API calls. Logs help teams audit data access, detect unauthorized access attempts, and prevent unwanted access, but pipelines must not log personally identifiable information (PII) or confidential data in experiments or log servers. Together, the pipeline depends on Logging for visibility, Monitoring for alerting, and Audit Logs for accountability.

This monitoring boundary separates trusted governance operations from untrusted data change. Training data poses the largest risk for bias and leakage, so the governance layer monitors collection, storage, and transformation across teams. When a quality rule fails, a distribution shifts, or access deviates from expected patterns, alerts route to the responsible owners for review. The result is ongoing compliance where quality is re-verified on every pipeline run and transformation, not only checked once during cleansing.

Cloud Dataflow (opens in a new tab) is a fully managed, serverless service for running Apache Beam pipelines at scale with unified stream and batch processing. A Dataflow pipeline reads from one or more sources, transforms the data, and writes the data to a destination, and Apache Beam lets teams apply the same transform logic to bounded batch data and unbounded streaming data. For data cleansing, that read-transform-write shape means validation and filtering live inside the transform stage where clean records continue to the analytical sink while corrupt records are diverted to a separate auditing sink without stopping the job.

2.1Unified Batch and Streaming Transforms With Apache Beam

Apache Beam pipelines in Dataflow use the same programming model for batch and streaming analytics, so one cleansing design can serve historical backfills and real-time feeds. Streaming pipelines can achieve low latency while ingesting, processing, and analyzing fluctuating volumes of real-time data, and batch pipelines process bounded sources such as files in Cloud Storage or tables in BigQuery. Unbounded sources are often converted to bounded chunks for task-style processing, then pre-processed into a shape and encoding that downstream validation and enrichment can use. Performance, scaling, availability, security, and compliance are handled automatically, so developers focus on pipeline code rather than managing worker clusters.

Dataflow provides two streaming correctness modes that change how cleansing handles duplicates:

2.2Filtering in the Read-Transform-Write Flow

Filtering in Dataflow is a transform between managed sources and managed sinks in the ordered flow from ingestion to storage. The pipeline pulls events from a durable ingestion service such as Pub/Sub (opens in a new tab), applies parsing, validation, and enrichment, and writes trusted output to destinations such as BigQuery (opens in a new tab) for warehousing, Cloud Storage for lake storage, or Bigtable for high-throughput serving. Using Google Cloud managed sources and sinks removes capacity-management complexity because those services provide built-in scalability, consistent performance, and quotas that accommodate most requirements. Apache Beam also supplies source and sink connectors, including BigQueryIO, so the same cleansing pipeline can read and write across Pub/Sub, BigQuery, and Cloud Storage without custom I/O code.

Validation that calls outside systems needs extra care because Dataflow parallelizes work across many workers. Calling external services from a transform incurs per-call overhead, so batching multiple elements into single requests is preferred, a pattern that native Beam I/O transforms such as BigQueryIO and streaming inserts already perform. Too much parallel traffic can overwhelm an external validation service or exhaust daily or per-second quotas, and autoscaling can make the pressure worse by adding workers to a slow external-call step. Limiting batch sizes and concurrent calls keeps the cleansing stage sustainable under autoscaling.

2.3Isolating Bad Records Without Stopping the Pipeline

Isolating bad records in Dataflow is the split of transform output into a clean path and a quarantine path that keeps the job running. Valid records flow from the transform stage to the primary analytical sink, while malformed, incomplete, or schema-violating records flow to a separate dead-letter sink such as a Pub/Sub topic, a Cloud Storage prefix, or a BigQuery quarantine table for auditing and reprocessing. That separation prevents one corrupt event from failing the whole job and preserves the original bad payload plus the validation error for later inspection, correction, and replay. The same branching idea appears in Cloud Data Fusion as error-collector and alert-publisher plugins, which shows the broader Google Cloud pattern of routing failures to dedicated collectors rather than dropping them silently.

An end-to-end cleansing pipeline follows an ordered flow from ingestion through quarantine:

1. Ingest raw data: Data flows from bounded sources or unbounded Pub/Sub streams into the Dataflow job, where separate Pub/Sub subscriptions let the cleansing pipeline pull and acknowledge messages independently of test or replica pipelines.
2. Pre-process and validate: Data flows from the source into parsing, schema-check, and enrichment transforms that filter, correct, or tag each element as valid or invalid.
3. Materialize results: Valid data flows to the primary sink for dashboards and machine learning, while invalid data flows to the dead-letter sink where it waits for auditing and reprocessing.

Pub/Sub strengthens this design because a subscription stores unacknowledged messages for up to 7 days, so delayed data can buffer in the subscription while pipelines are stopped and then be processed with low data loss when workers restart.

2.4Operability for Continuously Running Cleansing Jobs

Operability in Dataflow comes from managed execution, observability, and flexible deployment of continuously running jobs. When a Dataflow job runs, the service allocates a pool of Compute Engine worker virtual machines, distributes data in parallel, autoscales workers up or down, dynamically rebalances work among workers, and deletes the workers when the job completes or is cancelled. The work therefore survives slow workers and worker failures through built-in mechanisms:

• Fault tolerance with geographic redundancy keeps streaming jobs progressing through infrastructure failures.
• Dynamic work rebalancing redistributes parallel cleansing work so no worker stays overloaded.
• Straggler detection and correction identifies and mitigates slow stages.
• Centralized logging collects logs for all pipeline stages in one place.
• Monitoring interface shows a graphical pipeline with progress and execution details for each stage.

Deployment and security controls determine where and how the cleansing job runs. Teams can write code with the Java, Python, or Go SDKs, deploy a Dataflow template for repeatable execution, or iterate in JupyterLab notebooks, and multi-language pipelines allow mixing SDKs when needed. Worker placement is controlled by regional endpoint, worker region, and worker zone settings, so sources, sinks, and temporary files should sit in the job region to avoid sending data across regions. Customer-managed encryption keys, virtual private cloud networks, and firewall rules protect pipeline state and data in motion while preserving the autoscaling and exactly-once behavior that cleansing depends on.

Post-ingestion deduplication and standardization in BigQuery (opens in a new tab) is the practice of loading raw data into a staging table and then using BigQuery itself to clean, conform, and write trusted output to a final production table. Data flows from extract-and-load sources into the staging table, then through SQL queries that enforce types, formats, and consistency rules, then into the production table that serves analytics. This extract, load, and transform (ELT) pattern keeps heavy cleansing close to petabyte-scale storage while preserving a repeatable lineage from raw input to curated output.

3.1Validate Types, Formats, Ranges, and Distributions After Load

Post-ingestion validation in BigQuery verifies that loaded records match the expected schema and business rules before analytical consumption. Validation checks confirm that data types, table structures, formats, ranges, and distributions are correctly handled, and test cases compare BigQuery results against source results to expose conversion or structural drift. When mismatches appear, the converted queries and definitions are analyzed and fixed before the data is promoted to production.

BigQuery validation combines code-level checks with managed quality services:

• Verify types, formats, and ranges in code inside ingestion and preprocessing scripts, including Python logic used with BigQuery.
• Verify formats, ranges, and distributions with governance through automatic data quality capabilities in Knowledge Catalog (opens in a new tab).
• Detect anomalous patterns in tables with statistical methods or machine learning models through BigQuery anomaly detection.

The script checks enforce row-level constraints at transform time, while Knowledge Catalog manages data quality checks, data profiles, and column-level lineage for ongoing governance. Because BigQuery also provides column-level lineage, a failed format or distribution check can be traced from the production table back through the transform to the staging source.

3.2Execute Standardization at Scale with SQL, Procedures, and Physical Layout

Standardization at scale in BigQuery uses SQL transforms, stored procedures, and physical layout controls to apply the same cleansing logic efficiently across very large datasets. BigQuery supports SQL for stored procedures and SQL user-defined functions (UDFs) that execute inside BigQuery, plus external function calls through Cloud Run functions for logic that cannot be expressed in SQL. For other languages, external functions or client-side application logic carry the custom step while BigQuery retains the set-based SQL transform.

Performance and correctness depend on how the staging and production tables are organized and written. BigQuery supports explicit partitioning and clustering to control how data is distributed and organized, which can improve both cost and runtime performance for cleansing scans, joins, and aggregations used to find duplicates and conform values. Fine-grained data manipulation language (DML) improves latency and throughput for small writes, and materialized views can persist and automatically refresh common SQL computations used for standardized outputs. Data that BigQuery loads is stored synchronously in two zones within the specified region, so the staging-to-production flow retains zonal redundancy during cleansing.

3.3Govern Transforms, Monitor Pipelines, and Control Access to Curated Data

Governance for post-ingestion cleansing in BigQuery centrally discovers, manages, monitors, and governs data, transforms, and access across data marts. Knowledge Catalog provides the governance layer for lineage, data discovery, business glossary vocabulary, and metadata enrichment, while BigQuery contributes data governance features that help control access and ensure accuracy and consistency. The governance platform depends on pipeline logs and lineage, so every staging-to-production SQL transform remains auditable for who changed data, when the change occurred, and which artifacts were affected.

Monitoring and access control surround the cleansing flow from ingestion through curated serving. Comprehensive monitoring and alerting for data pipelines tracks inconsistent behavior, unexpected transformation, and unexpected access, supported by Cloud Logging (opens in a new tab), Cloud Monitoring, and Cloud Audit Logs for API and access trails. Access to datasets and resources within datasets is managed with Identity and Access Management (IAM) roles, with finer controls available through row-level security, column-level security, authorized views, and policy tags. Together, validation SQL, governed transforms, and layered access ensure that only cleansed, standardized, and authorized production data reaches analytical consumers.

Key Facts

• Knowledge Catalog manages data profiles that reveal missing values, outliers, and skewed distributions and manages automatic data quality rules that verify formats, ranges, and distributions before training.
• Cloud Dataflow runs Apache Beam pipelines with exactly-once processing by default and offers at-least-once mode for lower cost and latency when duplicates can be tolerated.
• Dataflow cleansing pipelines keep running by sending valid records to the primary sink and malformed records with their validation errors to a dead-letter sink for auditing and reprocessing.
• Pub/Sub subscriptions store unacknowledged messages for up to 7 days, allowing delayed data to buffer while Dataflow pipelines are stopped.
• BigQuery post-ingestion cleansing loads raw data into a staging table and uses SQL stored procedures, SQL UDFs, partitioning, clustering, and materialized views to write standardized output to a production table.