Choosing an incremental strategy

Description

Knowing that is_incremental() exists doesn't answer the question that really matters: once the block activates, what does dbt do with new rows versus rows that already exist? That decision is called the incremental strategy (incremental_strategy), and dbt-duckdb — this guide's adapter — supports four: append, delete+insert, merge, and microbatch. This lesson explains the first three with a real experiment — not just theory —, showing, with concrete row counts, why append produces exact duplicates where delete+insert doesn't, and why this guide chooses delete+insert for fact_orders.

Connection to the module. Lesson 3 gave you the mechanism (is_incremental()); this lesson gives you the decision criteria you're missing before writing lesson 5's final configuration. Without this lesson, you'd know when the conditional block activates, but not what to put inside it.

The three strategies, in one sentence each

The column that really matters for this module is the last one: append doesn't use any key to decide what to do — it simply appends — while delete+insert and merge both need a unique_key to know which row "already exists" and needs to be treated differently from a new row.

An analogy: adding a page at the end, versus replacing the correct page

Go back to lesson 1's diary. append is literally "add a page at the end of the notebook, without checking whether a page with that same date already existed" — it works perfectly the first time you write a given day's entry, but if you write Tuesday's entry twice by mistake, the notebook ends up with two pages dated Tuesday, both valid in the eyes of whoever's adding pages, because append never compares against what already exists. delete+insert, by contrast, is "look for Tuesday's page if it already exists, tear it out, and write the new one in its place" — no matter how many times you "add" Tuesday's entry, the notebook always ends up with exactly one page for that date, because every run starts by deleting whatever was there before. merge adds a finer nuance: instead of tearing out the whole page, it corrects only the lines that changed within it, leaving the rest untouched — useful when you want to update specific fields of an existing row without losing other data that same process isn't touching.

The experiment: append over the same date, twice

Before deciding in the abstract, test it with real evidence. Configure fact_orders.sql (still as an experiment, without committing to the final change) with incremental_strategy='append', with no unique_key — append doesn't need one:

-- fact_orders.sql ('append' experiment, NOT the module's final version)
{{
    config(
        materialized='incremental',
        incremental_strategy='append'
    )
}}

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 fact_orders already existing (40 rows, inherited from module 5), run it a first time over run_date: "2026-08-05" — the date with only two orders, ORD-3001 and ORD-3002:

dbt run --select fact_orders --vars '{"run_date": "2026-08-05"}'
dbt show --inline "select count(*) as n from {{ ref('fact_orders') }}"

What to expect.

1 of 1 OK created sql incremental model main.fact_orders ....................... [OK in 0.09s]

Previewing inline node:
|  n |
| -- |
| 42 |

42, not 40. append didn't delete anything before inserting: the two 2026-08-05 orders that were already in the table since module 5 (back when fact_orders was still table, with no filter) are still there, and this run added two more, identical ones. Run the exact same command a second time, without changing anything:

dbt run --select fact_orders --vars '{"run_date": "2026-08-05"}'
dbt show --inline "select count(*) as n from {{ ref('fact_orders') }}"
dbt show --inline "select order_id, count(*) as n from {{ ref('fact_orders') }} where cast(order_ts as date) = cast('2026-08-05' as date) group by order_id"

What to expect.

Previewing inline node:
|  n |
| -- |
| 44 |

Previewing inline node:
| order_id | n |
| -------- | - |
| ORD-3002 | 3 |
| ORD-3001 | 3 |

44, and every order_id for that date shows up three times. This is concrete proof that append is not idempotent for this use case: every run over the same run_date adds new rows, with no limit, with no error signal at all — dbt run finishes with PASS=1, exactly as if everything were fine. It's the same kind of silent failure you already saw in module 3 with the JOIN missing cast(): the SQL is perfectly valid, and the result is wrong anyway.

The same experiment, with delete+insert

Switch the config back to delete+insert, with unique_key='order_id' — the version you're going to leave in place permanently in lesson 5:

{{
    config(
        materialized='incremental',
        incremental_strategy='delete+insert',
        unique_key='order_id'
    )
}}

With the table already "contaminated" by the earlier experiment (44 rows, with duplicates), run dbt run --select fact_orders --full-refresh first to get back to a clean state of 40 rows — you're going to learn this command in depth in lesson 7 — and repeat the same experiment:

dbt run --select fact_orders --full-refresh
dbt run --select fact_orders --vars '{"run_date": "2026-08-05"}'
dbt run --select fact_orders --vars '{"run_date": "2026-08-05"}'
dbt show --inline "select count(*) as n from {{ ref('fact_orders') }}"

What to expect.

Previewing inline node:
|  n |
| -- |
| 40 |

40, not 44, not 42. Two runs in a row over the same run_date, and the count never moved from 40 — exactly the property this entire module exists to demonstrate, and that lesson 6 is going to verify in even more detail over the project's final configuration.

Why delete+insert and not merge, for fact_orders specifically

merge is also idempotent — the same experiment with merge instead of delete+insert would, just the same, produce 40 rows after two runs over the same date, because merge also uses unique_key to tell "this already exists" apart from "this is new". The difference between the two isn't idempotency — both guarantee it — it's what behavior they have over columns that didn't change within a row that does already exist:

• merge is designed for the case where an existing row might need a partial update — think of module 5's dim_product_snapshot: a product that already existed changed price, and you want to update that specific row without touching the others.
• delete+insert is designed for the case where a complete partition (in this module, a day's worth of orders) gets fully replaced every time it's reprocessed — it doesn't matter whether an individual order "changed" or not, the complete run_date partition is deleted and rebuilt from the source.

fact_orders fits the second case better: every order line is a fixed fact (a sale that already happened doesn't "get updated" field by field), and what this module models is "safely reprocess the whole day," not "update a specific field of an already-existing order." That's why this guide chooses delete+insert — not because merge is wrong, but because this section's criteria (do you replace the whole partition, or update individual rows?) clearly points to delete+insert for this particular model.

Diagram: the three strategies over the same repeated row

append (2 runs, same run_date)
┌────────────┐   ┌────────────┐   ┌────────────┐
│ ORD-3001   │ + │ ORD-3001   │ + │ ORD-3001   │  = 3 copies
│ (original) │   │ (run 1)    │   │ (run 2)    │
└────────────┘   └────────────┘   └────────────┘

delete+insert (2 runs, same run_date)
┌────────────┐   DELETE      ┌────────────┐   DELETE      ┌────────────┐
│ ORD-3001   │ ──────────►   │ ORD-3001   │ ──────────►   │ ORD-3001   │  = 1 copy
│ (original) │   + INSERT    │ (run 1)    │   + INSERT    │ (run 2)    │
└────────────┘               └────────────┘               └────────────┘

Common mistakes

Choosing append "because it's simplest," without considering whether the process can be retried. What happens: someone picks append for being the strategy with the least configuration (no unique_key to declare), without thinking about what happens if the run gets retried — because of a network failure, a timeout, or simply being run twice by mistake. Why it happens: less configuration feels like "less that can go wrong," a reasonable intuition in general, but wrong here. How to spot it: this lesson's experiment shows it with numbers — 42, then 44 — with no error or warning at all in dbt run's report. How to fix it: append is a correct choice only when you can guarantee, outside of dbt, that every run processes genuinely new data that's never going to be reprocessed — for example, an event stream where every run consumes a queue and never reads what was already consumed again. For any case where a run could repeat over the same data range (like this module, with a fixed run_date passed by hand), delete+insert or merge are the safe options.

Declaring incremental_strategy='delete+insert' without unique_key. What happens: someone copies the strategy but forgets to add unique_key='order_id' to the configuration. Why it happens: append doesn't need unique_key, and it's easy to generalize that absence to the other strategies without checking the requirements table. How to spot it: dbt fails to compile or run the model with a configuration error saying delete+insert requires unique_key — this lesson's table marks it explicitly. How to fix it: delete+insert and merge always need unique_key — it's the column (or combination of columns) dbt uses to decide which rows in the destination table to delete or update before inserting the new ones.

Confusing unique_key with a uniqueness test (data_tests: unique). What happens: someone, used to module 4, thinks declaring unique_key='order_id' in the incremental configuration automatically validates that order_id is unique across the whole table, the way the generic unique test would. Why it happens: both use the same word ("unique") and the same column, and it's natural to assume they do the same thing. How to spot it: unique_key in an incremental strategy is an operational instruction — "use this column to decide what to replace" — not a validation; dbt doesn't reject an incremental run even if order_id had duplicates within a single partition, while module 4's unique_fact_orders_order_id test (which is still active, unchanged) would fail in that case. How to fix it: keep relying on data_tests: unique (module 4) to validate that order_id is unique; unique_key in the incremental configuration is a completely different mechanism, which uses that column without checking it.

Exercises

Exercise 1 — Predict merge's result without running it. Without running any command, answer: if you configured fact_orders with incremental_strategy='merge' and unique_key='order_id', and ran dbt run --select fact_orders --vars '{"run_date": "2026-08-05"}' twice in a row, would the final count be 40, 42, or 44? Justify with this lesson's requirements table.

Exercise 2 — Reproduce the append experiment with a different date. Repeat this lesson's append experiment, but with run_date: "2026-08-08" (the day with the most orders: 9). After two runs in a row, what would fact_orders's total count be, and how many times would each order_id for that day show up?

dbt run --select fact_orders --full-refresh   # go back to a clean 40 rows first
dbt run --select fact_orders --vars '{"run_date": "2026-08-08"}'
dbt run --select fact_orders --vars '{"run_date": "2026-08-08"}'
dbt show --inline "select count(*) as n from {{ ref('fact_orders') }}"

Exercise 3 — Argue when merge would be the correct choice, instead of delete+insert, for a hypothetical model. In 2-3 sentences, describe a scenario — different from fact_orders — where merge is clearly preferable to delete+insert, using this lesson's criteria (do you replace the whole partition, or update individual rows?).

Summary and next step

This lesson compared three incremental strategies with real evidence: append over run_date: "2026-08-05" run twice ended up with 44 rows, with every order_id duplicated three times — concrete proof that append isn't idempotent for this use case. delete+insert, with the same experiment, always ended up with 40 rows, no matter how many times the run repeated. You also saw why merge would be equally idempotent, but why delete+insert — replacing the whole partition — fits fact_orders's semantics better than merge — updating individual rows.

Before moving on you should be able to: explain, with a numeric example, why append isn't safe to re-run; and decide, for a new hypothetical model, whether delete+insert or merge is the more appropriate choice depending on whether it replaces whole partitions or updates individual rows.

Lesson 5 applies everything learned so far — is_incremental(), delete+insert, unique_key — to the final, permanent change to fact_orders.sql, the one that's going to stay in Kiosko's real project.

Resources

• dbt Developer Hub — "About incremental strategy," the official reference for the append, merge, delete+insert, insert_overwrite, and microbatch strategies, and their general requirements. docs.getdbt.com/docs/build/incremental-strategy. In English.
• dbt Developer Hub — "DuckDB configurations," the dbt-duckdb adapter's specific reference: confirms it supports append, delete+insert, merge (DuckDB ≥ 1.4.0), and microbatch, with the unique_key requirement for delete+insert and merge. docs.getdbt.com/reference/resource-configs/duckdb-configs. In English.
• data-engineering-foundations-guide, module 6 ("Idempotency and backfill"), where the same pattern backing this lesson's delete+insert choice was implemented by hand.