Run MERGE incrementally

TutorialApplies to: Unified engine deployments

Append two source batches and apply only each new snapshot interval to an existing target. Run again with no new rows and verify the target stays unchanged.

Before you start

You need incremental SQL workloads, a writable Iceberg catalog, and a connector that supports append snapshot intervals and MERGE. A snapshot is a saved table version. This lesson uses appended event rows, not source overwrites or physical source deletes. Choose an unused namespace. The example uses learn_merge_08. Select your writable catalog in Workbench and use it for the workload too. Each appended batch contains at most one row per customer key. Versions increase per key. Do not create a new workload between runs.

1. Create separate source and target tables

Run once in the selected catalog:

SET spark.sql.caseSensitive = true;
CREATE NAMESPACE learn_merge_08;
CREATE TABLE learn_merge_08.changes (id BIGINT,name STRING,balance BIGINT,version BIGINT,is_deleted BOOLEAN) USING iceberg TBLPROPERTIES ('format-version'='2');
CREATE TABLE learn_merge_08.customers (id BIGINT,name STRING,balance BIGINT,version BIGINT,is_deleted BOOLEAN) USING iceberg TBLPROPERTIES ('format-version'='2');
INSERT INTO learn_merge_08.changes VALUES (1,'Ada',100,1,false),(2,'Ben',200,1,false);

2. Create one incremental workload

Open Workloads and create a manually started workload named Learn - customer merge.
Add one SQL task named merge_customers with the statement below.
Set its query catalog to your selected catalog and its namespace to learn_merge_08. Use case-sensitive names.
Enable Track progress for a source table. Select the same catalog, namespace learn_merge_08, table changes, and branch main.
Select available compute. Use Wait for overlap and keep this workload to one task with no dependencies.
Validate and create it.

MERGE INTO learn_merge_08.customers t USING learn_merge_08.changes s ON t.id=s.id
WHEN MATCHED AND s.version > t.version THEN
  UPDATE SET name=s.name,balance=s.balance,version=s.version,is_deleted=s.is_deleted
WHEN NOT MATCHED THEN INSERT (id,name,balance,version,is_deleted)
  VALUES(s.id,s.name,s.balance,s.version,s.is_deleted);

Run this statement through the incremental workload. Running it directly in Workbench reads the full source rather than the workload’s saved interval.

3. Run the initial batch

Start the workload. Wait for Succeeded and confirmed publication, then query:

SELECT id,name,balance,version,is_deleted FROM learn_merge_08.customers;

id	name	balance	version	is_deleted
1	Ada	100	1	false
2	Ben	200	1	false

4. Append another batch

Run the append once in Workbench:

INSERT INTO learn_merge_08.changes VALUES (2,'Ben',250,2,false),(3,'Cyd',300,1,false);

Start the same workload again. Query the target again. Expect:

id	name	balance	version	is_deleted
1	Ada	100	1	false
2	Ben	250	2	false
3	Cyd	300	1	false

5. Run without appending anything

Start the same workload a third time. Query the target again. It must still contain the same three rows and values. Inspect the run’s source progress as well as its final write state. Saved progress advances only after confirmed publication.

Change one thing

In a new append batch, add customer 2 with version 3 and is_deleted = true. Run the same workload. Customer 2 should remain stored as a deletion marker with version 3. This extends the tombstone rule from the preceding lesson.

Check the result

The first run inserted two customers. The second changed Ben and inserted Cyd. The third run had no new source rows and left the target unchanged. Source absence within a delta is not a deletion signal, so this workload does not use NOT MATCHED BY SOURCE. Keep the workload and source identities stable to retain progress. This lesson does not claim general row-level CDC from arbitrary overwrite or delete snapshots. For multiple changes to one key inside a batch, define their order and deduplicate the batch before MERGE.

Clean up

Keep scheduling paused and wait for active runs to finish. Delete only the lesson workload, then remove its tables:

DROP TABLE learn_merge_08.customers;
DROP TABLE learn_merge_08.changes;
DROP NAMESPACE learn_merge_08;

Continue with incremental workload behaviour or Process new data.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Run MERGE incrementally

Before you start

1. Create separate source and target tables

2. Create one incremental workload

3. Run the initial batch

4. Append another batch

5. Run without appending anything

Change one thing

Check the result

Clean up

​Before you start

​1. Create separate source and target tables

​2. Create one incremental workload

​3. Run the initial batch

​4. Append another batch

​5. Run without appending anything

​Change one thing

​Check the result

​Clean up

Before you start

1. Create separate source and target tables

2. Create one incremental workload

3. Run the initial batch

4. Append another batch

5. Run without appending anything

Change one thing

Check the result

Clean up