Late-arriving data

How do you insert something that happened before events you have already recorded and reported on?

A sale is made at 09:14 on Tuesday. The record of it reaches your warehouse on Friday afternoon — a phone was offline, a partner batched their exports, a message sat in a dead-letter queue until somebody replayed it. Tuesday has already been loaded, aggregated, and mailed to the finance director.

So where does the row go? Tuesday, where it happened, or Friday, where it arrived? Both answers are defensible, and that is the uncomfortable part. Notice that the difficulty is not storage — inserting a row with an old timestamp costs nothing. The difficulty is that other things were built on Tuesday's number after Tuesday closed, and they cannot be un-built by an insert.

Start by separating two clocks that we usually let blur together. Every record carries an event time — when the thing happened in the world — and acquires a processing time — when your system learned of it. In a well-behaved day these differ by seconds and nobody notices. Lateness is simply the gap between them growing large enough to cross a boundary you have already declared closed.

That reframing tells you something immediately: lateness is not a property of the data, it is a property of the boundary. If you never closed a period, nothing would ever be late. So ask what closing actually buys you. It buys the ability to say a number is final — to compute a total once instead of forever, to hand it to someone who will act on it. The cost of that finality is exactly the risk of a straggler.

Which suggests the first real design question: how long do you wait? Streaming systems make this explicit with a watermark — a moving assertion of the form "I believe I have now seen everything with event time before T". Frameworks in the Beam and Flink lineage let you add an allowed lateness on top: keep the window's state alive a little past the watermark, and update the result if a straggler shows up inside that grace period. After that, the window's state is released and later arrivals go to a side output rather than silently into a total. That is an honest bargain — you trade a stated amount of memory and delay for a stated amount of correctness — but notice what it does not do. It does not eliminate the late record. It picks a point past which you have decided to handle it differently.

So consider what "differently" can mean, because there are only three moves and each has a distinct cost.

You can restate: reopen Tuesday, add the row, recompute the total, and republish. The stored history is now correct. But every artifact downstream of the old number is not, and here is the part that surprises people — some of those artifacts are outside your system. A commission was paid, a reorder was triggered, a model was retrained on the wrong week. The database can be corrected; the consequences already ran. That path dependence is the real reason lateness is hard.

You can absorb: post the record with an effective date of Friday, as a correcting entry. Nothing already published changes. This is what double-entry accounting does with a prior-period adjustment, and it is not a hack — it is a deliberate choice that the audit trail matters more than the shape of the daily curve. The cost is that Tuesday now understates and Friday overstates, permanently.

Or you can keep both, which is what a bitemporal model does: store when the fact was true and when the database believed it, as two independent time dimensions. Then "what were Tuesday's sales?" and "what did we think Tuesday's sales were on Wednesday morning?" are different queries with different, both-correct answers. That is the only structure that lets you reproduce an old report and a corrected one from the same table — which is exactly what an auditor asks for. SQL:2011 standardised application-time and system-time period tables for this, and most warehouses now have some form of it.

And how late can a record be? In any system whose sources you do not control there is no bound — only a distribution with a tail you have measured and a tail you have not.

The tempting misconception is that late data is a bug to be engineered away — better retries, a stricter contract, a bigger buffer. Those shrink the tail, and shrinking it is worthwhile, but the tail is a property of a distributed world in which a device can be off for a week. The design question is never "how do I stop this?" but "what do I promise about a number, and what do I do when the promise turns out to have been premature?"

Two refinements connect the pieces. First, restatement is not a failure — it is a feature you must build deliberately, with a way to identify everything derived from the number you are changing. If you cannot enumerate the consumers, you cannot restate safely, and absorbing is the more honest choice. Second, the choice is usually not global. A regulatory filing wants bitemporality and restatement; an operational dashboard wants the current best answer and does not care what it said yesterday; a machine-learning feature store wants the value as known at prediction time, because training on a corrected value the model could not have seen at inference is a classic leakage error.

• How watermarks are estimated when a source is idle, and why an idle partition can stall an entire window indefinitely.
• Why feature stores insist on point-in-time correct joins, and what leakage looks like when they do not.

Every term the collection defines is gathered in the glossary.