The Carrier Moving to Azure

A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

medium

Seniority

L5

Problem

We're moving our insurance claims and policy platform to Azure Databricks, with three source systems feeding claims, policy, and premium data. Design the governed medallion architecture: how the storage layers are configured, and the access-policy stage that every team's reads pass through.

Claims arrive messy. The medallion cleans them up.

Business requirements

• Actuarial sees loss ratios from data that never contained claimant identifiers, finance sees premium aggregates without claim detail, regulators see statutory fields, and adjusters see only their own queue.
• Actuarial works on prior-day data, finance reconciles by 9am, the claims dashboard refreshes within minutes, and a high-severity claim has to alert quickly.
• Once a quarterly statutory filing has gone in, the underlying data can't quietly change; corrections have to be amendments, not overwrites.

Worked solution and explanation

What this really is

This is a data-visibility problem dressed up as a cloud migration. Anyone can draw bronze, silver and gold boxes on Databricks. The design is judged on two things. The first is whether each team's boundary is enforced by the data itself and the platform, or by a filter in somebody's report. The second is whether a filed number can still move after submission. Get the first wrong and one unfiltered notebook query hands claimant names to actuarial. Get the second wrong and the next correction silently rewrites what the regulator already holds.

The default shape is one nightly load into one shared gold table, with each team's view defined in the BI tool. It is too slow for claims, too loose for actuarial, and it has nowhere to freeze a filing.

Walk the design

Step 1: Land raw, then normalize

Claims flow from claims_system through a Kafka topic, claims_events, into bronze_claims_raw, append-only. Policy and premium both land in bronze_policy_raw. Silver holds silver_claims_normalized and silver_policy_current, the latter kept as SCD2 so a claim joins to the policy version in force on its loss date.

Step 2: Give each team a different table, not a different filter

remove_claimant_identifiers builds gold_actuarial_mart from claims and policy with the identifier columns dropped, so loss ratios exist and names do not. aggregate_premium builds gold_finance_mart from policy and premium only, with no claim grain. Both run nightly, which meets the 9am reconciliation.

Step 3: Split freshness at the queue

severity_detector reads claims_events directly and raises severity_alerts in real time, so the fastest path never waits on a table write. The adjuster dashboard reads silver within 15 minutes. Gold is the only prior-day tier.

Step 4: Make the platform own the rest

Every consumer read crosses row_column_access_policies. A row filter on adjuster identity gives each adjuster only their own queue, and column grants limit regulators to statutory fields. A badly written query returns less data, never someone else's.

Step 5: Freeze filings, append amendments

statutory_filing_snapshots is append-only and takes the statutory columns at filing time. A correction lands in filing_amendments as a new row carrying an amends_filing_id that points at the original, so the filed and amended numbers both stay queryable.

The reference design

Overwrite the gold row

A correction runs UPDATE on gold. The quarter's total changes, and nothing records what was filed.

Append an amendment

The original snapshot row is never touched. The correction is a new filing_amendments row whose amends_filing_id points at it, so both numbers stay queryable.

Common follow-up questions

• An adjuster is promoted to supervisor and must see her team's queue. What changes? (Only the row filter's group mapping in `row_column_access_policies` changes. No query or table is rewritten.)
• `severity_detector` falls behind. How does anyone find out before a claim is missed? (Tests consumer-lag monitoring on `claims_events` and a fallback alert path.)