Unique key for merge behavior in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the role of unique keys in dbt merge operations

In dbt, when performing a merge operation to update a target table from a source, why is defining a unique key important?

A It speeds up the merge by indexing the entire target table automatically.

B It ensures that each row in the source matches exactly one row in the target, preventing duplicate updates.

C It allows dbt to skip the merge step if the source table is empty.

D It automatically creates a primary key constraint on the target table.

💡 Hint

Think about what happens if multiple rows in the source match the same row in the target.

The unique key ensures that each source row corresponds to exactly one target row. This prevents ambiguous updates or duplicate rows during the merge.

❓ Predict Output

intermediate

Output of a dbt merge with duplicate keys in source

Given the following simplified merge logic in dbt, what will happen if the source table contains duplicate rows for the unique key?

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

A The merge will fail with an error due to multiple matches for the same target row.

B The merge will update the target row multiple times, ending with the last source row's value.

C The merge will insert duplicate rows into the target table.

D The merge will ignore duplicate source rows and update only once.

💡 Hint

Consider how SQL merge handles multiple matches on the same key.

SQL merge requires the join condition to uniquely identify target rows. Duplicate keys in source cause multiple matches, leading to an error.

❓ data_output

advanced

Resulting target table after merge with unique key

Consider the following target and source tables before a dbt merge operation:

Target table:
id | value
1  | 'A'
2  | 'B'
3  | 'C'

Source table:
id | value
2  | 'X'
4  | 'Y'

After running this merge:

merge into target as t
using source as s
on t.id = s.id
when matched then update set t.value = s.value
when not matched then insert (id, value) values (s.id, s.value)

What will be the content of the target table?

A 1|'A', 2|'X', 3|'C', 4|'Y'

B 2|'X', 4|'Y'

C 1|'A', 2|'X', 3|'C'

D 1|'A', 2|'B', 3|'C', 4|'Y'

💡 Hint

Think about which rows get updated and which get inserted.

Row with id=2 is updated from 'B' to 'X'. Row with id=4 is inserted. Other rows remain unchanged.

🔧 Debug

advanced

Identify the cause of merge failure due to unique key violation

A dbt merge operation fails with the error: SQL Error: The MERGE statement attempted to UPDATE or DELETE the same row more than once. Given this merge code:

merge into target t
using source s
on t.user_id = s.user_id
when matched then update set t.status = s.status
when not matched then insert (user_id, status) values (s.user_id, s.status)

What is the most likely cause?

A The source table is empty, so the merge cannot find rows to update.

B The target table has duplicate user_id values violating primary key constraints.

C The merge statement is missing a <code>delete</code> clause causing conflicts.

D The source table contains duplicate user_id values causing multiple matches to the same target row.

💡 Hint

Consider what happens if the join condition matches multiple source rows to one target row.

Duplicate keys in the source cause the merge to try updating the same target row multiple times, which is not allowed.

🚀 Application

expert

Designing a unique key for a complex merge scenario

You have a source table with columns user_id, event_date, and event_type. You want to merge this into a target table that stores the latest event per user and event type. Which unique key should you define for the merge to work correctly?

A A composite key on <code>(user_id, event_type)</code> to uniquely identify each event type per user.

B A composite key on <code>(user_id, event_date)</code> to uniquely identify events by date per user.

C A composite key on <code>(user_id, event_date, event_type)</code> to uniquely identify each event occurrence.

D A unique key on <code>event_date</code> only, since dates are unique.

💡 Hint

Think about what combination of columns uniquely identifies each event occurrence.

Using all three columns ensures each event is uniquely identified, preventing merge conflicts and allowing correct updates.