"What If Data Lands After the Run?"
Concepts covered: Late-Arriving Data
Every row in a fact table carries 2 clocks. The event clock says when the thing happened: the member pressed play at 21:14 on Tuesday. The arrival clock says when the pipeline first saw the row: Thursday at 03:40, when the device reconnected and flushed its upload queue. Late-arriving data is the gap between those clocks growing past the point where the pipeline already acted on the partition the row belongs to. The row is correct. The pipeline's assumption that Tuesday was finished was the error, and every technique in this lesson is a way of either narrowing that assumption or paying to revisit it.
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.
- raw_playback: event_id varchar PK, profile_id bigint FK, title_id bigint, event_ts timestamp, event_date date, processed_ts timestamp, processed_date date
- fact_playback: event_id varchar PK, profile_id bigint FK, title_id bigint, event_ts timestamp, event_date date, processed_ts timestamp
- dim_profile: profile_id bigint PK, country varchar, plan_tier varchar, is_inferred boolean, valid_from timestamp, valid_to timestamp
- fact_playback.profile_id references dim_profile.profile_id (N:1)
The lateness distribution for our running example
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.
| Arrival after event | Share of events | Cumulative | Typical cause |
|---|---|---|---|
| Same day | 96.0% | 96.0% | Connected device, batch upload within minutes |
| Day 1 to day 3 | 3.5% | 99.5% | Device asleep overnight, weekend travel |
| Day 4 to day 30 | 0.49% | 99.99% | Offline downloads, long trips |
| After day 30 | 0.01% | 100% | Device retired, then powered on months later |
Those 4 bands map to 4 costs. Same-day rows cost nothing. Rows in the 3-day band decide the size of the nightly lookback. The 30-day band decides whether a monthly rebuild exists at all. The final sliver decides whether a row is ever allowed to be discarded, and that is a business question rather than an engineering one, because the long tail of a day is still thousands of plays whose creators are paid by the hour watched.
- 50M
- 250K
- 5K
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 4 disguises the question wears
- A published daily number changed after the morning it went out, and nobody edited anything
- A fact row references a customer, profile or product the dimension has never heard of
- A refund, cancellation or corrected amount arrives for a transaction loaded weeks ago
- The source resends a corrected feed for a closed quarter and asks you to reprocess it
The question arrives as a late fact or a late dimension member, and just as often as a correction or a request to reprocess. Interviewers rarely name the pattern. They describe a symptom and watch whether you reach for the 2 clocks before you reach for a fix, because a fix chosen without the histogram is a guess about how late is late.
What the pipeline promised
Every option that follows trades between 3 promises a table can make to its readers, and a table can't keep all 3 at full strength. The decision an interviewer is listening for is which one you weaken and by how much, and then whether the readers were told.
A growth dashboard wants timely and tolerates a 3.5% upward drift over the following mornings. A royalty payment to content owners wants complete and will wait. A finance close wants reproducible and would rather carry a correction row than see history rewritten in place. The same playback table often serves all 3 readers, which is why the sections that follow keep every row's arrival clock on the row itself: it's the one property that lets a single physical table answer all 3.
The 3 promises also settle what 'late' even means. A timely table counts anything after 06:00 as late. A complete table treats nothing inside 3 days as late at all. Under reproducible, no row is late, because every row is recorded with its arrival and the question becomes which arrival cutoff a reader applies. The word 'closed' hides the same decision. A partition is closed when the pipeline chose to stop waiting, and that moment comes from the histogram: close at day 3 and you've committed to 99.5% completeness and a 3-partition lookback, close at day 30 and you have bought 99.99% for a 30-partition rewrite or a 30-day delay. No closing rule is correct in the abstract, only one that matches a stated promise.
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.