dbt · Incremental Models & Performance

Incremental without unique_key

Mediumunique_keyincrementalduplicates

Interview question

What happens if you run an incremental model without a unique_key?

How to answer

The short answer

Without a unique_key, dbt typically cannot safely upsert. Many adapters fall back to an append-only behavior: each incremental run inserts the filtered batch as new rows.

Consequences

When no unique_key is OK

  • Truly append-only event streams
  • Your is_incremental() filter guarantees disjoint batches (e.g. strict watermark with no late data)
  • You accept duplicates and dedupe in a downstream model

When you need unique_key

  • Source rows can be updated (updated_at changes)
  • Late data can reappear for an old key
  • Consumers expect 1 row per business key
SQL
{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
) }}

Interview tip: "No unique_key means I am betting on append-only semantics. If updates or late arrivals exist, I need a key and a merge/delete strategy."