Reconciliation and Record Matching

• Build count, sum and key-based record-level reconciliation between two systems
• Classify mismatches (missing, extra, changed) and route them
• Design idempotent match keys so joins never depend on formatting

• Row counts and totals in a warehouse can be quietly wrong (dropped batches, filtered rows, duplicate extras).
• Regulators and finance teams require an independent 'tie-out' between systems.
• Reconciliation is the fast path to root cause: a missing-count discrepancy names the exact losing step.
• It guards migrations — before cutting over res themselves, you prove the new system matches the old byte-for-byte.

You don't trust the ledger if the totals don't tie to the statement. Reconciliation checks counts, sums and line items the way a bookkeeper matches each transaction row to its document.

• Count reconciliation: compare row counts source vs target (with filters aligned).
• Total reconciliation: compare SUM of key monetary/volume columns, possibly per dimension bucket.
• Record-level match: build a natural key on both sides, then do a full outer join to classify exists/extra/missing/different.
• Difference analysis: for matched keys with differing values, compute per-field diffs.
• Publish a reconciliation report: totals, matched, missing, extra, changed, plus a pass/fail verdict.
• Escalate or auto-repair: halts before publishing, or spill lists for manual tie-out.

• A payment processor runs an hourly count+sum reconciliation between its Postgres source and Redshift landing; any mismatch blocks the downstream risk batch.
• Before a 2TB warehouse migration, a team reconciles row counts, checksums and sample keys between old and new — zero-difference is the go-live gate.
• A bank does daily record-level matching of customer-account feeds and publishes a signed tie-out report for auditors.

• Make match keys normalization-stable: trim, case-fold, coerce types, and document the key.
• Reconcile at the grain you verify (per partition, per day) so a mismatch names the failing slice.
• Compare checksums/hashes for whole-row equality on large tables instead of per-field diffs.
• Reconcile the earliest landing before transforms, or you can't distinguish copy-loss from transform bugs.

• Matching by PK the target auto-generates — that key doesn't exist in the source.
• Comparing raw text with formatting differences (currency, timezone) and paging false mismatches.
• Only checking counts — duplicated+missing pairs both pass a count check.
• Ignoring the reconciliation result because it 'usually misses a few' — drift pollution of your own signal.

• Diagnose by mismatch class: 'missing' points to extraction/retention, 'extra' to duplicated inserts, 'changed' to a transform rule.
• Pull 10 sample keys from each class and eyeball them — a pattern appears fast.
• Run reconciliation twice: once raw, once on cleansed keys; the diff between the two reports shows the normalization cost.
• When totals disagree but counts match, suspect rounding, currency or NULL handling in the SUM.