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_idcompleted_at (event time)loaded_at (processing time)Gap
t_882132026-03-02 23:50:002026-03-04 08:10:0032 h 20 m
t_882142026-03-02 21:12:032026-03-02 21:12:074 s
t_882152026-03-02 18:40:002026-03-03 01:55:007 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:

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_late

A 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?

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.

ReactionWhat happens to the 180March 2 countMarch 4 countCost
DropDiscarded as too late41,20039,800March 2 undercounts by 180, forever
UpdateAdded to March 2, and the day recomputed41,38039,800A published number moves after people saw it
Count on arrivalAdded to the day they arrived41,20039,980Both 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?

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.