Synchronise a complete source snapshot

TutorialApplies to: Unified engine deployments

Mark or remove target customers that are absent from a complete source snapshot. WHEN NOT MATCHED BY SOURCE applies to target rows with no matching source row. It can update or delete those rows. Use it only when the source is a complete snapshot for the intended scope. A partial change batch omits unchanged customers too; absence from that batch does not mean that those customers should be deleted.

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

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. Mark customers absent from a complete snapshot

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_04.snapshot_mark (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_04.snapshot_mark 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_04.snapshot_mark AS t
USING (SELECT 1 AS id, 'Ada' AS name, 100 AS balance, 1 AS version, false AS is_deleted UNION ALL SELECT 2 AS id, 'Ben' AS name, 200 AS balance, 2 AS version, false AS is_deleted) AS s
ON t.id = s.id
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET is_deleted = true;

Check all rows:

SELECT id, name, balance, version, is_deleted FROM learn_merge_04.snapshot_mark;

Expect these exact rows, in any display order:

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

2. Remove customers absent from a complete snapshot

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_04.snapshot_delete (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_04.snapshot_delete 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_04.snapshot_delete AS t
USING (SELECT 1 AS id, 'Ada' AS name, 100 AS balance, 1 AS version, false AS is_deleted UNION ALL SELECT 2 AS id, 'Ben' AS name, 200 AS balance, 2 AS version, false AS is_deleted) AS s
ON t.id = s.id
WHEN NOT MATCHED BY SOURCE THEN DELETE;

Check all rows:

SELECT id, name, balance, version, is_deleted FROM learn_merge_04.snapshot_delete;

Expect these exact rows, in any display order:

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

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. Add customer 3 to the source snapshot. Both examples should leave customer 3 present and active. The source values do not update matched target rows because these examples contain no matched action.

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_04.snapshot_mark;
DROP TABLE IF EXISTS learn_merge_04.snapshot_delete;
DROP NAMESPACE learn_merge_04;

Continue

Prepare a reliable MERGE source or review the MERGE SQL reference.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Synchronise a complete source snapshot

Before you start

Set up the namespace

1. Mark customers absent from a complete snapshot

2. Remove customers absent from a complete snapshot

Change one thing

Check the result

Clean up

Continue

​Before you start

​Set up the namespace

​1. Mark customers absent from a complete snapshot

​2. Remove customers absent from a complete snapshot

​Change one thing

​Check the result

​Clean up

​Continue

Before you start

Set up the namespace

1. Mark customers absent from a complete snapshot

2. Remove customers absent from a complete snapshot

Change one thing

Check the result

Clean up

Continue