Reconcile Pipeline Row Counts
A pipeline that "ran green" can still have lost rows. Reconcile counts and control totals at every hop so a divergence is localised to one stage instead of discovered in a report next quarter.
Procedure
- Define a control total per hop: row count plus a checksum of a business column (
sum(amount),count(distinct order_id)). - Capture the source count in the same window the extract reads:
SELECT count(*) FROM src WHERE updated_at > :last AND updated_at <= :run_start. - Capture the staging count after the parse/transform and the target count after the load.
- Compare hop by hop: extract equals parsed plus quarantined; parsed equals loaded plus deduped plus filtered.
- Investigate the first hop where the numbers diverge; that is the bug's location.
- Account for expected differences explicitly: filters (
WHERE status='paid'), dedupe, join fan-out. Fan-out that inflates counts is itself a bug. - Store control totals per run so drift is visible as a trend, not a one-off.
- Alarm on a hard mismatch:
(extract - loaded - quarantined) != 0. - Validate sums, not just counts: 100 rows with the wrong amounts passes a count check.
- Time-box the source count and the extract together, so a writer changing rows between the two does not create a phantom gap.
Pitfalls
- Comparing counts only after the load, with no per-hop capture, cannot localise the loss.
count(*)on a join fans out and inflates; usecount(distinct key).- A filter that should be a no-op (
status IS NOT NULLon a non-null column) silently dropping rows is found only by a per-hop count. - Reconciliation that ignores the dedupe step flags expected dedupe as a failure.
- Reading the source count at a different time than the extract creates a phantom mismatch.
- Summing a float column and comparing for equality fails on rounding; use a tolerance.
- A checksum over an unordered set of values changes when row order changes; aggregate with an ORDER BY.
- Reconciling only the happy-path partition misses the first run after a schema change.
Verification
psql -c "SELECT 'extract', count(*), sum(amount) FROM src WHERE dt='2026-10-01'
UNION ALL
SELECT 'loaded', count(*), sum(amount) FROM tgt WHERE dt='2026-10-01'"
Extract minus loaded minus quarantined is zero and the sums match within tolerance. Report each hop's count and sum and the first divergence, if any.