An incremental model builds its table once, then processes only new rows. is_incremental() is true when the table exists and the run is not a --full-refresh; {{ this }} names the existing table:
{{ config(materialized='incremental', unique_key=['order_id', 'line_no'],
incremental_strategy='delete+insert') }}
select cast(extract(year from o.order_date) * 10000 + extract(month from o.order_date) * 100
+ extract(day from o.order_date) as integer) as date_key,
{{ dbt.hash('i.book_id') }} as book_key,
{{ dbt.hash('o.customer_id') }} as customer_key,
i.order_id, i.line_no, o.order_ts, o.channel, o.status, o.coupon,
i.qty, i.unit_price, i.gross_amount
from {{ ref('stg_order_items') }} i
join {{ ref('stg_orders') }} o on o.order_id = i.order_id
{% if is_incremental() %}
-- only orders newer than what the table holds, with a 3-day lookback for late arrivals
where o.order_ts > (select max(order_ts) - interval '3 days' from {{ this }})
{% endif %}With delete+insert, dbt 37,942 deletes target rows whose unique_key appears in the new batch, then inserts the batch, so reprocessed lines replace themselves instead of doubling. Two sample orders arrive:
docker exec -i l2-pg psql -U postgres -d booknest < scripts/new_orders.sql
dbt build --select fct_sales
dbt source freshness17:23:04 1 of 6 OK created sql incremental model analytics.fct_sales [INSERT 0 798 in 1.81s] 17:23:06 Done. PASS=6 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=6 ... 17:23:34 1 of 1 PASS freshness of shop.orders ................... [PASS in 0.20s]
The batch held 798 lines: the 3 new ones plus 795 from the last three days of June, reprocessed by the lookback and replaced in place. The table grew to 137,947 lines, and freshness now passes. Other strategies are append, merge and microbatch (time-sliced batches for very large facts). After changing a model's logic, run --full-refresh: an incremental run only reprocesses its window.