Mini-project: Kiosko's star schema in dbt

Description

Time to bring the module's seven lessons together into a single flow, start to finish. Lessons 2 and 3 gave you ref() and materializations, separately and with small examples. Lessons 4 and 5 built the three real marts: dim_store, dim_date, fact_orders. Lesson 6 closed out dbt_project.yml's configuration. And lesson 7 taught you to read the resulting DAG without running anything.

This mini-project repeats that complete sequence with no pauses — from the state the project was left in at the end of module 2, to the complete star schema, verified and versioned — and adds the new piece: Kiosko project's third version-control commit, on top of the two modules 1 and 2 left behind.

Connection to the module. This project introduces no new concept — it's the synthesis of lessons 2 through 7, run back to back, plus the exact continuation of the version-control habit earlier modules started. By the end of this lesson, kiosko_analytics/ has a complete star schema — three marts, chained with ref(), materialized with judgment — on top of which module 4 is going to build the first real test suite.

An analogy: the second floor, on a foundation that already proved its weight

Module 2 compared its mini-project to a warehouse's first complete inventory: four suppliers registered, four shelves clean and counted. This mini-project is that same building's second floor: it doesn't put the foundation to the test again — the four staging models stay exactly the same, with this module never having touched a single line of them — it builds on top of them, with new material (the three marts) that depends, explicitly and verifiably, on the first floor still standing. A structural engineer doesn't recalculate the foundation's strength every time they add a new floor — they trust it's already been tested, and focus on making sure the new material is properly anchored to what already exists. That's, precisely, what ref() gave you in this module: the certainty that every new mart is anchored, verifiably, to the staging layer that already proved reliable.

The material: the three marts, gathered

If you already completed lessons 4, 5, and 6 in order, models/marts/ already has these three files. If you're starting this mini-project from module 2's state, this is the complete material to add.

-- models/marts/dim_store.sql
select
    store_id,
    store_name,
    city
from {{ ref('stg_stores') }}

-- models/marts/dim_date.sql
with recursive date_spine as (
    select date '2026-08-01' as calendar_date
    union all
    select calendar_date + interval 1 day
    from date_spine
    where calendar_date < date '2026-08-31'
)

select
    cast(strftime(calendar_date, '%Y%m%d') as integer) as date_key,
    calendar_date,
    strftime(calendar_date, '%A') as day_of_week,
    extract(month from calendar_date) as month,
    extract(quarter from calendar_date) as quarter,
    extract(year from calendar_date) as year,
    extract(dow from calendar_date) in (0, 6) as is_weekend
from date_spine

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

And each model's minimal descriptions, in marts/'s own _models.yml — the same underscore-file pattern you already used in models/staging/kiosko/ since module 2, this time with no data_tests: yet (that piece belongs to module 4):

# models/marts/_models.yml
version: 2

models:
  - name: dim_store
    description: "Store dimension, a dbt model over stg_stores via ref()."

  - name: dim_date
    description: "Date dimension, fixed August 2026 range generated with a deterministic recursive CTE."

  - name: fact_orders
    description: "Sales fact, order-line grain. Joins stg_orders with dim_store and dim_date via ref()."

And dbt_project.yml's update (lesson 6) — a single character changed from module 2:

# dbt_project.yml (only the models: block changes)
models:
  kiosko_analytics:
    +materialized: table
    staging:
      +materialized: view

The reference solution, verified

Part 1 — Run the complete project, from scratch

rm -f kiosko.duckdb
dbt run

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 7 models, 8 data tests, 4 sources, 500 macros

Concurrency: 4 threads (target='dev')

1 of 7 START sql table model main.dim_date ..................................... [RUN]
2 of 7 START sql view model main.stg_events .................................... [RUN]
3 of 7 START sql view model main.stg_orders .................................... [RUN]
4 of 7 START sql view model main.stg_products .................................. [RUN]
2 of 7 OK created sql view model main.stg_events ............................... [OK in 0.08s]
4 of 7 OK created sql view model main.stg_products ............................. [OK in 0.08s]
1 of 7 OK created sql table model main.dim_date ................................ [OK in 0.09s]
3 of 7 OK created sql view model main.stg_orders ............................... [OK in 0.09s]
5 of 7 START sql view model main.stg_stores .................................... [RUN]
5 of 7 OK created sql view model main.stg_stores ............................... [OK in 0.01s]
6 of 7 START sql table model main.dim_store .................................... [RUN]
6 of 7 OK created sql table model main.dim_store ............................... [OK in 0.01s]
7 of 7 START sql table model main.fact_orders .................................. [RUN]
7 of 7 OK created sql table model main.fact_orders ............................. [OK in 0.02s]

Finished running 3 table models, 4 view models in 0 hours 0 minutes and 0.21 seconds (0.21s).

Completed successfully

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

Seven models, seven OK, in the exact order the DAG requires — dim_date and the four staging views first (no dependencies among them, run in parallel), dim_store after stg_stores, and fact_orders last, the only time in the whole project a model waits on three different dependencies at once. If this run fails, don't move on to Part 2 — go back to whichever lesson matches the file that's causing it: dim_store.sql or dim_date.sql (lesson 4), fact_orders.sql (lesson 5), or dbt_project.yml (lesson 6).

Part 2 — Confirm module 2's inherited tests still pass

This module added no new test — that's module 4's job — but it's worth confirming that building three new marts, on top of the staging layer, broke nothing module 2 had already verified:

dbt test

What to expect.

Running with dbt=1.12.2
Registered adapter: duckdb=1.11.0
Found 7 models, 8 data tests, 4 sources, 500 macros

Concurrency: 4 threads (target='dev')

1 of 8 START test not_null_stg_events_event_id ................................. [RUN]
2 of 8 START test not_null_stg_orders_order_id ................................. [RUN]
3 of 8 START test not_null_stg_products_product_id ............................. [RUN]
4 of 8 START test not_null_stg_stores_store_id ................................. [RUN]
1 of 8 PASS not_null_stg_events_event_id ....................................... [PASS in 0.05s]
4 of 8 PASS not_null_stg_stores_store_id ....................................... [PASS in 0.05s]
3 of 8 PASS not_null_stg_products_product_id ................................... [PASS in 0.05s]
5 of 8 START test unique_stg_events_event_id ................................... [RUN]
6 of 8 START test unique_stg_orders_order_id ................................... [RUN]
7 of 8 START test unique_stg_products_product_id ............................... [RUN]
2 of 8 PASS not_null_stg_orders_order_id ....................................... [PASS in 0.07s]
8 of 8 START test unique_stg_stores_store_id ................................... [RUN]
8 of 8 PASS unique_stg_stores_store_id ......................................... [PASS in 0.01s]
7 of 8 PASS unique_stg_products_product_id ..................................... [PASS in 0.03s]
5 of 8 PASS unique_stg_events_event_id ......................................... [PASS in 0.04s]
6 of 8 PASS unique_stg_orders_order_id ......................................... [PASS in 0.04s]

Finished running 8 data tests in 0 hours 0 minutes and 0.18 seconds (0.18s).

Completed successfully

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

The exact same eight PASSes from module 2 — building three new marts, with two real JOINs in between, didn't affect the staging layer that was already tested at all. This is, on its own, a demonstration of why layer separation matters: staging's tests validate one responsibility (that each source table is clean), and that responsibility doesn't change no matter how much the project grows on top of it.

Part 3 — Inspect the complete DAG with dbt ls

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.staging.kiosko.not_null_stg_orders_order_id
kiosko_analytics.staging.kiosko.not_null_stg_stores_store_id
kiosko_analytics.staging.kiosko.unique_stg_orders_order_id
kiosko_analytics.staging.kiosko.unique_stg_stores_store_id

The same eleven-resource tree you already saw in lesson 7 — the confirmation that the project's DAG is exactly where it's supposed to be, with no lost dependency and no surprise.

Part 4 — The final reconciliation: 40 rows, 106.15 revenue

This is the check that actually matters — the one neither PASS=7 nor PASS=8 can replace:

import duckdb

con = duckdb.connect("kiosko.duckdb")
checks = {
    "raw_orders (raw)": con.sql("select count(*) from read_csv_auto('raw_data/kiosko/orders_*.csv')").fetchone()[0],
    "stg_orders (staging)": con.sql("select count(*) from stg_orders").fetchone()[0],
    "dim_store": con.sql("select count(*) from dim_store").fetchone()[0],
    "dim_date": con.sql("select count(*) from dim_date").fetchone()[0],
    "fact_orders (mart)": con.sql("select count(*) from fact_orders").fetchone()[0],
}
for k, v in checks.items():
    print(f"{k:<22}{v}")

total_revenue = con.sql("select sum(revenue) as total from fact_orders").fetchone()[0]
print(f"\ntotal revenue: {total_revenue}")

What to expect.

raw_orders (raw)     40
stg_orders (staging) 40
dim_store             3
dim_date              31
fact_orders (mart)   40

total revenue: 106.15

40 rows in the raw file, 40 in staging, 40 in the final mart — no order got lost or duplicated anywhere in the chain, even though fact_orders is this guide's first model to combine three different sources with two real JOINs. And 106.15 total revenue: the exact same number data-modeling-for-analytics-guide calculated by hand over this same dimensional model, now reproducible by anyone with a single dbt run.

Part 5 — The project's third commit

Review what changed since module 2's commit:

git status --short

What to expect.

 M dbt_project.yml
?? models/marts/

Two kinds of change: dbt_project.yml modified (the materialization default, lesson 6) and a completely new folder, models/marts/, not yet tracked. Just like in the two previous commits, no generated file — not kiosko.duckdb, not target/, not logs/ — shows up in this list: module 1's .gitignore keeps doing its job without you having touched it.

git add models/marts dbt_project.yml
git commit -m "Module 3: rebuild dim_store, dim_date and fact_orders with ref()"

What to expect.

[master c3d4e5f] Module 3: rebuild dim_store, dim_date and fact_orders with ref()
 4 files changed, 36 insertions(+), 1 deletion(-)
 create mode 100644 models/marts/_models.yml
 create mode 100644 models/marts/dim_date.sql
 create mode 100644 models/marts/dim_store.sql
 create mode 100644 models/marts/fact_orders.sql

(The commit's short identifier, c3d4e5f in this example, is going to be different on your machine — as you already saw in modules 1 and 2, 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.

c3d4e5f (HEAD -> master) 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

Three commits, each documenting a real, verifiable milestone: the project's scaffolding, the staging layer, and now the complete star schema. Any teammate who clones this repository and runs git log --oneline can read, with no ambiguity, exactly when each piece of Kiosko's warehouse became part of the project.

Diagram: the mini-project's complete flow

flowchart TD
    A["state at the end of module 2\n4 sources, 4 staging models, 2 commits"] --> B["dim_store.sql (lesson 4)\nref('stg_stores')"]
    B --> C["dim_date.sql (lesson 4)\nrecursive CTE, no ref()"]
    C --> D["fact_orders.sql (lesson 5)\n3 ref()s, 2 INNER JOIN"]
    D --> E["dbt_project.yml (lesson 6)\ndefault: table, staging: view"]
    E --> F["dbt run -> PASS=7"]
    F --> G["dbt test -> PASS=8 (inherited, no change)"]
    G --> H["dbt ls --select +fact_orders -> 11 resources"]
    H --> I["reconciliation: 40 rows, 106.15 revenue"]
    I --> J["git add + commit -> third commit"]
    J --> K["git status -> clean tree"]

Common mistakes

Running dbt run with --select marts and being surprised it fails on an empty database. What happens: someone, trying to be efficient, runs dbt run --select marts directly against a freshly deleted kiosko.duckdb, without running the staging layer first (or alongside it). Why it happens: --select marts selects only the models/marts/ folder, without automatically pulling in its dependencies — the same behavior you already saw with --select model_name in lesson 2, applied now to a whole folder. How to spot it: the error is the same Catalog Error: ... does not exist you already know, because stg_orders and stg_stores never got built. How to fix it: use --select +marts (with the + in front) if you want to build the marts and everything they need, or simply plain dbt run with no --select for the complete project, as this mini-project does in Part 1.

Committing models/marts/ without _models.yml, and losing the descriptions. What happens: someone runs git add models/marts/*.sql instead of git add models/marts (the complete folder), and the _models.yml file with the descriptions ends up left out of the commit by accident. Why it happens: the *.sql pattern seems to capture "everything important" about the mart, and it's easy to forget the YAML configuration is just as real a piece of the project. How to spot it: git status after the commit would still show _models.yml as untracked (??) — the sign it got left out. How to fix it: as this mini-project does, always add the complete folder (git add models/marts, not an extension pattern) so you don't lose configuration files that accompany the SQL.

Thinking PASS=7 in dbt run already certifies the dimensional model is well designed. What happens: someone sees the seven lines in green and considers the module closed, without running Part 4's reconciliation. Why it happens: seven OKs in a row feel like a complete confirmation, but — as module 2 already stressed, and this module's lesson 5 again — they only confirm the SQL is syntactically valid, not that the result is correct. How to spot it: a badly written JOIN, like the one you broke on purpose in lesson 5 (comparing timestamp against date with no cast()), runs perfectly and ends in PASS=1, with zero result rows. How to fix it: this mini-project's Part 4 — not Part 1 — is the real check; never close out a module in this guide without having confirmed a concrete number, not just the absence of an error.

Exercises

Exercise 1 — Rebuild the star schema in a practice folder. Without looking at this lesson's worked example, write from memory (or with your own notes) models/marts/'s three files in a practice copy of the project, and confirm with dbt run and Part 4's reconciliation that you get the same result: 40 rows, 106.15 revenue.

Exercise 2 — Trigger a cascading SKIP and read it correctly. Temporarily break stg_orders.sql (for example, with a badly written select) and run dbt run over the complete project. Identify, in the output, which model shows ERROR and which show SKIP.

-- stg_orders.sql, temporarily broken
select
    order_id,
    store_id,
    product_id,
    cast(quantity as integer) as quantity,
    cast(unit_price as decimal(10, 2)) as unit_price,
    cast(order_ts as timestamp) as order_ts
from {{ source('kiosko_raw', 'ordrs') }}   -- typo on purpose: "ordrs"

Exercise 3 — Argue why this mini-project is the right starting point for module 4. In 2-3 sentences, explain what guarantees having the complete star schema — three marts, chained with ref(), materialized with judgment, verified with a row-and-revenue reconciliation — gives you, before you start writing formal tests on it.

Summary and next step

In this mini-project you rebuilt the module's complete star schema: dim_store, dim_date, and fact_orders, all three run together with dbt run (PASS=7), with module 2's staging layer still passing (dbt test, PASS=8), and the complete DAG confirmed with dbt ls --select +fact_orders. You verified, with a four-point reconciliation — raw, staging, dimensions, mart — that 40 order lines arrived intact at fact_orders, with a total revenue of exactly 106.15. And you closed the module with the project's third commit, extending the version-control habit modules 1 and 2 already started.

With this you close module 3. You now have a kiosko_analytics/ project with seven models — four staging views, three marts materialized as tables — a complete DAG ref() resolves automatically on every run, and three commits in its history, each documenting a real, verifiable milestone.

Where you go next. Module 4 comes back to these same seven models with a different question: how do you confirm, automatically and repeatably, that they're going to stay correct even as Kiosko's data changes? You're going to meet the four out-of-the-box generic tests (unique, not_null, accepted_values, relationships), you're going to write a custom generic test of your own, and you're going to declare singular tests for business rules no generic test captures — all with the data_tests: key you already know since module 2, now applied with much more depth over the complete star schema you just built.

Resources

• dbt Developer Hub — "About dbt projects," the overview confirming the complete structure — sources, staging, marts — this mini-project consolidates. docs.getdbt.com/docs/build/projects. In English.
• dbt Developer Hub — "ref()" and "About materializations," this module's two central references, cited again as a summary of everything this mini-project puts into practice. docs.getdbt.com/reference/dbt-jinja-functions/ref · docs.getdbt.com/docs/build/materializations. In English.
• Git — official git log documentation, including the formatting options (--oneline) used in this mini-project's Part 5, already cited in module 2. git-scm.com/docs/git-log. In English.