Data reconciliation: techniques, and a worked retail example

#What data reconciliation is, and isn't

Validation asks whether one dataset is well formed: no empty keys, dates that are dates, amounts in range. Reconciliation asks whether two datasets agree. The difference matters, because two systems can each be perfectly valid and still disagree with each other.

It happens in three places: once, after a migration; after every load, between a source and a data warehouse; and every day, between live systems that should tell the same story, such as the till system and the ERP.

#The example: one Monday

An invented chain with 120 stores. The till system's extract for Monday has one line per store and department, 2,412 lines. The ERP's sales journal for the same day, at the same detail, has 2,410. Below, each technique is applied in turn, and each finds a little more.

#1. Row counts

Count the lines on each side.

SELECT COUNT(*) FROM till_sales WHERE business_date = '2026-11-02';  -- 2,412
SELECT COUNT(*) FROM erp_sales  WHERE posting_date  = '2026-11-02';  -- 2,410

Catches: rows missing or added in bulk. Misses: which rows, and anything that cancels out. One missing row and one duplicated row give the same count.

#2. Control totals

Add up the measure on each side: net sales of 1,184,220.40 at the tills against 1,181,347.89 in the ERP, so 2,872.51 apart.

Catches: amounts that changed or went missing. Misses: where they are. A total that agrees can still hide a shortage in one store and a surplus in another.

#3. Key matching

Join the two sides on the key that names a line, here store, department and date, with a full outer join so that lines missing from either side survive.

SELECT COALESCE(t.store, e.store) AS store,
       COALESCE(t.department, e.department) AS department,
       t.net_sales AS till_net,
       e.net_sales AS erp_net
FROM till_sales t
FULL OUTER JOIN erp_sales e
  ON e.store = t.store
 AND e.department = t.department
 AND e.posting_date = t.business_date
WHERE t.store IS NULL OR e.store IS NULL;

Monday: 2,410 keys on both sides, and two only at the tills: ST-047's Accessories and Footwear, 2,392.30 between them. Nothing is only in the ERP.

Catches: exactly which rows are missing, and on which side. Misses: rows present on both sides with different values.

#4. Field comparison

For the keys found on both sides, compare each measure, allowing a tolerance for rounding.

Monday: twelve lines differ. Nine by one to four cents, which is rounding and sits inside a five-cent tolerance. The other three are all at ST-014 and add up to 480.00.

Catches: values that changed, line by line. Misses: nothing on the columns you compare. Its cost is that you must name every column and choose a tolerance for each.

#5. Row hashes

When a table has dozens of columns, compare one hash per row instead of every column. Clean the values first, trimming spaces, fixing case and number formats, or every row will differ.

-- SQL Server
SELECT store, department,
       HASHBYTES('SHA2_256', CONCAT_WS('|', net_sales, tax, discount, units)) AS row_hash
FROM till_sales;

-- PostgreSQL
SELECT store, department,
       md5(concat_ws('|', net_sales, tax, discount, units)) AS row_hash
FROM till_sales;

Catches: a change in any column, cheaply, even on very wide tables. Misses: tolerance, since a one-cent rounding difference changes the hash, and it says which row differs, not which column.

#6. Compare at a shared grain

When the two systems do not keep the same detail, such as receipts on one side and a daily journal per store on the other, compare at the finest level both of them keep. In this example that is store × department × day. Finer is impossible; coarser hides problems that cancel out.

#What each technique finds

#Explaining the five

Monday ends with five lines to explain: ST-047's two missing departments, and ST-014's three differing ones. The nine rounding lines are inside the tolerance and need nothing.

Explaining means following the evidence, not guessing. ST-047's lines are in the till extract and not in the ERP, so the next question is whether they ever reached the ERP's interface. ST-014's three lines all contain items with no tax code in the ERP, which the ERP refused to post. Those are the same two questions behind most of the POS and ERP sales differences a retailer sees.

#In ETL, after a migration, and between live systems

• After each ETL load: row counts and control totals per batch, then key matching on the target. They are cheap enough to run every time.
• After a migration: all six techniques, once, on everything, with someone signing off the result.
• Between live systems: key matching and field comparison at a shared grain, every day, with a tolerance and a history. Two systems that agreed yesterday can drift today.

For two files rather than two databases, the same steps work in a spreadsheet: see how to compare two Excel files.

#Questions people ask

#What is data reconciliation?

Checking that two datasets that should hold the same facts agree, and explaining the rows where they don't, for example sales in the till system and in the ERP, or a table before and after a migration.

#What is the difference between data validation and data reconciliation?

Validation checks one dataset against rules: required fields, formats, ranges. Reconciliation compares two datasets with each other. Data can pass validation on both sides and still fail reconciliation.

#What are the main data reconciliation techniques?

Row counts, control totals, key matching with a full outer join, field-by-field comparison with a tolerance, row hashes for wide tables, and comparing at a level of detail both systems keep.

#How do you reconcile data between two databases?

Bring both sides to the same grain, join them on the key with a full outer join so missing rows survive, then compare each measure with a tolerance. If the databases cannot be queried together, export both sides and compare the files on the same key.