Module 3: Ref Marts And Materializations

Rebuilding `fact_orders` with `ref()`

Next →

Description

fact_orders is Kiosko's star schema's central piece — the sales fact, at order-line grain, that data-modeling-for-analytics-guide already hand-designed. This lesson rebuilds it as a dbt model: a SELECT that joins stg_orders with dim_store and dim_date, all three via ref(), materialized as table. It's this guide's first time a model depends on more than one source at once — the moment ref() and materializations stop being isolated mechanisms and become the real way to build a warehouse.

By the end of this lesson, fact_orders is going to have exactly 40 rows — the same number as stg_orders since module 2 — with a revenue sum of 106.15: the concrete proof that joining three models with ref() didn't lose or duplicate a single order.

Connection to the module. Lessons 2, 3, and 4 gave you, separately, ref(), materializations, and two already-built marts (dim_store, dim_date). This lesson combines all three pieces in the model that gives them all meaning: without dim_store and dim_date already existing, fact_orders would have nothing to join against.

Worked example: fact_orders.sql

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

Read this carefully, because every piece fills a specific role, and none of them is unnecessary.

All seven SELECT columns come from stg_orders, except one. order_id, store_id, product_id, quantity, unit_price, and order_ts are exactly the same columns you already cleaned in module 2 — same name, same type, no change. The only new column is revenue, calculated as quantity * unit_price: this fact's grain is an order line, so each row's revenue is simply how much that specific line cost. This shape — columns and grain — was already decided in data-modeling-for-analytics-guide; this lesson only translates it into dbt SQL.

The two INNER JOINs add no column to the final result. Look at the SELECT: no column comes from ds (dim_store's alias) or dd (dim_date's alias) — both aliases only appear in their JOIN's ON clause. This is intentional, not an oversight: the two JOINs exist to validate referential integrity, not to enrich the result with new columns. An INNER JOIN against dim_store guarantees every store_id in fact_orders corresponds to a real, already-known store — if any order had a made-up store_id, that INNER JOIN would silently drop it, and the final row count would flag the problem. Same with dim_date: it guarantees every order_ts falls inside the date range dim_date covers.

The JOIN against dim_date needs an explicit cast(), and it isn't optional. o.order_ts is a timestamp (with hour, minute, and second — 2026-08-03 08:14:00), but dd.calendar_date is a date (day only — 2026-08-03). cast(o.order_ts as date) drops the time-of-day part before comparing, so an order's row at 08:14:00 joins correctly against dim_date's August 3 row, no matter what exact time it happened at. This lesson's Common mistakes section shows, with a real number, what happens if you forget this cast().

Running the model

dbt run --select fact_orders

What to expect.

1 of 1 START sql table model main.fact_orders .................................. [RUN]
1 of 1 OK created sql table model main.fact_orders ............................. [OK in 0.03s]

Finished running 1 table 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

Notice something you already saw in lesson 2, now in real action: you didn't tell dbt to run stg_orders, dim_store, or dim_date first. If you'd already run them before (as in this case, after lessons 2 and 4), dbt finds their results already built and uses fact_orders directly. But if you ran the complete project from scratch, on an empty database, with a plain dbt run (no --select at all), dbt would resolve the whole order on its own:

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

Read this run's order carefully, because it's Kiosko's complete DAG working end to end. dim_date, stg_events, stg_orders, and stg_products start at the same time (1 of 7, 2 of 7, 3 of 7, 4 of 7) — none of them depends on any other model in this list, so dbt runs them in parallel, using the four threads profiles.yml configured since module 1. stg_stores starts afterward (5 of 7), and dim_store (6 of 7) only starts once stg_stores finished — the dependency declared with ref('stg_stores') in lesson 4. And fact_orders is, literally, the last one to start (7 of 7): dbt knows, from its three ref()s, that it needs stg_orders, dim_store, and dim_date all complete before attempting it — and all three are already ready by that point, with you never having written that order anywhere except inside fact_orders.sql itself.

Verifying the result: 40 rows, 106.15 total revenue

PASS=7 confirms the SQL is valid — but, as you already learned in module 2, that isn't the check that matters. The real check is the row count and the revenue sum:

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 — the exact same number as stg_orders since module 2, and the same one you already verified by hand in data-engineering-foundations-guide. No order got lost to an invalid store_id, none got lost by falling outside dim_date's range, and no JOIN duplicated one through a multiple match. 106.15 total revenue — the exact sum of quantity * unit_price over the 40 order lines, the same number as the fact_orders data-modeling-for-analytics-guide built by hand. Confirm some concrete rows too, ordered by order_id:

dbt show --inline "select * from {{ ref('fact_orders') }} order by order_id" --limit 5

What to expect.

Previewing inline node:
| order_id | store_id | product_id | quantity | unit_price | revenue |            order_ts |
| -------- | -------- | ---------- | -------- | ----------- | ------- | -------------------- |
| ORD-1001 | S01      | P001       |        3 |        0.55 |    1.65 |  2026-08-03 08:14:00 |
| ORD-1002 | S01      | P002       |        1 |        1.20 |    1.20 |  2026-08-03 08:20:00 |
| ORD-1003 | S02      | P003       |        2 |        0.75 |    1.50 |  2026-08-03 08:31:00 |
| ORD-1004 | S01      | P004       |        1 |        4.50 |    4.50 |  2026-08-03 09:02:00 |
| ORD-1005 | S03      | P001       |        5 |        0.55 |    2.75 |  2026-08-03 09:15:00 |

ORD-1001: three units of P001 at 0.55 each, revenue = 1.65 — exactly 3 × 0.55. Every fact_orders row is verifiable by hand, with the same simple arithmetic, something only possible to confirm because the grain is order-line and not something already aggregated.

Reconciliation: raw, staging, and mart, all three aligned

Close the complete loop, from the raw file to the mart, with an independent query:

import duckdb

con = duckdb.connect("kiosko.duckdb")
raw_orders = con.sql("select count(*) from read_csv_auto('raw_data/kiosko/orders_*.csv')").fetchone()[0]
staged_orders = con.sql("select count(*) from stg_orders").fetchone()[0]
mart_orders = con.sql("select count(*) from fact_orders").fetchone()[0]

print(f"{'raw':<10}{'staging':<10}{'mart':<10}")
print(f"{raw_orders:<10}{staged_orders:<10}{mart_orders:<10}")

What to expect.

raw       staging   mart
40        40        40

Three completely independent paths to the same data — the CSV file read straight, the staging view, and now the mart's table, built with two JOINs in between — and all three match exactly. That match is this module's real guarantee: a mart with real JOINs can stay just as reliable as a staging view with none, as long as referential integrity between the tables is solid — which is, precisely, what fact_orders.sql's two INNER JOINs validate.

Common mistakes

Forgetting the cast() in the JOIN against dim_date, and not noticing because there's no error at all. What happens: someone writes on o.order_ts = dd.calendar_date, without casting order_ts to date, and dbt run ends with PASS=1, with no warning message at all. Run that version and count the rows:

-- broken version, without the cast() -- do NOT use this version
inner join {{ ref('dim_date') }} as dd
    on o.order_ts = dd.calendar_date
dbt run --select fact_orders
dbt show --inline "select count(*) as n from {{ ref('fact_orders') }}"

The result is 0 rows, with no ERROR, with no WARN. Why it happens: when comparing a timestamp (2026-08-03 08:14:00) against a date (2026-08-03), DuckDB converts the date to midnight (2026-08-03 00:00:00) to be able to compare them — and since no real Kiosko order happens at exactly midnight, the condition o.order_ts = dd.calendar_date is never true for any row. How to spot it: exactly as module 2 taught — PASS=1 proves nothing except that the SQL is syntactically valid; the row count is the only real check, and here it would have revealed the problem immediately: 0 rows where you expected 40. How to fix it: any JOIN comparing a timestamp column against a date column needs an explicit cast() on one side or the other — cast(o.order_ts as date), as this lesson's correct model does — so the comparison happens at the correct granularity level.

Using LEFT JOIN instead of INNER JOIN, "so as not to lose any order." What happens: someone, thinking a LEFT JOIN is "safer" because it never drops rows from the left table, changes both JOINs to LEFT JOIN. Why it happens: it's a reasonable intuition in the abstract — a LEFT JOIN does keep every row of stg_orders, even with no match — but it ignores what the JOIN is supposed to be validating. How to spot it: with LEFT JOIN, an order with an invalid store_id (one that doesn't exist in dim_store) would keep showing up in fact_orders, with the JOIN's result simply going unused — the referential-integrity check the INNER JOIN provides disappears completely, silently. How to fix it: for this specific model, where the two JOINs exist to validate (not to enrich with new columns), INNER JOIN is the correct choice — if a real Kiosko order ever had an invalid store_id or date, you want fact_orders to exclude it and the row count to flag it, not let it slip through silently with a broken reference.

Confusing SKIP with ERROR when a dependency fails before fact_orders. What happens: someone breaks stg_orders.sql on purpose (for example, with a syntax error) and runs dbt run over the complete project, expecting to see the error reported on fact_orders. Why it happens: since fact_orders is the model that "didn't work," it's natural to look for the problem there first. How to spot it: the real output shows ERROR on stg_orders (where the problem actually is) and SKIP on fact_orders — dbt never even tried to build it, because one of its dependencies failed first. How to fix it: when you see SKIP in a dbt run report, don't check that model — check upstream in its ref() chain, looking for the real ERROR; fact_orders showing SKIP is, in this case, the correct symptom of a problem somewhere else, exactly as you already saw in lesson 2.

Exercises

Exercise 1 — Break the dim_store JOIN on purpose. Temporarily change fact_orders.sql so the JOIN against dim_store compares o.store_id against a value that doesn't exist ('S99' instead of ds.store_id), run dbt run --select fact_orders, and confirm the resulting row count.

See solution 
-- broken version, temporary, for this exercise
inner join {{ ref('dim_store') }} as ds
    on 'S99' = ds.store_id

The result is 0 rows — no row of dim_store has store_id = 'S99' (the only three are S01, S02, S03), so the INNER JOIN finds no match for any order, no matter what its real store_id is. This is the same mechanism that would protect the model if a real order ever came in with a made-up store_id: the INNER JOIN would exclude it, and the row count (40 expected against whatever the actual result is) would give it away immediately. Undo the change before continuing.

Exercise 2 — Verify the complete reconciliation with dim_store and dim_date too. Extend the worked example's reconciliation script to include dim_store's count (expected: 3) and dim_date's count (expected: 31), in addition to orders/stg_orders/fact_orders.

See solution 
import duckdb

con = duckdb.connect("kiosko.duckdb")
checks = {
    "raw_orders": con.sql("select count(*) from read_csv_auto('raw_data/kiosko/orders_*.csv')").fetchone()[0],
    "stg_orders": 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": con.sql("select count(*) from fact_orders").fetchone()[0],
}
for k, v in checks.items():
    print(f"{k:<15}{v}")

What to expect.

raw_orders     40
stg_orders     40
dim_store      3
dim_date       31
fact_orders    40

Five numbers, each confirming a different piece of the chain: 40 raw orders, 40 in staging, 3 stores, 31 calendar days, and 40 order lines in the final mart — no unexpected number anywhere.

Exercise 3 — Argue why the two JOINs don't add columns to the result. In 2-3 sentences, explain why fact_orders.sql doesn't select store_name from dim_store or day_of_week from dim_date, even though it technically could, and what would happen to the model's purpose if it did.

See solution

fact_orders's grain is an order line, with exactly the columns data-modeling-for-analytics-guide already decided: order_id, store_id, product_id, quantity, unit_price, revenue, order_ts — adding store_name or day_of_week directly to the fact would break the star schema's separation between facts and dimensions, duplicating information that already lives, normalized, in dim_store and dim_date. Any query that needs store_name alongside an order's detail does the JOIN itself, at query time — using store_id as the key — instead of the fact carrying that information upfront; this model's two JOINs exist only to validate those keys are real, not to bring in extra columns.

Summary and next step

In this lesson you built fact_orders, Kiosko's star schema's central fact, joining stg_orders with dim_store and dim_date via ref() — three dependencies in a single model, resolved automatically by dbt in the correct order, with you never declaring it anywhere except inside the ref()s themselves. You confirmed 40 rows and 106.15 total revenue, the exact same numbers you already knew from earlier modules and guides, and you saw, with a real number, what happens when a cast() gets forgotten in a date JOIN: zero rows, with no error to warn you.

Before moving on you should be able to: explain why fact_orders's two INNER JOINs add no new column to the result; and describe, without running anything, in what order dbt would build the project's seven models if you ran dbt run over an empty database.

With all three marts built and verified, lesson 6 comes back to materializations — this time with decision criteria applied to each concrete model in the project — and updates dbt_project.yml so table becomes the new default, with staging explicitly pinned to view.

Resources

← PreviousRebuilding `dim_store` and `dim_date`Next →Choosing materializations for Kiosko