How to do an idempotent upsert in ELT with MERGE

When a data pipeline runs more than once — because it failed halfway, because it was reprocessed, or simply because it is scheduled — we risk duplicating rows in the destination table. An idempotent load solves this: we can run it as many times as we want and the final result is always the same. Here you will learn how to do an idempotent upsert in ELT with the MERGE statement, updating and inserting data without ever creating duplicates.

Prerequisites

• Access to a database or warehouse that supports MERGE (SQL Server, Azure Synapse, Snowflake, Databricks/Delta Lake, BigQuery, Oracle, or PostgreSQL 15+).
• A staging table with the data already loaded (the "load" in ELT).
• Basic SQL knowledge (SELECT, INSERT, UPDATE).

Step 1: Understand what an idempotent load is

In ELT (Extract, Load, Transform), data is first loaded raw and only then transformed inside the warehouse. If the transformation uses a plain INSERT, every new run adds the same rows again. An idempotent load avoids this: instead of "always insert", it says "update if it already exists, insert if it is new". The central piece is the business key — the column (or columns) that uniquely identifies each record, such as cliente_id.

Step 2: Prepare the destination and staging tables

We need two tables: the staging table, which receives the new data on each run, and the destination table, which keeps the current state. The destination table should have a primary key on the business key so the engine can compare records efficiently.

-- Destination table: current state of customers
CREATE TABLE dim_cliente (
    cliente_id    INT           NOT NULL,   -- business key
    nome          VARCHAR(100),
    email         VARCHAR(150),
    atualizado_em DATETIME,
    CONSTRAINT pk_dim_cliente PRIMARY KEY (cliente_id)
);

The stg_cliente table has the same columns and is filled on each load with the latest data from the source. It is what we will use to feed the destination.

Step 3: Write the upsert with MERGE

The MERGE statement compares the source (staging) with the destination using the business key. When it finds a match, it updates the row; when it does not, it inserts a new one. This "update or insert" logic is what makes the load idempotent.

MERGE INTO dim_cliente AS destino
USING stg_cliente     AS origem
    ON destino.cliente_id = origem.cliente_id
WHEN MATCHED THEN
    UPDATE SET
        destino.nome          = origem.nome,
        destino.email         = origem.email,
        destino.atualizado_em = origem.atualizado_em
WHEN NOT MATCHED THEN
    INSERT (cliente_id, nome, email, atualizado_em)
    VALUES (origem.cliente_id, origem.nome, origem.email, origem.atualizado_em);

Notice there is no DELETE: we only update and insert. If you run this MERGE ten times in a row with the same staging data, the final table stays exactly the same — and that is precisely what being idempotent means. If your engine does not support MERGE, you can achieve the same effect with a DELETE of the affected keys followed by an INSERT, all inside the same transaction.

Step 4: Reprocess safely

Because the operation is idempotent, reprocessing is no longer scary. Just refill the staging table with the period you want to reload and run the MERGE again. There is no need to delete anything by hand or to fear duplicating data by mistake.

Tip: keep the atualizado_em column so you know when each record was last touched. It helps a lot when auditing loads and investigating issues.

Verify the result

The proof that the load is idempotent is simple: the total number of rows must equal the number of distinct keys. If they differ, it is a sign that there are duplicates.

SELECT COUNT(1)                    AS total_linhas,
       COUNT(DISTINCT cliente_id)  AS chaves_distintas
FROM dim_cliente;

Run the MERGE twice in a row and run this query again: both values stay equal and the total does not grow. If the total goes up on the second run, check that the ON condition really uses the correct business key.

Conclusion

With MERGE you turned a fragile load, which duplicated data on every attempt, into an idempotent load that is safe to reprocess — one of the foundations of any reliable ELT pipeline. The next step is to combine this technique with incremental loads, touching only the rows that changed since the last run. Which of your tables would benefit most from an idempotent upsert?