Late Arriving Data
Watermarks, Late Dimensions and Facts
Late arriving data is data that reaches a pipeline after the point where its consumers expected it, judged by event time rather than arrival time. Streams handle it with watermarks and allowed lateness, and warehouses handle it with inferred members for late arriving dimensions and lookback reloads for late facts. A 10 minute watermark, for instance, counts an event 5 minutes behind the newest one and drops one that arrives after its window has closed.
What is late arriving data?
Late arriving data is data that reaches your pipeline after the point where something downstream expected it. What counts as late depends on which clock you read: event time, when the thing happened, or processing time, when your system saw it. A tap on a phone at 12:09 that travels through a flaky connection and lands at 12:30 is on time by the processing clock and 21 minutes late by the event clock.
Every pipeline that groups data by when it happened meets the problem, because the 2 clocks never agree. Mobile apps buffer events offline, partner files arrive a day behind, replicas lag and upstream jobs finish late. Data will arrive late, so what matters is what the pipeline does then, and that has to be chosen on purpose: drop it, fold it into a result you already published, or park it for a person.
The topic splits by where the late data lands. In stream processing, late records are handled with watermarks and allowed lateness. In a warehouse, late rows are late arriving facts, and facts that reach the warehouse before their dimension row make it a late arriving dimension. The mechanics differ, and the decision underneath is the same.
The 3 kinds of late arriving data
What is a watermark?
A watermark is a stream processor's running estimate of how far event time has progressed. A window that ends before the watermark is considered complete, so the engine can emit its result and release its state. Beam's programming guide defines it as the system's notion of when all data in a certain window can be expected to have arrived.
Spark Structured Streaming makes the estimate concrete: the watermark is the maximum event time seen minus a delay you choose. Use a 10 minute delay on windows of 10 minutes. Events arrive in the order 12:14, 12:09, 12:21, 12:04. After the first one the newest event time is 12:14, so the watermark is 12:04, and the window 12:00 to 12:10 is still open.
The next event, 12:09, is 5 minutes older than the newest one and arrives out of order. It is still ahead of the watermark, so it updates the window. Then 12:21 arrives and the watermark moves to 12:11, past the end of the window. The window is closed and its state is cleared. When an event stamped 12:04 shows up after that, it is 17 minutes behind the newest event, and the Spark guide's own version of this example ignores it as too late. Lateness is judged against the watermark at the moment of arrival, never against the clock on the wall.
How a watermark is calculated
How long should the watermark delay be?
The delay is a trade between completeness and latency, and no value wins on both. The Dataflow Model paper from Google (Akidau et al., PVLDB 2015) starts from that point: assume you will never know whether all data has arrived, and choose how much correctness, latency and cost to buy. A long delay catches more stragglers and holds every window open longer, so results come later and state grows. A short delay returns results fast and drops more data.
Measure before you guess. Take the gap between each record's event time and its arrival time over a week and read off a high percentile of it. If 99% of events land within 4 minutes, a 10 minute delay covers the ordinary stragglers and the remaining 1% is a decision about side outputs or reprocessing, not about a bigger number.
The delay also sets when results appear. In append mode, Spark emits a window's final count only after the watermark passes its end. For the window ending 12:10, that means the newest event must reach 12:21, so the result is not final until the stream is 11 minutes of event time past the window's end.
A quiet input can stall everything. A watermark only moves when new events arrive, so a partition that stops sending gives the generator no new information. Flink's withIdleness marks such an input idle after a timeout, and its documentation's example uses 1 minute. Without an idleness rule, 1 dead partition holds every window open.
Know Late Arriving Data the way the interviewer who asks it knows it.
What happens to data that arrives after the watermark?
An engine can drop a record behind the watermark, which is the default in Flink and Beam, where allowed lateness is 0. It can update the window by keeping the window's state for an extra period and emitting a corrected result. It can divert the record to a side output so nothing is lost silently. Or it can write the record to storage and let a later batch job reconcile it. The pipeline's owner makes that choice.
Allowed lateness is the knob for the second option. In Flink, elements that arrive after the watermark passes the window's end but within the allowed lateness are still added to the window, and each one can make the window fire again. Beam works the same way: set withAllowedLateness, and the default trigger emits a new result whenever late data arrives. Both treat these late firings as updates to an earlier result, so whatever consumes them must overwrite or deduplicate, never add them to the first answer.
Allowed lateness costs state. Every window stays alive until the watermark passes its end plus the extra period, so 2 days of allowed lateness means 2 days of open windows. The cheaper pattern is a short lateness for the fast path and a side output for the rest, which a nightly job merges into the same table.
Spark gives a weaker promise than the other 2. A delay of 2 hours guarantees that data less than 2 hours late is aggregated, and data later than that may or may not be: the guarantee is strict in only 1 direction. Do not build a reconciliation story on data being dropped at exactly the threshold.
Late data settings in Spark, Flink and Beam
- Setting
- Default lateness
- Late update
- Side output
- SettingwithWatermark
- Default latenessWatermark delay
- Late updateUpdate mode
- Side outputNone built in
- SettingallowedLateness
- Default lateness0, late dropped
- Late updateLate firing
- Side outputsideOutputLateData
- SettingwithAllowedLateness
- Default lateness0, late dropped
- Late updateLate firings
- Side outputNot covered here
What is a late arriving dimension?
A late arriving dimension, also called an early arriving fact, is a fact row that reaches the warehouse before the dimension row it points to. A sale for customer C-77 is recorded at the register before the customer-master load that creates that customer, and the fact table cannot hold a foreign key to a row that does not exist.
Kimball's technique for this is the inferred member. When the fact arrives, the load creates a placeholder dimension row from the one thing it knows, the natural key, with generic values such as "Unknown" in the descriptive columns, and the fact takes that row's surrogate key, 412 here. When the real record lands, it matches on the natural key and the placeholder is overwritten with type 1 updates, so the customer becomes "Acme" and the fact row never changes.
Keep a flag such as is_inferred on the dimension. It lets the real-row load tell placeholders from finished rows, and it lets a data quality check count how many facts still point at a placeholder after the agreed wait. The alternative is the unknown member, 1 shared row for all missing keys. It is simpler, and it permanently loses which customer the fact belonged to, so it fits only when the dimension row may never arrive.
How to handle a late arriving dimension in your load
The load runs in 3 passes, in this order. First, left join the staged facts to the dimension on the natural key and keep the rows with no match. Second, insert 1 inferred member per distinct unmatched key. Third, join again so every fact finds a row, real or inferred, and takes its surrogate key.
The real row's load is the other half: match on the natural key, overwrite the descriptive columns in place and clear the is_inferred flag. Then add an alert on inferred rows older than the agreed wait, since a dimension that never arrives is an upstream problem, not a modeling one.
How to measure how late your data arrives
Stamp every row with both clocks: the event time from the source and a loaded_at time your pipeline writes. The difference is the arrival lag, and its distribution decides every lateness setting. Read the 50th, 99th and 99.9th percentiles per source, because a feed that is on time for 99% of rows and 3 days late for the rest needs a different design from a feed that is uniformly 5 minutes behind.
Then track the late rate: the share of rows that arrive after their period was first published. A rate that creeps up means an upstream change, such as a new app version buffering offline events or a partner moving a file drop later, and it is cheaper to catch in a dashboard than in a restated report.
What is a late arriving fact, and how do you load it?
A late arriving fact is a fact row that shows up after rows for later periods were already loaded. The risk is the dimension join. Kimball defines the case as a fact row whose most current dimensional context does not match the incoming row, so you must search the dimension for the keys that were in effect when the event occurred, not the current ones.
With a type 2 dimension, that means a point-in-time join: match on the natural key and require the event time to fall between the row's valid_from and valid_to. A join on the current-row flag attaches today's region and tier to an order placed last spring, and every report that slices by those attributes shifts its numbers.
A late arriving dimension change causes the mirror problem. Suppose a customer moved to a new region on day 10 and the update reaches the warehouse on day 14. The 4 days of facts loaded for days 10 to 13 point to the old row. The fix is to end the old row at day 10, insert a new row valid from day 10, and repoint those 4 days of facts to the new surrogate key. Kimball's guidance for a retroactive type 2 change is the same: insert the new row and restate the affected fact rows. The slowly changing dimensions guide and the SCD type 2 implementation cover the table mechanics.
SQL for an inferred member and a point-in-time lookup
-- 1. Give every unmatched customer_id an inferred member.
-- customer_sk is generated by the table.
INSERT INTO dim_customer (customer_id, customer_name, is_inferred, valid_from, valid_to)
SELECT DISTINCT s.customer_id, 'Unknown', TRUE,
TIMESTAMP '1900-01-01', TIMESTAMP '9999-12-31'
FROM stg_sales s
LEFT JOIN dim_customer d ON d.customer_id = s.customer_id
WHERE d.customer_id IS NULL;
-- 2. Look up the key that was in effect when the sale happened.
SELECT s.sale_id, d.customer_sk, s.amount
FROM stg_sales s
JOIN dim_customer d
ON d.customer_id = s.customer_id
AND s.sold_at >= d.valid_from
AND s.sold_at < d.valid_to;
-- 3. When the real row arrives, fill the placeholder in place.
UPDATE dim_customer
SET customer_name = :name, is_inferred = FALSE
WHERE customer_id = :customer_id AND is_inferred;The dimension sits in a type 2 layout; the inferred row spans all of time until the real row replaces it
How to reprocess late data without double counting
Batch pipelines handle late data by looking back. Each run reloads not just the newest period but a window of earlier ones, so rows that arrived late are picked up by the next run. dbt's microbatch incremental strategy has this built in: its lookback setting processes that many batches before the latest bookmark to capture late-arriving records, and the default is 1. With lookback=3 on October 1 it rebuilds September 28, 29, 30 and October 1.
Size the lookback from the arrival lag you measured, not from habit. If 99.5% of rows land within 2 days of their event, a lookback of 3 daily batches covers them and costs 3 times the daily compute. A longer window buys little, and a reconciliation job that runs monthly can sweep up the rest.
Reloading is only safe when each reload is idempotent. Rebuild a whole partition with an overwrite or a merge on a business key, so running it twice leaves the same table. An append of the lookback window double counts every row it saw before. The idempotent pipelines guide shows the patterns.
Drop, update or quarantine: how to decide
Decide by what a late record does to a published number. If a consumer acts on a result the moment it appears, such as a fraud alert or a live inventory count, a late record cannot undo that action, so the fast path drops or diverts it. If the number is a daily revenue table that people read the next morning, updating it is cheap and right. If neither is true and the record matters for audit, quarantine it in a side table with its arrival time and let a person or a job decide.
Then write the decision down per table: the delay, the allowed lateness or lookback, where late rows go, and how a consumer can tell a result was revised. Interviewers ask for exactly this in pipeline design rounds, usually phrased as what happens when an event arrives 3 hours late. A good answer names the event-time clock, the watermark or lookback, the policy and the reconciliation job, and it states the cost of each choice. The data pipeline interview questions and data pipeline practice problems cover this ground from the architecture side. For the modeling side, work through data modeling interview questions and data modeling practice problems, and see how SCD interview questions test the retroactive-change case. The system design round guide shows where this fits in the loop.
Late arriving data FAQ
What is late arriving data?+
What is a watermark in stream processing?+
What is a late arriving dimension?+
What is the difference between a late arriving fact and a late arriving dimension?+
What happens to data that arrives after the watermark?+
Does a watermark guarantee no late data is lost?+
How do you handle late arriving data in a data warehouse?+
What is allowed lateness?+
The candidate who gets the offer
- 01
Reading a solution is not the same as writing one
Every engineer who has frozen on a query they had read a dozen times knows the gap. The only preparation that closes it is producing the answer yourself, under time, before the interview does it for you
- 02
76% of hiring managers reject on the coding task, not the resume
From HackerRank's 2024 Developer Skills Report. Candidates who look strong on paper still fail the live screen if they haven't done timed, executable practice
- 03
System design comes down to the calls you defend out loud
Ingestion, batch vs streaming, the bronze/silver/gold layers, idempotency, backfill and replay. Sketching the pipeline and naming the failure modes is the signal, not the boxes
Related guides
Data pipeline interview questions
Pipeline design and operations questions interviewers ask
Data pipeline practice problems
Pipeline design problems to practice before the loop
Data modeling interview questions
Dimensional modeling questions, including slowly changing dimensions
Data modeling practice problems
Modeling problems on keys, grain and history
Data engineering system design round
What the design round asks and how to prepare for it
SCD type 2 in SQL
MERGE patterns and point-in-time joins
Compete in the Weekly
Dirty, production-shaped data, scored blind each week.