Module 5: Model it in SQL · Lesson 5.7 · about 40 min
Project 5: a tested star schema
Ship restaurant-dbt: a documented, tested Kimball star in dbt with SCD2, incremental facts and a conservation test, run by Dagster on two warehouses.
Module 5 project · about 40 minutes to read, a day to build · read first: Lessons 5.1 to 5.6. You’ll ship a dbt project that turns clean till files into a tested star schema, run it from Dagster on two warehouses, and present a short menu analysis a restaurant manager can act on.
1. What you are shipping
restaurant-dbt, for a made-up group of three quick-service brands (burgers, tacos, wings) with about 18 restaurants:
till files ─► Dagster: land, clean, load (Modules 1 to 4)
│
▼
dbt: stg_pos__order_lines ─► int_order_lines_deduped ─► fct_order_lines (incremental, look-back)
├─► fct_orders
├─► fct_restaurant_daily
└─► fct_menu_engineering
dim_date · dim_restaurant (is_comparable) · dim_menu_item (SCD2 snapshot) · dim_channel (seed)
│
▼
a dashboard and docs/analysis.md
All the data is synthetic, from the course generator in Module 1.
2. The proof that ties it together: conservation
A pipeline engineer’s star schema is trusted when revenue in equals revenue out, at every layer, for every business date. Encode that as a dbt test that returns the dates where it fails.
int_order_lines_deduped. The manifest records it.sum(line_revenue) in fct_order_lines for the date. Equal: pass. Different: the test returns the date and the gap, and the build stops before the dashboard.-- tests/assert_revenue_conserved.sql : returns the business dates where revenue went missing
with raw as (
select business_date, sum(line_revenue) as rev from {{ source('pos', 'raw_order_lines') }} group by 1
), removed as (
select business_date, sum(line_revenue) as rev from {{ ref('int_removed_lines') }} group by 1 -- duplicates + quarantine
), fct as (
select business_date, sum(line_revenue) as rev from {{ ref('fct_order_lines') }} group by 1
)
select r.business_date, r.rev - coalesce(x.rev, 0) as expected, f.rev as actual
from raw r
left join removed x using (business_date)
left join fct f using (business_date)
where abs(r.rev - coalesce(x.rev, 0) - coalesce(f.rev, 0)) > 0.005
Maths and principle
Principle: a model is trustworthy when its totals reconcile to the source at every layer.
Equation: for each business date , for revenue and for quantity,
Analogy: double-entry bookkeeping. Every dollar that leaves one place must arrive somewhere you can name.
Where it breaks: legitimate transformations such as currency conversion or netting refunds. Reconcile the measure before the transformation, then check the transformation on its own.
Try it: raw revenue $52,400; duplicates $310; quarantine $90. Expected fact revenue? ($52,000.)
- Conservation test
- A dbt test that returns the dates where revenue (or quantity) in does not equal revenue out.
- Grain sentence
- One line in each model's description saying what one row stands for and which rules apply.
- Version key
- The SCD2 surrogate key stored on each fact row, resolved at load time.
- Physical meaning
- A regional manager asks why the dashboard shows $200 less than the tills for one day. You open the test history: the conservation test caught it at 05:52, the build stopped, and the cause was a new menu item missing from the dimension. The dashboard never showed the wrong number.
“If dbt build is green, the numbers are right.” Green means every test you wrote passed. Without a conservation test, a transform can drop $200 a day and every unique and not-null test still passes. Test the totals, not only the keys.
3. Acceptance checks
| # | Requirement | Proof in the repo |
|---|---|---|
| 1 | Every mart model has a description with a grain sentence | dbt docs screenshot |
| 2 | Primary-key, relationships, accepted-values and conservation tests | dbt build output |
| 3 | SCD2 menu dimension; facts carry the version key | a test and a query |
| 4 | Incremental fact with a look-back; backfill by Dagster partitions | demo or recorded run |
| 5 | Runs on dbt-duckdb and dbt-sqlserver (Azure SQL) |
CI log or screenshot |
| 6 | docs/metrics.md with 20 metrics |
file |
| 7 | One-page menu engineering analysis by brand, with recommendations | docs/analysis.md |
| 8 | Lineage graph from the source files to the report | screenshot |
4. Build checklist
- Combine. Put the Dagster project (Modules 3 and 4) and the dbt project in one repo, or two folders in one monorepo.
- One command per target.
just build-local(DuckDB) andjust build-azure(Azure SQL), or the same withmake. - Tests. The conservation test above, plus the SCD2 tiling tests from Lesson 5.2.
- Backfill. Change one rule, backfill 60 days through Dagster partitions, and record the runs, concurrency and minutes.
- Analysis.
docs/analysis.md: menu engineering per brand, top movers and same-store sales, with one chart per point and a recommendation per brand. - README, in this order: a summary with the lineage screenshot, the problem, the star diagram, the grain sentences, the tests, how to run it, design decisions (SCD2, incremental strategy, business day), next steps.
Your story (fill the brackets from your own work): “I modelled a three-brand restaurant group as a Kimball star in dbt, with the business day ending at 05:00, an SCD2 menu dimension so price changes do not rewrite history, and a conservation test that proves revenue in equals revenue out on every build. Dagster runs dbt per business date, so a 60-day backfill is one click and took [N] minutes. The menu-engineering model flagged [item] as a plowhorse; a [x] % price change would add about $[y] a month at current volume.”
What done looks like: a stranger clones the repo, runs one command, gets a green dbt build on DuckDB, and can read in the README what each table means and how it is tested. Every acceptance line has its proof. Commit the final state as feat: project 5 restaurant-dbt.
- The project proves trust, not just SQL: grain sentences, SCD2, incremental loads and a conservation test on every build.
- Raw minus duplicates minus quarantine must equal the fact total for every date; encode it as a test that returns failing dates.
- Two warehouses from one project show that business logic lives in dbt, not in one vendor’s dialect.
- A short analysis with recommendations turns a technical project into something a manager values.
Check yourself
Sources
- Kimball, R. and Ross, M. (2013), The Data Warehouse Toolkit, 3rd edition, Wiley
- dbt documentation: data tests, documentation and incremental models https://docs.getdbt.com/
- Dagster documentation: dbt integration https://docs.dagster.io/
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.