What reconciliation proves
Data reconciliation compares source, intermediate, and target data to determine whether records and values were transferred and transformed as intended.
Source
↓
Expected Records / Values
↓
Transformation
↓
Target
↓
Compare
↓
Exceptions
Start with record-level control totals
- Source row count
- Accepted row count
- Rejected row count
- Target row count
- Insert count
- Update count
- Delete count
- Duplicate count
Reconcile business keys
Compare key identifiers to detect missing and unexpected records.
Example
Source customer IDs = 1,000
Target customer IDs = 998
The two missing keys should be identified and explained—not hidden by aggregate totals.
Target customer IDs = 998
The two missing keys should be identified and explained—not hidden by aggregate totals.
Reconcile financial or numeric totals
For monetary, quantity, or metric data, compare totals and grouped totals by relevant business dimensions such as date, product, region, or account.
Validate transformations
| Transformation | Reconciliation approach |
|---|---|
| Mapping | Compare source code to expected target category |
| Calculation | Recompute formula independently |
| Aggregation | Compare source detail totals with target summary |
| Join | Validate matched/unmatched records and cardinality |
| Deduplication | Confirm survivor rule and removed duplicates |
Investigate exceptions explicitly
Differences should be classified as expected transformation, source-quality issue, rejected record, late arrival, duplicate, defect, or unresolved exception.
Automate repeatable reconciliation
SQL comparisons, data-diff utilities, validation frameworks, and pipeline checks can automate high-volume reconciliation while preserving exception detail.
Do not stop at “counts match.” Two datasets can contain the same number of rows and still contain different records or incorrect values.