Money movement and reconciliation
Your ledger records what you intended; the bank's file records what actually settled. Reconciliation is the batch JOIN that forces those two truths to agree — and the exceptions it surfaces are where the real money is.
0. The hinge
The hinge of this case is a number, not an architecture. Reconciliation is conceptually trivial — JOIN two datasets on a key, look at the rows that don't match. What makes it forcing is the scale of the residue and the fact that you cannot fix it the way you'd fix any other data bug. You can't UPDATE a financial record to make it correct; mutating money is how you turn a small discrepancy into a fraud investigation. So the design is squeezed between two hard walls: the match must be almost perfect because every miss is human labour, and the corrections must be append-only and reversible because you are moving real money to fix real money.
1. Clarify the contract
Treat the prompt as a product contract before a box diagram. The system must:
- Ingest provider settlement files (daily batches: acquirer payouts, bank statements, network clearing reports) and store the raw bytes immutably.
- JOIN provider rows against the internal ledger on a stable match key, and classify every row: matched, internal-only, provider-only, or amount-mismatch.
- Open exception cases for everything that didn't auto-match, age them, and escalate by amount × age.
- Post compensating corrections — never mutations — once an exception is resolved, linked back to the case for audit.
- Report completeness: per provider, date, and currency, what fraction reconciled and what dollar value is still in limbo.
And explicitly what it need NOT do: it is not the ledger itself (that double-entry machinery is C33's job — we only read it here), it is not a real-time path (provider files arrive on a daily cadence; recon is intrinsically batch), and it is not the place that invents idempotency keys (C35 owns that — recon merely relies on row identity being stable).
2. Put numbers on it
The load-bearing arithmetic is the one that turns a tiny accuracy number into a staffing crisis. Take a mid-size payments platform:
- The daily settlement file is 1,000,000 rows (one row per settled transaction).
- The auto-match rate is 99.5%. That sounds excellent — until you compute the complement.
exceptions/day = 1,000,000 · (1 − 0.995) = 1,000,000 · 0.005 = 5,000
Five thousand cases a day, every day, each needing a human to look at it. Now watch the cliff. Suppose a provider tweaks their file format and your match rate slips from 99.5% to 99.0% — a half-point drop that a dashboard might not even flag:
exceptions/day = 1,000,000 · (1 − 0.990) = 10,000 → a 0.5-point drop doubles the manual queue
This is the recon analogue of lesson 02's utilisation cliff: the cost lives in the tail, and small movements in the match rate are multiplied by the enormous denominator. If a reviewer clears ~80 cases/day, the baseline 5,000 needs ~63 analysts; the slip to 10,000 needs ~125. Accuracy of the matcher is the headcount budget.
The exceptions don't just sit in a flat list — they age, and aging is what prioritises the queue. A discrepancy worth $4 that's a day old is noise; a $40,000 discrepancy that's a week old is a fire. So cases escalate by amount × age, bucketed:
| Bucket | Meaning | Routing |
|---|---|---|
| T+1 | Unmatched after the next day's file (one settlement cycle missed). | Normal analyst queue, sorted by amount. |
| T+3 | Still open after three cycles — provider delay or a real break. | Escalate; flag any case above a dollar threshold to a senior reviewer. |
| T+7 | A week unresolved — material risk to the financial close. | Block period close above threshold; finance + provider relations involved. |
The escalation weight is roughly priority = amount · days_open, so a $50k break crosses into "senior, now" after a single day, while a $5 break can drift to T+7 before anyone cares. This keeps 63 analysts pointed at the dollars that matter instead of the row count.
3. Data model & API
Four entities, and the discipline is that raw files and corrections are append-only. Nothing in this model is ever mutated in place.
| Entity | Holds | Key property |
|---|---|---|
raw_settlement_file | The provider's bytes verbatim + checksum + parser_version. | Immutable. The source of truth for replay. |
provider_row | Parsed row: provider_txn_id, amount, currency, settled_at, file_id. | Derived from raw + a parser version; re-derivable. |
recon_case | An exception: classification, the two rows involved, status, age bucket. | One per unmatched/mismatched row identity. |
correction_entry | A compensating ledger posting, linked to the case that justified it. | Append-only; references the original, never edits it. |
The API stays small and expresses the operation, not the storage:
| Operation | Why it exists |
|---|---|
import_settlement_file(provider, date, bytes) | Land the raw file immutably; record checksum + parser version. Idempotent on (provider, date, checksum). |
run_match(provider, date) | The batch JOIN. Produces the matched/exception split. Re-runnable. |
resolve_case(case_id, decision) | Close an exception, optionally emitting a compensating entry. |
completeness_report(provider, date, currency) | Fraction reconciled and dollar value outstanding — the close gate. |
4. Linearized design
Follow one daily file through the system. The first bottleneck — the matcher's accuracy — surfaces naturally at step 4.
- Land raw, immutably. The provider file arrives; store the exact bytes with a checksum and the
parser_versionthat will read it. This file is now frozen forever — it is the immutable source from which every derived view is computed (DDIA Ch. 11's derived-data principle: keep the source, regenerate the rest). - Parse to rows. A versioned parser turns bytes into
provider_rowrecords. Crucially the parse is a function of (raw file, parser version), so it can be re-run later with a fixed parser. - Pull the internal side. Read the ledger for the same window — the postings C33 produced. We only read; the ledger remains the authority for balances.
- JOIN on the match key. The heart of the case: a batch JOIN of two datasets on the stable provider reference (the same reference the payment carried out to the provider on the way in). This is the matcher whose accuracy we just costed; everything that falls out of the equi-join is an exception.
- Classify the residue into four buckets: matched (amount + key agree), internal-only (we think we paid, no provider row), provider-only (they report a settlement we have no record of), and amount-mismatch (key agrees, money doesn't).
- Open / age cases for the non-matched buckets, weight by amount × age, and feed the corrections queue.
- Resolve & report. Humans (or the narrow auto-correction path) close cases by appending compensating entries; completeness reporting gates the accounting-period close.
5. Deep dives
Why corrections must be compensating entries, never mutations
Say the provider-only bucket shows a $200 settlement we never recorded. The naive fix is to "add the missing record" — write a row that makes the ledger match the file. That is mutation, and it is wrong for two reasons. First, auditability: a regulator (or your own future self debugging) needs to see that on day N you discovered a discrepancy and on day N posted a correction because of case #1234. If you edit history, the discrepancy never existed and the correction has no justification trail. Second, and worse, "moving money to fix money" is itself a money movement that can be wrong. The very process that found the gap — your matcher, your parser — might be the thing that's broken. If you auto-mutate the ledger to close the gap and the gap was an artefact, you've now created a real error out of a phantom one.
So every resolution is an append: a compensating entry that references the original and the case. This is DDIA Ch. 7's compensating-transaction pattern (and the bookkeeper's centuries-older rule: you never erase ink, you post a reversing entry). It gives you a clean property — the ledger is the sum of an immutable sequence of postings, so any state is reconstructable and any correction is reversible by appending its inverse. The double-entry mechanics that make this balance are C33's anchor; here we only insist that the recon system emits corrections, never patches.
Reconciliation is a batch JOIN — and that framing buys you everything
Strip away the finance vocabulary and step 4 is a relational JOIN of two datasets keyed on the provider reference: matched rows are the inner join, internal-only is the left anti-join, provider-only is the right anti-join, amount-mismatch is the inner join filtered to amount_a ≠ amount_b. This is exactly DDIA Ch. 10's batch-processing model — two large immutable inputs, a deterministic transform, a derived output — and recognising it gives you three concrete wins. (1) It's restartable: a batch job over immutable inputs can be killed and re-run with identical output, so a crashed match at 2 a.m. just runs again. (2) It scales by partitioning the key (lesson 07): shard the JOIN by provider_txn_id range and a 1M-row file is embarrassingly parallel. (3) It's testable offline: because both inputs are stored, you can replay last Tuesday's match against this Tuesday's matcher to measure a code change before it touches production.
The contrast with real-time recon (the trade-off table's first row) falls straight out of this: streaming recon detects breaks within minutes instead of a day, but you give up the clean "two finite immutable datasets" model and inherit windowing and late-arrival complexity (lesson 11). Most rails are happy with batch; only high-risk, high-velocity rails justify the streaming machinery.
Idempotent reprocessing keyed by row identity
Because files get re-imported (a provider resends; you re-run after a fix), the match must be idempotent: running it twice produces one set of cases and one set of corrections, never two. The anchor for idempotency mechanics — insert-if-absent, key TTL, request-hash — is C35; here the only thing the recon system must supply is the key: a stable row identity. That identity is (provider, provider_txn_id, file_checksum) or, when the provider gives no per-row id, a content hash of the row's natural fields. With that key, reprocessing is a no-op for rows already matched, and a correction is posted at most once per (case, decision). The discipline mirrors C35's: the operation carries its own identity so the system can recognise a repeat. Get this wrong and a re-import double-counts every row in the file — 1M phantom dollars.
The sharpest version: a parser was buggy for a week — re-reconcile without double-correcting
This is the senior question, and it's where the immutable-source design pays off. A parser misread a fee column for seven days; you shipped corrections all week based on wrong rows. Now you have a fixed parser. The replay:
- Re-parse from raw, not from rows. Because step 1 stored the bytes immutably with their
parser_version, you re-run the new parser over the original files. You are regenerating derived data from an unchanged source — DDIA Ch. 11 in one move. Had you only stored the parsed rows, the bug would be baked in forever. - Recompute the match. The batch JOIN re-runs over the corrected rows, producing the correct exception set for those seven days.
- Diff against what you already did, don't blindly re-apply. Each correction is keyed by (case row-identity, decision). Reprocessing computes the target corrected state; the system posts only the delta between the corrections already booked and the corrections now required. Already-correct postings are recognised by their key and skipped (idempotent, C35); over-corrections are unwound by appending their reverse (compensating, C33).
The three properties together — immutable raw files + versioned parsers + idempotent reprocessing by row identity — are what make this safe. Without immutable raw, you can't re-derive. Without parser versioning, you can't tell which corrections were made under the bug. Without idempotency by row identity, re-running double-corrects. This trio is the entire answer, and it's why "store raw files vs parsed only" (trade-off row 3) is not really a trade-off for financial data — you store raw, full stop.
6. Trade-offs
| Choice | Buys | Costs | Choose when |
|---|---|---|---|
| Realtime recon vs batch recon | Detection in minutes, not a day. | Loses the clean two-immutable-datasets model; windowing + late-arrival complexity (11). | High-risk, high-velocity rails where a day of drift is unacceptable. |
| Strict auto-match vs loose / manual | Strict (key + exact amount) = near-zero false matches. | Strict pushes more rows into the exception queue → more labour. | Money is involved → bias strict; a false match is worse than a manual review. |
| Store raw files vs parsed only | Replayability — re-derive after any parser fix. | Storage cost (cheap: ~GB/day, retained for years). | Always, for financial data. The replay is non-negotiable. |
| Auto-correction vs human approval | Fast repair of the obvious. | Risk of moving money in the wrong direction off a bad signal. | Only small, deterministic, fully-explained mismatches under a ceiling. |
The sharpest of these is row 2 read together with the section-2 math. It's tempting to loosen matching (fuzzy amount tolerance, key-or-amount-or-date heuristics) to shrink the 5,000-case queue. Resist it: a loose match that wrongly pairs a $200 refund to a $200 payout doesn't reduce work, it hides a break and silently corrupts the ledger. The correct lever for queue size is a more accurate matcher (better references carried through the payment, better parser), not a more permissive one. Accuracy reduces labour honestly; permissiveness reduces it by lying.
7. Failure modes
| Failure | Mitigation |
|---|---|
| Provider file missing / late | Alert on expected-file absence; treat "no file" as an exception, not as "all matched." Block the accounting-period close until the file lands — silence must never read as success. |
| Parser bug (the sharp Q) | Versioned parsers over immutable raw files; re-derive and reprocess the delta idempotently by row identity. Never bake parsed output in. |
| Duplicate settlement row | Dedup on stable row identity (provider_txn_id + file_checksum); a second occurrence of the same identity is a no-op, not a second match. |
| File re-imported / match re-run | Idempotent by row identity (C35) — produces one case set, one correction per (case, decision); re-runs are no-ops. |
| Exception ages out unseen | Escalate by amount × age through T+1 / T+3 / T+7; block close above a dollar threshold so a stale six-figure break can't slip past. |
8. Interview prompts you should be ready for
- Why can't you just UPDATE the ledger to make it match the file? (senior answer) Two reasons: audit (a regulator must see the discrepancy and the justified correction, not a silently edited history) and safety (the matcher/parser that found the gap might be the broken thing — auto-mutating turns a phantom break into a real one). You always append a compensating entry linked to the case; DDIA Ch. 7's compensating transactions, the bookkeeper's reversing entry.
- A 99.5% auto-match rate sounds great. Convince me it isn't enough. (senior answer) 0.5% of a 1M-row file is 5,000 manual cases per day. A half-point slip to 99.0% doubles that to 10,000 — roughly 63 analysts to 125. The match rate is the headcount budget; the cost lives in the small complement times a huge denominator, like the lesson-02 utilisation cliff.
- Your parser had a bug for a week. Re-reconcile without double-correcting. (senior answer) Re-parse from the immutable raw files with the fixed, version-bumped parser; recompute the batch match; then post only the delta between corrections already booked and corrections now required — idempotent by row identity (C35), unwinding over-corrections with compensating entries (C33). The trio immutable-raw + versioned-parser + idempotent-by-identity is what makes it safe.
- What exactly is the matching step? (senior answer) A batch JOIN of two immutable datasets on the provider reference: inner join = matched, left anti-join = internal-only, right anti-join = provider-only, inner join filtered on amount ≠ amount = mismatch. Framing it as DDIA Ch. 10 batch processing buys restartability, partition-by-key scaling (07), and offline replay of matcher changes.
- Batch or real-time reconciliation? (senior answer) Default batch — provider files are daily and the immutable two-dataset model is clean and restartable. Go real-time only for high-risk rails where a day of undetected drift is intolerable, and accept windowing/late-arrival complexity (lesson 11) as the price.
- Why is fuzzy/loose matching dangerous? (senior answer) A loose match that pairs the wrong $200 rows doesn't reduce work, it hides a break and corrupts the ledger silently. Shrink the queue with a more accurate matcher (stable references carried through the payment, better parser), never a more permissive one.
- The provider just didn't send today's file. What does your system do? (senior answer) Absence is an exception, not a pass. Alert, treat the whole day as unreconciled, and block the period close above threshold. A reconciliation system that reports "all green" because there was nothing to compare is the worst failure of all.
Related lessons
This case sits on top of three anchors and reads across them. The ledger it corrects — double-entry, posted vs available, balance snapshots — is taught in full at C33; here we only read it and emit compensating entries into it. The idempotency mechanics — insert-if-absent, key TTL, request-hash — that make reprocessing safe are C35's anchor; recon supplies only the row-identity key. On foundations, the compensating-correction and idempotent-reprocessing discipline is lesson 12's sagas and idempotency applied to derived data, and the batch-JOIN framing (with the real-time contrast) is lesson 11's batch-vs-stream divide. Completeness reporting and the aging escalation are an observability (lesson 16) surface — the recon dashboard is the financial SLO.