Apply customer change events

TutorialApplies to: Unified engine deployments

Compare physical deletion with a stored deletion marker and reject an older update. These examples define an event contract: id is the customer key; version increases for each change to that key; name and balance contain the full new values; is_deleted marks a delete event. The physical-delete example assumes ordered input. A tombstone is a stored deletion marker. Keeping that marker and its version lets a later merge reject an older event instead of inserting the customer again. Use the preceding deduplication lesson when a batch contains several events for one key.

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_07. 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_07;

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. Apply ordered create, update, and delete events

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_07.events_physical (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_07.events_physical 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_07.events_physical AS t
USING (SELECT 1 AS id, 'Ada' AS name, 125 AS balance, 2 AS version, false AS is_deleted UNION ALL SELECT 2, 'Ben', 0, 3, true UNION ALL SELECT 4, 'Dan', 400, 1, false) AS s
ON t.id = s.id
WHEN MATCHED AND s.is_deleted THEN DELETE
WHEN MATCHED AND s.version > t.version THEN UPDATE SET name=s.name,balance=s.balance,version=s.version
WHEN NOT MATCHED AND NOT s.is_deleted THEN INSERT *;

Check all rows:

SELECT id, name, balance, version, is_deleted FROM learn_merge_07.events_physical;

Expect these exact rows, in any display order:

id	name	balance	version	is_deleted
1	Ada	125	2	false
3	Cyd	300	1	false
4	Dan	400	1	false

Physical deletion removes the stored row and its version. With out-of-order input, an older later insert could recreate that key. The next example keeps the deletion version.

2. Keep the delete version to reject a stale update

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_07.tombstone (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_07.tombstone 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_07.tombstone AS t
USING (SELECT 2 AS id, 'Ben' AS name, 0 AS balance, 3 AS version, true AS is_deleted) AS 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);

Check all rows:

SELECT id, name, balance, version, is_deleted FROM learn_merge_07.tombstone;

Expect these exact rows, in any display order:

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

Now apply the older event:

MERGE INTO learn_merge_07.tombstone AS t
USING (SELECT 2 AS id, 'Old Ben' AS name, 230 AS balance, 2 AS version, false AS is_deleted) AS 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);
SELECT id, name, balance, version, is_deleted FROM learn_merge_07.tombstone;

The result must still be:

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

Customer 2 remains in storage with is_deleted = true and version 3. Consumers should filter out deletion markers. Keeping the version prevents the older version 2 event from making it active again.

Change one thing

Continue from the existing tombstone example without rerunning its setup. After the tombstone example, send an active version 4 for customer 2. The newer event should make that customer active again. A version 2 event should remain ignored.

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_07.events_physical;
DROP TABLE IF EXISTS learn_merge_07.tombstone;
DROP NAMESPACE learn_merge_07;

Continue

Run MERGE incrementally or review the MERGE SQL reference.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Apply customer change events

Before you start

Set up the namespace

1. Apply ordered create, update, and delete events

2. Keep the delete version to reject a stale update

Change one thing

Check the result

Clean up

Continue

​Before you start

​Set up the namespace

​1. Apply ordered create, update, and delete events

​2. Keep the delete version to reject a stale update

​Change one thing

​Check the result

​Clean up

​Continue

Before you start

Set up the namespace

1. Apply ordered create, update, and delete events

2. Keep the delete version to reject a stale update

Change one thing

Check the result

Clean up

Continue