How to Make an ETL Pipeline Idempotent (PostgreSQL Upsert)

A plain INSERT-based ETL loader doubles your row count every time it re-runs with the same data. The fix is a unique index on the natural key plus an upsert - INSERT ... ON CONFLICT DO UPDATE - so re-running the pipeline any number of times always produces the same result.

Data Engineerpythonpostgresqletl

Why plain INSERT creates duplicates

When a pipeline uses a bare INSERT, PostgreSQL happily adds a new row every time the script runs - even if a row with the same order_id is already there. Run the same 100-row CSV twice and you get 200 rows. Three times: 300. Downstream dashboards that SUM(total_amount) now report double or triple revenue.

The table has no uniqueness constraint, so the database has no way to detect the duplicate - it trusts whatever the caller hands it.

Step 1: add a unique index on the natural key

Before changing the INSERT, tell PostgreSQL which column uniquely identifies a row. A unique index is the enforcement mechanism that makes ON CONFLICT work:

CREATE UNIQUE INDEX IF NOT EXISTS orders_order_id_uniq
    ON orders(order_id);

IF NOT EXISTS makes this safe to run inside the ETL script itself on every execution - it is a no-op after the first run.

Step 2: switch INSERT to an upsert

Replace the plain INSERT with INSERT ... ON CONFLICT DO UPDATE:

cursor.execute("""
    INSERT INTO orders (order_id, customer_id, product_name,
                        quantity, total_amount, order_date)
    VALUES (%s, %s, %s, %s, %s, %s)
    ON CONFLICT (order_id) DO UPDATE SET
        customer_id  = EXCLUDED.customer_id,
        product_name = EXCLUDED.product_name,
        quantity     = EXCLUDED.quantity,
        total_amount = EXCLUDED.total_amount,
        order_date   = EXCLUDED.order_date
""", (row['order_id'], row['customer_id'], row['product_name'],
      int(row['quantity']), float(row['total_amount']), row['order_date']))

EXCLUDED refers to the row that was attempted but conflicted. The DO UPDATE overwrites the existing row with the latest CSV values. Running this 10 times with the same CSV leaves exactly 100 rows every time.

What idempotency means for pipelines

An idempotent operation produces the same result regardless of how many times it runs. For ETL this matters because:

Schedulers retry on transient failures - the pipeline re-runs without warning.
Backfills replay historical data through the same loader.
On-call engineers run the script manually to test a fix.

None of these should corrupt the target table. INSERT ... ON CONFLICT is the standard PostgreSQL mechanism to guarantee this at the database level.

DO NOTHING vs DO UPDATE

DO NOTHING - silently skips conflicting rows; use it when the first write is canonical and later re-deliveries should be ignored.
DO UPDATE - overwrites the existing row with the new values; use it when the source CSV is the authoritative version and updates must propagate.

For an order pipeline where the CSV reflects the latest state, DO UPDATE is almost always the right choice.

Want to try it hands-on? HeyDevJob gives you this exact setup in a live cloud workspace in your browser - edit it, run it, and see it work. Free, nothing to install.

Try it in a workspace →

What you'll practice

Adding a unique index on a natural key with CREATE UNIQUE INDEX IF NOT EXISTS
Writing an upsert with INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col
Verifying idempotency by running the loader twice and checking COUNT(*) stays constant

FAQ

Why does my ETL pipeline create duplicate rows on re-run?

A plain INSERT has no duplicate check - it adds a new row every time, even if a row with the same business key already exists. Add a unique index on the natural key (e.g. order_id) and switch to INSERT ... ON CONFLICT DO UPDATE to make the loader idempotent.

What is INSERT ... ON CONFLICT DO UPDATE in PostgreSQL?

It is PostgreSQL's upsert syntax. When the INSERT would violate a unique constraint, the ON CONFLICT clause fires instead - DO UPDATE overwrites the existing row with the new values, DO NOTHING skips the conflicting row. The EXCLUDED keyword refers to the row that was attempted.

How do I make an ETL pipeline safe to re-run?

Use a unique index on the natural key plus INSERT ... ON CONFLICT DO UPDATE (upsert). Also create the index with IF NOT EXISTS so the script is idempotent itself - running it 10 times leaves the schema and data in the same state as running it once.

How do you make a pipeline idempotent?

Make re-running produce the same result: use upserts (INSERT ON CONFLICT DO UPDATE) keyed on a unique column instead of plain inserts, or delete-and-replace the target partition each run. Then a retry never doubles rows.

Keep learning

Speed Up a Slow SQL Report QueryData project Make a CSV Importer Resilient to Bad DataData project Convert Timestamps to Local Time (Timezones)Data project Data roadmapStep by step to hired Data interview questionsSTAR answers All Data projectsProjects hub

Learn it by doing. Open this in a live cloud workspace, make the change yourself, and keep a record of the work you can share.

Open the workspace →