Question

A retail company ingests daily sales files from hundreds of third-party partners into Cloud Storage, then loads them into BigQuery using a Dataflow pipeline. Recently, some partners have submitted files with missing columns, incorrect data types, and duplicate records, which corrupted downstream reporting before anyone noticed. The data engineering team needs to design a solution that detects and quarantines bad records before they reach the reporting tables, while still allowing valid records to flow through. What should they do?

Accepted Answer

Add a validation step in the Dataflow pipeline that checks each record against expected schema and business rules, routing invalid records to a dead-letter table and valid records to the reporting tables.

Answer

Load all records directly into the reporting tables and run a scheduled BigQuery query afterward to delete rows that fail validation checks.

Answer

Enable BigQuery's automatic schema detection on load so malformed files are rejected in their entirety before reaching reporting tables.

Answer

Configure Cloud Storage object versioning and lifecycle rules so that only the most recent version of each partner file is processed.