Unique key for merge behavior in dbt

Introduction

We use a unique key to tell dbt how to match rows when updating or inserting data. This helps keep data clean and avoid duplicates.

When you want to update existing records in a table without adding duplicates.

When you are combining new data with old data and need to know which rows match.

When you want to keep your data warehouse tables accurate and up-to-date.

When you are using dbt's incremental models to add or update data efficiently.

Syntax

dbt

unique_key: 'column_name'

or

unique_key: ['column1', 'column2']

The unique_key can be a single column or a list of columns that together identify each row uniquely.

This key is used in incremental models to decide which rows to update or insert.

Examples

Use a single column id as the unique key for merging rows.

dbt

unique_key: 'id'

Use a combination of user_id and order_date as the unique key to match rows.

dbt

unique_key: ['user_id', 'order_date']

Sample Program

This dbt model uses order_id as the unique key. When running incrementally, dbt will update rows with matching order_id and insert new rows.

dbt

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

select
  order_id,
  user_id,
  order_date,
  total_amount
from {{ ref('source_orders') }}

Important Notes

Make sure the unique key truly identifies each row uniquely to avoid unexpected duplicates.

If your unique key is not unique, dbt may raise errors or produce incorrect merges.

For composite keys, list all columns in the order that matches your data logic.

Summary

The unique_key tells dbt how to match rows when merging data.

It can be a single column or multiple columns combined.

Using the correct unique key keeps your data accurate and avoids duplicates.