Unique key for merge behavior in dbt - Step-by-Step Execution

Concept Flow - Unique key for merge behavior

Start Merge Operation

↓

Identify Unique Key

↓

Check for Duplicates

↓

Error or Handle

↓

Match Records on Unique Key

↓

Update or Insert Records

↓

End

The merge process starts by identifying a unique key to match records. If duplicates exist in the key, it must be handled before merging. Then records are matched and updated or inserted accordingly.

Execution Sample

dbt

merge into target_table as t
using source_table as s
on t.unique_key = s.unique_key
when matched then update set t.col = s.col
when not matched then insert (unique_key, col) values (s.unique_key, s.col);

This SQL-like merge matches records on a unique key to update existing rows or insert new ones.

Execution Table

Step	Action	Unique Key Check	Match Found	Result
1	Start merge	Unique key identified	N/A	Ready to merge
2	Check duplicates in source	No duplicates found	N/A	Proceed
3	Match source row 1 with target	Unique key valid	Yes	Update target row
4	Match source row 2 with target	Unique key valid	No	Insert new row
5	Match source row 3 with target	Unique key valid	Yes	Update target row
6	Merge complete	N/A	N/A	All rows processed

💡 All source rows processed with unique keys matched or inserted

Variable Tracker

Variable	Start	After Step 2	After Step 4	Final
unique_key_check	Not started	No duplicates	No duplicates	Validated
matched_rows	0	0	1	2
inserted_rows	0	0	1	1

Key Moments - 2 Insights

Why must the unique key have no duplicates before merging?

Duplicates in the unique key cause ambiguity in matching rows, leading to errors or incorrect updates as shown in execution_table step 2.

What happens if a source row's unique key does not match any target row?

The row is inserted as a new record, demonstrated in execution_table step 4 where no match is found and insertion occurs.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result at step 3?

A Insert new row

B Error due to duplicate key

C Update target row

D Skip row

💡 Hint

Refer to execution_table row with Step 3 under Result column

At which step does the merge insert a new row?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Check execution_table rows for 'Insert new row' in Result column

If duplicates were found in the unique key at step 2, what would likely happen?

A Merge proceeds normally

B Merge errors or stops

C Merge inserts duplicate rows

D Merge updates all rows

💡 Hint

See key_moments explanation about duplicates causing errors at step 2

Concept Snapshot

Unique key for merge behavior:
- Identify a unique key to match records
- Ensure no duplicates in unique key
- Match source and target on unique key
- Update matched rows
- Insert unmatched rows
- Prevents ambiguous merges and errors

Full Transcript

In dbt merge operations, a unique key is essential to match source and target records. The process starts by identifying this key and checking for duplicates. If duplicates exist, the merge can fail or produce errors. When no duplicates are found, each source row is matched to the target using the unique key. If a match is found, the target row is updated. If no match exists, the source row is inserted as new. This ensures data integrity and correct merge behavior.