Unique key for merge behavior in dbt - Mini Project: Build & Apply

Unique Key for Merge Behavior in dbt

📖 Scenario: You work as a data analyst managing customer data in a data warehouse. You want to update your customers table with new information from a staging table. To do this safely, you need to define a unique key for the merge operation so dbt knows how to match records.

🎯 Goal: Build a dbt model that uses the unique_key configuration to specify the column that uniquely identifies each customer for merge behavior.

📋 What You'll Learn

Create a dbt model SQL file named customers.sql with sample customer data.

Add a unique_key configuration in the model to specify the unique identifier column.

Use the merge strategy in the model configuration.

Print the final SQL query that dbt will run for the merge.

💡 Why This Matters

🌍 Real World

In real data warehouses, you often need to update tables with new or changed data. Using a unique key for merge behavior ensures data integrity and avoids duplicates.

💼 Career

Data engineers and analysts use dbt incremental models with unique keys to efficiently manage large datasets and keep data up to date.

1

Create the initial customers model with sample data

Create a dbt model SQL file named customers.sql that selects these exact rows: customer_id and customer_name with values (1, 'Alice'), (2, 'Bob'), and (3, 'Charlie'). Use a select statement with values to create this data.

dbt

-- Your code here

Hint

Use select * from (values ... ) as alias(column1, column2) to create sample rows.

2

Add unique_key configuration for merge behavior

Add a config block at the top of customers.sql to set materialized = 'incremental' and unique_key = 'customer_id'.

dbt

{{ config(materialized='incremental', unique_key='customer_id') }}

select * from (values
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie')
) as customers(customer_id, customer_name)

# Add the config block above the select statement

Hint

Use {{ config(...) }} at the top of the model to set configurations.

3

Add incremental merge logic using unique_key

Add a where clause inside the select statement to filter rows only when is_incremental() is false or customer_id is greater than 1. Use where not is_incremental() or customer_id > 1.

dbt

{{ config(materialized='incremental', unique_key='customer_id') }}

select * from (values
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie')
) as customers(customer_id, customer_name)
where not is_incremental() or customer_id > 1

# Add the where clause to filter incremental rows

Hint

Use where not is_incremental() or customer_id > 1 to handle incremental loading.

4

Print the final SQL query for merge

Print the entire contents of customers.sql to show the final SQL query including the config block and where clause.

dbt

print('''{{ config(materialized='incremental', unique_key='customer_id') }}

select * from (values
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie')
) as customers(customer_id, customer_name)
where not is_incremental() or customer_id > 1''')

# Print the full SQL query

Hint

Use a print statement with triple quotes to show the full SQL text.

Practice

1.

What is the main purpose of the unique_key in dbt's merge behavior?

easy

A. To delete rows from the source table

B. To sort the data before merging

C. To create new columns in the target table

D. To identify which rows to update or insert during a merge

Solution

1. Step 1: Understand the role of unique_key in merge

   The unique_key tells dbt which rows in the target table correspond to rows in the source table during a merge operation.

2. Step 2: Identify the correct behavior of unique_key

   It helps dbt decide whether to update existing rows or insert new rows based on matching keys.

3. Final Answer:

   To identify which rows to update or insert during a merge -> Option D

4. Quick Check:

   unique_key matches rows for merge = D [OK]

Hint: unique_key matches rows to update or insert [OK]

Common Mistakes:

• Thinking unique_key sorts data
• Believing unique_key creates columns
• Confusing unique_key with deleting rows

2.

Which of the following is the correct way to specify a composite unique_key in a dbt model's merge config?

merge:
  unique_key: ?

easy

A. ['id', 'date']

B. 'id, date'

C. {id, date}

D. id + date

Solution

1. Step 1: Recall syntax for composite unique_key

   dbt expects a list of column names for composite keys, written as ['col1', 'col2'].

2. Step 2: Match syntax options

   ['id', 'date'] uses a list syntax with square brackets and quotes, which is correct.

3. Final Answer:

   ['id', 'date'] -> Option A

4. Quick Check:

   Composite unique_key = list of columns [OK]

Hint: Use list syntax ['col1', 'col2'] for composite keys [OK]

Common Mistakes:

• Using a string with commas instead of a list
• Using curly braces which is invalid syntax
• Trying to add columns with + operator

3.

Given the following dbt merge config:

merge:
  unique_key: 'user_id'

And source data:

| user_id | value |
|---------|-------|
| 1       | 100   |
| 2       | 200   |
| 1       | 300   |

What will happen when merging into a target table with existing user_id 1 and 2?

medium

A. The row with user_id 1 will cause an error due to duplicate keys

B. The last row with user_id 1 will update the target, overwriting previous

C. Both rows with user_id 1 will be inserted as new rows

D. Only the first row with user_id 1 will update, others ignored

Solution

1. Step 1: Understand unique_key behavior with duplicates in source

   dbt uses unique_key to match rows. If source has duplicates, the last row usually overwrites previous ones during merge.

2. Step 2: Analyze the source data effect

   Two rows with user_id 1 exist; the last row with value 300 will update the target row with user_id 1.

3. Final Answer:

   The last row with user_id 1 will update the target, overwriting previous -> Option B

4. Quick Check:

   Duplicate keys in source overwrite last row = C [OK]

Hint: Last duplicate row in source overwrites target row [OK]

Common Mistakes:

• Expecting error on duplicate keys in source
• Thinking all duplicates insert as new rows
• Assuming only first duplicate updates

4.

Consider this dbt merge config snippet:

merge:
  unique_key: ['id']

But the source data has duplicate id values. What is the likely issue?

medium

A. Merge will fail or produce incorrect results due to non-unique keys

B. Merge will ignore duplicates and update only one row

C. Merge will create new columns for duplicates

D. Merge will automatically remove duplicates before merging

Solution

1. Step 1: Identify problem with non-unique keys in merge

   Unique_key must uniquely identify rows. If duplicates exist, dbt cannot correctly match rows.

2. Step 2: Understand merge behavior with duplicates

   Duplicates cause merge to fail or produce wrong updates because dbt doesn't know which row to update.

3. Final Answer:

   Merge will fail or produce incorrect results due to non-unique keys -> Option A

4. Quick Check:

   Non-unique keys cause merge failure = A [OK]

Hint: unique_key must be unique; duplicates cause merge errors [OK]

Common Mistakes:

• Assuming merge ignores duplicates silently
• Thinking merge creates new columns for duplicates
• Believing merge auto-removes duplicates

5.

You want to merge data from a source table into a target table using dbt. The source has columns user_id, event_date, and event_type. You want to update existing rows only if both user_id and event_date match. Which unique_key should you use?

hard

A. unique_key: 'user_id'

B. unique_key: ['event_type']

C. unique_key: ['user_id', 'event_date']

D. unique_key: ['user_id', 'event_type']

Solution

1. Step 1: Identify columns needed to uniquely match rows

   Since updates depend on matching both user_id and event_date, both must be part of unique_key.

2. Step 2: Choose correct unique_key syntax

   unique_key: ['user_id', 'event_date'] correctly uses a list with both columns to uniquely identify rows for merge.

3. Final Answer:

   unique_key: ['user_id', 'event_date'] -> Option C

4. Quick Check:

   Composite key matches both columns = B [OK]

Hint: Use all columns needed to uniquely identify rows [OK]

Common Mistakes:

• Using only user_id when event_date is needed
• Using event_type which is not unique key
• Mixing wrong columns in unique_key