How to upsert a Delta table in the Lakehouse with MERGE

João Barros 07 de July de 2026 4 min read

An upsert is the operation that, in a single step, updates the rows that already exist and inserts the ones that are new. In a Delta table in the Lakehouse, the MERGE command does exactly this atomically: it avoids duplicates and keeps the table consistent even when the source data arrives in batches. That makes it the foundation of any reliable incremental load, widely used to refresh dimensions and apply changes coming from a source system.

Prerequisites

A workspace with Microsoft Fabric capacity and a Lakehouse created.
A Fabric notebook attached to that Lakehouse.
A target Delta table (for example, dim_clientes) or the willingness to create one.
Basic knowledge of SQL or PySpark.

Step 1: Prepare the target table

The upsert needs a target Delta table. If you don't have one yet, create a small example table. In the Lakehouse, tables are Delta by default, but you can state it explicitly with USING DELTA. The IF NOT EXISTS makes the step safe to repeat, without dropping data that is already there.

CREATE TABLE IF NOT EXISTS dim_clientes (
  cliente_id INT,
  nome STRING,
  cidade STRING
) USING DELTA;

Step 2: Create the source with the new data

The source is the batch with the new and changed rows. For the example, create a DataFrame and register it as a temporary view so you can reference it in SQL. Notice that customers 1 and 2 will be used to update, and 3 is new, to insert.

from pyspark.sql import Row

origem = spark.createDataFrame([
    Row(cliente_id=1, nome="Ana Silva",   cidade="Porto"),
    Row(cliente_id=2, nome="Bruno Costa", cidade="Lisboa"),
    Row(cliente_id=3, nome="Carla Dias",  cidade="Braga"),
])
origem.createOrReplaceTempView("origem_clientes")

Step 3: Write the MERGE in SQL

The MERGE compares the target with the source through a key. When there is a match (WHEN MATCHED) it updates the row; when there is not (WHEN NOT MATCHED) it inserts a new one. Everything happens in a single transaction, so anyone reading the table never sees a half-updated state. This is the most readable way to write an upsert.

MERGE INTO dim_clientes AS destino
USING origem_clientes AS origem
  ON destino.cliente_id = origem.cliente_id
WHEN MATCHED THEN
  UPDATE SET destino.nome   = origem.nome,
             destino.cidade = origem.cidade
WHEN NOT MATCHED THEN
  INSERT (cliente_id, nome, cidade)
  VALUES (origem.cliente_id, origem.nome, origem.cidade);

Tip: the key used in the ON must be unique in the source. If there are repeated ids, the MERGE returns the error "multiple source rows matched". Remove the duplicates before running the command.

Step 4: The PySpark alternative (DeltaTable)

If you prefer Python, the DeltaTable API does the same upsert without writing SQL. The whenMatchedUpdateAll and whenNotMatchedInsertAll methods are handy shortcuts when the source and target columns match; if you need fine control, use whenMatchedUpdate and list only the columns to change.

from delta.tables import DeltaTable

destino = DeltaTable.forName(spark, "dim_clientes")

(destino.alias("destino")
    .merge(origem.alias("origem"),
           "destino.cliente_id = origem.cliente_id")
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute())

Verify the result

Query the table and confirm that customer 3 was inserted and that customers 1 and 2 now hold the latest values, with no duplicate rows. If you count the rows, you should have exactly three.

SELECT cliente_id, nome, cidade
FROM dim_clientes
ORDER BY cliente_id;

To confirm that the MERGE was recorded as a new version of the Delta table, run DESCRIBE HISTORY dim_clientes and look for the MERGE operation in the operation column.

Conclusion

With the MERGE command you can now perform reliable upserts on a Delta table in the Lakehouse, the foundation of any incremental load. Next, try adding a condition such as WHEN MATCHED AND destino.cidade <> origem.cidade to update only when something actually changes, or use a date column to process only the most recent records. Which table in your Lakehouse will be the first to move to an upsert with MERGE?