Actuarial Loss Reserving & Triangles data model
Kimball warehouse for actuarial reserving: claim-transaction facts, loss-development triangle cells, reserve estimates (ultimate/IBNR by method), earned premium by accident period, and per-claim valuation snapshots over conformed accident-period, development-period, line-of-business, and reserving-segment dimensions.
Actuarial loss-reserving analytics: claim financial transactions, loss-development triangle cells (paid/incurred by accident and development period), reserve estimates (chain-ladder and Bornhuetter-Ferguson ultimates and IBNR), earned premium, and per-claim valuation snapshots — a Kimball star schema with conformed line-of-business, accident-period, development-period, and reserving-segment dimensions for IFRS 17 / Solvency II reserving. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 13
- tables
- 99
- columns
- 23
- relationships
- 1
- 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
Triangle cell keyed by accident AND development period
The loss-development triangle is the fundamental reserving structure; modeling each cell at accident-period × development-period grain lets chain-ladder and BF run directly off the warehouse.
Separate reserve-estimate fact with a method dimension
Reserves are estimated by multiple methods (chain-ladder, BF, Cape Cod) each valuation; a method-keyed estimate fact stores every method's ultimate and IBNR with a selected-ultimate flag for the booked figure.
Earned premium by accident period
Bornhuetter-Ferguson and expected-loss methods need an a-priori exposure base; earned premium at accident-period grain provides it.
Per-claim snapshot alongside aggregate triangles
Large-loss and reopened-claim review needs claim detail; a per-claim valuation snapshot complements the aggregate triangle without changing its grain.
Tables in the Actuarial Loss Reserving & Triangles 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 (5)
fct_claim_transaction
Grain: one row per claim financial transaction
Transaction-grain claim financial movements feeding triangles.
| Column | Type | Keys |
|---|---|---|
| claim_transaction_key | bigint | PK |
| date_key | bigint | |
| claim_key | bigint | |
| line_of_business_key | bigint | |
| accident_period_key | bigint | |
| transaction_type | varchar | |
| paid_amount | decimal | |
| case_reserve_change | decimal | |
| recovery_amount | decimal | |
| expense_amount | decimal | |
| currency | varchar |
fct_loss_triangle_cell
Grain: one row per segment per accident period per development period (periodic snapshot)
Loss-development triangle cell — the core reserving data structure.
| Column | Type | Keys |
|---|---|---|
| valuation_date_key | bigint | |
| line_of_business_key | bigint | |
| reserving_segment_key | bigint | |
| accident_period_key | bigint | |
| development_period_key | bigint | |
| cumulative_paid_losses | decimal | |
| cumulative_incurred_losses | decimal | |
| case_reserves | decimal | |
| reported_claim_count | integer | |
| closed_claim_count | integer | |
| earned_premium | decimal |
fct_reserve_estimate
Grain: one row per segment per accident period per valuation per method
Reserve estimates (ultimate, IBNR) by method per valuation.
| Column | Type | Keys |
|---|---|---|
| reserve_estimate_key | bigint | PK |
| valuation_date_key | bigint | |
| line_of_business_key | bigint | |
| reserving_segment_key | bigint | |
| accident_period_key | bigint | |
| method_key | bigint | |
| actuary_key | bigint | |
| ultimate_loss | decimal | |
| paid_to_date | decimal | |
| case_reserve | decimal | |
| ibnr_reserve | decimal | |
| total_reserve | decimal | |
| loss_development_factor | decimal | |
| expected_loss_ratio | decimal | |
| selected_ultimate_flag | boolean |
fct_earned_premium
Grain: one row per segment per accident period per month (periodic snapshot)
Earned premium by accident period — the BF a-priori exposure base.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| line_of_business_key | bigint | |
| reserving_segment_key | bigint | |
| accident_period_key | bigint | |
| written_premium | decimal | |
| earned_premium | decimal | |
| unearned_premium | decimal | |
| exposure_count | integer |
fct_claim_snapshot
Grain: one row per claim per valuation (periodic snapshot)
Per-claim valuation snapshot for large-loss and development review.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| claim_key | bigint | |
| line_of_business_key | bigint | |
| accident_period_key | bigint | |
| reported_amount | decimal | |
| paid_amount | decimal | |
| case_reserve | decimal | |
| incurred_amount | decimal | |
| is_reopened | boolean | |
| development_age_months | smallint |
Dimension tables (4)
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_reserving_segment
Grain: one row per reserving segment
| Column | Type | Keys |
|---|---|---|
| reserving_segment_key | bigint | PK |
| segment_id | varchar | BK |
| segment_name | varchar | |
| region | varchar | |
| peril | varchar | |
| granularity | varchar |
dim_claim
Grain: one row per claim
| Column | Type | Keys |
|---|---|---|
| claim_key | bigint | PK |
| claim_id | varchar | BK |
| claim_number | varchar | |
| claim_type | varchar | |
| report_lag_days | integer | |
| is_large_loss | boolean | |
| is_catastrophe | boolean | |
| claim_status | varchar |
dim_actuary
Grain: one row per actuary / analyst
| Column | Type | Keys |
|---|---|---|
| actuary_key | bigint | PK |
| actuary_id | varchar | BK |
| actuary_name | varchar | PII |
| credential | varchar | |
| team | varchar |
Lookup tables (4)
dim_accident_period
Grain: one row per accident period
| Column | Type | Keys |
|---|---|---|
| accident_period_key | bigint | PK |
| accident_period_code | varchar | BK |
| accident_year | smallint | |
| accident_quarter | smallint | |
| accident_month | smallint | |
| period_label | varchar |
dim_development_period
Grain: one row per development lag
| Column | Type | Keys |
|---|---|---|
| development_period_key | bigint | PK |
| development_period_code | varchar | BK |
| development_lag_months | smallint | |
| development_year | smallint | |
| maturity_label | varchar |
dim_line_of_business
Grain: one row per line of business
| Column | Type | Keys |
|---|---|---|
| line_of_business_key | bigint | PK |
| lob_code | varchar | BK |
| lob_name | varchar | |
| coverage_group | varchar | |
| segment | varchar |
dim_method
Grain: one row per reserving method
| Column | Type | Keys |
|---|---|---|
| method_key | bigint | PK |
| method_code | varchar | BK |
| method_name | varchar | |
| method_category | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_claim_transaction.date_key → dim_date (many-to-one)
- fct_claim_transaction.claim_key → dim_claim (many-to-one)
- fct_claim_transaction.line_of_business_key → dim_line_of_business (many-to-one)
- fct_claim_transaction.accident_period_key → dim_accident_period (many-to-one)
- fct_loss_triangle_cell.valuation_date_key → dim_date (many-to-one)
- fct_loss_triangle_cell.line_of_business_key → dim_line_of_business (many-to-one)
- fct_loss_triangle_cell.reserving_segment_key → dim_reserving_segment (many-to-one)
- fct_loss_triangle_cell.accident_period_key → dim_accident_period (many-to-one)
- fct_loss_triangle_cell.development_period_key → dim_development_period (many-to-one)
- fct_reserve_estimate.valuation_date_key → dim_date (many-to-one)
- fct_reserve_estimate.line_of_business_key → dim_line_of_business (many-to-one)
- fct_reserve_estimate.reserving_segment_key → dim_reserving_segment (many-to-one)
- fct_reserve_estimate.accident_period_key → dim_accident_period (many-to-one)
- fct_reserve_estimate.method_key → dim_method (many-to-one)
- fct_reserve_estimate.actuary_key → dim_actuary (many-to-one)
- fct_earned_premium.date_key → dim_date (many-to-one)
- fct_earned_premium.line_of_business_key → dim_line_of_business (many-to-one)
- fct_earned_premium.reserving_segment_key → dim_reserving_segment (many-to-one)
- fct_earned_premium.accident_period_key → dim_accident_period (many-to-one)
- fct_claim_snapshot.date_key → dim_date (many-to-one)
- fct_claim_snapshot.claim_key → dim_claim (many-to-one)
- fct_claim_snapshot.line_of_business_key → dim_line_of_business (many-to-one)
- fct_claim_snapshot.accident_period_key → dim_accident_period (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.