Cross-System Reconciliation

Loading patterns…

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

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

Important

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.

Visual Flow

When to Use This Pattern

How It Works

Implementation Guide

Tips & Best Practices

Related patterns

CSV/Excel Import Validator

Data Enrichment Pipeline

Idempotency Key Deduplication