Late Data: Rerun Last 7 Days

Concepts covered: Late-Arriving Data

Recompute the window every night

The update reaction needs a mechanism, and for a batch pipeline the simplest one is a wider rerun. Instead of computing only yesterday, the 02:00 job recomputes the last 7 days by event time. When t_88213 lands in trips on March 4, the run at 02:00 on March 5 recomputes March 2 anyway, so the late trip reaches its correct day without anyone touching it.

Run at 02:00 onDays recomputedMarch 2 countWhat changed
March 3Feb 24 to Mar 241,200First count of March 2
March 4Feb 25 to Mar 341,200Late trips not in yet
March 5Feb 26 to Mar 441,380All 180 late trips folded in
March 9Mar 2 to Mar 841,380Last run that touches March 2
March 10Mar 3 to Mar 941,380March 2 is final

Replace Each Day, Never Append

The window works only if rerunning a day is idempotent. If the job appends, the March 4 run adds a second copy of March 2 on top of the first, and by March 9 the day has been inserted 7 times. The job must replace: delete the window's rows, then insert the recomputed ones. Run it once or 5 times and the table ends in the same state.

DELETE FROM daily_trip_counts
WHERE trip_date BETWEEN current_date - INTERVAL '7' DAY AND current_date - INTERVAL '1' DAY
INSERT INTO daily_trip_counts
SELECT
  date(completed_at) AS trip_date,
  city,
  count(*) AS trips
FROM trips
WHERE date(completed_at) BETWEEN current_date - INTERVAL '7' DAY AND current_date - INTERVAL '1' DAY
GROUP BY
  date(completed_at),
  city

Both the filter and the grouping use completed_at, the event time, because each day is rebuilt from every trip that belongs to it. If the insert fails after the delete, the retry repeats both statements and still ends correct.

A team switches its nightly job to cover the last 7 days with INSERT INTO daily_trip_counts SELECT ... and no delete. What does the table hold for March 2 after the March 9 run?

Each night the rebuild reads 7 days of trips, and the gate compares each rebuilt day with its previous count before the dashboard sees it.
  • Trip events (source, trip service)
  • trips (storage, warehouse table, a row per trip)
  • Rebuild last 7 days (transform, orchestrator, daily at 02:00); retries: 3; idempotency: delete + insert per day
  • Row count gate (quality gate, each rebuilt day vs its last count); on error: block if a day drops over 20%
  • daily_trip_counts (storage, warehouse table); freshness: by 03:00 daily
  • Demand dashboard (consumer, last 7 days marked provisional)
  • Trip events -> trips
  • trips -> Rebuild last 7 days
  • Rebuild last 7 days -> Row count gate
  • Row count gate -> daily_trip_counts
  • daily_trip_counts -> Demand dashboard

Choose the window from measured lateness

The 7-day window came from the lateness query in the previous section. Run it over a month and record what share of each day's trips had arrived by each point after the day ended.

Arrived withinShare of the day's trips, March average
2 hours (the first run)99.52%
1 day99.81%
3 days99.95%
7 days99.99%
30 days99.998%

Widening from 1 day to 7 catches nearly all of the remaining tail. Widening to 30 recovers a sliver more for more than 4 times the nightly work, and keeps 30 days provisional for readers instead of 7.

Where the window stops helping

A partner's daily sales file arrives late in this pattern: 92% of a day's rows within 1 day, 99.9% within 3 days, 99.95% within 14 days. Rebuilding a day takes 5 minutes. Which window is the best starting choice?

Capstone: drift and lateness together

The trip service starts renaming fare_usd to fare_amount_usd on March 10 with add, copy, switch, drop. The nightly job rebuilds 7 days of revenue from raw events stored exactly as they arrived. On March 14 the team points the job at fare_amount_usd alone. What happens to the rebuilt days?

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.