Thirty Countries, One Solvency Number

A hard 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
hard
Seniority
L6

Problem

Thirty business units, each with its own country, currency and policy and claims system, feed the solvency capital and reserve numbers a group actuary signs off every quarter. Design the platform that collects their premiums and losses and makes them queryable within hours of being written.

Premiums collected globally. Losses happen locally.

Business requirements

Worked solution and explanation

What this really is

This is a conformance problem dressed up as ingestion. Moving bytes from 30 countries is the easy part. The real skill is deciding where thirty source shapes become one, and what gets frozen on the row when they do. The trap is the single nightly extract that dumps raw tables into the warehouse and leaves actuaries to harmonize in notebooks. Build that and three things break. The close waits on a BU nobody can name. Last year's premiums drift every time the dollar moves. A BU whose writings collapse on a Tuesday surfaces six weeks later at quarter close.

Walk the requirements

Step 1: Give each BU its own task and its own deadline

Airflow runs one ingest task per BU, with a sensor on that BU's landing partition. The close still waits on the slowest unit, but now the orchestrator names it, and the alert fires before the window closes rather than after. Raw files land untouched in bu_premium_raw and bu_loss_events_raw, so one late BU never blocks the other 29.

Step 2: Map to the canonical schema in a per-BU adapter

The adapter is the only place that knows a BU's field names. Actuarial reads canonical_premium_fact and canonical_claims_fact, never a source shape. A new BU means a new adapter and nothing else changes.

Step 3: Store local amount, rate and converted amount together

transaction_date_fx joins each transaction to the daily_fx_rates row for its transaction date and writes the local amount, the rate and the converted amount. No query converts on the fly, so a historical premium keeps the value it was booked at. A restated rate becomes an explicit backfill, not a silent drift.

Step 4: Baseline daily volume per BU

A quality gate compares each BU's daily premium count and sum against its trailing baseline and alerts the CRO when one falls off a cliff. The per-BU split is the point. 'Volume is down somewhere' is not actionable. 'Brazil wrote 60% less yesterday' is.

The reference design

Raw landing per BU feeds adapters that write one canonical fact with FX frozen at the transaction-date rate. Marts carry the triangle and solvency view, and a per-BU volume gate alerts the CRO within hours.
  • bu_source_extracts (source)
  • daily_fx_rates (source, API)
  • bu_premium_raw (storage, object storage)
  • bu_loss_events_raw (storage, object storage)
  • bu_ingest_orchestrator (transform, Airflow); alerting: BU landing late vs close window
  • per_bu_adapter (transform, Spark)
  • transaction_date_fx (transform, dbt)
  • canonical_premium_fact (storage, warehouse); freshness: < 2h
  • canonical_claims_fact (storage, warehouse); freshness: < 2h
  • volume_baseline_check (quality gate, Great Expectations); alerting: BU daily volume vs baseline
  • group_cro_alerts (consumer, Email)
  • loss_development_mart (storage, warehouse)
  • solvency_reserve_mart (storage, warehouse)
  • bu_source_extracts -> bu_premium_raw
  • bu_source_extracts -> bu_loss_events_raw
  • bu_premium_raw -> per_bu_adapter
  • bu_loss_events_raw -> per_bu_adapter
  • bu_ingest_orchestrator -> per_bu_adapter
  • per_bu_adapter -> transaction_date_fx
  • daily_fx_rates -> transaction_date_fx
  • transaction_date_fx -> canonical_premium_fact
  • transaction_date_fx -> canonical_claims_fact
  • canonical_premium_fact -> volume_baseline_check
  • volume_baseline_check -> group_cro_alerts
  • canonical_claims_fact -> loss_development_mart
  • canonical_premium_fact -> solvency_reserve_mart
  • loss_development_mart -> solvency_reserve_mart
Convert at query time
amount_local * current_rate in a view. Every rebuild revalues history, so last year's Q3 solvency number changes when the euro moves, even though nobody touched the data.
Freeze the rate on the row
amount_local, fx_rate and amount_group_ccy are stored at load. History stays stable, and quarter-end revaluation is still possible because the local amount survives.

Common follow-up questions