Module 6: Incremental Models And Idempotency

Making `fact_orders` incremental

Next →

Description

With the mechanism (is_incremental(), lesson 3) and the decision criteria (delete+insert, lesson 4) already settled, this lesson makes the real, permanent change to fact_orders.sql — the one that's going to stay in Kiosko's project from here on. It's not an experiment on a working copy, like the previous two lessons: it's the definitive file, with the configuration you're going to use for the rest of this module (and that module 7 is going to inherit untouched).

Connection to the module. Lessons 3 and 4 gave you, separately, the mechanism and the criteria. This lesson brings them together in the real change: fact_orders.sql stops depending on dbt_project.yml's table default and declares its own explicit incremental configuration, inside the file itself.

The change: from inheriting table to declaring incremental

Since module 3, fact_orders never had any config() block of its own — it inherited materialized: table from the project default that dbt_project.yml has declared since that module's lesson 6 (models: kiosko_analytics: +materialized: table). This lesson breaks that inheritance for the first time: fact_orders is going to declare its own, specific configuration, which overrides the project default for this particular model — the same specificity-based override mechanism you already saw in module 3, applied here inside the .sql file itself instead of in dbt_project.yml.

-- models/marts/fact_orders.sql (this module's final version)
{{
    config(
        materialized='incremental',
        incremental_strategy='delete+insert',
        unique_key='order_id'
    )
}}

select
    o.order_id,
    o.store_id,
    o.product_id,
    o.quantity,
    o.unit_price,
    o.quantity * o.unit_price as revenue,
    o.order_ts
from {{ ref('stg_orders') }} as o
inner join {{ ref('dim_store') }} as ds
    on o.store_id = ds.store_id
inner join {{ ref('dim_date') }} as dd
    on cast(o.order_ts as date) = dd.calendar_date

{% if is_incremental() %}
where cast(o.order_ts as date) = cast('{{ var("run_date") }}' as date)
{% endif %}

Compare this against module 3's fact_orders.sql: the SELECT's seven columns, the two INNER JOINs with their respective cast()s, didn't change a single letter. The fact's grain, the columns, the revenue = quantity * unit_price business logic — all of that was already correct, and still is. The only new pieces are two: the config() block at the top, and the {% if is_incremental() %} block at the end. Converting a model to incremental, when you already had the correct SELECT from before, means adding a configuration and a conditional filter — not rewriting the business logic from scratch.

The config() block's three keys, one by one

  • materialized='incremental' — the third materialization you know, after view and table (module 3). It tells dbt this model can behave differently depending on the warehouse's state, activating the possibility that is_incremental() might return True at some point.
  • incremental_strategy='delete+insert' — the decision lesson 4 justified with evidence: every incremental run deletes the run_date partition in the destination table before inserting that same partition's fresh version.
  • unique_key='order_id' — the column delete+insert uses to decide which rows to delete. order_id was already this fact's natural key since module 3 (where it already has data_tests: unique and not_null) — it's not a new column, it's the same primary key as always, now with a second, operational purpose.

The filter: why it compares dates, not full timestamps

{% if is_incremental() %}
where cast(o.order_ts as date) = cast('{{ var("run_date") }}' as date)
{% endif %}

Two details deserve attention, and you already saw both before in this guide, applied here again:

  • cast(o.order_ts as date) — the same reason that already justified the cast() in the JOIN against dim_date in module 3: order_ts is a timestamp with hour, minute, and second (2026-08-05 08:10:00), but run_date represents a whole day, with no time. Without this cast(), no row would ever match — comparing an exact timestamp against a date with no time is almost never true — and the filter would silently discard every order for that day.
  • {{ var("run_date") }} — the Jinja var() function, the same syntax you already used to read external values passed with --vars in earlier configurations in this guide. With no default value, var("run_date") requires the variable to exist every time the block activates — you're going to see this error's exact form in a moment.

Running it: first, the expected error, without --vars

Before running the model successfully, it's worth seeing what happens if you forget to pass run_date — because, as you learned in lesson 3, fact_orders already exists as a table in the warehouse (inherited from modules 3 through 5), so is_incremental() is going to be True starting from the very first run after this change:

dbt run --select fact_orders

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 7 models, 15 data tests, 1 snapshot, 4 sources, 501 macros

Concurrency: 4 threads (target='dev')

1 of 1 START sql incremental model main.fact_orders ............................ [RUN]
1 of 1 ERROR creating sql incremental model main.fact_orders ................... [ERROR in 0.01s]

Finished running 1 incremental model in 0 hours 0 minutes and 0.10 seconds (0.10s).

Completed with 1 error, 0 partial successes, and 0 warnings:

[ERROR]: in model fact_orders (models/marts/fact_orders.sql)
  Compilation Error in model fact_orders (models/marts/fact_orders.sql)
  Required var 'run_date' not found in config:
  Vars supplied to fact_orders = {}

Done. PASS=0 WARN=0 ERROR=1 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

This error isn't a bug in the project — it's exactly what's supposed to happen. {{ var("run_date") }}, with no second default-value argument, requires whoever runs the command to explicitly declare which partition they're processing. dbt tells you this precisely: Vars supplied to fact_orders = {} — an empty dictionary, no variable arrived. This is, in fact, a real and deliberate consequence of converting a model to incremental: from this change on, running fact_orders incrementally requires you to declare the date, every time — a plain dbt run no longer suffices, like it did in modules 3 through 5. (Lesson 7 shows you the only way to run it without --vars: --full-refresh.)

Running it: now with run_date

dbt run --select fact_orders --vars '{"run_date": "2026-08-05"}'

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 7 models, 15 data tests, 1 snapshot, 4 sources, 501 macros

Concurrency: 4 threads (target='dev')

1 of 1 START sql incremental model main.fact_orders ............................ [RUN]
1 of 1 OK created sql incremental model main.fact_orders ....................... [OK in 0.11s]

Finished running 1 incremental model in 0 hours 0 minutes and 0.23 seconds (0.23s).

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

sql incremental model, OK, no error at all. Confirm the result with the same pair of numbers you already know from module 3:

dbt show --inline "select count(*) as n_rows, sum(revenue) as total_revenue from {{ ref('fact_orders') }}"

What to expect.

Previewing inline node:
| n_rows | total_revenue |
| ------ | -------------- |
|     40 |         106.15 |

40 rows, 106.15 revenue — exactly the same numbers as always. This run really did process only the 2026-08-05 partition (two orders: ORD-3001, ORD-3002) — it deleted those two rows from the table and inserted them fresh again — but the visible result, for anyone querying fact_orders, is indistinguishable from a complete rebuild. That's, precisely, the guarantee a well-built incremental model has to give you: the final content is the same, regardless of whether it was built all at once or partition by partition.

Confirming the configuration with dbt list

Verify, with the same mechanism you already used in module 3, that the materialization and strategy were configured correctly:

dbt list --resource-type model --select stg_orders dim_store dim_date fact_orders --output json --output-keys name config.materialized

What to expect.

{"name": "dim_date", "config.materialized": "table"}
{"name": "dim_store", "config.materialized": "table"}
{"name": "fact_orders", "config.materialized": "incremental"}
{"name": "stg_orders", "config.materialized": "view"}

Four models, now with three different materializations: stg_orders stays on view (staging, unchanged since module 2), dim_date and dim_store stay on table (unchanged since module 3), and fact_orders is, for the first time in the project, incremental — the only explicit override of dbt_project.yml's default that exists so far.

Diagram: what exists by the end of this lesson

kiosko_analytics/
├── dbt_project.yml           <- table/view default, UNCHANGED
├── models/marts/
│   ├── dim_store.sql          <- unchanged (table, inherited)
│   ├── dim_date.sql            <- unchanged (table, inherited)
│   └── fact_orders.sql         <- CHANGE: own config(), incremental
└── kiosko.duckdb
    └── fact_orders              <- 40 rows, 106.15 (no visible change)

Common mistakes

Forgetting the config() block and only adding the {% if is_incremental() %}. What happens: someone copies the conditional filter at the end of the file, but forgets to add the config(materialized='incremental', ...) block at the top. Why it happens: the filter is the "new," interesting part of the change, and it's easy to focus on that line and forget the configuration that makes it relevant. How to spot it: as you already saw in lesson 3, without materialized='incremental' in the config, is_incremental() always returns False — the conditional block would exist in the file, but would never activate, and dbt run would keep rebuilding the whole table every time, with no error warning you about the problem. How to fix it: the two pieces — config() with materialized='incremental', and the {% if is_incremental() %} block — have to exist together; one without the other produces no real incremental behavior.

Passing run_date as a number instead of text in --vars. What happens: someone writes --vars '{"run_date": 2026-08-05}', with no quotes around the date, inside --vars's JSON. Why it happens: a date "looks like" it could be some special type, and it's easy to forget that, inside --vars's JSON, any date has to be a text string in quotes. How to spot it: the command fails with a JSON parse error, because 2026-08-05 with no quotes isn't a valid number (the dashes break it as JSON syntax) or any other recognizable type. How to fix it: run_date always goes inside double quotes within --vars's JSON, exactly as in this lesson's worked example: --vars '{"run_date": "2026-08-05"}'.

Expecting dbt run --select fact_orders (without --vars) to keep working "like before" after this change. What happens: someone, used to the habit from modules 3 through 5, runs dbt run --select fact_orders plain, with no --vars, and is surprised by this lesson's error. Why it happens: nothing about the model's name or its place in the project warns, by itself, that its execution contract changed. How to spot it: the error message is explicit — Required var 'run_date' not found — and you already saw it in this very lesson. How to fix it: from this module on, running fact_orders incrementally always requires --vars '{"run_date": "..."}' — it's a real, expected consequence of the conversion, not a defect; the only exception is --full-refresh (lesson 7), which skips the conditional block entirely.

Exercises

Exercise 1 — Run the model over a date with no orders. Run dbt run --select fact_orders --vars '{"run_date": "2026-08-15"}' — a date within dim_date's range (all of August) but with no real Kiosko orders at all. What happens to fact_orders's total count?

See solution

The count stays at 40 rows, with no change and no error. The WHERE cast(o.order_ts as date) = cast('2026-08-15' as date) filter finds no rows in stg_orders for that date — no real Kiosko order happened that day — so the subquery feeding the DELETE and the INSERT is empty: there's nothing to delete (because no row in fact_orders has that date) and nothing to insert (because stg_orders doesn't have rows for that date either). The result is, in practice, a run that does nothing — no error, no change, exactly the correct behavior for a partition with no data.

Exercise 2 — Rewrite the filter to accept a date range, not just one day. Temporarily modify fact_orders.sql's {% if is_incremental() %} block to accept two variables, start_date and end_date, filtering by a range (BETWEEN) instead of a single day. Run the model with --vars '{"start_date": "2026-08-06", "end_date": "2026-08-08"}'.

See solution 
{% if is_incremental() %}
where cast(o.order_ts as date) between cast('{{ var("start_date") }}' as date) and cast('{{ var("end_date") }}' as date)
{% endif %}

This change would process three days' orders at once (2026-08-06, 2026-08-07, 2026-08-08: 5 + 7 + 9 = 21 orders), instead of a single run_date. It's a reasonable variation on the same pattern — useful when you want to reprocess several days in a single run — although this guide uses a single run_date for pedagogical simplicity. Undo this change and go back to the run_date version before continuing with the rest of the module, so the rest of the lessons match the worked example.

Exercise 3 — Explain why the model's SELECT didn't change, only its configuration. In 2-3 sentences, argue why converting a table model to incremental shouldn't, in general, require rewriting the SELECT's business logic — using what you observed comparing this module's fact_orders.sql against module 3's.

See solution

Materialization (table, view, incremental) and business logic (which columns get selected, how revenue gets calculated, what it JOINs against) are two completely separate concerns in dbt: the first lives in config(), the second lives in the SELECT's body. Switching from table to incremental only needs adding the corresponding configuration and, optionally, a conditional filter inside {% if is_incremental() %} to narrow down what data each run processes — the business logic that was already correct (the seven columns, the two JOINs, the revenue calculation) stays exactly the same, with no risk of introducing a new bug into something that already worked.

Summary and next step

This lesson made the permanent change: fact_orders.sql now declares materialized='incremental', incremental_strategy='delete+insert', unique_key='order_id', and filters by run_date inside {% if is_incremental() %} — without touching a single line of the business logic that was already correct since module 3. You saw the expected error from forgetting --vars (Required var 'run_date' not found), and the successful run that confirms the same 40 rows and 106.15 revenue as always, now built partition by partition instead of all at once.

Before moving on you should be able to: write, from memory, the three keys in a delete+insert model's config() block; and explain why running fact_orders without --vars now produces an error, when it didn't before this module.

Lesson 6 is this whole module's central proof: run the exact same command from this lesson, a second time, over the same date — and confirm, with row-count and individual-identifier evidence, that the result is identical.

Resources

← PreviousChoosing an incremental strategyNext →Proving idempotency by running twice