How to Handle Late-Arriving Data in an Incremental Load
An incremental load that only reads rows newer than its last watermark misses events that show up late for a day it already counted. A lookback window plus an upsert that replaces each day's total catches them, and running the load twice changes nothing.
Why late data breaks a simple watermark
Event data rarely arrives in order. A mobile client syncs after a flight, a partner sends yesterday's file at 3am, a retry lands an hour after the original. If the daily mart is built with "only rows newer than the last day I loaded", every one of those events is silently missed, and yesterday's number stays wrong forever.
The pattern: lookback window + replace, not add
Two choices make the load correct:
- Reprocess a window, not a point. Each incremental run recounts
everything from
max(day) - 24 hoursonward, so a late event for yesterday is inside the window. - Replace the total, never add to it. Recount the full day and write it
with
ON CONFLICT ... DO UPDATE SET n = EXCLUDED.n. Adding the new count to the old one double-counts on every rerun; replacing makes the load idempotent.
INSERT INTO events_daily (day, n)
SELECT date_trunc('day', event_time)::date AS day, count(*)
FROM events
WHERE event_time > (SELECT max(day) FROM events_daily) - INTERVAL '24 hours'
GROUP BY 1
ON CONFLICT (day) DO UPDATE SET n = EXCLUDED.n;
Days older than the window stay untouched, so the load stays cheap.
Picking the window size
The window is a trade-off between cost and completeness. Measure how late
your data actually arrives (the gap between event_time and the time the
row was inserted) and set the window to cover nearly all of it. Data later
than the window needs a separate, occasional backfill. dbt's incremental
models use the same idea with a lookback in the is_incremental() filter.
What you do in the project
You write load(mode) in Python against Postgres: a full mode that rebuilds
every day, and an incremental mode with the 24-hour lookback and the upsert.
Then you insert late events for yesterday, run the incremental load twice,
and confirm the totals are right and stable.
Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.
Get started - freeWhat you'll practice
- Writing a full and an incremental load for a daily aggregate table
- Recounting a lookback window so late events land on the right day
- Making the load idempotent with INSERT ... ON CONFLICT DO UPDATE
FAQ
What is late-arriving data?
Late-arriving data is an event that reaches the pipeline after the period it belongs to has already been processed, such as an event from yesterday that arrives today. A load that only looks forward from its last watermark misses it.
How do you handle late-arriving data in an incremental load?
Reprocess a lookback window on every run (for example the last 24 hours or the last few days), recount each affected period in full, and upsert the result so it replaces the old value. Data later than the window is handled by an occasional backfill.
Why replace the count instead of adding to it?
Adding new events to an existing total double-counts whenever the same window is processed again, so reruns change the result. Recounting the whole period and replacing the value gives the same answer no matter how many times the load runs.
Keep learning
Do real production work, free. Start with Junior projects in a live cloud workspace - every project you complete lands on your portfolio.
Get started - free