Prepare a reliable MERGE source

TutorialApplies to: Unified engine deployments

Choose one source row per key, match composite keys, and handle NULL keys explicitly. Star actions map columns by name, rather than by their position in the SELECT list. Each required target field needs one matching source field. Prepare one intended incoming row per business key before applying updates. ROW_NUMBER chooses a row only when its ordering defines a clear winner. Here version orders changes, and event_id breaks equal-version ties under an explicit example contract.

Before you start

You need a writable Iceberg catalog with namespace creation, table creation, and MERGE support. These examples create format-v2 tables in a private namespace. They use inline data and do not need an external sample dataset. Choose an unused namespace. The examples use learn_merge_05. Replace it everywhere if it already exists. In Workbench, select your writable catalog before running any SQL. Keep that catalog selected throughout the lesson. Opening an example does not execute it.

Set up the namespace

Run this setup once:

SET spark.sql.caseSensitive = true;
CREATE NAMESPACE learn_merge_05;

Each example creates a separate table. Run an example’s setup once. To start it again, remove only that example’s table first, using the cleanup section. Scripts execute as separate operations; they are not a multi-statement transaction.

1. Map star actions by column name

Start with these target rows:

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

CREATE TABLE learn_merge_05.star_actions (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_05.star_actions VALUES (1, 'Ada', 100, 1, false), (2, 'Ben', 200, 2, false), (3, 'Cyd', 300, 1, false);

Apply the incoming source below:

MERGE INTO learn_merge_05.star_actions AS t
USING (SELECT 'Renamed' AS name, 3 AS version, false AS is_deleted, 250 AS balance, 2 AS id UNION ALL SELECT 'Dan',1,false,400,4) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Check all rows:

SELECT id, name, balance, version, is_deleted FROM learn_merge_05.star_actions;

Expect these exact rows, in any display order:

id	name	balance	version	is_deleted
1	Ada	100	1	false
2	Renamed	250	3	false
3	Cyd	300	1	false
4	Dan	400	1	false

The source lists its columns in a different order. Customer 2 still receives the correct values because the star actions resolve columns by name. Extra source columns are not a schema-evolution request.

2. Choose one incoming row per key with an explicit tie-break

Start with these target rows:

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

CREATE TABLE learn_merge_05.deduplicate (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_05.deduplicate VALUES (1, 'Ada', 100, 1, false), (2, 'Ben', 200, 2, false), (3, 'Cyd', 300, 1, false);

Apply the incoming source below:

MERGE INTO learn_merge_05.deduplicate AS t
USING (SELECT id,name,balance,version,is_deleted FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY version DESC, event_id DESC) AS rn FROM (SELECT 2 AS id, 'Older' AS name, 210 AS balance, 3 AS version, false AS is_deleted, 10 AS event_id UNION ALL SELECT 2,'Latest',260,4,false,11 UNION ALL SELECT 2,'Winner',270,4,false,12) AS changes) AS ranked WHERE rn = 1) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET name = s.name, balance = s.balance, version = s.version
WHEN NOT MATCHED THEN INSERT (id,name,balance,version,is_deleted) VALUES (s.id,s.name,s.balance,s.version,s.is_deleted);

Check all rows:

SELECT id, name, balance, version, is_deleted FROM learn_merge_05.deduplicate;

Expect these exact rows, in any display order:

id	name	balance	version	is_deleted
1	Ada	100	1	false
2	Winner	270	4	false
3	Cyd	300	1	false

3. Match by customer ID and region

Start with these target rows:

id	region	balance
1	North	100
1	South	200

CREATE TABLE learn_merge_05.composite_key (id BIGINT, region STRING, balance BIGINT)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_05.composite_key VALUES (1, 'North', 100), (1, 'South', 200);

Apply the incoming source below:

MERGE INTO learn_merge_05.composite_key AS t
USING (SELECT 1 AS id, 'North' AS region, 150 AS balance UNION ALL SELECT 1, 'West', 400) AS s
ON t.id=s.id AND t.region=s.region
WHEN MATCHED THEN UPDATE SET balance=s.balance
WHEN NOT MATCHED THEN INSERT (id,region,balance) VALUES(s.id,s.region,s.balance);

Check all rows:

SELECT id, region, balance FROM learn_merge_05.composite_key;

Expect these exact rows, in any display order:

id	region	balance
1	North	150
1	South	200
1	West	400

4. Observe how equality treats NULL keys

Start with these target rows:

id	name	balance	version	is_deleted
NULL	Existing	5	1	false

CREATE TABLE learn_merge_05.null_key (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_05.null_key VALUES (NULL, 'Existing', 5, 1, false);

Apply the incoming source below:

MERGE INTO learn_merge_05.null_key AS t
USING (SELECT CAST(NULL AS BIGINT) AS id, 'Incoming' AS name, 10 AS balance, 2 AS version, false AS is_deleted) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET name = s.name, balance = s.balance, version = s.version
WHEN NOT MATCHED THEN INSERT (id,name,balance,version,is_deleted) VALUES (s.id,s.name,s.balance,s.version,s.is_deleted);

Check all rows:

SELECT id, name, balance, version, is_deleted FROM learn_merge_05.null_key;

Expect these exact rows, in any display order:

id	name	balance	version	is_deleted
NULL	Existing	5	1	false
NULL	Incoming	10	2	false

SQL equality does not treat two NULL keys as equal. The incoming NULL-key row is inserted. A NULL key is often better rejected before the merge.

5. Use an explicit null-safe match when NULL is a valid key

Start with these target rows:

id	name	balance	version	is_deleted
NULL	Existing	5	1	false

CREATE TABLE learn_merge_05.null_safe (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_05.null_safe VALUES (NULL, 'Existing', 5, 1, false);

Apply the incoming source below:

MERGE INTO learn_merge_05.null_safe AS t
USING (SELECT CAST(NULL AS BIGINT) AS id, 'Incoming' AS name, 10 AS balance, 2 AS version, false AS is_deleted) AS s
ON t.id <=> s.id
WHEN MATCHED THEN UPDATE SET name = s.name, balance = s.balance, version = s.version
WHEN NOT MATCHED THEN INSERT (id,name,balance,version,is_deleted) VALUES (s.id,s.name,s.balance,s.version,s.is_deleted);

Check all rows:

SELECT id, name, balance, version, is_deleted FROM learn_merge_05.null_safe;

Expect these exact rows, in any display order:

id	name	balance	version	is_deleted
NULL	Incoming	10	2	false

The <=> operator treats two NULL values as a match here. Use this only when the data contract defines NULL as a valid matching key.

Change one thing

Start the relevant example again from its original target rows. Drop only that example table, rerun its CREATE TABLE and INSERT statements, then apply the changed MERGE. Keep the lesson namespace. In the deduplication example, change event 11 to version 5. The winning row should have name Latest, balance 260, and version 5. Keep the explicit tie-break rule.

Check the result

Compare every row and field with the expected tables, including unchanged customers and the number of copies of each key. A Succeeded status alone is not the result check. Wait for confirmed publication before reading the target.

Clean up

Remove only the private tables created by this lesson:

DROP TABLE IF EXISTS learn_merge_05.star_actions;
DROP TABLE IF EXISTS learn_merge_05.deduplicate;
DROP TABLE IF EXISTS learn_merge_05.composite_key;
DROP TABLE IF EXISTS learn_merge_05.null_key;
DROP TABLE IF EXISTS learn_merge_05.null_safe;
DROP NAMESPACE learn_merge_05;

Continue

Make repeated MERGE batches predictable or review the MERGE SQL reference.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Prepare a reliable MERGE source

Before you start

Set up the namespace

1. Map star actions by column name

2. Choose one incoming row per key with an explicit tie-break

3. Match by customer ID and region

4. Observe how equality treats NULL keys

5. Use an explicit null-safe match when NULL is a valid key

Change one thing

Check the result

Clean up

Continue

​Before you start

​Set up the namespace

​1. Map star actions by column name

​2. Choose one incoming row per key with an explicit tie-break

​3. Match by customer ID and region

​4. Observe how equality treats NULL keys

​5. Use an explicit null-safe match when NULL is a valid key

​Change one thing

​Check the result

​Clean up

​Continue

Before you start

Set up the namespace

1. Map star actions by column name

2. Choose one incoming row per key with an explicit tie-break

3. Match by customer ID and region

4. Observe how equality treats NULL keys

5. Use an explicit null-safe match when NULL is a valid key

Change one thing

Check the result

Clean up

Continue