Why Your dbt Model Duplicates Rows (and How to Fix It)

Why an incremental model duplicates rows

When dbt materializes a model as incremental, it does one of two things on subsequent runs:

• If a unique_key is set, it merges/upserts on that key.
• If no unique_key is set, it appends the result of your SELECT to the existing table.

So a model like this:

{{ config(materialized='incremental') }}

SELECT order_id, customer_id, total
FROM raw_orders

Has no is_incremental() WHERE filter and no unique_key. On run #1 it builds 5 rows. On run #2 it appends another 5 - now 10 rows. Run #3: 15. Every nightly job makes the table grow by the full source size.

Fix 1: switch to table materialization

The simplest fix is to change the materialization to table. dbt then issues CREATE OR REPLACE TABLE on every run, so the result is always exactly the source rows - idempotent by construction:

{{ config(materialized='table') }}

SELECT order_id, customer_id, total
FROM raw_orders

Use table when the source is small enough that a full rebuild is cheap. This is the right default until you have a performance reason to optimize.

Fix 2: make the incremental model correct

If the source is large and full rebuilds are expensive, keep incremental but add both required guards:

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

SELECT order_id, customer_id, total
FROM raw_orders

{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

• unique_key='order_id' tells dbt to upsert on that column instead of blind-append.
• is_incremental() is false on the first full build and true on every subsequent run - the WHERE clause then filters to only rows newer than what is already in the table.

Without both, the incremental materialization is a ticking row-count bomb.

How to verify the fix

cd /workspace
DBT_PROFILES_DIR=/workspace dbt run
psql -h 127.0.0.1 -U postgres -d app -c "SELECT COUNT(*) FROM orders;"
# run it again - count must not change
DBT_PROFILES_DIR=/workspace dbt run
psql -h 127.0.0.1 -U postgres -d app -c "SELECT COUNT(*) FROM orders;"

A correct model returns the same row count on every run. If the number climbs, the incremental guards are missing.

When to use each

Situation	Materialization
Source is small, correctness matters most	table
Source is large, full rebuild is too slow	incremental + unique_key + is_incremental()
Never	incremental without both guards