The Clock That Runs Two Ways

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

L5

Asked in

dbt Labs

Problem

Orders arrive on two clocks: a nightly extract from an on-premises transactional database and a real-time event stream from microservices, each under its own scheduler today. Both paths must land in one landing zone under one orchestration layer, with one SLA dashboard and one failure alert covering both.

Nightly batch and live events. One dashboard.

Business requirements

• Finance reconciles every business day from the landing zone at 6am UTC; missing it is a P1 incident, and the on-prem network drops once a month.
• The streaming path covers the same business entities as the batch and consumer lag has to stay short during business hours; today there's no shared view.
• The same order can arrive on both the batch and streaming paths; the unified dataset has to merge them deterministically with the streaming record winning when both exist.

Worked solution and explanation

This is one control plane over two clocks, dressed up as a data lake project. Anyone can draw a batch path and a stream path into a lake. What separates candidates is whether one scheduler owns the deadline for both, and whether the lake has a deterministic rule when the same order shows up twice. Skip that and the on-prem network drops on a Sunday, the batch dies quietly, and finance opens an empty landing zone at 6am UTC on Monday while streaming lag builds on a dashboard nobody shares.

Walk the requirements

Step 1: Put one orchestrator over both paths

Airflow schedules the nightly extract and supervises the streaming consumer. It is the only node that knows both clocks, so it is the only place a 'batch at risk before 6am UTC' alert or a 'lag past tolerance' alert can be raised. Separate schedulers cannot give leadership one view.

Step 2: Give the SLA dashboard its own stage

The orchestrator feeds a Grafana SLA dashboard that shows batch lateness next to streaming lag. That is the one shared view the stakeholders asked for. Alerts fire from the orchestrator hours before the deadline, so on-call recovers the monthly network drop overnight instead of finding it at 6am.

Step 3: Merge into one landing zone with streaming winning

Both paths feed one merge step, named for its rule: streaming wins merge. It upserts on order_id and ranks the streaming record above the batch record, so each order resolves to one outcome whichever path arrived first. The landing zone it writes is where finance reads at 6am UTC.

Common follow-up questions

• The on-prem network drops Sunday night but streaming is healthy. What does finance see Monday? (Tests that the alert fires hours before 6am UTC and that the merge still serves streaming records while the batch is incomplete.)
• Streaming lag spikes during a busy hour. What does on-call see and do? (Tests that lag shows on the same SLA dashboard as batch lateness, so on-call scales the consumer before downstream users notice.)