Data Quality in High-Volume Pipelines

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

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

Output details

• Return columns in this order: batch_id, source_name, batch_date, expected_event_count, ingested_event_count, duplicate_event_key_count, null_event_key_count, null_event_ts_count, invalid_amount_count, invalid_status_count, quality_status.
• Order by batch_date ascending, then batch_id ascending.
• Count duplicate non-NULL event_key occurrences beyond distinct values, and count NULL event_key and NULL event_ts values.
• Treat NULL or negative amount as invalid, and accept only non-NULL statuses equal to accepted or rejected.
• Report zero ingested and issue counts for batches with no records.
• Set quality_status to PASS only when ingested equals expected and every issue count is zero; otherwise set it to FAIL