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 on | Days recomputed | March 2 count | What changed |
|---|---|---|---|
| March 3 | Feb 24 to Mar 2 | 41,200 | First count of March 2 |
| March 4 | Feb 25 to Mar 3 | 41,200 | Late trips not in yet |
| March 5 | Feb 26 to Mar 4 | 41,380 | All 180 late trips folded in |
| March 9 | Mar 2 to Mar 8 | 41,380 | Last run that touches March 2 |
| March 10 | Mar 3 to Mar 9 | 41,380 | March 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' DAYINSERT 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),
cityBoth 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?
- The correct 41,380, since each run includes the late trips that arrived.
- Seven stacked copies of March 2's rows, so its total is about 7 times too high.
- Only the first count of 41,200, since a day cannot be inserted a second time.
- An empty March 2, since by March 9 the window has already moved past 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 within | Share of the day's trips, March average |
|---|---|
| 2 hours (the first run) | 99.52% |
| 1 day | 99.81% |
| 3 days | 99.95% |
| 7 days | 99.99% |
| 30 days | 99.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.
- 7x
- 0.01%
Where the window stops helping
- Trips arriving after day 7 reach trips but never daily_trip_counts. A daily check on rows loaded today with a completed_at over 7 days old counts them.
- The rerun reads 7 days of history with today's code, so every schema change must stay backward compatible for at least that long.
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?
- One day, since 92% is close enough for a sales table.
- Fourteen days, since it catches the most rows of any option.
- Thirty days, since a wider window is always safer for late data.
- Three days, since it catches 99.9% for 15 minutes a night.
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?
- Trips from before the rename rebuild with null revenue, since their raw events carry only fare_usd.
- Nothing changes, since the copy step already gave every old row the new field name.
- The job fails, since a renamed field can never be read across a rerun window.
- Only March 14 is affected, since earlier days in the window are already final.
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.