Defining Incremental Strategies and Unique Keys

In this lesson, you will learn how to prevent duplicate records and handle data updates by configuring incremental strategies and unique keys in your dbt models.

When you use an incremental materialization, dbt only processes new data to save time and compute costs. However, simply appending new rows can lead to problems if a source system updates an existing record (e.g., changing an order status from "pending" to "shipped"). Without a strategy, you end up with two rows for the same entity.

The Power of Unique Keys

A unique_key acts as your model’s "fingerprint." By defining it in your config block, you tell dbt exactly which column(s) identify a single, distinct record. When dbt runs, it checks if an incoming row matches a unique_key already present in your destination table.

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

select
    order_id,
    order_status,
    updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Choosing an Incremental Strategy

Once a unique_key is set, dbt needs to know how to handle the update. The most common approach is the merge strategy:

• Merge: When dbt finds a record in the source that matches a unique_key in the target table, it updates the existing row with the new data. If the record is new, it inserts it. This ensures your final table reflects the most current state of your data without creating duplicates.

Without these configurations, your incremental tables would grow indefinitely with redundant versions of the same information. By pairing a unique_key with a merge strategy, you create a robust pipeline that keeps your datasets both performant and accurate.

Key Takeaway: Defining a unique_key allows dbt to identify and update existing records during incremental loads, while the merge strategy ensures your target table remains a clean, deduplicated reflection of your source data.