Lineage as a data property, not a report

Custodians agree on nothing. Not the format, not the schedule, not the field names, not the sign conventions, not what constitutes a settled position. Normalisation is not a parsing exercise, it is a semantic mapping exercise per source, and it is where the domain knowledge actually lives.

Reconciliation is a matching problem with no natural key. The same economic event appears in two feeds with different identifiers, different timestamps and sometimes different amounts, and deciding whether two records are the same event is a judgement the system has to encode explicitly.

Breaks are the point, not the exception. A reconciliation system's real output is its list of things that do not match. Any design that treats a break as a difference to be minimised — rather than a state to be tracked — will eventually net one away into an aggregate, which is the single worst failure available here because the result still looks correct.

And lineage cannot be reconstructed. A figure computed through an untracked transformation cannot have its provenance recovered afterwards. Either every transformation carries it or the trail has a hole, and holes are found at audit.

• Per-source semantic normalisation, not one parser with special cases

• Explicit matching rules, because there is no shared key across custodians

• Exceptions as first-class state, never as a residual difference

• Lineage attached at every transformation, because it cannot be added later

One path, deliberately not a set of stages joined by files. Every hand-off through something that is not the system is a place the trail breaks, which is why the ingestion-to-reporting sequence is a single governed pipeline rather than four tools.

Ingestion and normalisation is per-custodian by design. Each source gets its own mapping into the canonical model — its field semantics, its sign conventions, its settlement definitions — rather than a shared parser accumulating conditionals. This is more code and much less coupling: a custodian changing its format touches one mapping.

The canonical model is what everything downstream reconciles against. Records carry their origin from the moment they enter, so normalisation is itself a lineage-producing step rather than a lossy one.

Reconciliation matches normalised records across feeds under explicit rules, and produces two outputs: matched sets, and exceptions. Exceptions are records in their own right with their own lifecycle — raised, investigated, resolved, with the resolution attached — not a delta someone eyeballs.

Reporting reads the canonical store. Because lineage rode along through every transformation, a figure in a dashboard resolves back to the statement line it came from without a separate audit pipeline. There is deliberately no second reporting path for auditors, because a second path is a second version of the truth.

Design decision: an unresolved break holds state until someone resolves it explicitly. It is never absorbed into an aggregate to make a total balance. A reconciliation system that hides its own exceptions is worse than a spreadsheet, because it looks authoritative while doing it.

Ingest per source

Statements and brokerage data arrive on their own schedules in their own formats. Each is pulled and handled by its own mapping rather than a generic parser.

Normalise into the canonical model

Field semantics, sign conventions and settlement definitions are resolved per custodian. Origin metadata is attached here — the first lineage link, written at the first transformation.

Match across feeds

Explicit rules decide whether two records represent the same economic event, since no shared identifier exists. The rules are the domain knowledge and are versioned as such.

Raise exceptions as records

Anything unmatched becomes an exception with a lifecycle rather than a difference in a total. It stays visible until resolved, and its resolution is attached to it.

Report from the canonical store

Dashboards read the same store the reconciliation wrote. Every displayed figure resolves back through its transformations to a source statement line.

Governance

What may change a figure is bounded: normalisation mappings, matching rules and explicit exception resolutions, each versioned and attributed. There is no path by which a number changes without a recorded transformation. Access and approval sit on exception resolution specifically, because that is the one step where human judgement enters the data.

Orchestration

Ingest, normalise, match, raise exceptions, report — as one continuous path rather than scheduled stages passing files. Continuity is the design goal, since the trail breaks precisely where the process leaves the system.

Observability

The observable unit is the figure-to-source path. For any number: the source statement and line, what was extracted, the normalisation applied, what it was matched against under which rule version, any exception raised, how it was resolved and by whom, and how it reached presentation.

Break rates per custodian are the operational metric worth watching. A custodian whose break rate moves has usually changed a format, and that shows up here days before anyone reads a release note.

The failure that matters is not a pipeline crash — a crash is loud and recoverable. It is a silent semantic error: a custodian changes a sign convention or redefines a field, normalisation continues without error, and the resulting figures are wrong in a way that reconciles cleanly against itself.

The defence is that break rate is monitored per source, since a semantic change almost always shows up as an anomaly in matching before it shows up as a wrong report. A sudden drop in breaks is as suspicious as a spike.

Restatement is the other real case. A custodian reissuing a prior statement must produce a new linked record rather than overwriting history — the previous value stays, with the restatement attached — because an audit asks what you believed at the time, not only what you believe now.

Working behaviour of the built system, which reached internal stage and was not deployed.

• Month-end reconciliation in hours rather than days, with feeds ingested and matched automatically as they land

• One canonical store behind both the operational view and the audit trail, rather than two reporting paths

• Unresolved breaks held as tracked state, never netted into an aggregate

• Every figure resolvable to a source statement line, because lineage is written at each transformation

Directly demonstrable in the built system: per-custodian ingestion and normalisation, automated matching with explicit rules, exceptions as lifecycle records, the single portfolio view, and per-figure source lineage.

Working outcome statements rather than production results: the days-to-hours reconciliation change and the availability of the audit trail by default. Both describe how the system behaves, on a project that concluded at internal stage.

Status, stated plainly: a real client project that reached internal stage and was not deployed.

If you are building this, the decision that is expensive to reverse is whether lineage is structural or reported. Reported lineage is faster to ship and cannot be retrofitted onto figures already computed without it. Everything else here — the per-source mappings, the exception lifecycle — is work you can do later.