Reconciliation (Source-to-Target)
free10 data-quality rules for source-to-target reconciliation. Covers row count matching, control totals, distinct key counts, missing/extra key detection, duplicate-on-load, row hash sampling, balance-to-zero, watermark gap detection, and partition completeness.
Checks included (10)
Row Count: Source = Target
Target row count must equal source row count within tolerance. The first-line completeness control for any load.
Distinct Business-Key Count Match
Distinct count of the business key must match between source and target — detects dedup or fan-out introduced in transit.
No Missing Keys in Target
Every business key present in source must appear in target. Lists the dropped records, not just a count.
Partition / Batch Completeness
Every expected partition (date, region, batch_id) must be present in target with a non-zero row count.
Control Total (SUM) Match
Sum of a key measure (amount, quantity) must agree between source and target — catches partial loads and value corruption.
No Unexpected Keys in Target
Target must not contain business keys absent from source (duplication, stale rows, cross-batch leakage).
Row Hash Match (sampled)
For a sample of keys, a hash of the mapped columns must equal the source hash — detects silent value drift the totals miss.
No Duplicate Keys After Load
Business key must be unique in the target after load — a re-run or fan-out join often introduces duplicates.
Net Balance / Difference is Zero
Reconciliation breaks must net to zero (e.g., sum(debits)=sum(credits), or source_total - target_total = 0).
No Dropped Records vs Source Watermark
All source records up to the high-watermark must be present in target — guards incremental loads against gaps.