How to fix "unique_key" duplicates failing an incremental model in dbt

An incremental model that ran for weeks suddenly fails:

Duplicate row detected during DML action
Row Values: [...]

On Snowflake an incremental model with a unique_key is written with merge. When the new rows contain the same key twice, Snowflake cannot tell which of them should update the target row, and with the default setting it stops with this error. The fix is to make the batch unique before the merge.

Copy and paste

1. Find the duplicated keys:

dbt show --inline "select order_id, count(*) from {{ ref('stg_order_events') }} group by 1 having count(*) > 1"

2. Keep one row per key, the most recent:

-- models/marts/order_status.sql
{{ config(materialized='incremental', unique_key='order_id') }}

select order_id, status, updated_at
from {{ ref('stg_order_events') }}

{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

qualify row_number() over (partition by order_id order by updated_at desc) = 1

3. Remove the duplicates already in the table and rebuild once:

dbt run -s order_status --full-refresh

4. Keep it from coming back with a test on the model:

models:
  - name: order_status
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null

Why the first run worked

On the first run, and on every --full-refresh, there is no merge: dbt creates the table from the query, duplicates included. In the example, order 1 had two events, placed and shipped, and the table started with two rows for it. The error appears only later, when a batch with a repeated key is merged. With the qualify line, the same data gives one row per order, the shipped one.

Where duplicates come from

• Events. The source keeps one row per change, so the same key appears once per status. Keep the latest, as above.
• The wrong key. The table’s grain is wider than unique_key, for example order lines keyed on order_id alone. Use a list: unique_key=['order_id', 'line_number'].
• A join that multiplies rows. A join to a table with several rows per key duplicates the result. Check the grain of each join before adding qualify.
• Null keys. They never match and are inserted again. Filter them out with where order_id is not null.

What not to do

• Do not switch off the error. With ERROR_ON_NONDETERMINISTIC_MERGE set to false the merge completes, but Snowflake documents the result as nondeterministic: one of the duplicate rows wins at random.
• Do not hide it with delete+insert. That strategy deletes the target rows for the batch’s keys and inserts the whole batch, so the run succeeds and the table keeps both copies.

The deduplication was tested with dbt Core 1.12 (DuckDB adapter); the Snowflake error and parameter come from Snowflake’s documentation.