> ## 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.

# Make repeated MERGE batches predictable

> Compare a version-guarded merge with an additive update when a batch is

TutorialApplies to: Unified engine deployments

Compare a version-guarded merge with an additive update when a batch is
repeated.

Reapplying the same source batch can leave the same final rows when actions use
stable values and compare source and target versions. That does not make every
MERGE repeat-safe. An additive assignment applies its arithmetic again.
Final-row stability is also different from retrying a submission whose commit
outcome is unknown. Use the existing operation identity to inspect or retry that
original submission.

## 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_06`. 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_06;
```

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. Reapply a versioned batch without changing final rows

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_06.repeat_version (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_06.repeat_version 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_06.repeat_version AS t
USING (SELECT 2 AS id, 'Ben' AS name, 250 AS balance, 3 AS version, false AS is_deleted UNION ALL SELECT 4, 'Dan', 400, 1, false) 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_06.repeat_version;
```

Run only the MERGE and SELECT again. After two applications, expect:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 250 | 3 | false |
| 3 | Cyd | 300 | 1 | false |
| 4 | Dan | 400 | 1 | false |

Run the MERGE statement a second time without rerunning setup. The final rows
should be identical.

## 2. Observe an additive update applying twice

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_06.repeat_increment (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_06.repeat_increment 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_06.repeat_increment AS t
USING (SELECT 2 AS id, 'Ben' AS name, 25 AS balance, 3 AS version, false AS is_deleted) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET balance = t.balance + s.balance;
```

Check all rows:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT id, name, balance, version, is_deleted FROM learn_merge_06.repeat_increment;
```

After the first MERGE:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 225 | 2 | false |
| 3 | Cyd | 300 | 1 | false |

Run only the MERGE and SELECT again. After two applications, expect:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 250 | 2 | false |
| 3 | Cyd | 300 | 1 | false |

Run the MERGE statement twice without rerunning setup. The second application
adds the incoming amount again.

## 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 additive example, change the incoming amount from 25 to 10. Customer 2
should have balance 210 after one run and 220 after two runs.

## 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_06.repeat_version;
DROP TABLE IF EXISTS learn_merge_06.repeat_increment;
DROP NAMESPACE learn_merge_06;
```

## Continue

[Apply customer change events](https://docs.e6data.com/transformation/learn/merge-events.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.