Handling late arriving data after a show closes without breaking the report

The show closed on 3 May. The badge vendor delivered its final file on 5 May, the post-show pack went out on 8 May, and on 12 May a lead retrieval vendor emailed a spreadsheet with 2,300 rows nobody had seen. On 21 May the badge reconciliation came back and removed 640 scans against test badges that operations had used to check the readers. Signed rebooking contracts kept trickling in until July.

Late arriving data after a show closes is the normal state of an event warehouse, and pipelines built on the assumption that a day's data is complete at the end of that day handle it badly. The two failures are equally common. Either the late file never gets picked up, because the incremental filter only looks at rows newer than the last load, or it gets picked up and lands twice, because reprocessing an interval that was already loaded appends everything in it a second time.

What actually lands after the doors close

The Kimball Group's technique summary for late arriving facts, drawn from the third edition of The Data Warehouse Toolkit in 2013, defines the case tightly: "A fact row is late arriving if the most current dimensional context for new fact rows does not match the incoming row. This happens when the fact row is delayed."

In a show warehouse the delay comes from four routine sources, and it is worth writing down which of yours behaves how.

• Lead retrieval exports. Vendors reconcile their own devices before releasing a file. Three to fourteen days is typical, and a vendor who serves several of your shows may batch them.
• Badge reconciliation. Removals rather than additions, which is the case most loaders forget. Test badges, staff badges wrongly typed, duplicate registrations collapsed at the door.
• Signed contracts. Rebooking paperwork signed on the floor gets countersigned and filed over the following weeks, so the rebooking rate you compute on day five is structurally low.
• Survey responses. Open for two to four weeks by design.

Each of those has a different lag distribution, and treating them with one window is the source of most of the pain.

How long should the lookback window be?

Toby Mao's May 2023 piece on correctly loading incremental data at scale gives the general heuristic and is honest that it is one: "In general, most events are received close to when they were triggered, so a reasonable threshold (like 14 days) can usually catch 99% of events."

That is a good default and a bad substitute for measuring your own. The measurement is straightforward and almost nobody does it. For every row you load, record two timestamps: the business timestamp on the row, and the moment the file containing it landed. The difference is arrival lag. Plot it per source and read the window off the curve where it flattens.

Doing that for one edition usually produces something like this. Badge scans: 96 per cent within 48 hours, 100 per cent by day 5. Lead retrieval: nothing for 6 days, then 78 per cent on day 9, the rest by day 16. Contracts: a long flat tail out past 60 days with no natural cut-off at all.

Three sources, three windows. A 14 day lookback is generous for scans, correct for leads, and useless for contracts, which need either a much longer window or a different mechanism entirely.

Tobiko Data's SQLMesh documentation, current in 2026, exposes this directly as a lookback setting on incremental models, described as a way to handle late-arriving data by reprocessing prior intervals. The documented example reprocesses today, yesterday and the day before. The parameter is per model, which is the right granularity, because the contract model and the scan model have nothing in common except the show they belong to.

The 9 day file, worked through

Take the lead file that landed on 12 May, 9 days after close, carrying 2,300 rows.

Under a plain incremental filter, the loader asks for rows newer than the maximum business timestamp already in the table. Every row in that file is dated during show week, 3 May or earlier, so the filter excludes all 2,300. The load runs green and adds nothing. This is the failure that produces an exhibitor phoning in October to ask why their lead count is 41 when they scanned closer to 300.

Under a 7 day lookback, the loader reprocesses everything from 5 May onward. The file's rows are dated 1 to 3 May, so 7 days is still short and all 2,300 rows are still excluded. The window has to reach back past the business date of the rows, and those dates fall in show week while the delivery happened nine days later. That distinction catches people out constantly, because the window is measured against the data and the lag is measured against the delivery.

Under a 14 day lookback, the loader reprocesses from 28 April, the show week rows are inside the window, and all 2,300 land. So does everything else already loaded from that period. If the write appends, the table now holds those rows twice and the lead count for every exhibitor roughly doubles. With a merge keyed on vendor identifier plus lead identifier, the 2,300 rows update in place and the row count is unchanged for everything else. Which write to use for a batch that size against a full edition is a real decision with a cost attached, covered in merge versus insert overwrite.

There is a wrinkle specific to events that makes this easier than it looks. A show runs 3 or 4 days. A 14 day lookback around a 4 day show reprocesses the entire edition every time, so for practical purposes the lookback window for event data collapses to a single instruction: reprocess the edition. That is a partition sized unit, it is cheap at 38,900 scans, and it removes an entire class of off-by-one reasoning about window boundaries.

Why a lookback window on its own is not enough

The window decides which intervals get reprocessed. It says nothing about what happens to rows that were in the table already, and that is where the duplicates come from.

The pairing that works is a window plus an idempotent write. Reprocessing has to be an operation you can run any number of times with the same result. For an edition partition that means the write either replaces the partition wholesale or merges on a key that genuinely identifies a row.

The key is where event data fights back. A lead retrieval row has a vendor lead identifier, usually. A badge scan has a scan identifier, usually. A survey response has a response identifier, always. A signed contract arriving as a PDF has nothing at all until someone assigns it something, which is why contract data needs a deterministic surrogate built from the fields that cannot change: show, edition, exhibitor natural key and contract date. Build that key once, in one place, and every downstream reload becomes safe.

What happens when the late file removes rows?

The 21 May reconciliation removed 640 scans. Merge does not handle this. A merge statement matches incoming rows against the table and updates or inserts them, and a row that has been withdrawn is not in the incoming file, so nothing tells merge to touch it.

Two workable answers exist. Either the reconciliation file is a complete restatement of the edition, in which case replace the partition and the 640 disappear by construction. Or the reconciliation is a delta of removals, in which case it needs to be loaded as its own feed with its own key and applied as an explicit exclusion, so that a later replay reapplies it in the same way.

The one thing that never works is deleting the rows by hand. A manual delete is invisible to the pipeline, survives no rebuild, and turns the edition into a state nobody can reproduce.

When the late fact has no dimension to point at

A lead row for a company that never appears in your exhibitor file arrives with a foreign key you cannot resolve. Dropping the fact loses a measurement. Holding it in a quarantine table means someone has to remember it exists.

Neither is the standard answer, and the standard answer has been written down since 2013. Insert a stub dimension row carrying the natural key and resolve it later. The mechanics of that, and what happens to the 340 stubs a typical edition creates, belong to placeholder dimension keys.

The limit

A lookback window is a guess about the future dressed as a parameter, and every window you choose is wrong for something.

Set it at 14 days and the contract feed, whose tail runs past 60, is quietly truncated. Set it at 90 days and every load reprocesses three months of intervals, which for a portfolio with overlapping show calendars means reprocessing editions that have nothing to do with the file that triggered the run. There is no window that is correct for all your sources, which is why the parameter belongs on the model and not on the project.

The harder limit is that some data never stops arriving. Rebooking for a show 11 months out accumulates continuously, so there is no honest moment at which the rebooking rate for an edition is final. The answer there is a stated measurement date attached to the figure, so that a rebooking rate quoted at 90 days after close and the same rate quoted at 120 days read as two different measurements instead of a contradiction. When the post-show pack runs, and what it is allowed to wait for, is a scheduling question rather than a loading one, and it sits with dag dependencies for show day.

Start by adding one column. Record the file arrival timestamp on every row your loader writes, for every source, starting with the next show. One edition of that data tells you what your real lookback windows should be, and until you have it every window in your data platform is somebody's guess.