Basel, CCAR, and Monday Morning

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

The last Basel III liquidity ratio reached regulators after 9am, and a warehouse refresh altered numbers behind a submitted filing. Design the daily pipeline the risk and compliance team files from: two million loans and five million daily payments, in two operational databases, landing in a warehouse with each filed snapshot archived apart from it.

The regulator does not accept 'eventually consistent.'

Business requirements

Regulators receive the daily liquidity ratio by 9am every business day; missing the deadline has direct regulatory consequences, so on-call needs warning before it slips.
Basel III and CCAR fields are split across the loan origination database and the payment processor; a complete regulatory record has to bring both together.
Once a regulatory filing has gone in, the underlying data can't quietly change; a correction has to be a new filing alongside, not an overwrite.

Worked solution and explanation

What this really is

This is a two-source join with a deadline and a write-once rule, dressed up as regulatory reporting. Anyone can draw a warehouse. What separates candidates is seeing that the loan database and the payment database each hold half of the Basel III record, so a reconciliation step has to sit before the warehouse. The other half is keeping filed snapshots out of the warehouse that analysts keep refreshing. Skip the first and the liquidity ratio is built on half a record. Skip the second and last month's filing no longer matches what the regulator received.

The reference design

`orchestrator` sequences the daily run and warns on-call when a stage is at risk against the 9am deadline. `reconcile_loans_payments` is the only place the two sources meet.

loan_origination_db (source, PostgreSQL)
payment_db (source, PostgreSQL)
orchestrator (transform, Airflow); alerting: Stage at risk vs 9am SLA
reconcile_loans_payments (transform, Spark)
regulatory_warehouse (storage, warehouse)
immutable_filing_archive (storage, object storage)
compliance_reporting_dashboard (consumer, dashboard)

loan_origination_db -> reconcile_loans_payments
payment_db -> reconcile_loans_payments
orchestrator -> reconcile_loans_payments
reconcile_loans_payments -> regulatory_warehouse
regulatory_warehouse -> immutable_filing_archive
regulatory_warehouse -> compliance_reporting_dashboard

Walking the requirements

Step 1: Bring both databases in as separate sources

loan_origination_db and payment_db stay distinct nodes because they fail and lag independently. Merging them on the canvas hides which half of the record is late.

Step 2: Reconcile before anything lands

reconcile_loans_payments matches payments to loans and flags the ones that do not pair up. The warehouse then only ever holds complete regulatory records, so the facts behind the liquidity ratio are trustworthy.

Step 3: Let the orchestrator own the 9am deadline

orchestrator runs the daily sequence and carries the at-risk alert. The warning fires while on-call still has hours to fix a slipped stage, not after the regulator is already waiting.

Step 4: Write filings once to a separate archive

immutable_filing_archive is object storage that takes each filed snapshot and never overwrites it. A correction lands as a new amended record beside the original, so both stay queryable and the trail is intact.

Common follow-up questions

A payment arrives after the daily run but belongs to a loan already filed. What happens? (Tests whether the candidate sends the late record through as an amended filing instead of editing the archived snapshot.)
A regulator asks you to reproduce last quarter's filing exactly. Which node do you read from? (Tests whether the candidate reads the archive rather than re-querying a warehouse that has since refreshed.)