Property & Casualty Insurance Lakehouse

[AmitDECopilot]

When building a Property & Casualty Insurance Lakehouse, one of the first things to understand is:

A Claim is not just a Claim Amount.

Consider a simple auto claim.

At different points in the lifecycle, the same claim might have:

Indemnity Reserve = $15,000
Medical Reserve = $8,000
Legal/Defense Reserve = $4,000

Then actual payments start:

Indemnity Paid = $10,000
Medical Paid = $3,000
Legal Expense Paid = $1,500

And later the carrier may recover:

Subrogation Recovery = $6,000
Salvage Recovery = $1,000

This is where the data modeling becomes interesting.

If our Lakehouse has only:

claim_id | claim_amount

we have already lost much of the business context.

Instead, the model needs to preserve the relationship between:

Policy
→ Policy Term
→ Coverage
→ Insured Risk
→ Claim
→ Claim Exposure
→ Claim Financials

And Claim Financials themselves need to distinguish:

Reserve
Payment
Expense
Recovery

with additional classifications such as indemnity, medical, legal, subrogation and salvage.

Why does this matter?

Because the Lakehouse should eventually be able to answer questions such as:

What is the outstanding reserve?

How much has actually been paid?

How much of the loss is indemnity vs expense?

How much has been recovered through subrogation?

What was the applicable coverage when the loss occurred?

What is the incurred loss at a given point in time?

What is the loss ratio by coverage, risk, geography or line of business?

This is why I find industry data models interesting.

The value isn't simply having hundreds of tables.

The value is preserving the business grain and relationships so that downstream analytics can reconstruct what actually happened.

In Part 2 of my exploration of Databricks Lakehouse Industry Data Models, I went deeper into what a P&C Insurance reference architecture could look like — from Party, Submission and Policy through Coverage, Risk, Premium, Claims, Claim Financials, Reinsurance and Catastrophe.

Full article:

https://dataengineeringcopilot.com/blog/databricks-pc-insurance-data-model

My next step is to take this from architecture to implementation.

I want to build a small P&C Lakehouse on Databricks:

Bronze → raw policy, claim and financial transactions

Silver → standardized policy, coverage, claim exposure, reserve, payment and recovery data

Gold → Policy 360, Claim 360 and insurance KPIs such as paid loss, outstanding reserve, incurred loss and loss ratio

That is where an industry data model becomes much more than an ER diagram.

It becomes the semantic foundation of the Lakehouse.

#Databricks #Lakehouse #DataEngineering #Insurance #PropertyAndCasualty #DataModeling #DeltaLake #Claims #DataArchitecture