Insurance Claims Lifecycle

A hard Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
hard
Seniority
L5

Problem

We need a data model for our insurance claims system. A claim goes through many stages: filed, assigned, investigated, approved or denied, then paid out. We need to track the full lifecycle with version history for audit. Design it.

A claim gets filed. Then it gets complicated. Then it gets reassigned. Then it loops back.

Worked solution and explanation

This is a slowly changing dimension with an insurance badge on it. Every reassignment, status flip and amount revision is a new version of the same claim. So the real skill is keeping two things apart: who the claim is (claim_id, stable forever) and which version you are looking at (claim_sk, one per change). Anyone can draw claimants, adjusters and payments. The trap comes in two forms. One is making claim_id the primary key of claims and updating it in place. The other is still joining on it once versions exist. With the first, the audit trail is gone the moment an adjuster changes. With the second, a claim with four versions quadruples every payment total that joins through it.

Building it

Step 1: Declare the grain before the columns

One claims row is one claim during one stretch of time in which nothing about it changed. Say that sentence out loud. It forces valid_from and valid_to onto the table. It also rules out claim_id as the key, because a claim that loops back from 'investigated' to 'assigned' has many rows.

Step 2: Give each version its own surrogate key

claim_sk identifies the version row and is the PK. claim_id stays as a plain durable key that repeats across versions. It is the number an adjuster types into a search box. claimant_id and current_adjuster_id are ordinary FKs into the two dimensions.

Step 3: Point the facts at the version they happened under

claim_events holds one row per state transition. claim_payments holds one row per disbursement, so installments never collapse into a paid_amount column. Each fact carries claim_sk, the version in effect when it occurred, plus claim_id for grouping across versions. A fact joined to claims on claim_sk returns exactly one row, every time.

Step 4: Close the old row, open the new one

A reassignment sets valid_to on the current row to the event time. It then inserts a new row whose valid_from is that same instant, with valid_to left NULL. That one closing timestamp is the only value ever overwritten.

`claims` is Type 2: `claim_sk` names the version and `claim_id` names the claim. Both facts join on `claim_sk`.
  • dim_claimants: claimant_id BIGINT PK, full_name TEXT, policy_number TEXT, dob DATE
  • dim_adjusters: adjuster_id INT PK, full_name TEXT, region TEXT, hired_at DATE
  • claims: claim_sk BIGINT PK, claim_id BIGINT, claimant_id BIGINT FK, current_adjuster_id INT FK, status TEXT, claim_amount DECIMAL, valid_from TIMESTAMP, valid_to TIMESTAMP
  • claim_events: event_id BIGINT PK, claim_sk BIGINT FK, claim_id BIGINT, event_type TEXT, event_payload JSON, actor_id INT, occurred_at TIMESTAMP
  • claim_payments: payment_id BIGINT PK, claim_sk BIGINT FK, claim_id BIGINT, amount DECIMAL, paid_at TIMESTAMP, method TEXT
  • claims.claimant_id references dim_claimants.claimant_id
  • claims.current_adjuster_id references dim_adjusters.adjuster_id
  • claim_events.claim_sk references claims.claim_sk
  • claim_payments.claim_sk references claims.claim_sk

Reading history back

Claim state as of a date
SELECT
    c.claim_id,
    c.status,
    c.claim_amount,
    a.full_name AS adjuster
FROM claims c
JOIN dim_adjusters a ON a.adjuster_id = c.current_adjuster_id
WHERE c.claim_id = 90125
  AND TIMESTAMP '2026-01-15 00:00:00+00' >= c.valid_from
  AND (c.valid_to IS NULL OR TIMESTAMP '2026-01-15 00:00:00+00' < c.valid_to)

Exactly one version of claim '90125' matches any instant, so the join to dim_adjusters returns whoever held it then.

Update in place
UPDATE claims SET status = 'approved' keeps one row per claim and destroys the prior state. 'What did this claim look like on 15 January?' becomes unanswerable, and no payment records which adjuster authorized it.
Type 2 versions
Each change appends a row keyed by claim_sk. The as-of question becomes one indexed range filter. Every payment row still points at the exact version it was paid under, adjuster included.

Common follow-up questions