Module 6: Incremental Models And Idempotency

Why incremental models exist

Description

Since module 3, fact_orders.sql has been a simple SELECT with two INNER JOINs, materialized as table. Every time you run dbt run --select fact_orders, dbt literally executes CREATE TABLE fact_orders AS (SELECT ...) — it discards the old table completely and replaces it with the query's fresh result. With 40 rows, that work finishes in hundredths of a second; you didn't notice it even once in the three previous modules. This lesson explains, with concrete evidence from dbt's own generated SQL, why that pattern — perfectly reasonable at Kiosko's scale — stops being viable as soon as data volume grows, and presents the mechanism dbt offers to solve it: incremental models.

Connection to the module. Lesson 1 gave you the complete map and the two central analogies — the diary that doesn't get rewritten, the elevator button. This lesson stops on the problem's first half: why rebuilding everything, every time, doesn't scale. Lesson 3 is going to introduce the concrete mechanism (is_incremental()) that solves exactly this.

The real cost of "rebuild everything, always"

Before touching any code, it's worth looking at the SQL dbt already generates today for fact_orders, exactly as it was left at the end of module 5 — with no change yet. Run:

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

Notice something structural, not cosmetic: this SELECT has no mechanism at all to process "only what's new." There's no WHERE that tells apart an order that was already in fact_orders since yesterday from one that just arrived this morning — the query always brings the complete 40 rows of stg_orders, no exception. And dbt, running this model with table materialization, executes behind the scenes something like:

create table "kiosko"."main"."fact_orders" as (
    select ...  -- exactly the same SELECT as above, no filter
);

This isn't a flaw in this particular model — it's, literally, what the word table means as a materialization: "this SELECT's complete result, recalculated from scratch, every time you run dbt run." With Kiosko's 40 rows, recalculating from scratch is free. The problem shows up when the volume stops being toy-sized.

The scale argument: what would happen with ten million rows

Imagine, for a moment, that orders didn't have 40 rows but ten million — the real size of a retail business with years of history. Every morning a few thousand new orders arrive from the previous day. With today's table materialization, every dbt run --select fact_orders would have to:

Read stg_orders's complete ten million rows — not just yesterday's few thousand new ones.
Run the two JOINs against dim_store and dim_date again over the ten million rows.
Discard the complete fact_orders table — including rows from three years ago that didn't change a single bit — and rebuild it whole.

The real work of every run — processing yesterday's new orders — stays small, nearly constant over time. But the work the table materialization forces you to do grows with the table's total size, not with the size of what changed. That's the concrete, measurable-in-minutes-and-compute-cost annoyance an incremental model solves: separating "how much work needs to happen" from "how much historical data already exists."

The same problem, already solved once: the overwrite-partition pattern

If you already went through data-engineering-foundations-guide, this problem isn't entirely new to you. That guide's module 6 — "Idempotency and backfill" — introduced the overwrite-partition pattern, which Maxime Beauchemin (Apache Airflow's creator) named in his essay Functional Data Engineering: instead of only adding new rows with INSERT, every load completely replaces the data for the partition (typically, a date) it's processing, and only that partition — it never touches the others. That module implemented it by hand, with Python and SQLite: a function that first runs a DELETE over the specific date, and then an INSERT with that same date's fresh data.

This module teaches, precisely, the same pattern, now expressed as a native dbt feature instead of a hand-written Python function. The delete+insert incremental strategy you're going to use starting in lesson 4 is, literally, that same DELETE followed by that same INSERT — only dbt generates the exact SQL for you, from a declarative configuration (incremental_strategy: 'delete+insert'), instead of you writing the two statements by hand. If you already understood why "deleting the partition before inserting" is the key to idempotency in foundations, you already understood half of this module's concept — what's left is dbt's specific syntax.

Diagram: rebuilding everything, versus processing only what's new

TABLE materialization (modules 3-5)
┌─────────────────────────────────────────┐
│  stg_orders (ALL rows)                   │
│       │                                  │
│       ▼                                  │
│  JOIN dim_store, dim_date (EVERYTHING)   │
│       │                                  │
│       ▼                                  │
│  CREATE TABLE fact_orders AS (...)       │
│  <- replaces the WHOLE table, always     │
└─────────────────────────────────────────┘

INCREMENTAL materialization (module 6)
┌─────────────────────────────────────────┐
│  stg_orders WHERE order_date = run_date  │
│       │  <- only TODAY's partition       │
│       ▼                                  │
│  JOIN dim_store, dim_date (only that     │
│       partition)                         │
│       │                                  │
│       ▼                                  │
│  DELETE that partition + INSERT new data │
│  <- the rest of fact_orders is untouched │
└─────────────────────────────────────────┘

Why Kiosko doesn't need this yet, and why it learns it anyway

It's worth being honest about something: with 40 rows, turning fact_orders into incremental isn't going to make dbt run run noticeably faster — the difference between rebuilding 40 rows and rebuilding 2 is, in practice, zero measurable milliseconds. This lesson isn't asking you to solve a real Kiosko performance problem; it's teaching you the mechanism you'd use to solve that problem the day Kiosko (or any real project you work on) does have millions of rows. It's the same reason the whole guide uses toy data: the pattern — is_incremental(), the strategies, idempotency verified with row counts — is identical at any scale; the only thing that changes, at real scale, is that the time savings go from invisible to being the difference between a run measured in seconds and one measured in hours.

Common mistakes

Thinking "incremental model" means "always faster, in any project." What happens: someone converts a small, trivial model to incremental, expecting to see an immediate performance improvement. Why it happens: this lesson's argument — less repeated work — sounds like a universal optimization, with no nuance about scale. How to spot it: if the model has few rows and a simple query (like Kiosko's fact_orders today), timing a dbt run before and after converting it to incremental shows no perceptible difference — the overhead of managing is_incremental() and an incremental strategy could even, in trivial cases, be marginally larger than a simple CREATE TABLE AS. How to fix it: incremental models solve a growing-volume problem, not speed in the abstract — the right question before converting a model isn't "does this make it faster?" but "does the cost of rebuilding everything grow over time, while the real work per run stays small?"

Confusing an incremental model with a snapshot. What happens: someone who just finished module 5, with snapshots still fresh, assumes an incremental model also "remembers earlier versions" of each row. Why it happens: both mechanisms avoid rebuilding everything from scratch, and both use a unique_key in their configuration — the surface looks similar. How to spot it: a snapshot (dim_product_snapshot) does keep the past — two rows for the same product_id if it changed, with dbt_valid_from/dbt_valid_to — an incremental model like this module's fact_orders, with delete+insert, does exactly the opposite for every partition it touches: it deletes the previous version and replaces it, leaving no historical trace of the old row. How to fix it: remember this lesson's description's distinction — the snapshot solves "what changed, and when?" (dim_product_snapshot); the incremental model solves "what's new, and only that?" (fact_orders). They're mechanisms with opposite purposes, applied here to different models in the same project.

Assuming this module is going to visibly speed up Kiosko's complete dbt build. What happens: someone finishes this module expecting to see a noticeably faster dbt build at the end, and gets disappointed when the total time is still practically the same (fractions of a second, like in earlier modules). Why it happens: the lesson insists on the scale argument, and it's easy to expect a visible demonstration of that improvement within the guide itself. How to spot it: compare the times dbt build reports before and after this module — they're going to be, in practice, indistinguishable, exactly as the earlier section of this same lesson predicted. How to fix it: this module's value isn't a faster dbt build today, over toy data — it's that you know how to write, configure, and verify with evidence (idempotency proven in lesson 6) the correct mechanism, ready to apply the day data volume stops being trivial.

Exercises

Exercise 1 — Calculate the relative cost at a hypothetical scale. If fact_orders had 10,000,000 rows, and every dbt run (with table materialization) took, on average, 2 minutes per million rows processed, how long would rebuilding the complete table take? If an incremental model reduced the work to processing only a typical day's 5,000 new orders, roughly what fraction of those 20 minutes would that represent?

See solution

With 10,000,000 rows at 2 minutes per million, rebuilding the complete table would take 20 minutes per run — regardless of only 5,000 rows (0.05% of the total) being truly new. An incremental model that processes only those 5,000 new rows would work over a tiny fraction of the total volume — on the order of seconds, not minutes — a reduction of over 99% in per-run work. This is exactly this lesson's "The scale argument" section's point, with concrete numbers: table's cost grows with the data's total size; a well-designed incremental model's cost grows with the size of what changed.

Exercise 2 — Relate the overwrite-partition pattern to what you already know. If you already completed data-engineering-foundations-guide's module 6, in 2-3 sentences, explain in your own words the difference between load_naive_insert() (that guide's anti-pattern) and the delete+insert incremental strategy you're going to use in this module. If you didn't complete that guide, instead explain why an INSERT that only adds rows, with nothing deleted beforehand, wouldn't be safe to run twice over the same date.

See solution

load_naive_insert() only adds new rows, with nothing deleted beforehand — running it twice over the same date duplicates every row for that date, because there's no mechanism that recognizes "this is already loaded." The delete+insert strategy (and the overwrite-partition pattern that inspires it) solves this by adding an explicit step before the INSERT: deleting any row that already exists for that same partition (identified by unique_key), so the INSERT that follows always starts from a clean base — whether it's the first time that date is run or the fifth. That's, precisely, the property that makes a load step (or, in this module, a dbt model) safe to re-run without generating duplicates.

Exercise 3 — Argue why fact_orders's table materialization has, today, no filter mechanism at all. In 2-3 sentences, using the compiled SQL you saw in this lesson's worked example, explain why it would be impossible, with no change to the model's configuration, to make dbt run --select fact_orders process "only yesterday's orders."

See solution

fact_orders.sql's compiled SQL, as it was left in module 5, is a fixed SELECT with no conditional WHERE clause at all — there's no mechanism inside the file that tells apart "a row I already processed before" from "a new row from yesterday," because the model has no way of knowing, at compile time, what already existed in the destination table. Adding a fixed filter (WHERE order_ts >= '2026-08-09', for instance) wouldn't solve the underlying problem either: it would end up hardcoded, and the table materialization would rebuild the whole table anyway, replacing what already existed with only that filtered slice, losing the rest of the history. What's needed, precisely, is the mechanism lesson 3 introduces — is_incremental() — so the model knows, on every run, whether the destination table already exists and should behave differently.

Summary and next step

This lesson showed, with fact_orders's real compiled SQL, why the table materialization rebuilds the complete table on every run — with no mechanism to tell new data apart from already-processed data — and why that cost grows with the data's total size, not with what really changed. You connected this problem with the overwrite-partition pattern data-engineering-foundations-guide already introduced by hand with Python and SQLite — the same idea, which this module is going to express as a native dbt feature.

Before moving on you should be able to: explain, in your own words, why table's cost grows with the table's total volume; and describe the difference in purpose between a snapshot (remembers versions) and an incremental model (avoids repeating work).

Lesson 3 introduces the concrete mechanism that solves this problem: the is_incremental() macro, which lets a single .sql file compile to a different SELECT depending on whether the destination table already exists or not.

Resources

dbt Developer Hub — "About incremental models," the official introduction to the problem this module solves, including the explanation of why view and table materializations don't scale with data volume. docs.getdbt.com/docs/build/incremental-models. In English.
dbt Developer Hub — "About materializations," again module 3's central reference, now reread with this lesson's scale argument. docs.getdbt.com/docs/build/materializations. In English.
Maxime Beauchemin — "Functional Data Engineering — a modern paradigm for batch data processing," the essay that names the overwrite-partition pattern cited in this lesson. maximebeauchemin.medium.com/functional-data-engineering-a-modern-paradigm-for-batch-data-processing-2327ec32c42a. In English.
data-engineering-foundations-guide, module 6 ("Idempotency and backfill"), where the same pattern was implemented by hand, with Python and SQLite, before this guide expresses it as a native dbt feature.

← PreviousModule 6 — Incremental models and idempotency Next →The `is_incremental()` macro