# Before the Batch Is Lost Canonical URL: Domain: Pipeline Design · Difficulty: hard · Seniority: senior ## 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. ## Worked solution and explanation ### Why this problem exists in real interviews One sensor stream, two consumers with opposite correctness budgets. Operators want a line-drift alert in seconds and do not care if the count is off by a few units; finance wants an exact daily count per SKU and does not care if it lands at T+1. The trap is a single aggregation that serves both: it is either too slow to stop a batch from being scrapped, or too approximate to close the books on. The second trap hides underneath: plants go offline and replay their backlog hours later, so any design that counts on arrival time silently drops production from the plants with the worst connectivity. The whiteboard answer is one streaming job that rolls telemetry into a metrics table the ops dashboard and the finance report both read. Ops sees a count that lags because the job is doing heavy aggregation; finance sees a number that double-counts on a producer retry and under-counts whenever a plant was offline at midnight. Both stakeholders are unhappy in different ways, and the fix is not a bigger cluster. > **Trick to solving** > > Size two paths for two budgets, and make the split explicit. > > 1. Ops runs on a streaming path: telemetry to an anomaly detector to an alert, sub-minute, approximate is fine, at-least-once is fine. > 2. Finance runs on a daily batch that reconciles on the reading's event timestamp with a watermark and a late-data grace window, deduplicated on a stable unit id so a replay cannot inflate yield. > 3. A durable queue in front absorbs the backlog when an offline plant reconnects, so late data is buffered, not lost. --- ### Walk the requirements #### Step 1: Put ops on a streaming path with a sub-minute budget Fill level, temperature, and capper torque flow through a stream processor that scores each line against spec and fires an alert within seconds. Approximate is acceptable here: a viewer-count-style brief inconsistency does not matter, catching the drift before the batch is scrapped does. If this path is a scheduled batch job, the requirement is simply unmet, no matter how elegant the rest is. #### Step 2: Reconcile finance on event time, not arrival time The daily count per SKU is computed by a batch job keyed on the reading's PLC event timestamp. A watermark plus a late-data grace window keeps a production day open long enough to absorb the typical offline window before the day is closed. Anchor on arrival time instead and every plant with a flaky link quietly reports less than it made, and the shortfall is invisible because the rows were never late in the eyes of the system, just missing. #### Step 3: Make exactly-once a property of the batch sink Each good-unit event carries a stable id (line id plus PLC sequence number). The reconciliation deduplicates on it and writes with an idempotent upsert, so a producer retry or a full replay of a plant's backlog cannot double-count. Exactly-once lives at the sink; the transport and the streaming path can stay at-least-once because ops never needed exactness. Trying to make the whole transport exactly-once is the expensive way to solve a problem only the finance sink actually has. #### Step 4: Send bad readings to a dead-letter path, keep draining A stuck sensor emitting a constant value or an out-of-range spike gets caught by schema and range validation and routed to a dead-letter store; the main stream keeps moving. A rising dead-letter volume is itself an alertable signal that a sensor is failing. If a bad record can halt the line-monitoring pipeline, one flaky sensor blinds every operator until someone patches the parser. --- ### The shape that fits > **Scale and cost** > > At 4 billion readings a day the streaming job only has to score against spec, so it stays cheap; the expensive exactness (dedup index, watermark state, restatement) lives in the once-a-day batch where latency does not matter. Streaming the finance aggregation instead would run the heavy exactly-once machinery continuously against the full firehose, for a number nobody reads until tomorrow. The cost concentrates in the daily reconciliation's shuffle and the dedup key index, both of which run off-peak. > **Interviewers watch for** > > The tell of seniority is naming the split out loud: which consumers need streaming, which are batch, and why streaming the finance numbers would cost more and be less correct. Right behind it: event-time versus arrival-time reconciliation with a watermark, dedup on a stable id for exactly-once, and a dead-letter path so a bad sensor cannot halt the line monitors. Candidates who say stream everything or batch everything have not understood the two budgets. > **Common pitfall** > > Reconciling on arrival time. It looks correct in the demo because every plant is online, then in production the plants with the worst connectivity report less than they produced and the shortfall never shows up as an error, only as a yield number finance cannot reconcile. The other frequent miss is trusting the streaming aggregate for the daily count, which double-counts the moment a plant replays its backlog after reconnecting. --- ## Common follow-up questions - A plant comes back online after four hours and replays its whole backlog. Walk through exactly what keeps those readings from being double-counted and what makes sure they land in the right day's total. _(Tests whether the dedup key and the event-time watermark plus grace window are actually doing the work, versus being named but not wired.)_ - Finance closes the books on the 3rd of the month for the previous month, but a plant reports late on the 4th. How does your design restate a day that was already closed? _(Tests whether the candidate has a restatement path (versioned daily partitions, idempotent overwrite) rather than an infinitely open window.)_ - Volume grows 5x after a new region comes online and the streaming anomaly job starts lagging its sub-minute budget. Where do you look first? _(Tests capacity thinking: queue partitioning, stream parallelism, and keeping the ops path isolated from the batch reconciliation's resource spikes.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/before-the-batch-is-lost) - [System Design Interview Questions](https://datadriven.io/data-engineering-system-design) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.