Handling Late-Arriving Data
Every event has 2 times. event_time is when it happened, stamped by the producer, often a phone. ingested_at is when the log accepted it. For most events the 2 are seconds apart. For a phone in a tunnel, a laptop closed mid-upload, or an app that batches events and sends them every 15 minutes, event_time can be hours behind ingested_at. A tip tapped at 08:35 while the phone was offline keeps that event_time and reaches the log at 12:40, when the phone reconnects. The event is late, and it is still true.
| event_id | event_name | event_time | ingested_at | delay |
|---|---|---|---|---|
| evt_a1 | trip_completed | 2026-10-20 08:31:05 | 2026-10-20 08:31:06 | 1 s |
| evt_a2 | tip_added | 2026-10-20 08:35:12 | 2026-10-20 12:40:48 | 4 h 5 min |
| evt_a3 | page_viewed | 2026-10-20 23:58:40 | 2026-10-21 00:14:03 | 15 min |
| evt_a2 | tip_added | 2026-10-20 08:35:12 | 2026-10-20 12:41:02 | retry, same event_id |
Why yesterday's number keeps changing
Row 3 is the subtle case. The page view happened on October 20 and arrived on October 21. If the loader files events by arrival day, it lands in the wrong partition and October 20 is undercounted forever. File it by event_time, into event_date 2026-10-20, and a partition for a day already closed changes. A daily aggregate built at 01:00 on October 21 counted 1,204,118 events for October 20. By October 23 the true count was 1,209,744, about 0.5% higher. Neither figure is a bug. The aggregate was built before the data finished arriving.
The batch fix is a lookback: each run recomputes the last N days rather than yesterday alone, overwriting those partitions in the aggregate table. N comes from measurement. If 99.9% of events arrive within 48 hours, a 3-day lookback catches almost all of them, and anything later is accepted as a known, small error.
SELECT
event_date,
event_name,
count(*) AS events
FROM raw_events
WHERE event_date >= current_date - INTERVAL '3' DAY
AND event_date < current_date
GROUP BY
event_date,
event_nameWatermarks: declaring a window finished
A streaming job cannot wait forever to emit the 08:00 to 09:00 count. It keeps a watermark, a timestamp that says 'no event earlier than this is still coming', and advances it as event times arrive, minus an allowed lateness. With a 1-hour allowance, the watermark passes 09:00, the window's end, once events stamped 10:00 arrive, and the window is emitted then. The tip stamped 08:35 that arrives at 12:40 is behind the watermark, and the job needs a policy for it.
Retries arrive late too
The phone that failed to upload evt_a2 at 08:35 retries at 12:40 and again at 12:41, and the log holds 2 copies. The retry carries the same event_id, which is the reason the envelope has one. Dedupe on event_id inside each event_date partition, keeping the earliest ingested_at, with the same row_number() pattern that found the latest event per order.
About this lesson
This section is part of the Event Streams lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.