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

# Prepare a reliable MERGE source

> Choose one source row per key, match composite keys, and handle NULL keys

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:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/learn/merge-repeat.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.