System Design · hard · Asked at Stripe

Events can arrive up to 48 hours late. How do you handle that in a daily aggregation?

Asked in Data Engineer interviews, in the System Design round.

Short answer

Partition by event time, not ingestion time, and reprocess a trailing window — re-run the last few days each night so late events land in the day they actually belong to. The alternative is a watermark plus published corrections. Either way, state the tradeoff out loud: you wait and are complete but late, or publish early and restate. Bucketing by ingestion time is what makes the warehouse and the source system disagree, and nobody can tell you why.

How to answer it

Aggregate by the time the event happened, not the time it arrived, and accept that a day's number is not final until the lateness window has passed.

Two designs, and you should say which one the consumer needs:

• Reprocess a trailing window. Each night, recompute the last three days (today plus the 48-hour window), replacing those partitions. Late events land in the day they belong to. Yesterday's number changes slightly for two days and then settles. Simple, correct, and the extra compute is small.
• Watermark and publish corrections. Close a day once the watermark passes 48 hours, publish it as final, and record anything that arrives after as a correction in a separate table. Needed when downstream consumers cannot tolerate restated numbers, like a finance close, and it costs a restatement process.

-- nightly: replace the trailing window, partitioned by event time
DELETE FROM daily_events WHERE event_date >= CURRENT_DATE - 3;
INSERT INTO daily_events
SELECT event_date, COUNT(*) FROM raw_events
WHERE event_date >= CURRENT_DATE - 3
GROUP BY event_date;

Then say the consumer-facing part: the dashboard should mark the last two days as provisional, so nobody escalates a "drop" that is just lateness. And measure lateness itself: if the 99th percentile creeps past 48 hours, the window is wrong and the numbers are quietly incomplete.

• What they are checking: event time over arrival time, a trailing reprocess window, and the provisional-versus-final distinction.
• Common mistake: bucketing by ingestion date. The warehouse then disagrees with the source system and nobody can say why.

Related questions

• Design a data pipeline to ingest 1B events/day.
• Batch vs streaming: how do you decide for a given use case?
• A nightly pipeline fails halfway through. How do you design it so re-running is safe?
• When would you denormalize a warehouse table, and what do you give up?
• Design a data warehouse schema for an e-commerce company.
• How do you ensure data quality in a pipeline?

Practice this for real

• Check your resume against a Data Engineer job description — free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews — two free, no card