🛡 Quality, Testing & Ops · Chapter 5

Reconciliation: proving two systems agree

Two systems that should agree never quite do. Reconciliation is the discipline of explaining every rupee of the difference — and the explained part is the bit people forget.

Study this lesson free in the app →

The basics

Reconciliation is proving that two systems hold the same facts — that the warehouse matches the source database, that rides match payments, that payments match what the bank actually settled — and explaining every difference.

Why it exists: data is copied, transformed and re-derived many times between where it's created and where it's reported. Each hop can drop, duplicate or alter rows. Tests on one table can't see that; only comparing two tables can. For anything involving money, auditors and finance teams will ask you to prove the numbers agree.

The mental model — zoom levels:

1. Counts — 95,214 rides in source, 95,214 in warehouse?
2. Control totals — sum of fares equal?
3. Key-level diff — which ride_ids are in one side only?
4. Row/column diff — for matching keys, which values differ?

Start coarse and cheap, zoom in only when a level disagrees.

The other half: a difference isn't automatically a bug. Rides at 23:58 paid at 00:01, payments still pending, refunds, rounding — these are expected breaks that you classify and explain. What's left after that is the real problem.

Words to know: control total, break, tolerance, cut-off, anti-join.

Explained

Meghalaya Cabs, 4 November.

• Rides table, completed fares: ₹38,42,610
• Payment gateway, captured: ₹38,19,880
• Bank settlement, credited 6 Nov: ₹37,61,240

Three systems, three numbers, all ‘correct’. The CFO wants to know which one is revenue and why the others differ. Reconciliation is the answer to that question — line by line.

Closing the shop till

At 10 PM the shop owner in Police Bazaar counts the till: ₹41,300. The billing machine says ₹43,150 in sales.

She doesn't panic. She explains:

• ₹1,500 was paid by UPI — it's in the bank, not the till.
• ₹300 was a refund handed back in cash.
• ₹50 is unexplained.

₹1,850 difference, ₹1,800 explained. The ₹50 is the only number that needs investigating — and she can only find it because she explained the rest first.

That's reconciliation: categorise every difference until what remains is small enough to chase.

Zoom in only when you have to

Counts can match while totals don't (a fare changed); totals can match while keys don't (two errors cancelling). That's why serious recon goes at least to level 3 for money.

The key-level diff

WITH r AS (
  SELECT ride_id, fare_inr FROM fct_rides
  WHERE status = 'completed'
    AND completed_at >= '2026-11-04 00:00 +05:30'
    AND completed_at <  '2026-11-05 00:00 +05:30'
), p AS (
  SELECT ride_id, sum(amount_inr) AS paid_inr     -- a ride may have 2 attempts
  FROM gateway_payments
  WHERE state = 'captured'
    AND captured_at >= '2026-11-04 00:00 +05:30'
    AND captured_at <  '2026-11-05 00:30 +05:30'  -- 30-min grace for late capture
  GROUP BY ride_id
)
SELECT coalesce(r.ride_id, p.ride_id) AS ride_id,
       r.fare_inr, p.paid_inr,
       CASE
         WHEN p.ride_id IS NULL THEN 'missing_in_gateway'
         WHEN r.ride_id IS NULL THEN 'missing_in_rides'
         WHEN abs(r.fare_inr - p.paid_inr) > 1 THEN 'amount_mismatch'
         ELSE 'matched'
       END AS recon_status
FROM r FULL OUTER JOIN p ON r.ride_id = p.ride_id;

Note the explicit timezone, the grace window, and aggregating payments before joining so retries don't fan out.

Explaining the gap

Rides, completed fares (4 Nov)            ₹38,42,610
  − cash rides (not in gateway)             −14,200
  − captured after cut-off (paid 5 Nov)      −6,950
  − payment pending / failed, retry due      −1,580
= Expected in gateway                      ₹38,19,880   ✓ matches gateway
  − gateway fees + GST on fees              −52,440
  − refunds netted in settlement             −6,200
= Expected bank credit                     ₹37,61,240   ✓ matches bank
Unexplained                                       ₹0

This is the bridge (or walk) finance actually wants: start at one system's number, end at the other's, every step a named reason with its own query.

At scale: hash, then drill

Reconciling 2 billion rows after a warehouse migration with a full outer join is expensive. Instead:

1. Per partition (say per day), compute count(*) and an order-independent fingerprint, e.g. sum(hash(ride_id || fare_inr || status)) — or bit_xor of row hashes — on both systems.
2. Compare the 1,000 day-level fingerprints. Typically 995 match.
3. Run the full key-level diff only on the 5 that don't.

Watch out: both systems must normalise identically before hashing — same decimal scale (245.5 vs 245.50), same timestamp format and timezone, same null representation, same string trimming. Otherwise every partition ‘differs’ and the method is useless.

Tools like data-diff-style utilities and warehouse-native checksums do exactly this bisection.

Migration weekend

Meghalaya Cabs is moving rides from Postgres to Snowflake. Cut-over is Sunday.

Saturday: counts match for all 730 days. Totals match for 727. Three days differ by exactly ₹0.01 × thousands of rows.

Cause: Postgres numeric(10,2) vs a float column in the new staging layer — rounding drift on fares like ₹245.15. Not a lost ride, but finance would have found it in the first month's close.

Fix the type to number(12,2), rerun, all 730 days match on hashes. That's the sign-off artifact: a table of 730 rows, each with count, total and hash from both sides, all green.

Gotchas

• Mismatched cut-offs. One side in UTC, the other IST: every day ‘loses’ 5.5 hours of rides to its neighbour.
• Joining before aggregating. A ride with two payment attempts doubles in the join; your recon creates the error it's looking for.
• Only comparing totals. +₹500 missing and +₹500 duplicated nets to zero. Go to key level for money.
• No tolerance. Float rounding makes = fail on ₹0.0000001. Agree a tolerance per measure.
• Explaining away everything as ‘timing’. Timing breaks must clear the next day. If they don't, they weren't timing.
• Running recon once. Run it daily and trend the unexplained amount; a slow rise is an early warning.

Words to know

In two minutes

• Reconciliation compares two systems that should agree and accounts for every difference.
• Zoom levels: counts, then control totals, then key-level diffs, then column-level diffs — coarse first.
• A full outer join on the business key classifies every record: matched, missing in target, missing in source, value mismatch.
• Define the matching rule up front: key, time window (cut-off), tolerance, currency and rounding.
• Expected breaks — timing, pending, refunds, fees — are classified and explained, not ignored.
• Huge tables are reconciled by hashing per partition, then drilling only into partitions whose hashes differ.
• Money flows often need three-way recon: rides → payment gateway → bank settlement.

Test yourself

There are 8 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.