Data Quality in High-Volume Pipelines | Dataford Interview Questions

How do you ensure data quality and consistency in a high-volume pipeline?

Write a PostgreSQL query that audits each pipeline batch for completeness and record-level quality issues. Return every batch, including batches with no ingested records, and classify each batch as passing or failing.

Output

One row per batch with the batch identifiers, expected and ingested counts, issue counts, and quality_status.
Include duplicate keys, missing values, invalid amounts, and invalid statuses.
Order by batch_date, then batch_id.

Schema

pipeline_batches

Column	Type	Description
batch_idPK	INT	Unique pipeline batch identifier
source_name	VARCHAR(50)	Name of the upstream data source
batch_date	DATE	Date assigned to the pipeline batch
expected_event_count	INT	Expected number of events in the batch

ingested_events

Column	Type	Description
event_idPK	BIGINT	Unique ingested event row identifier
batch_id	INT	Pipeline batch containing the event
event_key	VARCHAR(80)	Source event identifier expected to be unique within a batch
event_ts	TIMESTAMP	Timestamp supplied by the source
amount	NUMERIC(12,2)	Non-negative event amount
status	VARCHAR(20)	Event processing status

Your question is Data Quality in High-Volume Pipelines. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem