# Upserts with Delta Lake MERGE

> Write idempotent upserts with MERGE INTO, handle duplicate source keys, apply deletes from CDC and avoid rewriting the whole table.

- Source: https://akshaythakare.com/notes/databricks/delta-merge-upserts
- Topic: Databricks
- Level: intermediate
- Reading time: 2 min
- Tags: databricks, delta-lake, merge, cdc, interview
- Updated: 2026-10-11

## The basic upsert

```sql
MERGE INTO silver.customers AS t
USING updates AS s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;
```

The `updated_at` condition makes it safe to replay: an older change never overwrites a newer one.

## Deduplicate the source first

MERGE fails when **several source rows match the same target row**. Keep the latest per key before merging:

```sql
CREATE OR REPLACE TEMP VIEW updates AS
SELECT * FROM staged_changes
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;
```

## Deletes from CDC

```sql
MERGE INTO silver.customers t
USING updates s ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op = 'DELETE' THEN DELETE
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET *
WHEN NOT MATCHED AND s.op <> 'DELETE' THEN INSERT *;
```

## Keep MERGE fast

- Add a partition or date predicate to the `ON` clause so Delta reads only the affected files.
- Run `OPTIMIZE` regularly. MERGE rewrites whole files, and smaller files mean less rewriting.
- Enable deletion vectors where available, so updates mark rows instead of rewriting files.

> **Tip: Interview framing**
>
> "Dedupe the source to one row per key, guard updates with a version or timestamp, and prune the target with a partition predicate."