unique_key, predicates, and date-window increments
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
- You cannot name a stable unique key and you cannot accept append-only duplicates.
- The “window” you would have to scan is most of the table. A full refresh may be cheaper and easier to explain. No numbers are claimed here; measure on your warehouse.
- You need history of overwritten values — use snapshots.
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
- About incremental strategy (
unique_key,mergewithout a key,incremental_predicates) - incremental_strategy config