Before the Batch Is Lost
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
- senior
- Asked in
- Anheuser-Busch
Problem
We run bottling and canning lines across a few hundred breweries, and every line streams sensor readings (fill level, temperature, capper torque) at about 4 billion events a day, some of them stuck at a constant value or spraying out-of-range garbage. Plant operators need to catch a line drifting out of spec within seconds so they can stop it before a whole batch is scrapped, while supply chain needs an exact daily count of good units produced per SKU that reconciles for finance, and a stuck or garbage reading can neither halt the line monitors nor corrupt that count. Many plants have flaky connectivity, so readings arrive late and out of order, and the daily numbers still have to include them.
Business requirements
- As a plant operator I need to know a line is drifting out of spec within seconds so I can stop it before we scrap a whole batch.
- As supply chain I need an exact daily count of good units produced per SKU that reconciles cleanly for finance.
- As supply chain I need the daily numbers to include readings from plants that were offline and reported late.
- As the data engineering team I need a stuck or malformed sensor to not corrupt the numbers or halt the line-monitoring pipeline.
Worked solution and explanation
What this really is
This is a lambda architecture dressed up as a bottling line. One sensor stream feeds two consumers with opposite correctness budgets. Operators want drift flagged in seconds and can live with an approximate count. Finance wants an exact per-SKU daily count and can wait until tomorrow. Anyone can draw Kafka and Flink. What separates candidates is refusing to let the fast aggregate become the financial number. That aggregate buckets by arrival time, so a plant that reconnects after four hours either lands its production in the wrong day or double-counts it on replay. Then the count never reconciles.
The second trap sits upstream. A stuck sensor repeating a constant torque, or spraying out-of-range fill levels, trips false pages or wedges the monitor, and it quietly inflates the count. Quarantine it before it reaches the reconciliation, and never let it block the line monitors.
Walk the requirements
Step 1: Put ops on a sub-minute streaming path
Flink reads the queue, scores fill level, temperature and capper torque per line against spec, and pages within seconds. Approximate and at-least-once are fine here. A scheduled job is not, however elegant the rest of the design is. In-line range guards drop stuck or garbage values so they cannot fire false pages.
Step 2: Reconcile finance on event time
The daily count keys on the reading's PLC timestamp, with a watermark and a grace window sized to the typical offline gap. If you key on arrival time, the plants with the worst links report less than they made, and no error is raised anywhere.
Step 3: Make exactly-once a property of the sink
Deduplicate on a stable unit id (line id plus PLC sequence number) and upsert idempotently, so a retry or a full backlog replay cannot inflate yield. The transport stays at-least-once, because only finance ever needed exactness.
Step 4: Quarantine bad readings and keep draining
Schema and range checks route garbage to a dead-letter store and the main flow keeps moving. A rising dead-letter rate is itself the signal that a sensor is failing.
The reference design
- line_sensors (source, API)
- ingest_queue (queue, Kafka)
- anomaly_stream (transform, Flink); freshness: < 1min
- ops_alerts (consumer, PagerDuty); freshness: real-time
- reading_validation (quality gate, Great Expectations); on error: dlq
- dead_letter_queue (storage, S3)
- daily_dedup_reconciliation (transform, Spark); freshness: < 24h; idempotency: upsert
- orchestrator (transform, Airflow)
- production_warehouse (storage, Snowflake); freshness: < 24h
- supply_chain_bi (consumer, Tableau); freshness: < 24h
- line_sensors -> ingest_queue
- ingest_queue -> anomaly_stream
- anomaly_stream -> ops_alerts
- ingest_queue -> reading_validation
- reading_validation -> dead_letter_queue
- reading_validation -> daily_dedup_reconciliation
- orchestrator -> daily_dedup_reconciliation
- daily_dedup_reconciliation -> production_warehouse
- production_warehouse -> supply_chain_bi
- Ingest: line_sensors, ingest_queue
- Streaming (ops): anomaly_stream, ops_alerts
- Batch (finance): reading_validation, dead_letter_queue, daily_dedup_reconciliation, orchestrator
- Serving: production_warehouse, supply_chain_bi
Common follow-up questions
- A plant replays four hours of backlog. What stops double-counting, and what puts each reading in the right day? (Whether the dedup key and the watermark are wired in, or just named.)
- Finance closed last month on the 3rd, and a plant reports late on the 4th. How do you restate a closed day? (Versioned daily partitions and idempotent overwrite, not a window left open forever.)
- Volume grows 5x and the anomaly job misses its sub-minute budget. Where do you look first? (Queue partitioning, skewed hot lines, and isolating the ops path from batch spikes.)