Tom Xiao

Stage 3 · Data platform case

Reconciliation platform

Balances held with several outside parties never quite matched the internal ledger. I designed and built the platform that compares them every business day and sends real differences to a person.

What I did
I designed and built it with a small team
Status
Running daily at a financial platform
Principle
Counts before amounts

One business day, compared

Four steps, in this order. The day is unified, counts are compared, then amounts, and each account lands in one of four states. Everything here is sample data.

reconciliation · business day 2026-11-20sample data
1 unify the day2 compare counts3 compare amounts4 classify
acct-00112 / 1210,450.00 / 10,450.00matched
acct-0028 / 85,200.00 / 5,200.40acceptable
acct-0039 / 8skipped, counts differmismatched
acct-004no statement yetwaitingcarried
4 compared1 to manual approvalacceptable = inside the set tolerance
awaiting approvalacct-003: a person approves or rejects the difference. Nothing is adjusted automatically.
done

How it decides

One business day

  • Outside parties cut their day at different times, so both sides are first moved onto the same business day
  • Nothing is compared across days

Counts before amounts

  • If the number of records differs, the amounts are not compared at all
  • A count gap is the cheapest alarm there is

Four outcomes

  • matched, acceptable, mismatched, carried
  • Acceptable means inside a tolerance. Carried means it cannot be compared yet

Real differences go to a person

  • A mismatch becomes an approval task with the evidence attached
  • The platform never writes the difference away

The day about 40,000 rows became about 91

One upstream sub-account endpoint also returned the main account's balance. Deduplication ran after the sub-account rows were expanded, so distinct rows looked like repeats and were dropped. Switch the order and replay it.

daily load · incident replaysample reproduction
  1. Fetch40,000
  2. Expand sub-accounts40,000
  3. Deduplicate91
  4. Load91
rows loaded91 of about 40,000 expected

Each sub-account response also carried the main account’s balance. Deduplicating after the expand step treated distinct rows as repeats, and the load kept almost nothing.

done

Fix 1 order

Deduplication moved ahead of the expand step, so it only sees raw responses and removes only the repeated main-account balance.

Fix 2 deploy

The job can be deployed only from the repository. A hand-edited copy cannot reach production.

What I took from it

  • Order of steps is part of correctness. The same steps in a different order gave a different answer.
  • Compare counts first. A row count that falls by a few hundred times is visible long before any amount is.
  • Close the route that let an unreviewed copy run, not just the bug.

Count the rows before you trust the totals.

Next