System Design · hard · Reported 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
The problem
An aggregation for Monday computed on Tuesday morning is incomplete, because events for Monday keep arriving until Wednesday night. Whatever you publish is wrong; the question is how you handle being wrong.
Two timestamps, and they are not the same
event_time when it happened <- aggregate by this
ingest_time when it reached us <- filter incremental loads by thisConfusing them is the root of most late-data bugs. You want counts by when things happened, but you need to find new rows by when they arrived.
The options
Lookback window each night, recompute the last 3 days rather than 1
simple, cheap, and correct for a bounded lateness
this is usually the right answer
Watermarking declare "Monday is final on Wednesday at 6am"
anything later goes to a correction path
explicit, and needs a policy for the stragglers
Append-only with
restatement keep every version, publish the latest, and let
consumers see that the number movedWith a stated 48-hour bound, a three-day lookback is the direct answer: it covers the window with margin and costs three days of compute a night.
Say the number will move
The organizational half matters as much as the technical one. If Monday's figure changes on Wednesday, everyone reading it needs to know that is expected rather than a bug. Publish a completeness indicator alongside the number: "Monday, 97% complete" is trusted, and a number that silently changes is not.
Key points
- Aggregate by event time, load by ingest time.
- A lookback window slightly wider than the known lateness is the simplest correct fix.
- Partition by event date so recomputing a day is cheap.
- Publish completeness alongside the metric, or a restatement looks like a bug.
- Decide what happens beyond the bound, because something always arrives on day four.
FAQ
- What if lateness is unbounded? Then there is no final number. Pick a cutoff, state it, and route later arrivals to a correction table.
- Does this apply to streaming? Yes: this is exactly what watermarks and allowed lateness express in Flink or Beam.
- Why not just wait 48 hours? Because a two-day-old dashboard is usually worse than a number that improves.
The 30-second answer
The first thing I would separate is event time from ingest time. I want to aggregate by when the event happened, but find new rows by when they arrived, and confusing those two is the root of most late-data bugs.
With a known forty-eight hour bound the simplest correct answer is a lookback window: every night recompute the last three days rather than just yesterday. It covers the window with margin and costs very little if the table is partitioned by event date.
The other half is organizational. Monday's number will change on Wednesday, so I would publish a completeness indicator next to it. A number labeled ninety-seven percent complete is trusted; a number that silently moves looks like a bug and costs credibility.
In the interview
- 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.