Learn
What is data reconciliation?
Data reconciliation is the process of comparing two sets of data that should agree, finding every difference, and explaining or fixing each one. In a data warehouse migration it is the proof that the new platform gives the same answers as the old one. A migration is not finished when the code converts. It is finished when the numbers reconcile.
Definition
What does data reconciliation mean?
Reconciliation answers one question: do these two data sets agree, and if not, where and why? The two sets are usually a source and a target: a legacy table and its migrated copy, a ledger and a report, or the output of an old procedure and the output of its replacement. A reconciled result is one where every difference has been found and accounted for.
- Data validation checks one data set against rules, such as a column that must not be null.
- Data reconciliation checks two data sets against each other.
- Data testing is the wider activity; reconciliation is the part that proves equivalence.
Methods
What are the main data reconciliation checks?
Four checks are used, usually in this order. Each one is stronger and more expensive than the one before it.
| Check | What it compares | What it proves | What it misses |
|---|---|---|---|
| Schema | Tables, columns, data types and nullability | The structure arrived | Anything about the values |
| Row count | Number of rows in source and target | Nothing was dropped or duplicated in bulk | Wrong values in the right number of rows |
| Aggregate | Sums, minimums, maximums and distinct counts per column | Totals agree, which catches most arithmetic and filter errors | Errors that cancel out, and differences in individual rows |
| Row or hash level | Each row, by key, value by value or by a hash of the row | Every value agrees | Nothing in the data compared; it is the slowest and needs a stable key |
Counts and totals can agree while individual rows differ. A report that stops at row counts has not proved the values.
Worked example
What does a reconciliation look like in practice?
Take a nightly procedure that loads an orders table. Run the old procedure and the converted one on the same input, then compare the two outputs.
- Schema: both outputs have the same 14 columns and types.
- Row count: both have the same number of rows.
- Aggregate: the order total differs by a few cents.
- Row level: the differing rows all have a discount applied. The old platform rounded half up; the new one rounds half to even.
- Resolution: fix the rounding rule in the converted code, rerun, and all four checks pass. The difference and the fix are recorded.
The totals looked close enough to wave through. Only the row-level check showed which rule had changed.
In a migration
How do you reconcile a data warehouse migration?
Reconcile each converted object, on the same inputs, before cutover. Reconciling only the final tables shows that something is wrong, not which procedure caused it.
- Freeze or snapshot the inputs so both sides read the same data.
- Run the legacy object and the converted object on those inputs.
- Compare the outputs with all four checks, and record the result per object.
- Explain every difference: a conversion defect, a platform difference such as rounding, collation or NULL handling, or a bug in the original.
- Keep the report. It is the evidence an auditor asks for.
Common causes
Why do reconciliations fail?
Most differences come from a short list of platform behaviours, not from missing data.
- Rounding and numeric precision rules that differ between platforms.
- NULL handling in joins, comparisons and aggregates.
- Collation and case sensitivity in string comparisons.
- Time zones and date arithmetic.
- Row order assumed by the old code and not guaranteed by the new platform.
- Inputs that changed between the two runs.
With Novuz
How does Novuz reconcile a migration?
Novuz produces a reconciliation report for every converted object, on the same inputs, inside your environment. Reconciliation is typically above 95% on first pass, and each remaining difference is resolved with an engineer approving the change. Novuz can also reconcile objects it did not convert.
FAQ
Frequently asked questions
What is data reconciliation in simple terms?
It is checking that two sets of data that should match do match, and explaining every difference. In a migration, it proves the new platform gives the same answers as the old one.
What is the difference between data reconciliation and data validation?
Validation checks one data set against rules, such as required fields or allowed ranges. Reconciliation compares two data sets against each other, such as a source table and its migrated copy.
Are row counts enough to reconcile a migration?
No. Row counts show that nothing was dropped or duplicated in bulk. They cannot show a wrong value in a row. Aggregate and row-level checks are needed to prove the values.
What are the types of data reconciliation checks?
Four are common: schema comparison, row counts, aggregates such as sums and distinct counts per column, and row-level or hash-level comparison by key.
When should reconciliation happen in a migration?
For each object as it is converted, and again in the parallel run before cutover. Leaving it to the end makes every difference harder to trace to its cause.
Written by Ted Ghose, Co-founder and CTO, Novuz
Last reviewed:
Find out what your logic debt is costing you.
Every object counted, lineage, dead code and a fixed price in three business days. $3,000, credited.
Not sure which door? The scorecard takes ten minutes and tells you.