Out of the Data Center
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
- L6
Problem
How should the platform lead move about 150 daily pipelines, roughly 500 GB a day, from an on-prem warehouse that can't scale into a cloud warehouse, a few pipelines at a time? Until a pipeline cuts over, its on-prem jobs keep loading the on-prem warehouse that about 200 analysts read their daily reports from.
The on-prem servers are not getting any younger.
Business requirements
- Executives review dashboards at 8am every day; the migration can't ever miss a morning report, on-prem or cloud.
- Three prior modernization attempts failed; leadership will only sign off if cloud and on-prem produce identical output for a defined period before cutover.
- If a migrated pipeline misbehaves after cutover, that one has to revert to on-prem without disturbing any of the others.
- Analysts' existing tools hold saved connections to one warehouse address; after any cutover those workbooks must still open at that address, whichever side holds the pipeline's data.
Worked solution and explanation
What this really is
This is a change-management problem wearing a cloud-migration costume. Anyone can draw on-prem boxes, an arrow and a cloud warehouse. What separates candidates is making every pipeline independently reversible: dual-run, a numeric parity gate, per-pipeline cutover, and a connection endpoint users never see move. Skip that and you ship the fourth failed attempt: one pipeline diverges, nothing can revert only that one, the whole wave rolls back, and every analyst's workbook points at a dead connection.
Walk the requirements
Step 1: Let one orchestrator own the 8am contract
orchestrator schedules each pipeline on whichever side is authoritative today and alerts when either side risks missing 7:30am. Without it, nothing watches the deadline across both halves of the migration.
Step 2: Dual-run and gate cutover on a number
on_prem_sources feeds on_prem_warehouse untouched, and the same changes cross hybrid_bridge into cloud_landing. Both warehouses feed parity_gate daily, and a pipeline flips only after divergence stays in tolerance for the agreed window. Leadership signs off on the gate's record, not on someone eyeballing two dashboards.
Step 3: Scope rollback to one pipeline
Pipeline A can be live on cloud while B and C still dual-run. If A misbehaves, only A's route flips back. That isolation is what the three prior attempts lacked.
Step 4: Put a stable endpoint in front of users
Analysts keep their existing dashboards pointed at unchanged_endpoint_routing_per_pipeline, a set of views at today's address that resolves each table to its authoritative warehouse. A cutover is a routing change there, so nobody rebuilds a connection.
The reference design
- on_prem_sources (source, CDC)
- orchestrator (transform, Airflow); alerting: page if either side risks the 7:30am load
- on_prem_warehouse (storage, PostgreSQL)
- hybrid_bridge (queue, Kafka)
- cloud_landing (storage, object storage)
- cloud_transform (transform, Spark)
- cloud_warehouse (storage, warehouse)
- parity_gate (quality gate, Great Expectations)
- unchanged_endpoint_routing_per_pipeline (transform, SQL)
- analytics_users (consumer, dashboard)
- on_prem_sources -> on_prem_warehouse
- on_prem_sources -> hybrid_bridge
- hybrid_bridge -> cloud_landing
- cloud_landing -> cloud_transform
- cloud_transform -> cloud_warehouse
- orchestrator -> on_prem_sources
- orchestrator -> cloud_transform
- orchestrator -> unchanged_endpoint_routing_per_pipeline
- on_prem_warehouse -> parity_gate
- cloud_warehouse -> parity_gate
- parity_gate -> unchanged_endpoint_routing_per_pipeline
- on_prem_warehouse -> unchanged_endpoint_routing_per_pipeline
- cloud_warehouse -> unchanged_endpoint_routing_per_pipeline
- unchanged_endpoint_routing_per_pipeline -> analytics_users
- On-prem: on_prem_sources, on_prem_warehouse
- Hybrid bridge and control: orchestrator, hybrid_bridge, cloud_landing
- Cloud: cloud_transform, cloud_warehouse
- Parity and serving: parity_gate, unchanged_endpoint_routing_per_pipeline, analytics_users
Common follow-up questions
- After cutover a pipeline drifts from its dual-run output. Where do you look first? (Tests whether they reach for the parity history and per-pipeline rollback.)
- Three migrating pipelines depend on a fourth still on-prem. What happens? (Tests cross-side reads through `unchanged_endpoint_routing_per_pipeline` instead of forcing migration order.)