Data engineering design patterns, part 1: idempotent loads

This is part 1 of a series on data engineering design patterns. Each part starts from a concrete failure, shows the pattern that prevents it, and then shows the variant of that pattern that still breaks. I use Bartosz Konieczny's Data Engineering Design Patterns (O'Reilly, 2025) as the reference I read alongside. The part list, the examples and the code are my own.

The first pattern underpins all the others. Every scheduler retries a failed task. Every on-call engineer eventually clears a task and runs it again. Every backfill reruns days that already loaded once. If running a load a second time changes the table, every one of those ordinary events is a data incident.

#The failure

A daily load writes the 2 October transactions into a fact table. The task succeeds, but the run is marked failed because a downstream sensor times out. Someone clears it and it runs again. The next morning the daily total for 2 October is exactly twice what it was.

Nothing crashed and nothing logged an error. The load did what it was written to do: it added the rows in the file to the table. Running it twice added them twice.

The property we want has a name. A load is idempotent when running it for the same batch any number of times leaves the table as running it once would.

#What "the same table" means

That definition needs a decision before it can be tested. Most fact tables carry audit columns, and a rerun happens at a different time from the first run.

I keep two columns for this:

A rerun is therefore idempotent when every column except loaded_at matches. As it turns out below, loaded_at is also what explains the damage when a load is not idempotent.

#The companion code

Everything below comes from data-engineering-design-patterns, part 1. It runs on Docker: Postgres 16 in one container, and Python 3.11 with Spark 3.5 and Delta Lake 3.2 in another. From the part's folder:

docker compose build
docker compose up -d postgres
docker compose run --rm lab python make_landing_files.py
docker compose run --rm lab python postgres_loads.py
docker compose run --rm lab python delta_loads.py
docker compose run --rm lab python -m unittest -v test_idempotency

Each scenario starts from an empty table, loads three daily batches, and then reruns some of them. After every step, the script writes the whole table to out/<engine>/<scenario>/ as a CSV, so you can open the table at each point instead of trusting a final count. Each engine gets a one-page report.md, and the Delta scenarios also write their commit history. The reports used in this post are in example-results.

#The data

Three landing files, one per batch:

T-0105 is a late transaction. It was booked on 1 October and delivered a day late in the 2 October file. That is ordinary: a card payment that settles the next day, or a trade booked after the cut-off. It is a single row, and it decides which version of the pattern survives a rerun.

After the first load of all three batches, every scenario holds 13 rows: five dated 1 October, four for 2 October and four for 3 October.

#Append

The obvious load inserts what the file contains:

insert into fact_transactions (txn_id, account_id, business_date, amount, batch_date, loaded_at)
values (...);

Rerunning the 2 October batch gives:

Delta Lake's mode("append") gives the identical 18 rows.

A primary key on txn_id would change the symptom without curing it. The rerun would fail on the first duplicate instead of adding rows, so a retry could never succeed. My rule is that a plain append is never the load for anything that can be rerun. Append is only safe when the source guarantees each record arrives once, and a scheduler retry breaks that guarantee.

#Delete, then insert

My default for a daily batch is to delete the slice that the batch owns, then insert the batch again, inside one transaction:

begin;
delete from fact_transactions where <slice key> = '2026-10-02';
insert into fact_transactions (...) values (...);
commit;

It is two explicit statements. Each can be read on its own and checked on its own, and the delete states exactly what a rerun is allowed to replace. The pattern has two places where it can still break: the key in the where clause, and the transaction boundary.

#The variant that breaks: the wrong delete key

The natural slice key for a daily fact table is the business date. Both scenarios below are identical except for that key:

Keyed on business_date, the 2 October rerun deletes the four rows dated 2 October. It then inserts five, because the file also holds T-0105. The late row is now in the table twice. The two copies differ only in loaded_at:

T-0105,A-004,2026-10-01,52.75,2026-10-02,2026-10-31 13:06:51.421297+00:00
T-0105,A-004,2026-10-01,52.75,2026-10-02,2026-10-31 13:06:51.453677+00:00

Rerunning 1 October then deletes every row dated 1 October, including both copies of T-0105, and inserts the four rows in the 1 October file. Now the late transaction is gone entirely. One rerun duplicated it and the next one lost it, and neither raised an error.

The cause is that the delete and the insert define their scope from two different places. The delete uses a column whose values come from the source data. The insert uses whatever happens to be in the file. When they disagree, even by one row, the rerun is not idempotent.

Keyed on batch_date, they cannot disagree. Every row the load inserts carries the run's own batch_date, so the delete removes exactly what an earlier run of that batch wrote. Late rows stay with the batch that delivered them. That is the second job of the batch_date column: it identifies which load owns a row, which the business date cannot.

#The variant that breaks: two commits

The second place is the commit boundary. In the next two scenarios, the rerun of 2 October is killed after the delete and before the insert:

With two commits, the crash leaves a hole. All of 2 October is gone, and so is the late 1 October row, because it belonged to the 2 October batch. Anything that reads the table before the retry sees totals that are wrong, with no error anywhere. With one transaction, the crash rolls the delete back and the table never changes.

The same gap exists on a run that does not crash. With two commits, any reader that runs between the delete and the insert sees the hole. With one transaction, a reader sees either the old rows or the new ones.

Both retries end in the right table, so a test that only checks the final state would pass both versions. The difference only shows up between the crash and the retry, which is why every step of the companion code writes its own snapshot.

#Delta Lake: the same pattern in one commit

Delta Lake has no begin and commit around several statements. Each operation is its own commit. The direct translation of delete-then-insert is therefore the two-commit version:

DeltaTable.forPath(spark, path).delete("batch_date = DATE'2026-10-02'")
batch.write.format("delta").mode("append").save(path)

It fails in the same way. A crash between the two lines leaves 8 rows, and the commit history shows the delete with nothing after it until the retry:

The one-commit form is an overwrite with a replaceWhere predicate. Delta deletes the rows matching the predicate and adds the new ones in a single commit:

(
    batch.write.format("delta")
    .mode("overwrite")
    .option("replaceWhere", "batch_date = DATE'2026-10-02'")
    .save(path)
)

Keyed on batch_date, reruns of 2 October and 1 October both leave the same 13 rows, with one WRITE commit for each load.

Keyed on business_date, Delta behaves differently from Postgres. By default it checks that every written row matches the predicate. The 2 October batch contains T-0105, dated 1 October, so the write is refused:

[DELTA_REPLACE_WHERE_MISMATCH] Written data does not conform to partial
table overwrite condition or constraint 'business_date = DATE'2026-10-02''.

Nothing is committed. Postgres silently duplicated the late row. Delta fails loudly instead, before anything is written. That is better, but the batch still does not load, so the business-date key is still the wrong key. The check simply moves the problem from a wrong table to a failed run.

#Side by side

Partition overwrite and MERGE are two other ways to make a load idempotent. They have their own failure modes, so each gets its own part: part 4 on upserts with MERGE, and part 5 on partition overwrite.

#Checks worth keeping

The companion code's tests state the claims above as assertions, on both engines:

• An append rerun produces 18 rows.
• A delete keyed on the business date duplicates T-0105 and later loses it.
• A delete keyed on the batch date produces the same table after every rerun.
• Two commits leave 2 October empty after a crash. One transaction leaves the table unchanged.
• replaceWhere on the business date refuses the 2 October batch.

The test I would carry into any real pipeline is simpler: load a batch, snapshot the table, load the same batch again, and compare the two snapshots on every column except the wall-clock one. If that test cannot be written, the load does not have a defined slice yet.

#What is next

Every load in this post was handed its batch: one file, one date. In practice, someone has to decide which rows count as the next batch. Part 2 is incremental loading and watermarks: high-water-mark tables against the orchestrator's data intervals, and the rows lost when the watermark moves before the write commits.

#Summary to save

• A load is idempotent when running the same batch any number of times leaves the table as running it once would. Retries, cleared tasks and backfills all depend on it.
• Decide what "the same table" means before testing it. Keep a logical-date column that a rerun reproduces, and a wall-clock column for forensics that the comparison ignores.
• A plain append is never the load for anything that can be rerun. A primary key only turns the duplicate into a failed retry.
• Delete-then-insert needs the delete and the insert to agree on scope. Key the delete on the batch the load owns, not on a business column the source controls. One late row is enough to show the difference.
• Put the delete and the insert in one transaction. Two commits leave a hole that readers can see, and a crash keeps the hole until a retry.
• On Delta Lake, each operation is its own commit. The one-commit delete-then-insert is mode("overwrite") with replaceWhere, and its default check refuses rows outside the predicate.
• Test idempotency directly: load, snapshot, load again, compare.