Cleaning up bad or malformed records during ingestion

Data cleansing on Google Cloud is primarily handled through Dataprep by Trifacta for interactive, visual data wrangling, Dataflow for programmatic batch/streaming cleansing at scale, and BigQuery for SQL-based cleansing during or after load. The right tool depends on whether cleansing needs to be visual/exploratory, code-based and scalable, or SQL-native and post-load.

1 · Learn the must-know

• 1Dataprep by Trifacta provides a serverless, visual UI for exploring, cleaning, and transforming data, automatically suggesting transformations and generating a reusable recipe that can run as a Dataflow job.
• 2Dataflow (Apache Beam) is the preferred choice for complex, programmatic, and scalable cleansing logic (deduplication, schema validation, type coercion) applied to both batch and streaming pipelines.
• 3BigQuery supports cleansing via SQL during ELT (using CREATE TABLE AS SELECT, MERGE, or scheduled queries) and can handle malformed rows on load using options like max_bad_records and ignoreUnknownValues.
• 4Cloud Data Fusion offers a GUI-based, code-free way to build ETL/ELT pipelines with built-in cleansing transformations (like Wrangler directives) and is often preferred when a low-code, plugin-based approach is required.
• 5For streaming data, cleansing logic should be embedded early in the pipeline (e.g., in Dataflow or Pub/Sub schema validation) to avoid propagating bad data downstream, since streaming inserts into BigQuery do not support the same load-time error handling as batch loads.
• 6Data quality and cleansing decisions should consider idempotency and exactly-once processing, especially in streaming Dataflow pipelines, to avoid duplicate or inconsistent cleansed records.

2 · Check your understanding