P&C Insurance Analytics data model
Kimball warehouse for P&C insurance: premium transactions, monthly in-force policy snapshots, an accumulating claim snapshot, and claim payments over conformed policy, policyholder, coverage, peril, and agent dimensions.
Property & casualty insurance analytics: the policy lifecycle, premium transactions (written/earned), monthly in-force snapshots, and claims with peril and coverage detail — a Kimball star schema with conformed policy, policyholder, coverage, and agent dimensions supporting loss ratio, earned premium, and reserve analysis. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 12
- tables
- 98
- columns
- 18
- relationships
- 10
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Databricks). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Written vs earned premium as transaction + snapshot
Written premium is transactional; earned premium accrues over time and needs a monthly in-force snapshot to report correctly.
Accumulating snapshot for claims
A claim moves FNOL → open → settled with reserve and paid amounts that evolve; an accumulating snapshot captures the milestones and lag.
Tables in the P&C Insurance Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (4)
fct_premium_transaction
Grain: one row per premium transaction
| Column | Type | Keys |
|---|---|---|
| premium_transaction_key | bigint | PK |
| date_key | bigint | |
| policy_key | bigint | |
| policyholder_key | bigint | |
| coverage_key | bigint | |
| agent_key | bigint | |
| transaction_type | varchar | |
| premium_amount | decimal | |
| commission_amount | decimal | |
| tax_amount | decimal |
fct_policy_snapshot
Grain: one row per policy per month (periodic snapshot)
Monthly in-force snapshot for earned-premium reporting.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| policy_key | bigint | |
| policyholder_key | bigint | |
| in_force_flag | boolean | |
| written_premium | decimal | |
| earned_premium | decimal | |
| unearned_premium | decimal | |
| exposure_count | integer |
fct_claim
Grain: one row per claim (accumulating snapshot)
Accumulating snapshot through the claim lifecycle.
| Column | Type | Keys |
|---|---|---|
| claim_key | bigint | PK |
| loss_date_key | bigint | |
| report_date_key | bigint | |
| policy_key | bigint | |
| policyholder_key | bigint | |
| coverage_key | bigint | |
| peril_key | bigint | |
| status_key | bigint | |
| insured_asset_key | bigint | |
| claim_number | varchar | |
| claim_amount | decimal | |
| paid_amount | decimal | |
| reserve_amount | decimal | |
| deductible_amount | decimal | |
| salvage_amount | decimal | |
| is_litigated | boolean | |
| is_catastrophe | boolean |
fct_claim_payment
Grain: one row per claim payment
| Column | Type | Keys |
|---|---|---|
| claim_payment_key | bigint | PK |
| date_key | bigint | |
| policyholder_key | bigint | |
| claim_number | varchar | |
| payment_type | varchar | |
| payment_amount | decimal | |
| payee_type | varchar |
Dimension tables (6)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint |
dim_policyholder
Grain: one row per policyholder per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| policyholder_key | bigint | PK |
| policyholder_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| varchar | PII | |
| phone | varchar | PII |
| address_line1 | varchar | PII |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | PII |
| credit_tier | varchar | |
| customer_since_date | date |
dim_policy
Grain: one row per policy per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| policy_key | bigint | PK |
| policy_number | varchar | BK |
| line_of_business | varchar | |
| product_name | varchar | |
| agent_key | bigint | |
| effective_date | date | |
| expiration_date | date | |
| policy_status | varchar | |
| payment_plan | varchar |
dim_coverage
Grain: one row per coverage type
| Column | Type | Keys |
|---|---|---|
| coverage_key | bigint | PK |
| coverage_code | varchar | BK |
| coverage_name | varchar | |
| limit_amount | decimal | |
| deductible_amount | decimal |
dim_agent
Grain: one row per agent
| Column | Type | Keys |
|---|---|---|
| agent_key | bigint | PK |
| agent_id | varchar | BK |
| agent_name | varchar | PII |
| agency_name | varchar | |
| license_number | varchar | |
| region | varchar |
dim_insured_asset
Grain: one row per insured asset (vehicle/property)
| Column | Type | Keys |
|---|---|---|
| insured_asset_key | bigint | PK |
| asset_id | varchar | BK |
| asset_type | varchar | |
| vin | varchar | PII |
| make | varchar | |
| model | varchar | |
| model_year | smallint | |
| property_address | varchar | PII |
| construction_type | varchar | |
| year_built | smallint | |
| insured_value | decimal |
Lookup tables (2)
dim_peril
Grain: one row per peril
| Column | Type | Keys |
|---|---|---|
| peril_key | bigint | PK |
| peril_code | varchar | BK |
| peril_name | varchar | |
| peril_category | varchar |
dim_claim_status
Grain: one row per claim status
| Column | Type | Keys |
|---|---|---|
| status_key | bigint | PK |
| status_code | varchar | BK |
| status_name | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- dim_policy.agent_key → dim_agent (many-to-one)
- fct_premium_transaction.date_key → dim_date (many-to-one)
- fct_premium_transaction.policy_key → dim_policy (many-to-one)
- fct_premium_transaction.policyholder_key → dim_policyholder (many-to-one)
- fct_premium_transaction.coverage_key → dim_coverage (many-to-one)
- fct_premium_transaction.agent_key → dim_agent (many-to-one)
- fct_policy_snapshot.date_key → dim_date (many-to-one)
- fct_policy_snapshot.policy_key → dim_policy (many-to-one)
- fct_policy_snapshot.policyholder_key → dim_policyholder (many-to-one)
- fct_claim.loss_date_key → dim_date (many-to-one)
- fct_claim.policy_key → dim_policy (many-to-one)
- fct_claim.policyholder_key → dim_policyholder (many-to-one)
- fct_claim.coverage_key → dim_coverage (many-to-one)
- fct_claim.peril_key → dim_peril (many-to-one)
- fct_claim.status_key → dim_claim_status (many-to-one)
- fct_claim.insured_asset_key → dim_insured_asset (many-to-one)
- fct_claim_payment.date_key → dim_date (many-to-one)
- fct_claim_payment.policyholder_key → dim_policyholder (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.