R7 — Building a reconciliation system: the pipeline, the break lifecycle and the controls

R6 covers what reconciliation is and why it is the strongest correctness argument you have. This section is how you would actually build one, which is the follow-up when an interviewer likes the first answer.

flowchart LR
  ING["1. Ingest<br/>land raw files immutably,<br/>hashed and dated"] --> NRM["2. Normalise<br/>one canonical record<br/>per format adapter"] --> LD["3. Load<br/>partition by rail + value_date<br/>carry last cycle's unmatched"] --> MT["4. Match"]
  MT --> T1["Tier 1: exact on UETR / payment ID"]
  MT --> T2["Tier 2: amount + currency +<br/>counterparty + date window"]
  MT --> T3["Tier 3: many-to-one netted lines"]
  T1 --> CLS["5. Classify and repair"]
  T2 --> CLS
  T3 --> CLS
  CLS -->|"known safe pattern"| AR["Auto-repair: correcting entry,<br/>attributable to rule + run"]
  CLS -->|"anything else"| BRK["Open a break — durable entity<br/>with its own lifecycle"]
  AR --> RPT["6. Report: metrics, ops queue,<br/>daily control statement"]
  BRK --> RPT

What it is, in one line. A pipeline that independently proves two or three records of the same money movement agree, classifies every disagreement, and drives each one to resolution. It is a control, not a report: its output is a decision about whether you can trust your own books.

Why it earns its cost. It detects money loss that no system metric shows — duplicate sends, silent drops, fee and FX drift, fraud, and bugs that only appear at the boundary with an external party. It satisfies audit and regulatory obligations. It quantifies exposure as a number (value unreconciled, oldest break) that you can put in front of an executive. And it is the safety net that lets you ship faster: a team that reconciles daily can tolerate a bug for hours rather than months.

Scenario 9 walks the pipeline below at the level of a single run: independent read, tiered matching, two breaks classified differently, one auto-repaired and one replayed.

Architecture

Six stages, each independently restartable:

1. Ingest. Pull rail files and statements on their schedule, not yours: camt.053 end-of-day statements, card settlement files, NACHA return files, PSP reports. Land raw files immutably in object storage first, hashed and dated, so a run is always reproducible against exactly what arrived.
2. Normalise. Parse each format into one canonical record: source, source_ref, our_ref, amount_minor, currency, value_date, counterparty, direction, status, fees. All downstream logic works on the canonical form, so adding a rail means writing one adapter, not touching the engine.
3. Load. Stage into a partitioned table keyed by (rail, value_date). Include the previous cycle's unmatched items in the working window, since a break usually clears one cycle late.
4. Match. Tiered, strongest first. Tier 1: exact on your end-to-end reference (UETR or your payment ID) — this is why you stamp every outbound payment with one. Tier 2: composite key of amount, currency, counterparty and a value-date window. Tier 3: many-to-one, where a rail nets several payments into one settlement line, matched by summing candidates to the line total. Anything unmatched after tier 3 is a break.
5. Classify and repair. Apply tolerance and repair rules (fee netting, FX rounding, known cutoff drift). Auto-repairs post correcting ledger entries; everything else opens a break.
6. Report. Emit metrics, feed the operations queue, and produce the daily control statement that finance signs off.

Data model

CREATE TABLE recon_run (
  id         bigserial PRIMARY KEY,
  rail       text NOT NULL,
  value_date date NOT NULL,
  file_hash  bytea NOT NULL,
  state      text NOT NULL,        -- running | complete | failed
  UNIQUE (rail, value_date, file_hash)   -- makes reruns idempotent
);

CREATE TABLE recon_match (
  id             bigserial PRIMARY KEY,
  run_id         bigint NOT NULL,
  ledger_txn_id  bigint,
  external_ref   text,
  tier           int NOT NULL,     -- which rule matched
  confidence     text NOT NULL     -- exact | composite | grouped
);

CREATE TABLE recon_break (
  id           bigserial PRIMARY KEY,
  run_id       bigint NOT NULL,
  type         text NOT NULL,      -- missing_external | missing_ledger | amount | timing
  amount_minor bigint,
  currency     char(3),
  first_seen   timestamptz NOT NULL,
  state        text NOT NULL,      -- open | investigating | auto_repaired | resolved | written_off
  resolution   text,
  assignee     text
);

The break is a durable entity with its own lifecycle, not a line in a report. That single design decision is what turns reconciliation from a spreadsheet exercise into a system: breaks age, get assigned, get resolved, and the same break recurring daily becomes a visible pattern rather than a fresh surprise.

Properties that matter

Idempotent and replayable. Reruns are routine — after a fix, after a late file. Key the run on file hash and make correcting entries idempotent, or a rerun double-posts.

Independent path. The reconciler must not reuse the code that produced the payments, or a bug in that code cancels out on both sides and the check proves nothing. Read the ledger directly; parse the rail file independently.

Scale. This is a batch join, partitioned by rail and date, and it parallelises cleanly. Millions of rows per day is a Spark job or a well-indexed set-based SQL run, not row-by-row processing. Keep the matching window bounded, typically the last few cycles.

Near-real-time where it pays. Batch is the backbone, but for irrevocable rails add a streaming check on stuck payments — anything in submitted past its expected settlement window gets flagged within minutes rather than at end of day.

Metrics and alerting. Match rate by rail and tier, break count by type, oldest open break, total value unreconciled, auto-repair rate, and run completion against the expected file arrival time. A file that never arrived is itself an incident. Alert on break age and unreconciled value, not on raw counts.

Controls. Every auto-repair rule is reviewed and versioned, correcting entries are attributable to a rule and a run, write-offs need an approval threshold, and the whole thing is auditable end to end. Finance owns the sign-off; engineering owns the pipeline.

The manager framing. Reconciliation lag is an SLO with an owner, not a chore that slips. Staff it deliberately — the failure mode is a team that builds it once, watches the break queue grow, and quietly stops looking.

What to say in the interview

"I'd build it as six restartable stages — ingest raw files immutably, normalise to one canonical record, load partitioned by rail and date, match in tiers starting with the end-to-end reference, classify and auto-repair the known patterns, then report. The design decision I'd defend is making the break a durable entity with its own lifecycle rather than a line in a report, so breaks age, get assigned and get resolved. And the run reads the ledger directly rather than through the code that produced it — otherwise a bug cancels itself out on both sides and the check proves nothing."