Six Hours to Miss a Deadline
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
- Asked in
- S&P Global
Problem
Design an incremental replacement for our nightly warehouse load, which today rebuilds about 12 billion rows of credit positions and ratings feeding risk models and regulatory reporting. Only around 1 percent of those rows change on a normal day, yet every night we reload all of them.
The rebuild works. It just doesn't finish in time.
Business requirements
- The trading desk reads the morning risk reports at 5am every business day; today's six-hour rebuild misses the deadline.
- An incremental loader can quietly miss records and the warehouse drifts from the source without an error; the team needs a routine reconciliation that catches drift first.
- Source systems sometimes restate historical credit ratings; we have to apply those corrections without rebuilding the full history.
Worked solution and explanation
Why this problem exists in real interviews
Three properties pull at one pipeline: an incremental load to hit the 5am deadline, a reconciliation that catches drift before risk reads stale data, and back-dated corrections that don't force a six-hour rebuild. The trap is either going incremental and trusting that nothing drifts, or running a full refresh on every back-dated correction and missing the deadline again.
The usual first move is to swap full refresh for incremental and call it done. The deadline holds for a few weeks. Then the incremental loader misses some events during a connector hiccup, the warehouse drifts from the source without any error, and the trading desk reads stale credit ratings before anyone notices. Later a back-dated correction arrives for last quarter, the team triggers a full refresh, and the next morning's report is late.
Walk the requirements
Step 1: Land the warehouse before 5am via incremental CDC
Log-based CDC reads only the changes since the last run, so the nightly window covers a day's worth of changes rather than the full table. A scheduler starts the load after the sources close and keeps it ahead of 5am. The deadline becomes reachable because the work scales with change volume, not table size. Without CDC the only options are full refresh or polling, and the source DBAs have already ruled out polling.
Step 2: Reconcile against the source on a schedule, not when somebody complains
Incremental loaders can quietly miss records in several ways: a connector hiccup, replication slot lag, or an event-shape edge case. The same scheduler runs a reconciliation after every load that compares warehouse counts and key aggregates to the source. Drift past tolerance fires an alert and holds the morning publish. Without the reconciliation, drift stays silent until a downstream consumer flags it. With it, the team catches drift before the trading desk reads.
Step 3: Back-dated corrections recompute only the affected partition
When a source restates a historical credit rating, the corrected window is usually small: last quarter, occasionally last year. The corrections processor reads the restatement, identifies the affected period, and partition-overwrites only that slice. The rest of the warehouse doesn't move. A full refresh on every correction pushes the next morning past 5am each time one arrives, while a targeted partition overwrite keeps the deadline intact.
The shape that fits
- source_databases (source, CDC)
- nightly_scheduler_before_5am (transform, Airflow)
- incremental_loader (transform, Spark)
- risk_warehouse (storage, warehouse)
- reconciliation_step (quality gate, Great Expectations); on error: alert
- corrections_processor (transform, Spark); backfill: partition_overwrite
- morning_risk_reports (consumer, dashboard)
- source_databases -> incremental_loader
- nightly_scheduler_before_5am -> incremental_loader
- nightly_scheduler_before_5am -> reconciliation_step
- incremental_loader -> risk_warehouse
- risk_warehouse -> reconciliation_step
- source_databases -> reconciliation_step
- reconciliation_step -> morning_risk_reports
- source_databases -> corrections_processor
- corrections_processor -> risk_warehouse
Common follow-up questions
- Reconciliation flags drift on a Tuesday morning. What does the design do, and what does the trading desk see? (Tests whether the candidate sees the reconciliation alert holding the morning publish, or putting a drift flag on the report, so the trading desk doesn't act on stale data. On-call investigates the cause, and the publish goes out once reconciliation passes.)
- Multiple back-dated corrections arrive across overlapping periods. What in this design lets them apply correctly without conflicting? (Tests whether the candidate applies overlapping corrections in event-time order and overwrites the union of affected periods, so the warehouse converges to the same final state regardless of arrival order.)