An Incremental Model in Python

A table model is rebuilt from scratch at every dbt build: all its rows are read again. For a week of orders that's nothing, but a real shop's orders go back years, and rereading them every hour costs time and money. An incremental model is a table that dbt fills completely the first time and, at each later build, only adds the rows that are new. Data teams use it for big tables that mostly grow.

Three pieces of Jinja make it work. config(materialized='incremental', unique_key=…) asks for it; with a unique key, a new row whose key is already in the table replaces the old row instead of being added a second time. is_incremental() is true when the table already exists from an earlier build, so an {% if is_incremental() %} … {% endif %} block is left out of the first build. And {{ this }} is the model's own table, as the last build left it. For example, a model of page views that only reads the new events:

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

SELECT event_id, user_id, page, viewed_at
FROM {{ ref('stg_page_views') }}
{% if is_incremental() %}
WHERE event_id > (SELECT MAX(event_id) FROM {{ this }})
{% endif %}

You edit models/marts/orders.sql, a mart with one row per order and its amount. It reads this given staging model, models/staging/stg_orders.sql:

SELECT
    order_id,
    customer_id,
    product,
    TRY_CAST(quantity AS INTEGER) AS quantity,
    CAST(unit_price AS INTEGER) AS unit_price,
    status,
    CAST(ordered_at AS TIMESTAMP) AS ordered_at
FROM {{ source('shop', 'raw_orders') }}
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY filename DESC) = 1

The check builds once. Then a loader adds orders/2026-05-08.csv (orders 5076 to 5078) and, as a test, changes old order 5001 to cancelled in raw_orders. The check builds again and prints the table and the SQL dbt made for the second build. Your code must print:

model stg_orders (view): OK
model orders (incremental): OK
row_count | latest
75 | 2026-05-07 20:41:00
(1 row)
The loader added orders/2026-05-08.csv, and old order 5001 is now cancelled in raw_orders:
model stg_orders (view): OK
model orders (incremental): OK
row_count | latest
78 | 2026-05-08 14:05:00
(1 row)
order_id | status | ordered_at
5001 | paid | 2026-05-01 08:25:00
5076 | paid | 2026-05-08 09:10:00
(2 rows)
orders, compiled:
  …  (the SQL of the second build, with your filter in it)

5001 still says paid: the second build read only the orders placed after the table's latest one, so the change to an old row never came in. That is the price of incremental, and why dbt build --full-refresh exists: it rebuilds such a table from scratch. At work you type dbt build in a terminal, usually on a schedule, against a warehouse such as Snowflake or BigQuery instead of the DuckDB file.

Your task: make orders incremental with order_id as its unique key, and from the second build on read only the orders placed after the latest ordered_at already in the table.

Assignment
Follow the Coding Tutorial to turn orders into an incremental model that reads only the new orders.

Hint
The {% if is_incremental() %} and {% endif %} lines go below the FROM line, and the WHERE goes between them. {{ this }} is the table itself, so a subquery on it gives the latest ordered_at it already holds.