How to upsert a Delta table in Databricks with MERGE

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

Updating the records that already exist and inserting the ones that are new, all in a single atomic operation: that is exactly what the MERGE command does on a Delta table. Instead of deleting and rewriting the whole table, MERGE compares a source with the target using a key and applies only the necessary changes — the so-called upsert. It is the correct way to keep a Delta table in sync on Databricks without duplicating data or running heavy pipelines.

Prerequisites

A Databricks workspace with access to an active cluster or SQL warehouse.
Permissions to create and write tables in the catalog (Unity Catalog or hive_metastore).
Basic knowledge of SQL or PySpark (DataFrames).
Knowing which key column uniquely identifies each record (for example, an id).

Step 1: Create a target Delta table

Start by creating the table you want to keep updated over time. On Databricks, any managed table uses the Delta format by default, which gives it ACID transactions — and that is precisely what makes MERGE safe. Run this SQL in a notebook cell:

CREATE TABLE clientes (
  id INT,
  nome STRING,
  cidade STRING
) USING DELTA;

INSERT INTO clientes VALUES
  (1, 'Ana', 'Lisboa'),
  (2, 'Bruno', 'Porto');

You now have two customers in the target table. This is the table MERGE will act on, comparing it with the new data you prepare next.

Step 2: Prepare the source with the new data

The source holds the changes you want to apply and can be a table, a view or a DataFrame. In this example, customer 2 has moved city and customer 3 is brand new. To keep things simple, create a temporary view as the source:

CREATE OR REPLACE TEMP VIEW clientes_novos AS
SELECT * FROM VALUES
  (2, 'Bruno', 'Coimbra'),
  (3, 'Carla', 'Braga')
AS t(id, nome, cidade);

Notice that the id column is the key linking the two tables: id 2 already exists in the target (it will be updated) and id 3 does not (it will be inserted). Choosing this key well is the most important part of the whole process.

Step 3: Run the MERGE in SQL

Now the central step. MERGE goes through the source row by row and, for each one, decides what to do based on the ON condition that compares the keys:

MERGE INTO clientes AS destino
USING clientes_novos AS origem
ON destino.id = origem.id
WHEN MATCHED THEN
  UPDATE SET destino.cidade = origem.cidade
WHEN NOT MATCHED THEN
  INSERT (id, nome, cidade)
  VALUES (origem.id, origem.nome, origem.cidade);

It reads almost literally: when the source id matches (MATCHED) an id already present in the target, update the city; when it does not match (NOT MATCHED), insert a new record. The key point is that this all happens in a single atomic transaction — it either runs completely or changes nothing, even if the cluster fails halfway.

Tip: the ON condition should always use a unique key. If the source has duplicate ids matching the same target row, MERGE fails on purpose, to avoid ambiguous results.

Step 4: Do the same in PySpark (optional)

If you prefer working in Python, the DeltaTable API does exactly the same with a chained syntax. It is the natural choice when the source is already a DataFrame produced by another transformation:

from delta.tables import DeltaTable

destino = DeltaTable.forName(spark, "clientes")
origem = spark.createDataFrame(
    [(2, "Bruno", "Coimbra"), (3, "Carla", "Braga")],
    ["id", "nome", "cidade"],
)

(destino.alias("d")
  .merge(origem.alias("o"), "d.id = o.id")
  .whenMatchedUpdate(set={"cidade": "o.cidade"})
  .whenNotMatchedInsertAll()
  .execute())

The whenNotMatchedInsertAll() method inserts every column automatically when the names match between source and target, saving you from listing them one by one. Both SQL and PySpark produce the same result — use whichever is more comfortable for your team.

Check the result

Query the table to confirm the upsert ran as expected:

SELECT * FROM clientes ORDER BY id;

You should see three rows: customer 1 unchanged, customer 2 with the city updated to Coimbra, and customer 3 (Carla) newly inserted. To confirm the operation history, run DESCRIBE HISTORY clientes and you will see a MERGE entry with the number of rows inserted and updated.

Conclusion

With a single MERGE you kept the Delta table in sync, without deleting data or risking duplicates — the foundation of any incremental pipeline on Databricks. The natural next step is to connect the source to real data, such as a file that arrives every day, and schedule the MERGE in a Databricks Workflow. One final tip: add a WHEN MATCHED AND ... THEN DELETE clause if you also need to remove records that no longer exist in the source. What other synchronization rule would make sense in your case?