Late-arriving data: why Monday's number changed on Wednesday

A daily table is computed at 00:05. By Wednesday, Monday's number has changed, and someone has already screenshotted the old one. This page covers why that happens, the lookback window that fixes it, the upsert that makes the fix safe to rerun, and the watermark that tells readers which rows are final. The print-ready A4 PDF is at the bottom.

The problem

• Late events: logged Monday, loaded Wednesday. The event time and the load time are days apart.
• A job that runs at 00:05 computes Monday before Monday is all there.

Mobile apps flush their queues when the phone comes back online. Partners send yesterday's file at noon. A failed batch gets retried two days later. None of this is a bug in your pipeline, it is how the data arrives.

The answer

• A lookback window: recompute the last N days on every run, not only yesterday.
• Upsert those days. Insert them again and you get duplicates.
• A watermark: the day before which nothing moves any more.

The daily job, with a lookback

Recompute the last three days on every run and write them in place. Yesterday is always included, and so are the two days that may still receive events.

INSERT INTO daily_orders (day, orders)
SELECT event_time::date, COUNT(*)
FROM events
WHERE event_time >= CURRENT_DATE - 3
GROUP BY 1
ON CONFLICT (day) DO UPDATE
  SET orders = EXCLUDED.orders;

Three days of rows are rewritten each night. A rerun gives the same table (see the upsert sheet). Readers see Monday move until Wednesday, then it settles: that is the watermark.

The ON CONFLICT needs a unique index on day. In BigQuery or Snowflake the same job is a MERGE on day.

Remember

• Pick N from the lag you measured, not from a guess.
• Keep two timestamps on every event: event_time and loaded_at.
• Tell the readers: the last three days may still move.

The trap: the lookback that is too short

Three ways the daily table stays wrong after the fix.

The lag is a distribution. Plot loaded_at - event_time once:

SELECT
  PERCENTILE_CONT(0.99) WITHIN GROUP (
    ORDER BY loaded_at - event_time
  ) AS p99_lag
FROM events
WHERE loaded_at >= NOW() - INTERVAL '30 days';

Pick N where 99% of the rows have arrived, round up to whole days, and write that N next to the table for its readers. If the p99 is 50 hours, the lookback is three days and the watermark is three days old.

When the lookback is not enough

Some sources send corrections months later: a refund on a March order, a partner restating a quarter. A lookback window does not cover that. For those, keep the raw events, and either recompute the affected days on demand or accept that the table is a snapshot and say so in its description. What matters is that the readers know which one it is.

The full page is a free print-ready A4 PDF: the lookback window, the upsert, the watermark.