dbt Incremental Models

Incremental models process only new or changed data rather than rebuilding the entire table on every run. For large tables with millions of rows, this difference means a run that would take hours completes in minutes. Incremental models are the most important performance tool in dbt for handling big datasets.

The Core Idea

Full table refresh (materialized='table'):
  Every run: delete all rows, insert all rows from scratch
  Run 1:  insert 10,000,000 rows  (10 minutes)
  Run 2:  insert 10,001,000 rows  (10 minutes)
  Run 3:  insert 10,002,000 rows  (10 minutes)

Incremental model (materialized='incremental'):
  Run 1:  insert 10,000,000 rows  (10 minutes) ← full load first time
  Run 2:  insert 1,000 new rows   (2 seconds)
  Run 3:  insert 1,000 new rows   (2 seconds)

Basic Incremental Model Syntax

-- models/fct_events.sql
{{ config(materialized='incremental') }}

select
    event_id,
    user_id,
    event_type,
    event_timestamp,
    properties
from {{ source('analytics', 'raw_events') }}

{% if is_incremental() %}
  -- Only load rows newer than the latest row already in this table
  where event_timestamp > (select max(event_timestamp) from {{ this }})
{% endif %}

Key Parts Explained

materialized='incremental' — Tells dbt to use incremental logic.

{% if is_incremental() %} — This block runs only on incremental runs (not the first full load).

{{ this }} — Refers to the current state of the incremental table already in the warehouse.

First Run vs Subsequent Runs

First run:
  is_incremental() = False
  WHERE clause is skipped
  All rows from the source load into a new table
  Result: table with 10,000,000 rows

Second run (next day, 1,000 new events arrived):
  is_incremental() = True
  WHERE clause: event_timestamp > '2025-06-30 23:59:59'
  Only 1,000 new rows are selected
  dbt inserts these 1,000 rows into the existing table
  Result: table now has 10,001,000 rows

The unique_key Configuration

By default, incremental models only append new rows. If your source data can update existing records (orders changing status, customers updating their profile), you need a unique_key to handle updates:

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

select
    order_id,
    status,
    updated_at,
    amount_dollars
from {{ source('ecommerce', 'orders') }}

{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

With unique_key='order_id': if a new row arrives with the same order_id as an existing row, dbt replaces the existing row rather than appending a duplicate.

When to Filter Incrementally

Pick the best column for your WHERE clause:

Column Type           Use Case
-----------           --------
event_timestamp       Event streams (each event has a creation time)
updated_at            Records that can change (orders, customers)
load_date             Batch-loaded data with a daily partition column
_fivetran_synced      Fivetran-loaded data (Fivetran adds this column)

Full Refresh Override

When you change the model's logic significantly or add new columns, rebuild the entire table from scratch:

dbt run --select fct_events --full-refresh

The --full-refresh flag forces is_incremental() to return False, causing dbt to drop and recreate the table from scratch.

Late-Arriving Data

Some source systems send data late. An event that occurred three days ago might arrive in the raw table today. A simple max timestamp filter would miss these late rows.

-- Handle late-arriving data with a lookback window
{% if is_incremental() %}
  where event_timestamp > (
    select dateadd(day, -3, max(event_timestamp))  -- 3-day lookback
    from {{ this }}
  )
{% endif %}

This re-processes the last three days on every run to catch any late-arriving events.

Incremental Model Checklist

[ ] Set materialized='incremental' in config block
[ ] Add is_incremental() conditional WHERE clause
[ ] Choose the right timestamp column for filtering
[ ] Set unique_key if records can be updated (not just inserted)
[ ] Test with --full-refresh after changing model logic or adding columns
[ ] Consider late-arriving data and add a lookback window if needed