Module 6: Incremental Models And Idempotency

The `is_incremental()` macro

Next →

Description

is_incremental() is the piece that makes it possible for a single .sql file to behave in two different ways, depending on when you run it: the first time an incremental model is built — when its destination table doesn't exist yet —, it compiles to a complete SELECT, with no filter at all; any run after that — once the table already exists — compiles to a filtered version of that same SELECT. It's not magic, or a separate command: it's a Jinja function that dbt evaluates at compile time, checking the warehouse's real state before generating the final SQL. This lesson explains exactly which conditions it evaluates, with real evidence — the compiled SQL, twice, in two different states of the project.

Connection to the module. Lesson 2 made clear why a mechanism like this would be needed — the cost of rebuilding everything, always. This lesson presents the mechanism itself, still without applying it permanently to fact_orders (that starts in lesson 5): you're going to experiment with is_incremental() on a working copy, to understand its behavior before committing to changing the project's real model.

What is_incremental() evaluates, exactly

dbt's official documentation is precise about this: is_incremental() returns True only when the following four conditions are all met, all at once:

  1. The destination relation already exists in the warehouse (the fact_orders table, in this case, was already created by an earlier run).
  2. That relation is a real table (not, for example, a view left behind by a previous materialization change that wasn't cleaned up).
  3. The model's configuration says materialized='incremental' — if the model were table or view, is_incremental() would always be False, no matter what else exists in the warehouse.
  4. The run doesn't include the --full-refresh flag — that flag, as you're going to see in lesson 7, forces dbt to treat the run as if the table didn't exist, regardless of whether it actually does.

If any of the four isn't met, is_incremental() returns False, and the {% if is_incremental() %} ... {% endif %} block in your model simply isn't included in the compiled SQL — as if those lines didn't exist.

An analogy: the guard who only asks for ID the second time

Think of an event with free entry the first time in, but that requires ID for any re-entry after that. The first time someone arrives, the guard doesn't ask for anything — they let them straight through, because there's no prior record to check against. But if that same person leaves and comes back in, the guard does ask for ID this time, because now there's an earlier record to compare against: were they already inside? are they the same person who left a moment ago? is_incremental() is exactly that question the guard asks before deciding how to behave: "does a record of this already exist?" — the answer changes the whole procedure, without the event itself (the .sql model) having to be two different files.

Worked example: the same file, two different compiled SQL statements

Work on a temporary copy of fact_orders.sql to experiment without committing the project's real model yet (lesson 5 makes the permanent change). Add, at the end of the file, a conditional block with the macro:

-- models/marts/fact_orders.sql (this lesson's experimental version)
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 %}

With just this, without adding {{ config(materialized='incremental') }} yet, compile it:

dbt compile --select fact_orders
cat target/compiled/kiosko_analytics/models/marts/fact_orders.sql

What to expect.

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 "kiosko"."main"."stg_orders" as o
inner join "kiosko"."main"."dim_store" as ds
    on o.store_id = ds.store_id
inner join "kiosko"."main"."dim_date" as dd
    on cast(o.order_ts as date) = dd.calendar_date

No WHERE at all. This confirms condition 3 from the list above: even though the {% if is_incremental() %} block is already written in the file, as long as fact_orders stays configured as materialized='table' (inherited from dbt_project.yml), is_incremental() is always False — the block never activates, no matter what exists in the warehouse.

Worked example (continued): activating condition 3

Now add the missing config block, with the strategy and key you're going to use permanently starting in lesson 5 (for now, it's still just an experiment):

-- models/marts/fact_orders.sql (with materialized='incremental' added)
{{
    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 %}

Compile it again, without having run dbt run yet — the fact_orders table still exists in the warehouse, with 40 rows, from module 5:

dbt compile --select fact_orders --vars '{"run_date": "2026-08-03"}'
cat target/compiled/kiosko_analytics/models/marts/fact_orders.sql

What to expect.

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 "kiosko"."main"."stg_orders" as o
inner join "kiosko"."main"."dim_store" as ds
    on o.store_id = ds.store_id
inner join "kiosko"."main"."dim_date" as dd
    on cast(o.order_ts as date) = dd.calendar_date


where cast(o.order_ts as date) = cast('2026-08-03' as date)

Now the WHERE shows up. Nothing changed in the .sql file between this compiled version and the earlier one except the config(...) block — but since fact_orders already existed as a table in the warehouse (condition 1), it's a real table (condition 2), it's now configured as materialized='incremental' (condition 3), and you didn't pass --full-refresh (condition 4), all four conditions were met at once, and is_incremental() returned True.

Running the model: from compiling to executing

Compiling only shows you the SQL that would be generated; running it confirms what dbt-duckdb actually does with that SQL:

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

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.19 seconds (0.19s).

Completed successfully

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

Notice sql incremental model — a third model type in dbt's log, distinct from the sql view model and sql table model you already knew. Inspect the SQL dbt actually ran against DuckDB:

cat target/run/kiosko_analytics/models/marts/fact_orders.sql

What to expect (the temporary table's numeric suffix is a timestamp — it's going to be different on your machine, without that changing the behavior).

delete from "kiosko"."main"."fact_orders"
where (
    order_id) in (
    select (order_id)
    from "fact_orders__dbt_tmp20260812192626453363"
);

insert into "kiosko"."main"."fact_orders" ("order_id", "store_id", "product_id", "quantity", "unit_price", "revenue", "order_ts")
(
    select "order_id", "store_id", "product_id", "quantity", "unit_price", "revenue", "order_ts"
    from "fact_orders__dbt_tmp20260812192626453363"
)

Two statements, not one: a DELETE that removes, from the real fact_orders table, any row whose order_id matches what the query filtered by run_date brought back (first materialized into a temporary table, fact_orders__dbt_tmp...), followed by an INSERT that adds those same fresh rows. This is exactly the delete+insert strategy you're going to formally choose in lesson 4 — and it's, precisely, the same overwrite-partition pattern you already met in lesson 2: delete the partition before inserting it again, never just append.

Confirm the total count didn't change — fact_orders still has its usual 40 rows, because the run_date you used (2026-08-03) was already represented in the table from before:

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 |

Diagram: the four conditions, one by one

flowchart TD
    A["is_incremental()"] --> B{"Does the destination\nrelation exist?"}
    B -- No --> F["False -> complete SELECT,\nno WHERE"]
    B -- Yes --> C{"Is it a real table?"}
    C -- No --> F
    C -- Yes --> D{"materialized='incremental'\nin the config?"}
    D -- No --> F
    D -- Yes --> E{"Was --full-refresh\npassed?"}
    E -- Yes --> F
    E -- No --> G["True -> the block\n{% if is_incremental() %} activates"]

Going deeper: why is_incremental() is a function, not a variable

It's worth noting something about the syntax: is_incremental() is written with parentheses, like a function call — {% if is_incremental() %} —, not like a plain variable ({% if is_incremental %}, with no parentheses, would fail). That's intentional: every time dbt compiles a model, it needs to re-evaluate the warehouse's real state at that exact moment — it can't cache the answer from one run to the next, because the destination table's state may have changed (for example, if someone dropped it by hand, or if it's the first time the project is cloned onto a new machine). A function that runs on every compile, instead of a fixed value, is the only correct way to model a question whose answer depends on when it's asked.

Common mistakes

Writing is_incremental with no parentheses. What happens: someone, used to Jinja variables like {{ target.name }}, writes {% if is_incremental %} without the function's parentheses. Why it happens: Jinja doesn't visually distinguish, at a glance, between a variable and a function with no arguments — both look like a single word. How to spot it: dbt fails to compile with an error saying is_incremental isn't a valid boolean value, or the block simply never activates (depending on the exact Jinja error version). How to fix it: is_incremental() is always a function call, with empty parentheses — copy it exactly as it appears in this lesson's worked example.

Running dbt compile after deleting kiosko.duckdb, and being surprised no WHERE shows up. What happens: someone deletes the database to "start from scratch," runs dbt compile --select fact_orders, and sees the unfiltered SELECT — same as before adding materialized='incremental' — and thinks the config change didn't work. Why it happens: it's easy to forget that is_incremental() depends on the warehouse's state, not just the file's configuration. How to spot it: check whether kiosko.duckdb exists and whether the fact_orders table is in it — if the database was deleted, condition 1 (the destination relation already exists) is false, so is_incremental() returns False no matter the model's configuration. How to fix it: this isn't a bug, it's the correct behavior — the first time an incremental model is built (destination table doesn't exist), it always compiles complete, with no filter; only starting from the second run onward, with the table already created, does the conditional block activate.

Thinking is_incremental() "knows" which rows are new, automatically. What happens: someone writes {% if is_incremental() %} where order_id not in (select order_id from {{ this }}) {% endif %} or something similar, expecting dbt to automatically detect which orders are already in the table. Why it happens: the macro's name — "is incremental" — sounds like it carries some built-in logic for detecting new rows. How to spot it: is_incremental() only answers a boolean question — should incremental mode activate? — it doesn't filter anything by itself; the actual filter (WHERE cast(o.order_ts as date) = cast('...' as date), in this module) is one you write yourself, inside the conditional block. How to fix it: think of is_incremental() as a switch, not a filter — it turns a block of code you wrote on or off; the "what's new" logic is always something you define explicitly, typically comparing against a variable like run_date (this module) or, in real projects, against a timestamp from the last run.

Exercises

Exercise 1 — Predict the result without running anything. Without running any command, answer: if you deleted kiosko.duckdb completely and ran dbt compile --select fact_orders --vars '{"run_date": "2026-08-03"}' over the model with materialized='incremental' already configured, would the compiled SQL have the WHERE or not? Justify your answer with this lesson's four conditions.

See solution

No, it wouldn't have the WHERE. Even though conditions 3 (materialized='incremental' in the config) and 4 (no --full-refresh) are met, condition 1 — that the destination relation already exists — would be false: without kiosko.duckdb, there's no earlier fact_orders table to compare against. With any of the four conditions unmet, is_incremental() returns False, and the whole block is omitted from the compiled SQL — the result would be identical to this lesson's first compile, with no filter, processing all 40 rows.

Exercise 2 — Verify condition 3 directly. Temporarily change materialized='incremental' to materialized='table' in fact_orders.sql (without touching the rest of the file, including the {% if is_incremental() %} block), and run dbt compile --select fact_orders --vars '{"run_date": "2026-08-03"}'. Does the WHERE show up?

See solution

It doesn't show up, even though the fact_orders table does exist in the warehouse (condition 1 met) and you didn't pass --full-refresh (condition 4 met). Condition 3 — materialized='incremental' in the config — is a necessary condition, not optional: without it, is_incremental() returns False no matter the rest of the project's state, exactly the behavior you already saw in this lesson's first worked example, before adding the config(...) block. Switch back to materialized='incremental' before continuing with the rest of the module.

Exercise 3 — Explain, in your own words, the guard analogy applied to fact_orders. In 2-3 sentences, using this lesson's analogy, explain what specific question is_incremental() answers in the context of fact_orders, and why that question needs to be re-evaluated on every run instead of answered once.

See solution

is_incremental() answers, every time fact_orders.sql compiles, the same question as the guard in the example: "does a prior record of this already exist?" — in this case, "was the fact_orders table already built before?". That question can't be answered once and have the answer saved, because the warehouse's state can change between one run and the next — someone could drop the table, clone the project onto a new machine with no database yet, or pass --full-refresh to force a complete rebuild; that's why is_incremental() is a function that gets re-evaluated on every compile, checking the real state at that exact moment, instead of a fixed constant in the file.

Summary and next step

This lesson dissected is_incremental(): the four conditions it evaluates (relation exists, is a table, materialized='incremental', no --full-refresh), and the real evidence that the same .sql file compiles to two different SQL statements depending on the warehouse's state — no filter the first time, with a WHERE over run_date on any run after that. You saw, with the actual SQL that ran, that dbt-duckdb translates this into a DELETE followed by an INSERT — the same overwrite-partition pattern from lesson 2, now generated automatically by dbt from declarative configuration.

Before moving on you should be able to: name is_incremental()'s four conditions from memory; and explain why it's written with parentheses, like a function, and not as a plain variable.

Lesson 4 takes the next step: with the mechanism already understood, it's time to decide what to do inside the {% if is_incremental() %} block — the choice between append, delete+insert, and merge, with a real experiment showing why not just any strategy works for fact_orders's case.

Resources

← PreviousWhy incremental models existNext →Choosing an incremental strategy