Module 6 — Incremental models and idempotency

Description

So far, every time you ran dbt run --select fact_orders, dbt rebuilt the whole table from scratch: it read stg_orders's 40 rows, joined them again with dim_store and dim_date, and replaced the entire table with the result. With 40 rows, that work is instant — you didn't notice it even once in modules 3, 4, and 5. But the pattern doesn't scale: if fact_orders had ten million rows instead of 40, rebuilding the complete table on every run would mean re-reading and reprocessing ten million rows every time, even if only a handful of new orders had arrived since the previous run.

This module solves exactly that problem. You're going to meet dbt's incremental models — the materialization that processes only the new slice of data, instead of rebuilding everything — the is_incremental() macro that makes it possible to write a single .sql file that behaves differently depending on the table's state, and the incremental strategies (append, delete+insert, merge) dbt-duckdb supports for deciding, precisely, what to do with new data versus what already exists. You're going to turn fact_orders — the same table as always, with no change to its shape or columns — into an incremental model, filtered by a run_date variable you pass explicitly on every run. And you're going to prove, with real evidence and row counts, the property that gives this module its title: idempotency — running the same command twice over the same date produces exactly the same result, not one row more.

Connection to the previous module. Module 5 gave you Kiosko's project's first resource that remembers its own past: dim_product_snapshot, which archives every version of a product without deleting the previous one. This module works on a different, almost opposite problem: fact_orders doesn't need to remember versions — every order line is a fixed fact that doesn't change once it happens — what it needs is to never redo the same work twice. The snapshot solves "what changed, and when?"; the incremental model solves "what's new, and only that?" They're two different questions, with two different dbt mechanisms, applied here to two different models in the same project.

An analogy: the diary that doesn't get rewritten every night

Imagine you keep a personal diary, with one entry per day. Every night, instead of adding today's page, someone suggests a different method: pull out every page in the notebook, copy every entry from every previous day back out by hand — word for word, from the first day you started writing — and, at the end, add today's entry. It would work, technically: when you finished, the diary would have exactly the same content as if you'd only added one page. But the cost of "rewriting everything to add a little" grows every day that passes — today it's a few pages, a year from now it's hundreds — while the real work (what changed) is always the same: one new page.

An incremental model is the sensible version of keeping a diary: you add today's page to the end of the notebook, without touching a single word of what's already written. The complete notebook — every page together — is still the same object you check when you want to read your whole history; the only thing that changed is how it gets built each night: piece by piece, not from scratch.

And idempotency, this module's second concept, has its own everyday analogy: pressing the button on an elevator that's already on its way. Pressing it once calls the elevator. Pressing it five times in a row, impatiently, doesn't call five elevators — the system recognizes there's already a request in progress for that floor, and the final result is the same no matter how many times you pressed the button. An idempotent incremental model behaves the same way: running dbt run --select fact_orders --vars '{"run_date": "2026-08-05"}' once adds (or updates) August 5's partition. Running it five times in a row, with the same run_date, leaves the table in exactly the same state as running it once — not one row more, no matter how many times you pressed the button.

What you're going to build in this module

You're going to reconfigure fact_orders — the same sales fact as always, same seven columns, same order-line grain — to be an incremental model with the delete+insert strategy, filtered by a run_date variable you pass with --vars. By the end of the module you're going to have run, with real row-count evidence:

1. An incremental run over a specific date (run_date: "2026-08-05").
2. The same run, a second time, over the same date — to prove the row count doesn't change.
3. A dbt run --select fact_orders --full-refresh — to rebuild the complete table from scratch, when you really need to.

flowchart TD
    A["fact_orders as table\n(modules 3-5)\nevery run rebuilds EVERYTHING"] --> B["M6 L2-L3: why it doesn't scale\nis_incremental()"]
    B --> C["M6 L4: choosing a strategy\ndelete+insert (with evidence)"]
    C --> D["M6 L5: fact_orders incremental\nfiltered by run_date"]
    D --> E["M6 L6: run twice\nsame date -> same count"]
    E --> F["M6 L7: full-refresh\nwhen a real rebuild is needed"]
    F --> G["M6 L8: complete project\nsixth commit"]

This module's 8 lessons

#	Lesson	What it solves
1	Module introduction	This map.
2	Why incremental models exist	The cost of rebuilding everything, and the pattern you already used in foundations (overwrite-partition), now inside dbt.
3	The is_incremental() macro	What it evaluates exactly, and evidence of the different SQL compiled depending on its result.
4	Choosing an incremental strategy	append vs delete+insert vs merge, with a real experiment showing why append doesn't work here.
5	Turning fact_orders into incremental	The real configuration change, the first run with run_date.
6	Proving idempotency by running twice	This module's central proof: same command, same date, same result.
7	--full-refresh when you need to rebuild	The escape hatch, and when to really use it.
8	Mini-project: Kiosko's incremental fact_orders	The whole module, end to end, with the project's sixth commit.

This module's boundary

This module proves idempotency is a property of the dbt model — something that lives inside fact_orders.sql, verifiable with a dbt run run by hand, twice, from your own terminal. What it does not cover is who decides when to run each run_date, what happens if a run fails halfway through and has to be retried, or how you automatically backfill months of history with no one typing --vars by hand every time — that's an orchestrator's job, and it's exactly airflow-and-declarative-orchestration-guide's topic, the guide that follows this one in the ecosystem. Here, you trigger every run yourself, by hand, from the terminal — and that's, on purpose, enough to demonstrate the property this module teaches.

Why fact_orders, and not a new model

Every earlier module in this guide introduced at least one new file: stg_orders.sql in module 2, dim_store.sql in module 3, test_is_positive.sql in module 4, dim_product_snapshot.yml in module 5. This module breaks that pattern on purpose: you're not going to create any new model, macro, or test — you're going to reconfigure one that already exists and that you already trust, with data you already verified (40 rows, 106.15 revenue) since module 3. That choice is deliberate: learning to turn a table model into incremental is clearer when you already know, from memory, what the correct result is — so any deviation, like the append duplicates you're going to see in lesson 4, jumps out immediately, with no need to calculate by hand what the "right" number should be.

Common mistakes

Assuming this module is going to change fact_orders's columns or grain. What happens: someone, reading "incremental model," expects the table to gain new columns (like a load timestamp, or a flag showing which run processed each row). Why it happens: other data systems do add audit columns when converting a table to an incremental load pattern, and it's easy to generalize that expectation to dbt. How to spot it: review module 3's column contract — order_id, store_id, product_id, quantity, unit_price, revenue, order_ts — and confirm, at the end of the module, it's still exactly the same, with no column added. How to fix it: this module only changes how fact_orders gets built (materialization and strategy), never what it contains — the table's shape, its seven columns, and its order-line grain stay exactly the same from start to finish.

Starting the module expecting to see an immediate performance improvement in Kiosko's project. What happens: someone runs dbt run --select fact_orders before and after this module, stopwatch in hand, expecting to see a noticeable difference. Why it happens: the module's central argument (lesson 2) is about scale, and it's easy to expect a visible demonstration within the guide itself. How to spot it: with 40 rows, the time difference between rebuilding everything and processing a single partition is, in practice, zero measurable milliseconds — you're going to confirm this explicitly in lesson 2. How to fix it: this module's value is the mechanism — is_incremental(), the strategy choice, verified idempotency — not a speed demonstration over toy data; the real savings only become visible at production-project scale.

Exercises

Exercise 1 — Locate the column contract this module must not break. Before starting lesson 2, open models/marts/fact_orders.sql exactly as it was at the end of module 5, and note the seven exact columns in its SELECT. You're going to use them as a reference to confirm, in lesson 5, that the configuration change altered none of them.

See solution

The seven columns are: order_id, store_id, product_id, quantity, unit_price, revenue (calculated as quantity * unit_price), and order_ts. None of this module's lessons add, remove, or rename any of them — the module's complete change lives in the config() block and in a conditional WHERE filter, never in the SELECT's column list.

Exercise 2 — Match this lesson's two analogies to their corresponding lessons. Without looking at the rest of the module, predict: does the diary analogy (adding a page, not rewriting the notebook) relate more directly to lesson 2, 3, or 5? And the elevator-button analogy?

See solution

The diary analogy relates most directly to lesson 2 (why incremental models exist): the cost of "rewriting everything" versus "adding only what's new" is, precisely, the scale argument that lesson develops with compiled-SQL evidence. The elevator-button analogy relates most directly to lesson 6 (proving idempotency by running twice): the idea that repeating an action doesn't multiply its effect is, exactly, what that lesson proves with three row-count checks over fact_orders.

Exercise 3 — Explain, in your own words, this module's boundary with airflow-and-declarative-orchestration-guide. In 2-3 sentences, using what you already read in the "This module's boundary" section, explain what part of "running fact_orders safely" gets demonstrated in this guide, and what part is left for the orchestration guide.

See solution

This guide demonstrates that fact_orders, as configured by the end of this module, is safe to re-run over the same partition with no data duplicated — a property that lives inside the .sql file itself and that can be verified with dbt run run by hand, twice, from the terminal. What's left out is who decides when to run each run_date and what happens if a run fails halfway through: that requires a system that schedules runs, automatically retries on failure, and passes --vars with no human intervention — exactly an orchestrator's role, like Airflow, the next guide's topic in the ecosystem.

Summary and next step

This module is going to turn fact_orders — the same sales fact, with no change to its seven columns or its grain — into an incremental model with the delete+insert strategy, filtered by a run_date variable you pass explicitly with --vars. The throughline is two questions: why rebuilding everything on every run doesn't scale (lessons 2 through 4), and how to prove, with real row-count evidence, that the resulting model is idempotent — running the same command twice over the same date duplicates nothing (lessons 5 through 8).

Before moving on to lesson 2 you should be able to: explain, in your own words, the difference between what a snapshot solves (module 5) and what an incremental model solves (this module); and name the seven columns fact_orders shouldn't lose at any point in this module.

Lesson 2 starts at the beginning: the real cost, measured in fact_orders's own compiled SQL, of rebuilding a complete table on every run.

Resources

• dbt Developer Hub — "About incremental models," this whole module's central reference. docs.getdbt.com/docs/build/incremental-models. In English.
• dbt Developer Hub — "About incremental strategy," the supported strategies and their requirements. docs.getdbt.com/docs/build/incremental-strategy. In English.
• dbt Developer Hub — "DuckDB configurations," the specific incremental strategies the dbt-duckdb adapter supports (append, delete+insert, merge, microbatch). docs.getdbt.com/reference/resource-configs/duckdb-configs. In English.