Pipeline to Dashboardnihalsulaiman.com

Week 5 · Model it in SQL › Day 35

Day 35. Proof 5: restaurant-dbt, a tested star schema

⏱ 4h · build 150m · README 60m · review 30m🏷 multi-brand restaurants (BurgerBay, TacoTrail, WingWorks)Before: Days 29 to 34

By the end of today you can

  • Ship a dbt project with a documented star schema, SCD2, tests and metrics
  • Run it from Dagster on DuckDB and Azure SQL
  • Present a menu engineering and same-store sales analysis

1. What you're shipping

restaurant-dbt: Dagster ingests and cleans POS files → dbt builds fct_order_lines, fct_orders, fct_restaurant_daily, fct_menu_engineering, dim_date, dim_restaurant, dim_menu_item (SCD2), dim_channel → a dashboard and a written analysis.

Acceptance criteria

# Requirement Proof
1 Every mart model has a description with a grain sentence dbt docs screenshot
2 PK, relationships, accepted values, and conservation tests dbt build output
3 SCD2 menu dimension; facts carry the version key test + query
4 Incremental fact with look-back; backfill via Dagster partitions demo
5 Runs on dbt-duckdb and dbt-sqlserver (Azure SQL) CI or screenshot
6 docs/metrics.md with 20 metrics file
7 Analysis: 1-page "menu engineering by brand" with recommendations docs/analysis.md
8 Lineage graph from source files to the report screenshot
Maths and principle

Principle: a model is trustworthy when its totals reconcile to the source at every layer.

Equation: for each business date \(d\): \(\sum \text{raw}_d - \text{dups}_d - \text{quarantine}_d = \sum \text{stg}_d = \sum \text{fct}_d\) for revenue and quantity. Encode it as a singular dbt test that returns the dates where it fails.

Analogy: double-entry bookkeeping: every amount appears on both sides. Where it breaks: legitimate transformations (currency conversion, refunds netting). Reconcile the pre-transformation measure, then the transformation separately.

Try it: raw revenue $52,400; duplicates $310; quarantine $90. Expected fact revenue? ($52,000.)

Your story

"I modelled a three-brand restaurant group as a Kimball star in dbt, with the business day ending at 5 a.m., an SCD2 menu dimension so price changes don't rewrite history, and a reconciliation 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. The menu-engineering model flagged [item] as a plowhorse; a [x]% price change would add about $[y] a month at current volume." Fill the brackets from your own analysis.

Build checklist

  1. Combine Weeks 3–5 code: Dagster project + dbt project in one repo (or a monorepo with two folders).
  2. just build-local (DuckDB) and just build-azure targets.
  3. Analysis doc: menu engineering per brand, top movers, same-store sales; one chart per point.
  4. README: summary + lineage screenshot, problem, model diagram (star), grain statements, tests, how to run, decisions (SCD2, incremental strategy, business day), next steps.

Check yourself

1. Raw $80,000; duplicates $500; quarantine $200; fact shows $79,100. What do you conclude?

2. What belongs in every mart model's description?

My notes