Consider an invented company, Kaveri Fresh, a grocery delivery start-up in Coimbatore. On Tuesday morning the finance lead opens the sales dashboard: Monday’s revenue is ₹18.2 lakh. She pastes it into a note. On Thursday she opens the same dashboard and Monday says ₹18.9 lakh. Nobody changed the formula. She emails the data team with one question: which number is right?
Both were, in a sense. Tuesday’s figure was right about what the warehouse knew on Tuesday. Between Tuesday and Thursday, more Monday data arrived. That is late-arriving data, and every real pipeline has it.
Where the late rows come from
Cash-on-delivery payments get reconciled by the courier partner two days later. A mobile app queues orders placed in a lift with no signal and uploads them when the phone reconnects. A payment gateway posts a settlement file overnight. A warehouse system is down for three hours and then replays its backlog. In each case the business event happened on Monday, but the row reached the warehouse after the Monday load had finished.
The key idea is that every row has two times. Event time is when the thing happened (the order was placed). Load time, or ingestion time, is when your system received it. Reports are almost always about event time, while pipelines are almost always driven by load time. The gap between them is the lateness.
What an incremental load does, and how it fails
Reloading a billion-row table every night is wasteful, so teams load only what is new. The usual device is a watermark: store the highest value of some column you have already processed, and at the next run, fetch rows above it. The column is commonly an updated-at timestamp or an auto-increment id.
Three things go wrong with naive watermarks.
- Watermark on event time. If you pull “orders with order date after Monday”, the row for Monday that lands on Wednesday is never fetched, because you have already moved past Monday.
- Rows that change. An order marked “placed” gets updated to “delivered” or “refunded”. If the source does not bump a reliable updated-at value, or you filter on creation time, you load the first version and never see the change.
- Deletes. A watermark fetches rows that exist above a value. A row removed at the source simply does not appear, and the warehouse keeps the stale copy.
Making it robust
Watermark on load or update time, not event time. Pull everything that the source modified since the last run, whatever date the event itself carries.
Add a lookback window. Instead of starting exactly at the stored watermark, start a few days earlier, say three, and reprocess that overlap every run. Late rows inside the window get picked up. The price is some repeated work, so choose the window from observed lateness: if 99 percent of rows arrive within 48 hours, a 3-day window is reasonable.
Because the lookback means you will see some rows twice, loading must be idempotent: running the same load twice gives the same result as running it once. The standard tool is a merge (upsert) on a unique key, which updates existing rows and inserts new ones instead of blindly appending. Without that, the overlap creates duplicates and inflates revenue, which is a worse problem than late data. Alternatively, delete and reinsert whole date partitions for the window, which is simple and easy to reason about in warehouses that handle partitions well.
For deletes, either ask the source to soft-delete (mark a flag instead of removing the row), use change data capture from the database log, or occasionally compare key counts against the source and reconcile.
The conversation with finance
Even with a perfect pipeline, recent days will move. The honest way to handle that is to say so on the dashboard. Show a “data complete as of” marker, shade the last two or three days as provisional, or add a line stating that figures for the most recent 48 hours may be revised. Then the ₹18.2 lakh to ₹18.9 lakh change becomes an expected event, not a mystery.
For month-end close, freeze it. Agree a cut-off after which a period’s numbers are locked, and handle anything later as an adjustment in the next period. Finance teams already think this way; the data team just needs to give them the same behaviour in the warehouse.
Measure lateness itself. A simple table of event date against days-to-arrival shows how long data takes to settle for each source. When that number suddenly changes, something upstream has broken, and you hear about it before the CFO does.
Figures and names in the examples are illustrative.
Leave a Reply