Cross-System Reconciliation
Compare records between two systems to find mismatches, missing entries, and data drift. The detective pattern that finds problems before they become costly — essential for finance, HR, and IT.
On this page
Visual Flow
Rendering diagram…
When to Use This Pattern
Use cross-system reconciliation when:
- The same data exists in two or more systems and they're supposed to match
- You suspect data drift — systems that were in sync have diverged over time
- Financial compliance requires proof that records match (bank statements vs ledger, PO vs invoice)
- You need to detect missing records — items in System A that don't exist in System B
How It Works
Rendering diagram…
Implementation Guide
Step 1: Define the Reconciliation Scope
| Parameter | Example |
|---|---|
| System A | ERP (source of truth for financials) |
| System B | Bank statement / Payment processor |
| Matching key | Invoice number, PO number, or transaction ID |
| Fields to compare | Amount, date, status, counterparty |
| Tolerance | Amounts within $0.01 are considered matching |
| Frequency | Daily for financial, weekly for operational |
| Period | Reconcile records from the last 7 days |
Step 2: Extract Data from Both Systems
Pull records from each system for the reconciliation period:
records_a = query_system_a({
date_range: [period_start, period_end],
status: ['completed', 'pending']
})
records_b = query_system_b({
date_range: [period_start, period_end],
status: ['completed', 'pending']
})
Normalise the data:
- Convert all amounts to the same currency
- Standardise date formats
- Trim whitespace and normalise case for text fields
- Map status values (System A's "Completed" = System B's "Paid")
Step 3: Perform the Match
For each record in System A:
- Look for a match in System B by the matching key
- If found → compare each field
- If not found → record as "missing in B"
For remaining unmatched records in System B: 4. Record as "missing in A" (orphans)
Matching strategies for fuzzy scenarios:
| Strategy | When to Use |
|---|---|
| Exact key match | Both systems share a common ID (invoice number) |
| Composite key | Match on amount + date + counterparty when no shared ID |
| Fuzzy match | Names/descriptions may differ slightly (Levenshtein distance) |
| One-to-many | One PO in System A corresponds to multiple invoices in System B |
Step 4: Classify Results
| Result | Priority | Action |
|---|---|---|
| ✅ Full match | None | Log and move on |
| ⚠️ Partial match (some fields differ) | Medium | Create investigation task |
| ❌ Missing in target | High | Investigate — failed sync? delayed entry? |
| ❌ Orphan in target | High | Investigate — unauthorized entry? |
| 🔄 One-to-many resolved | Low | Log the mapping for reference |
Step 5: Generate the Reconciliation Report
📊 Daily Reconciliation — Mar 15, 2025
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ERP (System A) vs Bank Statement (System B)
Period: Mar 14, 2025
Summary:
• Total records compared: 245
• ✅ Matched: 232 (94.7%)
• ⚠️ Mismatched: 5 (2.0%)
• ❌ Missing in Bank: 6 (2.4%)
• ❌ Orphans in Bank: 2 (0.8%)
Top mismatches by value:
1. INV-4521: ERP $5,000.00 vs Bank $5,200.00 (Δ $200)
2. INV-4498: ERP $12,350.00 vs Bank $12,350.50 (Δ $0.50)
Missing in Bank (may be pending):
• INV-4530 ($3,200) — Created today, likely not yet processed
• INV-4528 ($8,900) — Created 3 days ago ⚠️ OVERDUE
Step 6: Automate Follow-Up
Based on the results:
| Finding | Automated Action |
|---|---|
| Mismatch > tolerance | Create investigation task for Finance |
| Missing > 3 days | Escalate to Accounts Receivable |
| Orphan record | Flag for fraud review |
| Reconciliation rate < 95% | Alert Finance Director |
Tips & Best Practices
Always run reconciliation in read-only mode first. Never auto-correct mismatches without human review. A "mismatch" might mean System A is wrong, not System B.
- Start with the easy cases. Match on exact keys first (invoice number). Then attempt fuzzy matching on the remaining unmatched records. This avoids false positive matches.
- Track reconciliation trends. If your match rate drops from 98% to 90% over a month, something changed. Investigate the new error patterns.
- Handle timing differences. A payment processed at 11:59 PM might be in today's bank statement but tomorrow's ERP batch. Set appropriate lag windows.
- Reconcile bidirectionally. Don't just check A→B. Also check B→A to catch orphan records that exist in the target but not the source.
Related patterns
CSV/Excel Import Validator
Accept file uploads (CSV, Excel), validate every row against business rules, report errors to the submitter, and import clean data into the target system. The essential pattern for bulk data intake.
Data Enrichment Pipeline
Take a basic record and enhance it with data from external APIs — geocoding addresses, resolving company info, validating phone numbers, scoring leads. Transform minimal input into rich, actionable data.
Idempotency Key Deduplication
Tag every side-effecting request with a unique key, persist the outcome, and return the stored result on replays. Stops duplicate charges, duplicate tickets, and duplicate emails dead.