Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 41 ยท Incremental Models and Materializations

Incremental Models: the Basics

Build big tables faster with dbt incremental models: is_incremental(), the {{ this }} reference, unique_key, and a runnable simulation of the pattern.

A table model rebuilds everything on every run. That is fine for thousands of rows and painful for billions. An incremental model builds the table once, then on each later run only processes new (or changed) rows and adds them to the existing table. Runs go from hours to minutes, and warehouse bills shrink.

The price is complexity. You must tell dbt how to find the new rows, and you must think about what happens when data changes or arrives late.

The shape of an incremental model

SQL
-- models/marts/fct_events.sql
{{ config(
    materialized='incremental',
    unique_key='event_id'
) }}

select
    event_id,
    user_id,
    event_type,
    occurred_at
from {{ ref('stg_events') }}

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

Three new pieces:

  • materialized='incremental' selects the materialization.
  • is_incremental() is true only when the target table already exists, the model is incremental, and you did not pass --full-refresh.
  • {{ this }} is a reference to the model's own table in the warehouse, so you can ask "what is already loaded?".

On the first run the table does not exist, so is_incremental() is false and the whole select runs. On later runs only rows newer than the current maximum are selected, and dbt adds them.

flowchart TD
  R["dbt run"] --> Q{"Table exists and not --full-refresh?"}
  Q -->|"No"| F["Build whole table from the SELECT"]
  Q -->|"Yes"| N["Select only rows passing the is_incremental filter"]
  N --> M["Insert or merge into existing table"]

The same SQL file serves both the first and the later runs.

Simulating it in SQL

Here is the whole mechanism with a hand-made target table, using plain SQL. Run it, then think about what dbt does: it generates statements very close to these.

SQL First run, then an incremental run

Press Run (or Ctrl + Enter) to see the result here.

The target ends with 5 rows, but the second statement only touched 2. That is the saving. On a warehouse with a billion rows, it scans just the new slice of the source (if the source is partitioned or clustered by time, the filter prunes the rest).

unique_key: updates, not just inserts

The filter above only appends. If a row can change after loading, for example an order moving from pending to shipped, plain inserts create a second version of the same order. Setting unique_key makes dbt replace matching rows: delete-then-insert or merge, depending on the strategy (see strategies).

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

select order_id, status, updated_at
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Now the rows changed since the last run are selected, and each replaces the old row with the same order_id:

SQL What unique_key achieves

Press Run (or Ctrl + Enter) to see the result here.

Without unique_key you would have two rows for order 1. A unique test on the key (see generic tests) is the best protection against that mistake.

Choosing the filter column

The filter column must be reliably increasing and available: updated_at, loaded_at, or an event timestamp. Prefer a column set by the source system at write time (_loaded_at). A common safe pattern is to reprocess a small window, for example the last three days, so slightly late rows are picked up (more in late-arriving data).

When not to use incremental

  • The table builds quickly as a table. Simpler is more reliable.
  • Rows are deleted at the source and must be removed from the target.
  • The logic needs the full history on every run (a window function over all time).
  • You cannot trust any column to identify new rows.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When is is_incremental() true?

    All three conditions must hold; on the first run or with --full-refresh the full SELECT runs.
  2. 2. What does {{ this }} refer to?

    It lets the model look at what has already been loaded, such as the maximum timestamp.
  3. 3. Why set unique_key?

    dbt uses the key to delete or merge matching rows before inserting the new batch.
  4. 4. Which case is a poor fit for an incremental model?

    If a table builds quickly, the extra complexity is not worth it.

Summary

  • Incremental models build once, then add only new or changed rows.
  • Use is_incremental() and {{ this }} to filter to new data.
  • unique_key prevents duplicates when rows change.
  • Choose a reliable time column, and do not use incremental where a table is cheap.

Next: Incremental Strategies