What Late Data Means
Concepts covered: Late-Arriving Data
2 clocks on every event
Every event has 2 timestamps, even if the pipeline stores only one. Event time is when the thing happened in the world: the moment the trip ended, recorded by the phone in completed_at. Processing time is when the pipeline received it: the loader stamps loaded_at on each row it writes into trips. Usually the 2 sit seconds apart, but sometimes they sit days apart.
| trip_id | completed_at (event time) | loaded_at (processing time) | Gap |
|---|---|---|---|
| t_88213 | 2026-03-02 23:50:00 | 2026-03-04 08:10:00 | 32 h 20 m |
| t_88214 | 2026-03-02 21:12:03 | 2026-03-02 21:12:07 | 4 s |
| t_88215 | 2026-03-02 18:40:00 | 2026-03-03 01:55:00 | 7 h 15 m |
Trip t_88213 ended in a tunnel, and the phone held the event until it found a signal on the morning of March 4. Trip t_88215 was sent on time, then sat in a buffer during a trip service outage. Gaps like these have a handful of causes:
- Phones offline in tunnels, on flights or in rural areas send events when they reconnect
- A producer outage holds events in a buffer, then releases hours of them at once
- A partner delivers Monday's file on Wednesday
- An upstream retry resends a failed batch long after its events happened
An event is late once its period is computed
A gap matters once the pipeline has already answered a question about the event's period. The nightly job runs at 02:00 and counts the previous day's trips. At 02:00 on March 3 it published 41,200 trips for March 2. Trip t_88215 arrived at 01:55 and made it in. Trip t_88213 arrived more than 30 hours after that count went out. A late event is one whose event-time period was already computed when it arrived.
SELECT
date_diff('hour', completed_at, loaded_at) AS hours_late,
count(*) AS trips
FROM trips
WHERE date(completed_at) = DATE '2026-03-02'
GROUP BY date_diff('hour', completed_at, loaded_at)
ORDER BY hours_lateA row in trips shows completed_at 2026-03-05 22:10 and loaded_at 2026-03-06 09:30. Each day's count runs at 02:00 the next morning. Is this trip late, and for which day?
- Late for March 5: that day was published at 02:00, 7.5 hours before the trip arrived.
- Late for March 6: it arrived on March 6, so March 6 is the day the trip belongs to.
- On time: it arrived within 12 hours of the trip, so no published day is affected.
- On time: late data only exists in streaming pipelines, and this pipeline runs nightly.
3 reactions to a late event
By the end of March 4, 180 more March 2 trips have arrived, all of them after that morning's 02:00 run. March 4 has 39,800 trips of its own. The pipeline can do 1 of 3 things with the 180.
| Reaction | What happens to the 180 | March 2 count | March 4 count | Cost |
|---|---|---|---|---|
| Drop | Discarded as too late | 41,200 | 39,800 | March 2 undercounts by 180, forever |
| Update | Added to March 2, and the day recomputed | 41,380 | 39,800 | A published number moves after people saw it |
| Count on arrival | Added to the day they arrived | 41,200 | 39,980 | Both days are off; the 2-day total is right |
Count on arrival groups by processing time: a published day never moves and a month's total comes out right, while a Monday trip shows up as Wednesday's. Drop and update both group by event time and differ in whether a published day may change. Update is the one reaction that ends with every day correct, and the next section builds it for a batch pipeline.
A payroll report is filed with a government agency each month, and a filed month can never be changed. Which reaction to late events fits it?
- Update: recompute the filed month so it matches what really happened.
- Drop: discard late events so that the filed month stays fixed.
- Count on arrival: put late events into the next open month.
- Any of the 3: payroll is monthly, so lateness cancels out.
About this lesson
This section is part of the Schema Drift: Beginner 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.