Explain every difference between source and target.
Deterministic reconciliation and migration assurance for financial-services, fintech, and data teams moving critical records across systems.
Where discrepancies arise
Database migrations, ledger synchronizations, payment provider reconciliations, and reporting feed comparisons all require proving that two separate data representations agree.
A successful reconciliation does not mean ignoring differences. It means cataloguing every mismatch with mathematical precision, isolating data transformations from genuine errors, and providing an auditable proof of state.
The 7-step reconciliation process
How we turn cross-system uncertainty into an auditable, repeatable operation.
1. Understand
Identify participating source and target systems, underlying business entities, volume expectations, and core reconciliation objectives.
2. Map
Define explicit data mapping specifications, documenting exactly how source fields, structures, and enumerations translate to target schema equivalents.
3. Match
Establish primary and composite keys, normalization rules, numeric tolerance thresholds (currency rounding), and timing cut-off windows.
4. Reconcile
Compare datasets deterministically using automated, client-perimeter tooling built for large extracts, without cloud data egress.
5. Investigate
Systematically classify discrepancies: missing records, unexpected extra rows, transformed values, floating-point variances, or cut-off anomalies.
6. Automate
Package validated matching configurations into repeatable pipelines for continuous integration, regression testing, or recurring ledger balancing.
7. Evidence
Produce auditable reports and exception manifests that business stakeholders, compliance teams, and technical owners can inspect with confidence.
How reconciliation works in practice: an end-to-end example
A tangible specimen demonstrating our core verification mechanism: how raw records move from source through transformation mappings into target stores, where discrepancies emerge, and how each is deterministically diagnosed.
1. The Source & Mapping Contract
Source: Core Banking Trading OLTP (Postgres / SQL extract).
Target: General Ledger & Regulatory Reporting Store (Snowflake / Analytical Warehouse).
• Primary Reconciliation Key:
TRX_ID → TRANSACTION_REF• Value Mapping:
AMOUNT_RAW rounded to 2 decimal places → POSTED_AMOUNT• Cut-Off Window: Calendar day cut-off at
23:59:59 UTC (records after 00:00 UTC post to next business day)• Instrument Mapping: Source legacy codes (
FX_FWD, EQ_SWP) mapped to ISO 20022 taxonomy in Target
2. Source → Mapping → Target Comparison Matrix
Deterministic reconciliation run across sample portfolio cohort (4 sample records, 3 mismatches isolated):
| Transaction Key | Source Record | Transformation Rule | Target Ledger Record | Reconciliation Status |
|---|---|---|---|---|
TRX-9401 |
EUR 124,500.00 TS: 14:22:05 UTC | Code: EQ_SWP |
1:1 Direct Post ISO: SWAP_EQ |
EUR 124,500.00 ISO: SWAP_EQ |
Match (Clean) |
TRX-9402 |
USD 84,210.456 Float precision 3dp |
Half-Up Rounding to 2dp Expected: USD 84,210.46 |
USD 84,210.45 Truncated (0.01 variance) |
Mismatch #1: Rounding Variance |
TRX-9403 |
GBP 350,000.00 TS: 23:59:58 UTC (Day T) |
Target Cut-off: 23:59:59 UTC Expected in Day T Batch |
Absent in Day T Shifted to Day T+1 batch |
Mismatch #2: Timing Cut-Off Lag |
TRX-9404 |
CHF 15,200.00 Code: FX_FWD_ND (Non-deliverable) |
ISO 20022 Mapping Table Requires code FWD_NDF |
Unprocessed / Unjoined Rejected by target ingest schema |
Mismatch #3: Schema & Key Rejection |
3. Discrepancy Analysis & Diagnostic Explanations
Instead of vague error alerts, our reconciliation workflow isolates each exception to its root engineering cause with actionable remediation:
Mechanism & Explanation: The target ETL pipeline used integer bitwise truncation (CAST AS NUMERIC(18,2) in legacy SQL without explicit rounding mode), stripping the third decimal instead of applying half-up rounding. Over millions of daily micro-transactions, this produces systematic unbalancing in downstream financial general ledgers.
Fix: Enforce deterministic banker's rounding (ROUND_HALF_EVEN) in the ingestion contract before target persistence.
Mechanism & Explanation: Source executed trade at 23:59:58 UTC. Due to a 4-second replication queue latency across the message broker, the target ingest pipeline stamped arrival at 00:00:02 UTC and partitioned the record into Day T+1. The trade was not lost, but Day T balance showed a £350,000 shortfall and Day T+1 showed a matching surplus.
Fix: Bind reconciliation windows to the source transaction execution timestamp rather than consumer pipeline arrival timestamp.
Mechanism & Explanation: The legacy source introduced a variant instrument code FX_FWD_ND that was absent from the target warehouse lookup dictionary. The strict target schema rejected the entire row into a dead-letter queue without throwing a fatal pipeline exception, leaving the target ledger silently short CHF 15,200.
Fix: Update reference mapping enum dictionary and add automated dead-letter queue intake validation alerts.
Migration Assurance
Prove your data survives migration and transformation intact.
When cutting over between core platforms, databases, or warehouse layers, migration teams need independent, automated verification that records arrived completely, accurately, and without silent corruption.
Core Migration Assurance deliverables:
- Completeness & Volume Proofs: Verifying row counts and entity coverage across all migrated partitions.
- Pre/Post Cutover Comparisons: Fast, deterministic reconciliation during dry-runs and live go-live cutover windows.
- Transformation Integrity: Auditing complex business logic transitions to verify calculations match historical baselines.
- Cutover Sign-Off Reports: Defensible documentation for steering committees, risk officers, and auditors.
The Reconciliation Diagnostic
A bounded, high-impact initial engagement to prove whether two critical systems actually agree.
Before launching an open-ended transformation or migration initiative, bring us two representative datasets. We will establish the matching contracts, execute automated comparison, and deliver an auditable discrepancy report.
ShipDataFast Reconciliation Engine
To deliver reproducible results without recurring cloud dependencies, we build bespoke local-first reconciliation tooling that runs inside your security boundary, or run the reconciliation ourselves from files and rules you supply.
Discuss reconciliation consulting
Let's discuss your cross-system comparison requirements, migration timeline, and reconciliation criteria.