Insurance data platform

The engagement

A Lloyd's syndicate had acquired ten companies and inherited ten data estates. Each had its own way of modelling a policy, a claim, a broker, an insured, an exposure. Not just different column names — different grain, different date conventions, different code lists, claims split across two tables in one book and one table in another.

The syndicate wanted ML-driven risk modelling across the combined portfolio. It couldn't have one, because it didn't have a portfolio — it had ten portfolios in a trenchcoat.

We built a unified platform on Cosmos DB and Databricks and got them to a single risk model. It worked. But the expensive part wasn't the platform. The expensive part was a data architect sitting with a subject-matter expert for six weeks per acquired company, working out that POL_EFF_DT in one system meant the same thing as inception_date in another — and that in a third, it didn't quite.

That was 2022. That six weeks is now two days, and this is how.

What I'd change

Then	Now	Why
Cosmos DB as the analytical store	Medallion lakehouse — bronze / silver / gold	A document store doing analytics work. Defensible in 2022, wrong now. Columnar storage, time travel, and schema evolution are the job.
Bespoke canonical model	ACORD as the canonical model	Inventing a schema meant defending it forever. Using the industry standard means every actuary and every vendor already understands it.
Humans map schemas by hand	LLM proposes, human approves	This is the entire point of the rebuild. The model reads column names, sample values, distributions and the source system's own documentation, and proposes a mapping with a rationale and a confidence score. The human's job changes from authoring to adjudicating.
Cloud-hosted inference	Local inference, no egress	A syndicate cannot send broker submissions to a US API. This isn't a preference, it's the condition of the system existing at all.
Trust the pipeline	Every mapping cites its evidence	An unexplainable mapping is an unauditable number in a regulatory return.

Architecture

Raw extracts land in bronze unchanged — one schema per acquired company, nothing transformed, nothing lost. That immutability matters: when a mapping turns out to be wrong eighteen months later, you re-derive silver from bronze rather than going back to the acquired company's decommissioned system.

The mapping engine is the hard core. Human approval is the gate. Silver is canonical ACORD. Gold is what the actuaries actually query.

The mapping engine

For each column in each source system, the engine assembles evidence:

• The column name, and the table it sits in
• Data type, null rate, cardinality, distribution
• Sample values — the single most informative signal, and the one a pure schema-matcher never sees
• Foreign-key relationships inferred from the data
• Any documentation the acquired company left behind
• Retrieved precedent: ACORD field definitions, the relevant code lists, and every mapping a human has already approved on this or a previous book

That evidence goes to a local model, which returns a proposed ACORD field, a rationale, and a confidence score. Prior approved mappings are the highest-value retrieval: by company seven, the system has seen most of the patterns, and accuracy should climb measurably book over book.

The model does not write to silver. It proposes. That distinction is the whole design.

The transform is generated, not written

An approved mapping emits a dbt model or a Spark transform, versioned in git. The LLM's output is a declaration that gets compiled into deterministic, testable, reviewable code. Nobody has to trust a model at runtime, because the model isn't there at runtime — it was only there at design time.

This is the answer to "how do you put an LLM in a regulated data pipeline?" You don't. You put it in the pipeline's authoring process.

Entity resolution

The same broker appears in all ten books, spelled ten ways. Same insured. Same reinsurer.

• Blocking on cheap keys (postcode, normalised name prefix) to generate candidate pairs — you cannot compare 10⁶ records to 10⁶ records
• Embeddings for fuzzy candidate generation where blocking misses
• A deterministic scorer makes the match decision — Fellegi–Sunter or a trained classifier
• The LLM adjudicates only the ambiguous band, and only with the full record in front of it

Letting a model score every pair would be non-deterministic, unauditable and eye-wateringly expensive. This is the same rules-first-model-second discipline as the mapping engine, and it is the thing that makes the system defensible in front of an architecture review board.

Where the humans sit

The React review UI is not a dashboard. It's the product.

A data architect sees a proposed mapping, the evidence behind it, the confidence score, and sample records rendered both ways — as the source system sees them, and as ACORD would. They accept, correct, or reject. Corrections are the highest-signal training data in the system and feed straight back into the precedent store.

Low-confidence proposals route to a queue. High-confidence ones can be batch-approved. The measure of the system is not how many mappings it gets right — it is how much human attention it saves per correct mapping. That's the number a CFO cares about, and it's the one I'll be measuring.

Sovereignty

Nothing leaves. Local inference via vLLM, on-premise or in the syndicate's own VPC. Every prompt and completion is logged for audit — a regulator can ask what the model was told and what it said, eighteen months later.

The interesting engineering constraint is that a quantised open-weight model has to be good enough at this specific task. That's an empirical question, not an article of faith, so it gets measured: local model vs frontier API on identical mapping tasks, accuracy and cost side by side.

If the local model is materially worse, that's a finding worth publishing, not a failure worth hiding.

Results

Method. One public claims dataset, deliberately forked into ten schema dialects with known ground truth — different column names, date formats, code lists, and table splits. Ground truth means mapping accuracy is measurable, not asserted.

Metric	Baseline	This build
Mapping accuracy — top-1	___	___
Mapping accuracy — top-3	___	___
Human decisions per 100 columns	___	___
Accuracy, book 1 vs book 10	___	___
Entity resolution — precision / recall	___	___
Local model vs frontier API — accuracy	___	___
Cost per acquired company	___	___

The row that matters is accuracy, book 1 vs book 10. If precedent retrieval works, the system should get measurably better with every company it ingests. If it doesn't, the retrieval design is wrong and I want to know.

Decisions I'd defend in an architecture review

1. ACORD, not a bespoke canonical model. ADR-001.
2. The LLM proposes; deterministic code transforms. No model in the runtime data path, ever. ADR-002.
3. Rules score, models adjudicate. Entity resolution decisions are reproducible. ADR-003.
4. Bronze is immutable. Every silver record is re-derivable from source. ADR-004.
5. Local inference is a constraint, not a preference. ADR-005.

What I got wrong

Phase two — the unstructured half

The structured mapping is the tractable half. The commercially interesting half is broker submission slips, loss adjuster reports and treaty wordings: PDF in, structured exposure data out, with the source region cited.

That's an agentic extraction pipeline — read, extract, validate against the canonical schema, flag contradictions, escalate what it can't resolve. It writes into the same silver layer through the same approval gate, which is why the architecture above accommodates it without change.

Deliberately out of scope for v1. A half-built extraction pipeline would undermine a working mapping engine.

Run it

git clone https://github.com/Hass1966/insurance-data-platform
docker compose up
make eval

make eval reproduces every number on this page.