dbt Packages & Tests

unique_key, predicates, and date-window increments

Why merge without a unique_key appends, why a date window is not a key, and how incremental_predicates are documented on Snowflake.

The problem

A team wants to reload “the last seven days of orders” every run, because late-arriving facts are common, but they do not want to rebuild the whole table. Official dbt documentation gives you two separate ideas that are often mashed together: a unique_key (how a row is identified for merge / delete+insert) and a time filter (which source rows you even consider this run). They are not substitutes for each other.

A working example

A delete+insert model with a real key, executed in the companion project:

{{
  config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='delete+insert'
  )
}}

select order_id, customer_id, status, amount, order_date
from {{ ref('stg_orders') }}
{% if is_incremental() %}
  where order_date >= (select coalesce(max(order_date), '1970-01-01') from {{ this }})
{% endif %}

dbt run with dbt-duckdb 1.11.1 created orders_by_day successfully. The where clause only limits incoming rows. The unique_key tells dbt which target rows to delete before inserting the new versions of those keys.

Official docs, for the Snowflake-flavored merge path, also show incremental_predicates that limit the scan of the existing table, using the aliases DBT_INTERNAL_DEST and DBT_INTERNAL_SOURCE:

# Official dbt documentation example (Snowflake SQL, not executed here)
models:
  - name: my_incremental_model
    config:
      materialized: incremental
      unique_key: id
      incremental_strategy: merge
      incremental_predicates:
        - "DBT_INTERNAL_DEST.session_start > dateadd(day, -7, current_date)"

Official warning: dbt does not syntax-check those expressions. A typo becomes a warehouse error at run time.

The gotcha

A date window is not a unique key. If you delete+insert “all rows where order_date is in the last week” but you configure no unique_key (or you set incremental_strategy='append'), official append behavior is to insert whatever the select returns. Late-arriving updates then stack as extra rows, not replacements.

Official docs are also explicit: merge without unique_key behaves like append.

is_incremental() filters can hide updates. If you write where event_time >= max(event_time) and a correction arrives with an earlier timestamp, the merge will never see it. If two versions share the same timestamp, you can also get a duplicate-source merge error.

delete+insert on a high-cardinality key that is not actually unique will delete all target rows for that key and insert whatever the source currently has for it. Official docs mention this as a reason people choose the strategy — it is also how you can accidentally delete more than you meant.

When not to use this

Verification

The delete+insert model ran successfully on dbt-core 1.12.7 / dbt-duckdb 1.11.1. The merge-without-a-key, append, and incremental_predicates notes are from official dbt documentation. The Snowflake predicate example was not executed on Snowflake.

Official docs