Proving idempotency by running twice

Description

This is the whole module's central lesson — the one that turns "it should be idempotent" into a fact verified with evidence. You're going to run the exact same command from lesson 5 — dbt run --select fact_orders --vars '{"run_date": "2026-08-05"}' — a second time, without changing anything at all, and you're going to confirm, with three independent checks, that the result is indistinguishable from what you already had: the same total count, the same order_ids with not a single duplicate, and the other partitions (the other six days of the week) completely untouched.

Connection to the module. Lesson 5 made the configuration change and ran the model once. Lesson 4 already demonstrated, with append, what a strategy that is not idempotent looks like (42, then 44 rows). This lesson applies the real, definitive proof, over the project's final configuration — delete+insert — and leaves no room for doubt: run twice, measure three times.

One last analogy: the elevator button, with evidence

Lesson 1 introduced the elevator-button analogy: pressing it once calls the elevator; pressing it five times doesn't call five elevators. This lesson is the equivalent of checking that with a stopwatch and a passenger list: press the button, count how many elevators arrived (one), press it again, count again (still one) — the intuition that "it should work that way" isn't enough, the real proof is measuring the result, twice, and confirming it didn't change.

Worked example: the second run, identical to the first

If you completed lesson 5, fact_orders already ran once incrementally over run_date: "2026-08-05", with 40 rows and 106.15 revenue confirmed. Run the exact same command, with no changes:

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.09s]

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

Completed successfully

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

At a glance, indistinguishable from the first run — same OK, same PASS=1. But PASS=1 only confirms the SQL ran with no errors, exactly the same warning module 2 already insisted on since its very first lesson: never trust the absence of errors as proof that the result is correct. The real proof is the three counts that follow.

Check 1: the total count didn't change

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 — identical to lesson 5, after two complete runs over the same date. If delete+insert were, due to a configuration mistake, actually append (the error you already saw in lesson 4), this number would be 42, not 40 — the first warning sign, always visible with just this one query.

Check 2: no order_id for that date got duplicated

The total count, by itself, could hide a problem if two errors happened to cancel each other out (unlikely with real data, but worth not depending on). Verify, column by column, that every 2026-08-05 order shows up exactly once:

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 order by order_id"

What to expect.

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

The only two orders for that date — ORD-3001 and ORD-3002 —, each with n = 1. Not a single duplicate, after delete+insert deleted and inserted that same partition twice in a row. This is the query that, in lesson 4's append experiment, would have shown n = 3 for each one — the exact difference between an idempotent strategy and one that isn't, measured with the same kind of query.

Check 3: the other partitions weren't touched

The final check — and the most important one for trusting an incremental model in production — is confirming that a run scoped to run_date: "2026-08-05" didn't affect, not even a little, the orders from the other six days of the week:

dbt show --inline "select cast(order_ts as date) as order_date, count(*) as n from {{ ref('fact_orders') }} group by 1 order by 1" --limit 10

What to expect.

Previewing inline node:
| order_date |  n |
| ---------- | -- |
| 2026-08-03 |  8 |
| 2026-08-04 |  6 |
| 2026-08-05 |  2 |
| 2026-08-06 |  5 |
| 2026-08-07 |  7 |
| 2026-08-08 |  9 |
| 2026-08-09 |  3 |

8 + 6 + 2 + 5 + 7 + 9 + 3 = 40 — Kiosko's exact seven days of the week, each with the count you already know from module 2. The 2026-08-05 row (2 orders) is the only one this module touched, twice, with two incremental runs — and it still shows exactly 2, not 4. The other six dates were never part of the WHERE cast(o.order_ts as date) = cast('2026-08-05' as date) filter, so delete+insert's DELETE never reached them — they remain exactly as they were left by module 5's original build.

Confirming with the full test suite

As an extra close, run the seven data_tests module 4 already declared on fact_orders — including unique_fact_orders_order_id, the one most directly relevant to this lesson:

dbt test --select fact_orders

What to expect.

Finished running 7 data tests in 0 hours 0 minutes and 0.15 seconds (0.15s).

Completed successfully

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

unique_fact_orders_order_id — the test that would fail immediately if delete+insert had produced any duplicate — is still PASS, with no change since module 4. This is an additional, automated confirmation of exactly what you already verified by hand with the three queries above: the test suite that already existed in the project, with this module adding not a single new test, is already protecting you against the exact scenario this whole module exists to prevent.

Diagram: the three checks, stacked

flowchart TD
    A["dbt run --vars run_date=2026-08-05\n(1st run)"] --> B["dbt run --vars run_date=2026-08-05\n(2nd run, identical)"]
    B --> C{"Check 1:\ncount(*) = 40?"}
    C -- Yes --> D{"Check 2:\nORD-3001, ORD-3002 = 1 each?"}
    D -- Yes --> E{"Check 3:\nthe other 6 dates unchanged?"}
    E -- Yes --> F["dbt test --select fact_orders\nPASS=7"]
    F --> G["Idempotency proven\nwith evidence, not just theory"]

Common mistakes

Trusting only PASS=1 and skipping the three checks. What happens: someone runs the command twice, sees Completed successfully both times, and concludes idempotency is proven, without running any of this lesson's three queries. Why it happens: PASS=1 feels like a complete confirmation — already emphasized in modules 2 and 3, but easy to forget under time pressure. How to spot it: if the model ever had a configuration bug (for example, incremental_strategy='append' by mistake, as in lesson 4), dbt run would still finish with PASS=1 both times, exactly the same as with delete+insert — the only way to tell the two scenarios apart is measuring the real count. How to fix it: idempotency is never proven by the absence of errors — it's always proven with a concrete number before and a concrete number after, compared explicitly, the way this lesson's three checks did.

Running the second run with a slightly different run_date, by typo, and not noticing. What happens: someone writes --vars '{"run_date": "2026-08-06"}' on the second run instead of repeating "2026-08-05", and is surprised when the per-date count (Check 3) shows changes in two different partitions instead of none. Why it happens: a single-digit typo in a date is easy to make and hard to notice at a glance. How to spot it: if Check 3 shows a different count than expected on a date that shouldn't have changed, first review the exact --vars you passed on each run — this is, precisely, why Check 3 exists: it catches runs where you touched the wrong partition, something Check 1 (the total count) doesn't always reveal, because the total could still be 40 even if you accidentally moved orders from one "wrong" date to another. How to fix it: to really prove idempotency, the two runs need the same run_date, character for character — copy and paste the value instead of retyping it by hand.

Thinking fact_orders's idempotency also protects stg_orders or the sources. What happens: someone generalizes this lesson's guarantee beyond what it actually proves, assuming "the whole project is idempotent" because fact_orders is. Why it happens: idempotency feels like a property of the whole project, not of a specific model. How to spot it: stg_orders is still a view with no incremental configuration at all — every query recalculates it completely, always, which is correct for a staging view (module 2) but has nothing to do with an incremental model's idempotency; and the sources (kiosko_raw.orders) aren't even a concept "idempotency" applies to, they're fixed files read directly, with no state to preserve between runs. How to fix it: the idempotency you proved in this lesson is a specific property of fact_orders, guaranteed by its configuration (incremental_strategy='delete+insert', unique_key='order_id') — not an automatic property of "any dbt model" or "the whole project."

Exercises

Exercise 1 — Run a third time, and a fourth. Run dbt run --select fact_orders --vars '{"run_date": "2026-08-05"}' two more times (for a total of four runs over the same date, counting the ones from lesson 5 and this lesson). Confirm the total count is still 40 after the fourth run.

Exercise 2 — Prove idempotency over a different date, with more rows. Repeat this lesson's three checks, but with run_date: "2026-08-08" (the day with the most orders: 9). Run the command twice and confirm the total count and the breakdown by order_id.

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_rows 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-08' as date) group by order_id order by order_id"

Exercise 3 — Argue why the three checks are necessary, not redundant. In 2-3 sentences, explain what kind of error Check 2 (count by order_id) would catch that Check 1 (total count) might not, and what kind of error Check 3 (count by date) would catch that the other two might not.

Summary and next step

This lesson ran dbt run --select fact_orders --vars '{"run_date": "2026-08-05"}' a second time, exactly like lesson 5, and proved it with three independent checks: the total count stayed at 40 rows / 106.15 revenue; every order_id for that date (ORD-3001, ORD-3002) showed up exactly once, with no duplicates; and the other six dates in the week stayed completely untouched (8, 6, 5, 7, 9, 3 rows, with no change at all). The suite of seven data_tests on fact_orders, inherited unchanged from module 4, confirmed the same thing automatically. This module's idempotency stopped being a theoretical claim — it's now a fact measured three times.

Before moving on you should be able to: name this lesson's three checks from memory; and explain why none of them, on its own, would be enough evidence of idempotency.

Lesson 7 completes an incremental model's operational toolkit: --full-refresh, the command that rebuilds the complete table from scratch when you really need to — for example, if you changed the SELECT's logic and wanted every historical partition to reflect the change, not just the ones you reprocess from now on.

Resources

• dbt Developer Hub — "About incremental models," again this module's central reference, now reread with this lesson's concrete evidence. docs.getdbt.com/docs/build/incremental-models. In English.
• dbt Developer Hub — "Add data tests to your DAG," already cited in module 4, now confirming that the existing suite protects against duplicates with no need for any new test. docs.getdbt.com/docs/build/data-tests. In English.
• Wikipedia — "Idempotence," the formal definition of the mathematical/computer-science concept this lesson demonstrates with evidence over fact_orders. en.wikipedia.org/wiki/Idempotence. In English.