How would you design a daily batch ETL pipeline to handle late-arriving data?
Late-arriving data is any record that shows up in your source system after the batch window that should have included it has already run and closed, a sale logged with yesterday’s timestamp that only syncs from a mobile device today, for instance. A naive pipeline that processes “yesterday’s data” once and moves on will simply miss these records forever, silently under-reporting yesterday’s numbers.
The core design decision
Separate the event’s business timestamp (when it actually happened) from its processing timestamp (when your pipeline saw it), and always partition and aggregate by the business timestamp, not by when the batch job happened to run. Then build in a reprocessing window: rerun the last N days’ aggregation (not just yesterday’s) on every run, so a late record from three days ago gets picked up and folded into the correct historical partition instead of being permanently lost.
Best practice
Make the aggregation step idempotent, running it twice on the same data should produce the same result, not double-counted totals, usually by fully overwriting a partition rather than appending to it. Track a watermark (the latest business timestamp you’ve fully processed with high confidence) so downstream consumers know how far back results are considered final versus still subject to revision. Set a practical cutoff, most late data arrives within a few days, so reprocessing a rolling 7-day window is usually enough; treat anything arriving after that as an explicit backfill case handled separately, not baked into the daily job’s normal logic.
Edge case interviewers probe for
Ask what happens to a downstream dashboard or report that already showed “final” numbers for a day that later gets revised by late data. The honest answer: either clearly mark recent days as provisional in the UI until they age past the reprocessing window, or accept that some metrics are eventually-consistent and communicate that explicitly, rather than pretending yesterday’s number was ever truly final the moment it was first computed.
Common mistake
Appending new aggregation results instead of overwriting the partition, which double-counts every record that was already included in a previous run when the job reprocesses that same day again.
What the interviewer is checking
Whether you design pipelines assuming data arrives in perfect order and on time (which real-world systems never guarantee), or whether you build in reprocessing and idempotency from the start as first-class concerns.
Imagine you’re counting how many letters arrived at a mailroom each day, and you lock the mailbox and announce the day’s total every evening at 6pm sharp. But sometimes a letter that was mailed yesterday gets delayed and shows up two days later, its postmark says yesterday, even though it physically arrived just now. If you only ever announce a day’s count once and never look again, that late letter never gets counted anywhere, and yesterday’s official total is quietly wrong forever.
The fix is to not treat any day’s count as fully final the moment you first announce it. Instead, every evening, you re-count not just today’s mail but also recheck the last week’s totals in case any delayed letters showed up, and you replace last week’s number with the corrected one rather than just adding the late letters onto today’s total, which would count them under the wrong day entirely.
The tricky part interviewers care about: if you already told your boss “Tuesday had 200 letters,” and Wednesday you discover 3 more Tuesday letters arrived late, do you quietly update Tuesday’s number to 203, or do you make clear that recent days are still provisional until enough time has passed? Real systems have to pick one and be honest about which.
Why interviewers ask this
Late data is one of the most common real-world data engineering problems, and it exposes whether a candidate has actually operated a production pipeline versus only built toy ETL scripts assuming clean, on-time data.
What a strong answer signals
That you separate business time from processing time by default, and treat idempotent, overwritable aggregation as a first-class design requirement, not an afterthought.
Common follow-ups
- How would you set the reprocessing window’s length in practice?
- What’s a watermark and how do streaming systems use the same idea?
- How do you communicate “provisional” data to downstream dashboard consumers?
Advanced variation
“How would this design change if the pipeline was streaming instead of daily batch,” which expects discussion of event-time windowing and watermarks in tools like Apache Flink or Spark Structured Streaming, the streaming-world version of the same late-data problem.
A retail analytics pipeline aggregating daily sales originally processed only “yesterday” on each run and appended results, which caused daily revenue totals to silently drift upward over time as the same late-arriving mobile-sync sales got double-counted whenever the job accidentally reran. Redesigning it to fully overwrite a rolling 7-day partition window on every run, keyed by the sale’s actual transaction timestamp rather than sync time, fixed both the double-counting and the missing-late-data problem, at the cost of slightly more compute per run since 7 days get recalculated instead of just 1.
-- Reprocesses a rolling 7-day window on every run, keyed by the
-- transaction's actual business date, not when the row was loaded.
DELETE FROM daily_sales_summary
WHERE sale_date >= CURRENT_DATE - INTERVAL '7 days';
INSERT INTO daily_sales_summary (sale_date, total_revenue, order_count)
SELECT
DATE(transaction_timestamp) AS sale_date, -- business time, not load time
SUM(amount) AS total_revenue,
COUNT(*) AS order_count
FROM raw_sales
WHERE transaction_timestamp >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE(transaction_timestamp);- 1Partition and aggregate by business timestamp (when the event happened), not by when the pipeline processed it.
- 2Reprocess a rolling window of recent days on every run so late records get folded into the correct historical partition.
- 3Make aggregation idempotent by overwriting partitions, never appending, to avoid double-counting on reprocessing.
- 4Use a watermark to communicate how far back results are considered final versus still provisional.
- 5Treat data arriving after the reprocessing window as an explicit backfill case, not silently dropped or miscounted.