Idempotent warehouse loads mean you can rerun show day without fear

Eighteen hundred hours on the first day. The scan loader has been running every five minutes all day and the 17:45 run died partway through, somewhere after it had written most of the day's rows. The person on call has a show director asking for the day one number in ten minutes, so they run it again, because running it again is the obvious thing to do and it usually works.

It does work, in the sense that the job completes. The scan count is now larger than the number of scans that happened, and nobody will notice for a week.

Idempotent warehouse loads exist to remove that failure mode entirely, and for a business whose entire year of operational data arrives across four days under time pressure, it is the single most valuable property a load can have.

The definition, and why show week is where it bites

Toby Mao of Tobiko Data wrote it down cleanly in May 2023: "Idempotency is the property that running the same query twice with the same inputs results in the same output."

He is equally direct about the common way of losing it. "Loading data with the append strategy is an example of a non idempotent pipeline. If you accidentally run it twice on the same data, you'll end up with duplicates."

Most warehouse loads start as appends, because appending is what the first version of anything does. That is survivable in a business with a daily cycle, where an operator notices a duplicated day and reloads it tomorrow. It is much worse in an exhibition, because the reruns cluster into the four days when nobody has time to check, the errors compound across five minute intervals, and the resulting numbers get quoted in a closing press release before anyone reconciles anything.

The Apache Airflow project's best practices documentation, as it stands in 2026, frames the requirement in terms most engineers already accept elsewhere: "You should treat tasks in Airflow equivalent to transactions in a database. This implies that you should never produce incomplete results from your tasks." Then it gives the concrete instruction: "Do not use INSERT during a task re-run, an INSERT statement might lead to duplicate rows in your database. Replace it with UPSERT."

What an accidental rerun does to the numbers

Work it through, because the shape of the error is what makes it hard to catch.

A full edition is 38,900 scans. Rerun a completed append load from the start and the table holds 77,800. That one is nearly catchable, because 77,800 is a suspiciously round doubling and somebody comparing to the vendor's own console might spot it.

The interrupted case is nastier. Suppose the failed run had written 24,000 rows before it died, and the rerun writes the full 38,900. The table now holds 62,900, an inflation of 62 per cent that corresponds to no clean multiple of anything. Nothing about 62,900 looks wrong.

Then look at what happens to the derived figures. If the edition has 9,600 distinct badges scanned, the true scans per badge is 38,900 over 9,600, which is 4.05. After the interrupted rerun it reads 62,900 over 9,600, or 6.55. The distinct badge count is unchanged, because duplicating rows does not invent people, so unique visitor figures stay correct while every intensity measure inflates. A report showing both will look internally inconsistent in a way that takes an afternoon to diagnose, and a report showing only the intensity measure will look like a very good show.

The exhibitor league table is the worst of it. Every stand inflates roughly proportionally, so the ranking is unchanged and only the magnitudes are wrong. Ranking is what people look at. The error passes review.

What makes a load idempotent?

Three properties, and the first two are usually the only ones discussed.

The write has to replace rather than accumulate. The unit of replacement has to be well defined and match the unit of work. And nothing inside the job may depend on when the job ran.

On the unit of replacement, the Airflow documentation is specific: "Read and write in a specific partition. Never read the latest available data in a task. Someone may update the input data between re-runs, which results in different outputs." For an exhibition the natural partition is the edition, sometimes the edition and show day, and almost never the calendar date on which the load happened to execute.

On the third property, the same documentation names the usual culprit: "The Python datetime now() function gives the current datetime object. This function should never be used inside a task, especially to do the critical computation, as it leads to different outcomes on each run." A job that stamps rows with the time it ran, or that filters source records to the last two hours relative to the clock, produces a different result on every execution by construction, and no write strategy repairs that.

The two mechanisms, briefly

Two write strategies deliver the property, and the choice between them is a real design decision with enough substance to belong to L21 rather than to this post.

Insert overwrite replaces a whole partition atomically. Toby Mao's description of the mechanism is the clearest short version: "Insert overwrite will atomically delete a folder before writing contents into it, ensuring idempotency." Load the 2026 edition partition and whatever was there before is gone, so the second run and the first run leave the same table.

Merge matches incoming rows against existing rows on a key and updates or inserts accordingly. Mao describes it as a newer technique that "works by matching rows between a source and target on a key, and then updating or inserting it". Merge needs a key that genuinely identifies a row, which for badge scans is the vendor's own scan identifier carried through as a degenerate dimension, and the quality of that key is the whole risk.

Both work. The trade offs between them, and which suits a completed edition, are worth a separate argument.

What stays non idempotent even when the write is clean

A repeatable write does not make a repeatable job, and the leftovers are easy to miss.

Surrogate keys assigned from a database sequence advance on every run, so a rerun produces the same logical rows with different keys. If any downstream table already captured the old keys, it now points at nothing. The fix is to derive keys deterministically from the natural key, or to assign them in a dimension load that is itself idempotent.

Current row flags on type 2 dimensions are the second. A rerun that closes off a row and opens a new one, applied twice, leaves two open rows or a history with a gap in it. The dimension load has to be written so that applying the same source twice converges.

Side effects are the third and the most damaging. A job that emails the exhibitor report, posts to a channel, or calls a vendor API to mark records as processed does something on the second run that cannot be taken back. Keep those out of the load. Put them behind a separate step that reads a completed state, so a rerun of the load costs nothing and a rerun of the notification is an explicit decision.

How do you actually test for it?

Run it twice and diff. The test is cheaper to write than the discussion about whether it is needed.

Take a fixture edition, restore it into a scratch schema, run the load, snapshot the target tables. Run the load again with identical input and snapshot again. Compare three things: the row count, the count of distinct natural keys, and a checksum over the sorted rows. All three matching means the load survived a repeat. Row count matching while the checksum differs means something inside is time dependent, which is usually a load timestamp column and occasionally something worse.

Run it a third time for free while you are there. A load that is stable from run two to run three but differs between one and two is doing first run initialisation somewhere, which is worth knowing about before an edition depends on it.

This belongs in whatever exercises the pipeline between shows, because an idempotency test that only runs during show week has the same problem as everything else in a pipeline that sits idle for eleven months.

Where idempotency stops helping

Idempotency is a claim about the same input twice. It says nothing at all about different input, and in an exhibition the input keeps changing after the show closes.

Lead retrieval exports arrive for weeks. Registration corrections trickle in. A hall that lost connectivity delivers its scans in a lump on the Friday. Every one of those is a new input to the same edition, and an idempotent load handles them correctly by definition: reload the edition and the table reflects everything currently known. What it does not do is tell you that a published figure has moved, which is a reporting and communication problem that late arriving data creates on its own terms.

The second limit is that insert overwrite makes destruction easy. Replacing a partition is idempotent and it is also irreversible, so a bug that produces an empty extract will cheerfully replace a good edition with nothing, atomically and without complaint. The guards are a non zero row count assertion before the swap and a retained previous version, and neither is provided by the write strategy itself.

Take your current loader, point it at a copy of last edition's data, and run it twice. Count the rows after each run. If the second number is larger than the first, you have found the thing to fix before the next show opens, and the fix is usually smaller than the reconciliation you will otherwise be doing in the week after it closes. Record which write strategy you settled on in the data platform conventions so the next person does not quietly reintroduce an append.