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

Need a 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

Need a 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

Need a 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

Need a hint?

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