Late-Arriving Data: Advanced

Model both clocks as columns: event_ts for when it happened and processed_ts for when it landed. Lateness is processed_ts - event_ts, and a histogram of that difference over a few weeks of history is the most useful artifact in this whole topic, because every design choice below is a bet on its shape.

The example this lesson follows is a streaming service's playback fact, with one row per play event. The table is partitioned by event_date and loaded nightly. Devices buffer events locally and upload in batches. A downloaded title watched offline uploads nothing until the device next reaches a network. Measured over 6 weeks of history, the lateness falls into 4 bands, and the bands matter more than the exact figures.

Throughout, the percentages are the example's measured history, not an industry constant. Your pipeline's own histogram is the one that matters, and the first thing to build.

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.

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.

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.

A new member creates a profile at 21:13 and presses play at 21:14. The playback event reaches the fact load that night. The profile row reaches dim_profile through a separate feed that runs hourly and had a 3-hour outage. The fact join finds no profile. A late dimension member is a fact that knows more than the dimension does, and the job has 3 choices at the moment of the join.

If the dimension carries a surrogate key column, the inferred row must receive the same key the real row would have received, which is the deterministic key argument of the final section.

Here dim_profile is a slowly changing dimension of type 2: each profile has one row per version. valid_from and valid_to bound each version, and a fact joins to the version in effect at its event time. When the real profile record arrives 3 hours later, the tempting move is the standard one: close the inferred row and open a new version carrying the real country and plan. That's wrong. The inferred row recorded the pipeline's ignorance, and a new version would leave the first 3 hours of plays attached to 'unknown' forever. Overwrite the inferred row's attributes in place and flip is_inferred to false: the facts keyed to it resolve untouched, and its 1900 valid_from floor still catches a late play with any event time.

Profile 88213 upgraded from standard to premium on 1 October. The subscription system's export failed for 2 weeks, and the dimension feed delivers the change on 15 October, correctly stamped with the 1 October effective date. The loader does the right thing and opens a premium version with valid_from of 1 October. Now look at the facts. Every play from 1 to 15 October was loaded while the standard version was current, and if the fact stored a version surrogate key, 2 weeks of plays point at the standard row. The dimension is right and the fact is wrong, and the plays-by-plan report has misattributed premium hours for 14 days without a single failed job.

This case has 2 workable designs. The first stores only the natural key profile_id on the fact and resolves the version at query time with a range join on event time. Nothing needs re-keying, and the price is a join predicate on every query plus a dimension that must stay small enough to broadcast. The second stores the version key for speed and accepts a re-keying job. Whenever a dimension version arrives with an effective date in the past, that job rewrites every fact partition from the backdated valid_from forward, which in this case means 15 partitions and 150 GB.

On 2 October the billing feed delivers charge 7731: account 4410, 15.49. It loads into fact_charge in the 2 October partition and the month's revenue report sums it. On 9 October the feed delivers a correction: charge 7731 was prorated and should have been 9.99. The 2 October partition is closed, the report went out, and finance has read it. A correction is a late fact that disagrees with a fact already loaded, and that disagreement is what makes it harder than plain lateness, because now 2 rows claim the same event.

Update in place is what a transactional system does and what a fact table should refuse. It rewrites a closed partition, leaves no trace that 15.49 was reported, and a reader reproducing the 3 October revenue report gets a number it never showed. Versioned rows keep the history but push a filter onto every reader, and the moment someone forgets is_current = true, revenue doubles for every corrected charge.

sum(amount) is 9.99 and sum(row_sign) is 1, so a count of charges is a sum of signs, never count(*), which would say 3. Every measure on the table must be additive or be computed from additive parts. A max(amount) over this ledger returns 15.49 and means nothing, and an average must be sum(amount) / sum(row_sign). That constraint is the real cost of the reversal pattern, and it's the one to state in an interview before the interviewer asks.

The join on supersedes_version negates the exact row the source says it is correcting, so a charge corrected twice reverses version 2 and leaves version 1's reversal alone. The anti-join against rows already carrying this correction's source_version is what makes the job safe to rerun. Without it, a retried load appends a second reversal, revenue for the account goes negative, and the error is found by an accountant rather than a test. The corrected 9.99 row is the second half of a UNION ALL in the same statement, omitted here, so a run writes both rows or neither.

The ledger above puts the reversal in the 9 October arrival partition while keeping charge_date of 2 October on the row. That is option B from section 1 applied to one row, with the same consequence: a report filtered on charge_date must scan forward through later arrival partitions to see the correction. If the serving table is partitioned by charge_date, the reversal lands in the 2 October partition through the lookback rewrite, which works only when the correction arrives inside the window. A correction 3 months late falls outside every window, the strongest argument for keeping processed_date on every row: with it, 'in the 3 October report, this charge was 15.49' remains answerable after the row has moved.

Because every row carries processed_ts, the same fact_playback partition answers 2 different questions. 'How many plays happened on 14 October?' reads the partition as it stands today. 'How many plays did the 15 October report show for 14 October?' adds a single predicate on the arrival clock.

The second query reproduces a published number from a table the lookback has since rewritten 3 times. That holds because the rewrite keeps the first arrival per event_id, with its original processed_ts, and because corrections arrive as reversal rows with their own arrival clock rather than as edits. Remove either property and the as-of read is gone. A table with this property is bitemporal: one axis for when it happened, one for when you knew, and a report is a cut along the second.

Each step leans on a design choice above. Raw makes step 1 an append. Reversal rows guarded by source_version make step 2 a loop over 92 dates that is safe to rerun. The run log names the rollups that read those partitions. Step 4 works because each original amount stays in fact_charge beside its reversal, so an as-of cutoff on processed_date still returns the old total that a rebuild in place would erase. The compute takes hours. Without the old totals, the restatement conversation with finance takes weeks.