Late Facts in Closed Partitions

Concepts covered: Late-Arriving Data

Tuesday's partition was written Wednesday at 03:00. On Thursday 40,000 Tuesday plays arrive. There are 3 places those rows can go, and each one moves a cost somewhere else rather than removing it.

Where the late row lives	Nightly write cost	Query by event_date	Report behaviour	History kept
A: rewrite the event_date partition	Rewrites every partition in the lookback window	Reads 1 partition	Drifts upward until the window closes	Only if processed_ts stays on the row
B: append to the processed_date partition	Writes only the rows that arrived	Scans up to 30 partitions and filters	Drifts upward for as long as rows arrive	Yes, append-only
C: side table for late rows, union at read time	Writes only the late rows	Reads 1 partition plus the side table	Drifts, and every reader must remember the union	Yes

Option A: the lookback rewrite

The nightly job deletes the 3 most recent event_date partitions and rebuilds them from raw, so a Tuesday row arriving Thursday lands where every reader expects it. The price is write volume: 3 partitions of 50 million rows, around 30 GB in a columnar format at 200 bytes a row, rewritten to place roughly 1.75 million late rows that occupy 350 MB. That's about 85 bytes rewritten for every late byte placed. It remains the most common choice, because it keeps every query a single-partition read and keeps the schema honest: event_date means event date.

DELETE FROM fact_playback
WHERE event_date BETWEEN date '2026-10-19' AND date '2026-10-21'

INSERT INTO fact_playback
SELECT
  event_id,
  profile_id,
  title_id,
  event_ts,
  event_date,
  processed_ts
FROM (
  SELECT
    event_id,
    profile_id,
    title_id,
    event_ts,
    event_date,
    processed_ts,
    row_number() OVER (
      PARTITION BY event_id
      ORDER BY processed_ts ASC
    ) AS rn
  FROM raw_playback
  WHERE event_date BETWEEN date '2026-10-19' AND date '2026-10-21'
    AND processed_date BETWEEN date '2026-10-19' AND date '2026-10-22'
) d
WHERE rn = 1

The design depends on 2 details. The dedupe on event_id makes the rewrite idempotent: run it twice and the partition is identical, so a failed night can be retried without inspection. And the filter on processed_date bounds the raw scan to 4 arrival partitions, which is the mechanical form of the 3-day promise. A row that arrives on day 5 is never read by this job. Such a row needs either a slower second path or a rewritten promise.

Option B: partition by arrival, filter by event

Flip the partition key to processed_date and keep event_date as a column. Loading becomes a pure append of whatever arrived, nothing is ever rewritten, and a row's position records when you learned about it. The cost moves to every reader. A question about Tuesday must scan every arrival partition that could hold a Tuesday row, which under the 30-day tail means 30 partitions, and the predicate on event_date does the real work.

SELECT
  event_date,
  count(*) AS plays
FROM fact_playback_by_arrival
WHERE processed_date BETWEEN date '2026-10-14' AND date '2026-11-13'
  AND event_date = date '2026-10-14'
GROUP BY event_date

Column statistics and clustering on event_date inside each partition let most of those 30 scans skip quickly, but the reader must know to write both predicates, and a reader who writes only the event_date filter scans the whole table. Option B suits a raw layer and an audit layer, where append-only history is the point. It's a poor serving layer, because it asks every analyst to understand the pattern this lesson teaches.

Option C, a separate late table unioned in a view, is option B with a smaller search space: the side table holds only the 4% that was late, so the union is cheap. It fails in the same place, at the reader. The day an analyst queries the base table directly, the number is 4% low and nobody is alerted. Treat it as a staging area that feeds option A's rewrite, never as a destination.

Which partitions are closed, and who gets told

The lookback window is also a restatement schedule. Under option A, Tuesday's count rises each morning from Wednesday through Friday, then freezes, except for the 0.49% that arrives later and is placed by a monthly rebuild of the previous 31 partitions. Every downstream rollup built from the fact must rebuild that window in the same nightly run; otherwise the fact and its rollup disagree for 3 days. The strong answer says that unprompted: the window belongs to the lineage. It is declared once and every job in the lineage reads it, so no job carries its own typed constant.

Nightly lookback of 3 partitions

Rewrites 30 GB to place 1.75 million rows
Captures 99.5% of plays
Reports freeze after 3 mornings
Rollups rebuild the same 3 days

Monthly rebuild of 31 partitions

Rewrites 310 GB once a month
Captures the 0.49% offline tail
Restates a whole month in one morning
Needs a restatement notice to readers

Choosing the window from the histogram

Set the window where the next day of waiting costs more to catch than to restate later. Widening from 3 to 7 days more than doubles the nightly rewrite, from 30 GB to 70 GB, and catches only a slice of the remaining 0.49%, because the offline tail is spread across 4 weeks rather than bunched at day 4. The 3-day window plus a monthly rebuild captures 99.99% for 30 GB a night and 310 GB a month. Those 2 figures, read off the histogram, are the answer an interviewer wants to hear justified.

About this lesson

This section is part of the Late-Arriving Data: Advanced 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.