> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Apply customer change events

> Compare physical deletion with a stored deletion marker and reject an older

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:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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 |

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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 |

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/learn/merge-incremental.md) or review the
[MERGE SQL reference](https://docs.e6data.com/transformation/sql/writes.md#merge).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.