Building a Modern Lakehouse for P&C Insurance

Why P&C is a hard data problem

Property and casualty insurance looks, from the outside, like a straightforward transactional business. A policy is sold, premium is collected, claims are paid. In practice it has a data shape that breaks most generic warehouse designs.

Premium is not one number. There is written premium, earned premium, unearned premium, ceded premium, net premium, and each of them answers a different question and moves on a different clock. Written premium is a transaction. Earned premium is an accrual that changes daily without any transaction occurring at all. If your model treats them as columns on the same row at the same grain, every aggregate downstream is quietly wrong.

Losses develop over years. A claim opened today may reserve at fifty thousand dollars, settle at four hundred thousand, and subrogate back sixty thousand three years later. Actuaries need to see that development by accident year, policy year, and accounting year simultaneously — three different ways of assigning the same dollar to a time period, all of which must reconcile.

And the whole thing is regulated. Statutory reporting, rate filings, and reinsurance treaty settlements all require that a number produced in a dashboard can be traced back through its transformations to a source system record. "The BI tool computed it" is not an audit answer.

Nine schemas, one governed catalog

We deploy a P&C lakehouse as a single Unity Catalog catalog with nine schemas. The separation is not cosmetic. Each schema has a different refresh contract, a different access model, and a different set of people allowed to write to it.

The reason external data gets its own schema rather than being blended into gold is licensing. Verisk and D&B feeds carry contractual restrictions on who may query them and whether derived values can leave the building. Keeping them in a separate schema means access control is a grant, not a code review.

Bronze: earn the right to replay

The single highest-leverage decision in the whole architecture is the bronze ingestion contract. Every raw table in our blueprint carries the same eight metadata columns, regardless of source system:

-- Present on every bronze table, identically
_ingest_timestamp   TIMESTAMP   -- when we landed it
_ingest_file_path   STRING      -- which file or partition it came from
_ingest_batch_id    STRING      -- which run landed it
_cdc_operation      STRING      -- I / U / D
_cdc_timestamp      TIMESTAMP   -- source-side commit time
_is_deleted         BOOLEAN     -- soft-delete tombstone
_source_system      STRING      -- policy admin, claims, billing...
_raw_payload        STRING      -- the original record, untouched

Business columns in bronze are all typed as STRING. This is deliberate and it is the part clients push back on most. The argument against it is that you are throwing away type information. The argument for it is that a source system will eventually send you "N/A" in a date field, and you want that to land and be quarantined rather than fail the ingestion job at 2am and page someone.

With _raw_payload and _ingest_batch_id retained, any downstream layer can be dropped and rebuilt from scratch. Without them, a modeling mistake discovered in month eight means going back to source systems that may have already aged out their history.

Gold: grain discipline is the whole game

The gold layer is a conformed star schema: 22 dimensions, 6 facts, 6 bridges. The dimensions cover the entities a carrier actually reasons about — insured, policy, claim, broker, location, vehicle, program, treaty, reinsurer, underwriter, claims handler, account manager, branch, and so on.

The facts are where discipline pays. Six of them, each with one stated grain:

The bridges handle the many-to-many relationships that P&C is full of and that flatten badly: a policy covering four hundred scheduled locations, a policy listing a fleet of vehicles, a treaty with a panel of nine reinsurers each taking a different signed line, a claim with six assigned vendors. Trying to model any of these as columns on the policy row is how you end up with a table that has location_1 through location_50.

Five decisions that separate a good lakehouse from a fragile one

These are the specific choices we make on every build. Each of them exists because we have seen the alternative fail in production.

1. Money is DECIMAL. Always.

Every monetary column in the blueprint is DECIMAL(18,2). Not DOUBLE, not FLOAT. This sounds obvious and it is violated constantly, usually because a Spark inference step typed a column as double and nobody checked.

Floating point cannot represent most decimal fractions exactly. Sum a few million premium transactions in double precision and you will be off by cents — then a reinsurance settlement will not tie out, and someone will spend two weeks finding out why. The only place we permit DOUBLE is geospatial coordinates, where the precision semantics genuinely are floating point.

2. Facts are the only source of truth for measures

It is tempting to denormalize. Put total_incurred and loss_ratio on the policy dimension so the policy screen loads in one query. It works, right up until the dimension and the fact disagree.

Measures belong in facts. If the serving layer needs them pre-joined, that is what the serving layer is for — and it must be computed from the fact, so there is exactly one lineage path from source to number.

3. If you claim SCD Type 2, use surrogate keys

Half-implemented slowly-changing dimensions are the most common defect we find. A dimension gets is_current, effective_from, and effective_to columns, which signals full history tracking — but the facts still join on the natural business key.

The moment a second version row appears for an insured, that natural key is no longer unique, and every fact join fans out silently. Premium doubles. Nobody notices for a quarter.

Either commit to it — surrogate keys on the dimension, facts carrying the surrogate, point-in-time joins resolved at load — or drop the columns and be honestly Type 1. The dangerous state is the middle one, where the schema promises history the joins cannot deliver.

4. Model a real date dimension and carry currency

P&C needs accident year, policy year, accounting year, and fiscal calendar to coexist and reconcile. Deriving those with year(transaction_date) scattered across a hundred queries guarantees that two teams will eventually disagree about which year a December 31st loss belongs to. A conformed date dimension makes it one decision, made once.

Currency is the same argument one layer up. A US-only book does not need it — until the carrier writes surplus lines, takes assumed reinsurance from a London cedent, or acquires a book in another market. Adding currency_code to facts on day one costs nothing. Retrofitting it into a loaded warehouse is a migration project.

5. Cluster on how the data is actually queried

Liquid clustering is excellent, and it is frequently pointed at the wrong columns. We see dimensions clustered on attributes that look analytically interesting — state, industry segment, risk tier — when the dominant query pattern from the application is a point lookup by entity ID.

Cluster keys should be derived from the query log, not from intuition. And note that liquid clustering and Hive partitioning are mutually exclusive in Delta: a table cannot declare both PARTITIONED BY and CLUSTER BY. It is a surprisingly easy line to write and it will fail at deploy time.

Platinum: the serving layer earns sub-second dashboards

Gold is modeled for correctness. It is not modeled for a dashboard panel that must render in under a second while an executive scrolls. The platinum layer closes that gap with pre-aggregated tables, one per entity type, each grained at entity and snapshot date.

Two details matter here. First, these tables must be computed from gold facts, never from dimensions or from each other — otherwise you have reintroduced the multiple-sources-of-truth problem one layer higher. Second, snapshot-grained tables grow forever, and serving "the current value" via a max(snapshot_date) correlated subquery on every panel render is exactly the latency you were trying to avoid. Maintain an is_latest flag at write time, or expose a current-state view. Small detail, large difference.

Intelligence: predictions are data, and data needs lineage

Model outputs live in their own schema and follow the same governance rules as everything else. Every prediction table in the blueprint carries snapshot_date, model_version, and model_run_ts. Contributing factors are stored as typed structures rather than JSON blobs:

risk_factors ARRAY<STRUCT<
  factor:      STRING,
  impact:      DECIMAL(5,2),
  direction:   STRING,
  description: STRING
>>

This is what makes a model explanation queryable. An underwriter asking "why is this account flagged high risk" gets a structured answer joined from a table, and a regulator asking the same question six months later gets the answer as it stood on that date, from the model version that was live at the time. Storing SHAP output as a JSON string makes both of those a parsing exercise.

The rule we hold to: a model score displayed on a dashboard must be traceable to the run that produced it. If you cannot reconstruct which model version generated a decision, you cannot defend that decision.

Governance belongs in the catalog, not the BI tool

Row-level security implemented in a dashboard filter protects nothing. The moment someone connects a notebook, a JDBC client, or an AI agent to the same warehouse, the filter is gone.

Unity Catalog is where these controls belong. In our blueprint that means group-based grants per schema, row filters and column masks on PII columns such as FEIN and claimant details, and a tag taxonomy applied at deploy time — layer, domain, entity, grain, refresh cadence, PII status, and owning dashboard, attached to every table.

Tags are not documentation for humans. They are what lets you answer "which tables contain PII and who has access to them" as a query rather than a spreadsheet exercise, and they are increasingly what lets an AI agent navigate the catalog safely. An agent that can read tag.pii = true can be instructed to avoid those tables. An agent facing an untagged catalog cannot.

What we look for when we audit an existing lakehouse

Most of our Databricks engagements start with an assessment of something already built. The findings cluster in the same places, so this doubles as a checklist you can run yourself:

That last one is worth dwelling on. Undocumented tables carrying real data are usually a sign that the architecture on paper and the system in production have quietly diverged — someone needed something, built it, and the blueprint was never updated. It is rarely malicious and always expensive, because the next person to plan a change is planning against a map that no longer matches the terrain.

The bottom line

The gap between a carrier that ships AI and one that does not is almost never the model. It is whether there is a governed, reconciled, documented foundation for the model to stand on.

A P&C lakehouse that works has strict layer separation with a real replay point at the bottom. It has one conformed star schema where facts are definitionally the source of truth for every measure. It has monetary precision that survives an actuarial audit. It has governance enforced in Unity Catalog so that a dashboard, a notebook, and an AI agent all see the same permitted rows. And it has enough metadata — comments, tags, explicit grain — that both a new analyst and a language model can navigate it without guessing.

Get that right and the AI work becomes comparatively straightforward. Skip it and you will spend two years building models on numbers nobody trusts.