Floemdata
Data & ETL

Idempotent Data Loads: No Duplicates on Re-run

A data load that is interrupted and restarted must not double your rows. Unique keys, upserts and versioning, plus what we found and did not find in Floemdata.

Floemdata · · 4 min read
Two identical tanks filled to the same level: one fed by one arrow, the other by two

The nightly load stopped halfway because the connection dropped. In the morning someone ran it again from the start. The next day the sales report shows exactly twice the revenue on some days. If the load were idempotent, running it any number of times would leave the same result.

Where duplicates come from

  • The load inserts every time and never checks whether the row already exists.
  • An interrupted load wrote some rows and not the rest; restarting inserts everything again.
  • An incremental load keeps a safety overlap, but nothing defines how the overlapping rows are handled.

Three approaches

  • Unique key + upsert: every row has a business key (document number plus line number, for example). If the key exists, the row is updated; otherwise it is inserted. The most common and easiest to understand.
  • Delete and rewrite (per slice): the date range being loaded is deleted first, then written. The slice must be explicitly bounded, or too much gets deleted.
  • Keep versions: every pull is stored as its own version; reading takes the latest version per key. The raw layer is untouched and history survives; the cost is storage.

When to advance the progress marker

In an incremental load, advance the "loaded up to here" marker (the watermark) only after the load is confirmed. If you advance it first and the load fails, the rows in between are never fetched again and nobody notices.

Test your own pipeline

  • Run the same day's load twice; the row count and the total amount must not change.
  • Stop a load in the middle and restart it; the result must equal a single clean run.
  • Change one row in the source and run again; the warehouse should show a new version, not a second row.

Where Floemdata stands

Floemdata's incremental extraction pattern pulls with a one-day overlap from the update date and upserts by business key. The Bronze layer is append-only; every pull is stored as a separate version and Silver takes the latest version of each record, so pulling the same row twice does not create a duplicate in Silver. The progress marker advances after Bronze confirms.

Our project notes state one exception openly: master-data tables that are pulled in full (the exchange-rate table, for example) are appended to Bronze again on every pipeline run. In a measurement dated 2026-09-15, the second run appended 2,535 unchanged master-data rows as new rows. The Bronze key still includes a timestamp in today's code. We have not re-verified the effect on Silver results or whether this was fixed since; Bronze has no retention period and we found no code that deletes from it.

To start, pick one table, run its load twice and compare the row counts. Questions are welcome at info@floemdata.com or via Floemdata.

← All guides