Mini-project: Kiosko's incremental `fact_orders`

Description

It's time to bring this module's seven lessons together into a single, end-to-end flow, over Kiosko's complete project — not just fact_orders in isolation. This mini-project rebuilds the whole project from scratch with dbt build, runs the incremental run twice over the same date to confirm (once more, now over the project's final, clean state) that it doesn't duplicate anything, confirms the 15 data_tests inherited from module 4 are still green, rebuilds with --full-refresh, and exposes — on purpose, as part of the learning — a real change to the project's operational contract that this module introduced: a plain dbt build, with no --vars or --full-refresh, no longer works once fact_orders exists as an incremental table.

And it closes with this module's new piece: Kiosko project's sixth version-control commit, on top of the five modules 1 through 5 left behind.

Connection to the module. This project introduces no new concept — it's the complete synthesis of lessons 2 through 7, run end-to-end over the real project, including the one piece no earlier lesson has shown yet: what happens to a dbt build with no arguments, run out of habit, after a model became incremental.

An analogy: the complete inventory, with a new process on the line

Modules 2 through 5 already used the warehouse analogy for their own mini-projects. This mini-project adds a different piece: not a new shelf or a new filing cabinet, but a procedure change on a line that already existed. Think of an assembly line that, until yesterday, stopped completely every night and started over from scratch every morning — simple, but increasingly slow as the line grows. Today, someone installed a mechanism that lets the line keep going from where it left off, processing only the day's new batch. The rest of the factory — the other shelves, module 4's quality inspector, module 5's archivist — keeps working exactly the same. The only thing that changed is that specific line's procedure, and this mini-project is the day the whole team has to learn the new procedure.

The material: this module's change, in a single file

If you already completed lessons 2 through 7 in order, kiosko_analytics/ already has this change. If you're jumping straight into this mini-project, this is the only file that needs to be in place:

-- models/marts/fact_orders.sql
{{
    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 %}

No new file, no new folder — unlike modules 4's mini-project (which added macros/ and tests/) and 5's (which added snapshots/), this module modifies a single file that already existed.

The reference solution, verified

Part 1 — Rebuild the complete project from scratch

rm -f kiosko.duckdb
dbt build

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')

[... sources, staging, snapshot, and marts, in the same DAG order as module 5 ...]

16 of 23 START sql incremental model main.fact_orders .......................... [RUN]
16 of 23 OK created sql incremental model main.fact_orders ..................... [OK in 0.05s]

[... 15 data tests, all PASS ...]

Finished running 1 incremental model, 1 snapshot, 2 table models, 15 data tests, 4 view models in 0 hours 0 minutes and 0.44 seconds (0.44s).

Completed successfully

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

23 resources, 23 PASS, zero errors. Notice the Finished running line: it says 1 incremental model, 1 snapshot, 2 table models, 15 data tests, 4 view models — before this module, it said 3 table models (module 5); now fact_orders counts separately, as the project's only incremental model. This first dbt build, over a freshly deleted database, didn't need any --vars — the fact_orders table didn't exist yet, so is_incremental() was False (lesson 3's first of four conditions), and the model processed all 40 rows with no filter, exactly like a normal table. If this part fails, don't move on to Part 2 — go back to the lesson matching whichever specific resource is failing.

Part 2 — The first real incremental run, over a specific date

With the project already fully built, run the first truly incremental run — the table already exists, so this time is_incremental() is going to be True:

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

What to expect.

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

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

Previewing inline node:
| n_rows | total_revenue |
| ------ | -------------- |
|     40 |         106.15 |

Part 3 — The same run, again: the idempotency proof

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

What to expect.

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

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

Previewing inline node:
| n_rows | total_revenue |
| ------ | -------------- |
|     40 |         106.15 |

40, 106.15 — identical to Part 2. Two runs in a row over run_date: "2026-08-05", and the count never moved — the same check from lesson 6, now repeated over the project's final, complete state, after a dbt build from scratch.

Part 4 — Confirm the complete test suite

dbt test

What to expect.

Found 7 models, 15 data tests, 1 snapshot, 4 sources, 501 macros

Finished running 15 data tests in 0 hours 0 minutes and 0.21 seconds (0.21s).

Completed successfully

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

Fifteen out of fifteen, with no change since module 4 — this module didn't add a single new data_test, and didn't need to: unique_fact_orders_order_id, already declared since module 4, is exactly the test that would fail if delete+insert were misconfigured.

Part 5 — --full-refresh, to close the loop

dbt run --select fact_orders --full-refresh
dbt show --inline "select count(*) as n_rows, sum(revenue) as total_revenue from {{ ref('fact_orders') }}"

What to expect.

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

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

Previewing inline node:
| n_rows | total_revenue |
| ------ | -------------- |
|     40 |         106.15 |

Rebuilding everything from scratch, with no --vars at all, produces exactly the same result as the two incremental runs from Parts 2 and 3 — the final confirmation that "partition by partition" and "all at once" converge to the same content, exactly as lesson 7 demonstrated.

Part 6 — What happens to a dbt build run out of habit, now

This is the piece no earlier lesson has shown you yet, and it's worth seeing before closing the module. Run dbt build exactly the way you ran it at the end of modules 2, 3, 4, and 5 — with no --vars, no --full-refresh:

dbt build

What to expect.

Found 7 models, 15 data tests, 1 snapshot, 4 sources, 501 macros

[... sources, staging, and snapshot run with no problem ...]

16 of 23 START sql incremental model main.fact_orders .......................... [RUN]
16 of 23 ERROR creating sql incremental model main.fact_orders ................. [ERROR in 0.01s]
19 of 23 SKIP test is_positive_fact_orders_quantity__True ...................... [SKIP]
18 of 23 SKIP test assert_no_negative_revenue .................................. [SKIP]
17 of 23 SKIP test accepted_values_fact_orders_product_id__P001__P002__P003__P004  [SKIP]
20 of 23 SKIP test is_positive_fact_orders_unit_price__False ................... [SKIP]
21 of 23 SKIP test not_null_fact_orders_order_id ............................... [SKIP]
22 of 23 SKIP test relationships_fact_orders_store_id__store_id__ref_dim_store_  [SKIP]
23 of 23 SKIP test unique_fact_orders_order_id ................................. [SKIP]

Finished running 1 incremental model, 1 snapshot, 2 table models, 15 data tests, 4 view models in 0 hours 0 minutes and 0.39 seconds (0.39s).

Completed with 1 error, 0 partial successes, and 0 warnings:

[ERROR]: in model fact_orders (models/marts/fact_orders.sql)
  Compilation Error in model fact_orders (models/marts/fact_orders.sql)
  Required var 'run_date' not found in config:
  Vars supplied to fact_orders = {}

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

ERROR=1, SKIP=7. This isn't a flaw in this mini-project — it's the real consequence, already anticipated in lesson 5, of fact_orders now requiring run_date on any run where it already exists as a table. dim_date, dim_store, and the four staging models run with no problem (they don't depend on any variable), but the seven data_tests that depend on fact_orders — including module 4's six column tests and the singular assert_no_negative_revenue — end up in SKIP, exactly the same cascade mechanism you already saw in module 3: an ERROR in one model propagates as SKIP to everything downstream of it, never as an independent ERROR. This is, precisely, the moment Kiosko's project stopped having a single closing habit (dbt build, plain) and started having two valid paths: one for routine incremental runs (--vars), and another for complete rebuilds (--full-refresh).

Part 7 — Closing correctly, with --vars

The correct way to run dbt build over the complete project, now that fact_orders is incremental, is to explicitly declare which partition you're processing:

dbt build --vars '{"run_date": "2026-08-09"}'

What to expect.

Found 7 models, 15 data tests, 1 snapshot, 4 sources, 501 macros

[... sources, staging, snapshot, and marts, including fact_orders this time ...]

16 of 23 START sql incremental model main.fact_orders .......................... [RUN]
16 of 23 OK created sql incremental model main.fact_orders ..................... [OK in 0.05s]

[... 15 data tests, all PASS ...]

Finished running 1 incremental model, 1 snapshot, 2 table models, 15 data tests, 4 view models in 0 hours 0 minutes and 0.48 seconds (0.48s).

Completed successfully

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

23, 23, zero errors. Confirm the final result:

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 — Kiosko's project's final, clean state, with fact_orders incremental, proven twice over the same date, rebuilt with --full-refresh, and now closed with the correct form of dbt build this module introduced.

Part 8 — Confirming the DAG didn't change

dbt ls --select +fact_orders

What to expect.

kiosko_analytics.marts.dim_date
kiosko_analytics.marts.dim_store
kiosko_analytics.marts.fact_orders
kiosko_analytics.staging.kiosko.stg_orders
kiosko_analytics.staging.kiosko.stg_stores
source:kiosko_analytics.kiosko_raw.orders
source:kiosko_analytics.kiosko_raw.stores
kiosko_analytics.marts.accepted_values_fact_orders_product_id__P001__P002__P003__P004
kiosko_analytics.assert_no_negative_revenue
kiosko_analytics.marts.is_positive_fact_orders_quantity__True
kiosko_analytics.marts.is_positive_fact_orders_unit_price__False
kiosko_analytics.marts.not_null_fact_orders_order_id
kiosko_analytics.staging.kiosko.not_null_stg_orders_order_id
kiosko_analytics.staging.kiosko.not_null_stg_stores_store_id
kiosko_analytics.marts.relationships_fact_orders_store_id__store_id__ref_dim_store_
kiosko_analytics.marts.unique_fact_orders_order_id
kiosko_analytics.staging.kiosko.unique_stg_orders_order_id
kiosko_analytics.staging.kiosko.unique_stg_stores_store_id

The same eighteen-resource tree you already know from module 4 (module 3's eleven plus the seven data_tests on fact_orders that module added) and confirmed unchanged in module 5. Converting fact_orders to incremental changed how it gets built — partition by partition instead of all at once — but not what it depends on — it's still, exactly, stg_orders, dim_store, and dim_date, not one dependency more or less, and its own suite of seven tests stays intact.

Part 9 — The project's sixth commit

git status --short

What to expect.

 M models/marts/fact_orders.sql

A single modified file — the smallest module, in terms of on-disk changes, of the whole guide so far. No new file, no new folder: the conversion to incremental is, by design, a configuration change to a model that already existed, not a new component of the project.

git add models/marts/fact_orders.sql
git commit -m "Module 6: make fact_orders incremental with delete+insert, prove idempotency"

What to expect.

[master f6a7b8c] Module 6: make fact_orders incremental with delete+insert, prove idempotency
 1 file changed, 12 insertions(+), 1 deletion(-)

(The commit's short identifier, f6a7b8c in this example, is going to be different on your machine — as you already saw in earlier modules, it's a hash generated from the exact content and the moment of the commit.) Confirm the complete history and a clean working tree:

git log --oneline
git status

What to expect.

f6a7b8c (HEAD -> master) Module 6: make fact_orders incremental with delete+insert, prove idempotency
e5f6a7b Module 5: snapshot dim_product with SCD type 2 over the P002 change
d4e5f6a Module 4: add data tests to fact_orders, a custom generic test and a singular test
c3d4e5f Module 3: rebuild dim_store, dim_date and fact_orders with ref()
a1b2c3d Module 2: declare Kiosko sources and build the staging layer
af0b710 First dbt project: kiosko_analytics scaffolding

On branch master
nothing to commit, working tree clean

Six commits, each documenting a real, verifiable milestone: the project's scaffolding, the staging layer, the star schema, the test suite, the automated SCD type 2, and now the first time a model in Kiosko's project processes its data in pieces, instead of all at once.

Diagram: the mini-project's complete flow

flowchart TD
    A["state at the end of module 5\n7 models, 1 snapshot, 15 data tests, 5 commits"] --> B["fact_orders.sql: incremental,\ndelete+insert, unique_key (L2-L5)"]
    B --> C["dbt build from scratch -> PASS=23\n(1st time, no filter, is_incremental=False)"]
    C --> D["dbt run --vars run_date=2026-08-05\n(1st real incremental run)"]
    D --> E["dbt run --vars run_date=2026-08-05\n(2nd run, same date) -> 40 rows, idempotent"]
    E --> F["dbt test -> PASS=15"]
    F --> G["dbt run --full-refresh -> 40 rows, idempotent"]
    G --> H["dbt build with NO --vars -> ERROR=1, SKIP=7\n(the operational contract changed)"]
    H --> I["dbt build --vars run_date=2026-08-09\n-> PASS=23, the correct way to close"]
    I --> J["git add + commit -> sixth commit"]

Common mistakes

Ending the mini-project at Part 6, without reaching Part 7. What happens: someone sees Part 6's ERROR=1, assumes something broke permanently in the project, and doesn't continue on to the correct run with --vars. Why it happens: an ERROR in a dbt build report instinctively feels like the end of the road, not an intermediate lesson. How to spot it: if you end this mini-project with fact_orders in an unresolved error state, check whether you ran Part 7 — the run with --vars that correctly closes the cycle. How to fix it: Part 6 is, on purpose, a demonstration of new, real behavior — not a bug to fix in the code — and Part 7 is the correct resolution: pass --vars with the matching date, exactly like any routine run from here on.

Committing with git add . instead of naming the specific file. What happens: someone, after experimenting with lesson 4's strategies (append, merge) on temporary copies of the file, runs git add . without first checking what changed, risking committing an experimental version instead of the final delete+insert version. Why it happens: after several experiments within the same module, it's easy to lose track of which one is the file's "final" version. How to spot it: before any git add, run git diff models/marts/fact_orders.sql and confirm the strategy says delete+insert, not append or merge — either of those, even though it's also a valid configuration in the abstract, isn't the one this module left as the reference version. How to fix it: always name the file explicitly (git add models/marts/fact_orders.sql, as in Part 9), and review the content with git diff before committing — the same habit modules 1 through 5 already insisted on.

Thinking Part 6's ERROR=1 means the module's idempotency is broken. What happens: someone connects the "missing run_date" ERROR with the idempotency concept this module spent seven whole lessons demonstrating, and concludes something contradicts what was already proven. Why it happens: both are "problems" that show up around fact_orders, and it's easy to mix them up without distinguishing their cause. How to spot it: idempotency (lesson 6) is about what happens when you run the same command twice with the same run_date — and that property stays completely intact, confirmed again in Parts 2 and 3 of this very mini-project. Part 6's error is about a missing variable, a run-configuration problem, not data duplication. How to fix it: keep the two concepts separate — idempotency is about the result of repeating an operation; the Required var 'run_date' not found error is about having forgotten a required argument, something completely different.

Exercises

Exercise 1 — Reproduce the complete mini-project from module 5's state. If you have access to a copy of the project as it was left at the end of module 5 (before this module's lesson 2), apply lesson 5's configuration change without looking at the material, and reproduce this mini-project's nine parts. Confirm you land at exactly PASS=23 in the final dbt build with --vars.

See solution

If you followed lessons 2 through 7's sequence, the result should be exactly the same: dbt build --vars '{"run_date": "..."}' ends at PASS=23 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=23, with fact_orders showing 40 rows and 106.15 revenue, no matter how many times you ran the incremental run over the same date before this final build. If the number doesn't match, first check that fact_orders.sql has the three exact config() keys — materialized='incremental', incremental_strategy='delete+insert', unique_key='order_id' — and that the {% if is_incremental() %} block filters by cast(o.order_ts as date), not o.order_ts directly (the same cast() mistake module 3 already warned about).

Exercise 2 — Simulate a complete backfill, day by day. Starting from a freshly deleted kiosko.duckdb (rm -f kiosko.duckdb, without running the complete dbt build yet), run dbt run --select stg_orders dim_store dim_date first (to have the dependencies ready), and then dbt run --select fact_orders --vars '{"run_date": "..."}' once for each of Kiosko's seven weekday dates, in order (2026-08-03 through 2026-08-09). What total count would you expect after the first run (only 2026-08-03)? And after all seven?

See solution

This scenario is different from the earlier lessons, because it starts from a table that doesn't exist yet when you run the first date. The first run (run_date: "2026-08-03") would find that fact_orders doesn't exist — is_incremental() would be False, lesson 3's condition 1 — so it would process all 40 rows of stg_orders, with no filter at all, ignoring that you only asked for August 3rd. Only starting from the second run (run_date: "2026-08-04"), with the table now existing, would the filter really activate — but since the table would already have all 40 rows from the first run, the total count would stay at 40 after all seven runs, with no real change. This result — counterintuitive at first glance — is why a day-by-day backfill, starting from an empty table, needs an extra mechanism (like dbt's --empty flag, or building the first partition with an explicit WHERE from the start) that's outside this module's scope — a good example of why real backfills are managed with an orchestrator, the topic of the next guide in the ecosystem.

Exercise 3 — Argue why this mini-project is the right starting point for module 7. In 2-3 sentences, explain what guarantees you get from having fact_orders already converted to incremental, proven idempotent, with the 15-test suite green, before module 7 starts writing macros and generating documentation over the complete project.

See solution

Module 7 is going to introduce a reusable macro (calculate_revenue) that's likely going to replace the o.quantity * o.unit_price calculation inside fact_orders.sql — a change to the SELECT's logic, exactly the kind of change this module's lesson 7 identified as one of the three scenarios that require --full-refresh. Having idempotency already proven and the test suite already verified means that, when module 7 modifies fact_orders's logic, you're going to already have the tools (--full-refresh to propagate the change across the whole table, dbt test to confirm the result is still correct) mastered, instead of having to learn them for the first time in the middle of a business-logic change.

Summary and next step

In this mini-project you rebuilt Kiosko's complete project from scratch (dbt build, PASS=23, with fact_orders processing all 40 rows with no filter for being the first run), ran the first real incremental run over run_date: "2026-08-05", repeated it a second time and confirmed the count didn't change (40 rows, 106.15), verified that the 15 data_tests inherited from module 4 are still green, and rebuilt with --full-refresh to confirm the result converges to the same content. You saw, with real evidence, this module's change to the project's operational contract — a dbt build with no arguments no longer suffices, once fact_orders exists as an incremental table (ERROR=1, SKIP=7) — and the correct way to close every run from here on (--vars with the matching run_date, or --full-refresh when appropriate). You closed the module with the project's sixth commit.

With this you close module 6. You now have a kiosko_analytics/ project with seven models — six unchanged since module 5, one (fact_orders) converted to incremental with proven idempotency —, one snapshot, fifteen data_tests with no change at all, and six commits in its history, each documenting a real, verifiable milestone.

Where you go next. Module 7 changes the question: until now, every piece of repeated logic — like o.quantity * o.unit_price, which shows up as-is inside fact_orders.sql — was written directly, with no reuse mechanism. You're going to meet dbt's macros — reusable Jinja templates, written once and used across several models —, you're going to document every model and column in the project, and you're going to generate, with dbt docs generate, the project's complete lineage as a browsable artifact, instead of a hand-drawn diagram. fact_orders doesn't change its incremental strategy again from here on — it stays delete+insert, with the idempotency this module proved — the only thing that changes is that its revenue calculation is going to move into a shared macro.

Resources

• dbt Developer Hub — "About incremental models," already cited throughout the module, now as a summary of everything this mini-project puts into practice. docs.getdbt.com/docs/build/incremental-models. In English.
• dbt Developer Hub — "About dbt build," already cited in module 5, again confirming the execution order (sources → snapshots → models → tests, in DAG order) and the cascading SKIP behavior Part 6 of this mini-project put on display. docs.getdbt.com/reference/commands/build. In English.
• Git — official git log documentation, already cited in modules 1 through 5, including the format options (--oneline) used in Part 9 of this mini-project. git-scm.com/docs/git-log. In English.