Incremental Models in Detail

Incremental models cut build time from hours to minutes for big append-heavy facts. Done wrong, they silently drop or duplicate data.

The pattern

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

SELECT
    order_id,
    customer_id,
    order_total,
    order_placed_at
FROM {{ ref('stg_orders') }}

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

• First run: is_incremental() is FALSE. Full build.
• Subsequent runs: TRUE. Filter applied; new rows MERGEd or INSERTed.

Three incremental strategies

merge (default)

For each SELECT row, UPDATE if unique_key matches, else INSERT.

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

append

Just INSERT every row. Use when source is purely append-only.

delete+insert

DELETE matching keys, then INSERT. Useful for partition replacement.

The late-arriving data problem

WHERE order_placed_at > MAX(order_placed_at) silently drops late-arriving rows (e.g., orders synced today but placed yesterday).

Defenses

Use sync time, not event time:

WHERE _fivetran_synced > (SELECT MAX(_fivetran_synced) FROM {{ this }})

Add a lookback window:

WHERE order_placed_at >= (SELECT MAX(order_placed_at) - INTERVAL '3 days' FROM {{ this }})

With merge, existing rows get updated; new ones inserted. Safer.

Periodic full-refresh:

dbt run --select fct_orders --full-refresh

Weekly or monthly to catch anything missed.

unique_key gotcha

Must genuinely be unique in SELECT output. Composite keys supported:

{{ config(unique_key=['customer_id', 'event_type', 'event_time']) }}

Backfilling

After fixing a bug:

1. dbt run --select fct_orders --full-refresh — simple but expensive for big tables.
2. Manual backfill UPDATE in the warehouse, then continue incremental.

Common mistakes

1. No unique_key with merge — duplicates or errors.
2. Filter on event time when sync time would be safer.
3. Forgetting is_incremental() guard — WHERE runs on first build against non-existent table.
4. No periodic full-refresh — drift accumulates.
5. Schema changes without on_schema_change config.

Testing incrementals

models:
  - name: fct_orders
    tests:
      - unique:
          column_name: order_id
      - dbt_utils.recency:
          datepart: day
          field: order_placed_at
          interval: 1

Recency test asserts the model has fresh rows.

When NOT to use incremental

• Small models (<10M rows) — full rebuild is fast.
• Heavy aggregations across all history — incremental doesn't help.
• First 3 months — premature optimization. Default to table.

Takeaway

Pattern: is_incremental() guard + sync-time filter + unique_key + merge + lookback + periodic full-refresh. Default to table until you need incremental.