QuestionAnsweredPipelines and Python #idempotency #pipelines

A retry of my nightly load doubled the rows for that day

chloe_dubois8 October 20260 views

My load job inserts the previous day's sales into a reporting table. One night it failed halfway, I re-ran it, and now that day has twice the rows and the totals are wrong.

I do not want to add a manual clean-up step. How do people make a load safe to run twice?

3 replies

Accepted answer
kwame_asanteRegular
8 October 2026
What you want is an idempotent load: running it once or five times leaves the same result. The simplest way is to give the target table a key that identifies a row, and write with an upsert instead of a plain insert:
```
CREATE TABLE daily_sales (day TEXT, region TEXT, total REAL, UNIQUE (day, region));

INSERT INTO daily_sales (day, region, total) VALUES (?, ?, ?)
ON CONFLICT (day, region) DO UPDATE SET total = excluded.total;
```
Run the same batch twice and you still have one row per day and region. (The syntax is the same in SQLite 3.24 and later and in PostgreSQL.)

The other common pattern, when there is no natural key, is to replace a whole partition: in one transaction, delete the rows for that day, then insert them. If the job fails halfway, the transaction rolls back and the re-run starts from a clean state.
- chloe_dubois
  9 October 2026
  I used the unique key plus upsert. I also removed the duplicate rows by hand once, then tested by running the job twice on purpose. Same totals both times.

Your reply

Be kind and specific. Read the community guidelines.