Insurance EDW data model
Data Vault 2.0 raw vault for insurance: hubs (party/policy/claim/coverage/agent), links among them, and satellites for party, policy, claim, coverage, and agent history plus a policy-party effectivity satellite for role-over-time. A shared party hub unifies policyholder, insured, payer, and claimant.
Insurance enterprise data warehouse as a Data Vault 2.0 raw vault: party, policy, claim, coverage, and agent hubs; policy-party, policy-coverage, claim-policy, and policy-agent links; and descriptive plus effectivity satellites — designed to integrate many policy-admin and claims source systems with the historization and auditability required for NAIC, Solvency II, and IFRS 17. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 16
- tables
- 88
- columns
- 14
- relationships
- 5
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Single party hub for all roles
The same person can be policyholder on one policy, claimant on another, and a payer; one party hub with role captured on links/effectivity satellites avoids duplicating the party across systems.
Claim and policy as separate hubs joined by a link
Claims arrive from a different system than policies and have their own business key; a claim-policy link integrates them without coupling the two source loads.
Effectivity satellite for policy-party role
Roles (insured/owner/payer/beneficiary) change over a policy's life; an effectivity satellite records the valid period of each role for as-of reporting.
Tables in the Insurance EDW 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.
Hubs (5)
hub_party
Grain: one row per unique hub_party business key
A person or organization — policyholder, insured, payer, or claimant.
| Column | Type | Keys |
|---|---|---|
| party_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_policy
Grain: one row per unique hub_policy business key
| Column | Type | Keys |
|---|---|---|
| policy_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_claim
Grain: one row per unique hub_claim business key
| Column | Type | Keys |
|---|---|---|
| claim_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_coverage
Grain: one row per unique hub_coverage business key
| Column | Type | Keys |
|---|---|---|
| coverage_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_agent
Grain: one row per unique hub_agent business key
| Column | Type | Keys |
|---|---|---|
| agent_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_policy_party
Grain: one row per lnk_policy_party relationship
Parties associated with a policy (role tracked in effectivity sat).
| Column | Type | Keys |
|---|---|---|
| policy_hk | binary | |
| party_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_policy_coverage
Grain: one row per lnk_policy_coverage relationship
Coverages attached to a policy.
| Column | Type | Keys |
|---|---|---|
| policy_hk | binary | |
| coverage_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_claim_policy
Grain: one row per lnk_claim_policy relationship
Which policy a claim is filed against.
| Column | Type | Keys |
|---|---|---|
| claim_hk | binary | |
| policy_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_policy_agent
Grain: one row per lnk_policy_agent relationship
Which agent wrote the policy.
| Column | Type | Keys |
|---|---|---|
| policy_hk | binary | |
| agent_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_party_details
Grain: one row per party_hk per change
Party descriptive history.
| Column | Type | Keys |
|---|---|---|
| party_hk | binary | PK |
| load_date | timestamp | PK |
| party_type | varchar | |
| full_name | varchar | PII |
| date_of_birth | date | PII |
| tax_id | varchar | PII |
| varchar | PII | |
| state | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_policy_details
Grain: one row per policy_hk per change
Policy descriptive history.
| Column | Type | Keys |
|---|---|---|
| policy_hk | binary | PK |
| load_date | timestamp | PK |
| line_of_business | varchar | |
| product_name | varchar | |
| effective_date | date | |
| expiration_date | date | |
| policy_status | varchar | |
| annual_premium | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_claim_details
Grain: one row per claim_hk per change
Claim descriptive history.
| Column | Type | Keys |
|---|---|---|
| claim_hk | binary | PK |
| load_date | timestamp | PK |
| loss_date | date | |
| report_date | date | |
| peril_code | varchar | |
| claim_amount | decimal | |
| paid_amount | decimal | |
| reserve_amount | decimal | |
| is_catastrophe | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_coverage_details
Grain: one row per coverage_hk per change
Coverage descriptive history.
| Column | Type | Keys |
|---|---|---|
| coverage_hk | binary | PK |
| load_date | timestamp | PK |
| coverage_name | varchar | |
| limit_amount | decimal | |
| deductible_amount | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_agent_details
Grain: one row per agent_hk per change
Agent descriptive history.
| Column | Type | Keys |
|---|---|---|
| agent_hk | binary | PK |
| load_date | timestamp | PK |
| agent_name | varchar | PII |
| agency_name | varchar | |
| license_number | varchar | |
| region | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_policy_party_role
Grain: one row per policy-party relationship per effectivity change
Effectivity satellite on the policy-party link.
| Column | Type | Keys |
|---|---|---|
| policy_party_hk | binary | PK |
| load_date | timestamp | PK |
| party_role | varchar | |
| effective_from | date | |
| effective_to | date | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_peril
Grain: one row per peril
| Column | Type | Keys |
|---|---|---|
| peril_code | varchar | BK |
| peril_name | varchar | |
| peril_category | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_policy_party.policy_hk → hub_policy (many-to-one)
- lnk_policy_party.party_hk → hub_party (many-to-one)
- lnk_policy_coverage.policy_hk → hub_policy (many-to-one)
- lnk_policy_coverage.coverage_hk → hub_coverage (many-to-one)
- lnk_claim_policy.claim_hk → hub_claim (many-to-one)
- lnk_claim_policy.policy_hk → hub_policy (many-to-one)
- lnk_policy_agent.policy_hk → hub_policy (many-to-one)
- lnk_policy_agent.agent_hk → hub_agent (many-to-one)
- sat_party_details.party_hk → hub_party (many-to-one)
- sat_policy_details.policy_hk → hub_policy (many-to-one)
- sat_claim_details.claim_hk → hub_claim (many-to-one)
- sat_coverage_details.coverage_hk → hub_coverage (many-to-one)
- sat_agent_details.agent_hk → hub_agent (many-to-one)
- sat_policy_party_role.policy_party_hk → lnk_policy_party (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.