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.

Get started - freeMid and Senior open as you complete Junior projects
Data Engineerpostgresqlincremental-loadlate-arriving-data

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:

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 - free

What you'll practice

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

Incremental Data Loading in an ETL PipelineData projectIdempotent ETL Pipeline (INSERT ON CONFLICT)Data projectdbt SCD2 Snapshots: Track Dimension HistoryData projectData roadmapStep by step to hiredData interview questionsSTAR answersAll Data projectsProjects hub

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
Get started - free