End-to-end case study: a pizzeria's warehouse
Everything from this guide, once, in order, on something simple: IsmaFrost wants to know what sells and whether the month is going well.
IsmaFrost is a pizzeria. One room, a wood oven, a pasta station, and Gina, who owns it. The brief could not be smaller: three raw feeds and two questions, how is the month going, and which dishes actually sell. Small enough to hold in your head, complete enough that most of the guide shows up somewhere.
The whole build, at a glance
Six steps, each built from lessons you've already done. The rest of this page just walks the arrow.
1 · The sources: three raw feeds
Applies: sources & freshness, from the setup and models lessons.
Every night the till drops an export in S3, and a COPY INTO loads three tables into RAW_DB: orders (one row per order: date, dine-in or delivery, table number, total), menu_items (one row per dish: name, category, current price, vegetarian or not), and order_lines (one row per line on the ticket: which order, line number, which item, how many). Declared once, with a freshness check on orders, because a till that quietly stops exporting is exactly the silent failure freshness catches:
# models/staging/ sources: - name: raw database: "{{ target._ sources. yml database if target.type == 'duckdb' else 'RAW_DB' }}" # step 15 tables: - name: orders config: # dbt 1.10 layout, as in the data tests lesson loaded_at_ freshness: warn_after: {count: 2, period: day} # closed Mondays, so two days - name: menu_items - name: order_linesfield: loaded_at
2 · Staging: clean, rename, cast, nothing else
Applies: models & refs, data tests.
-- models/staging/ select order_id, ordered_at, -- kept: the price join needs the exact time ordered_stg_ orders. sql, one row per order, tidied at: lower(trim(channel)) as channel, table_: date as order_date, number: amount_: int as table_ number, cents: discount_: int as amount_ cents, pct: from {{ source(: float as discount_pct 'raw', 'orders') }}
stg_menu_items and stg_order_lines follow the same one-to-one pattern. Tests go on immediately, not later: order_id unique and not_null, category constrained to the real values with accepted_values (pizza, pasta, dessert, drink), and channel to dine_in or delivery. Nothing here joins anything.
3 · One macro, the pizzeria's only business rule
Applies: macros, unit tests.
Line revenue is the rule everyone must agree on: quantity times price, minus the discount, in euros not cents. That's a macro, written once, so no model ever re-invents it slightly differently:
-- macros/line_ {% macro line_revenue_ eur. sql revenue_ round( {{ quantity }} * {{ price_cents }} / 100.0 * (1 - coalesce({{ discount_pct }}, 0)), 2) {% endmacro %}eur( quantity, price_cents, discount_ pct) %}
And because revenue is pure logic, the model that calls it (fct_order_lines, below) gets a unit test: given two margheritas at 900 cents with a 10% discount it must produce 16.20, given one carbonara at 1200 with no discount exactly 12.00. It runs on fake rows, locally, in milliseconds, and the day someone "simplifies" the rounding it fails before the merge.
4 · The star: two dimensions, one fact
Applies: modeling (facts, dimensions, snapshots), packages.
The marts layer is a small star, grain declared before anything else. fct_order_lines: one row per order line, with its quantity. dim_order: one row per order. dim_menu_item: one row per version of a dish, the Type 2 pattern from the modeling lesson, because last month's revenue must keep last month's price. The till export only holds today's menu, so the history comes from a snapshot:
# snapshots/snap_ snapshots: - name: snap_menu_ items. yml (dbt 1.9+) menu_ relation: source(items 'raw', 'menu_ config: schema: snapshots unique_key: item_id strategy: check # the export has no updated_at, so compare columns check_cols: [price_cents, category, is_items') vegetarian ]
dim_menu_item is a thin model on ref('snap_menu_items'): it adds a key per version and backdates each dish's first dbt_valid_from so orders older than the first snapshot still find a price.
dim_menu_itemfrom a snapshot · SCD2fct_order_lines1 row = 1 order linequantity, revenue, two keysdim_orderdate, channel, table-- models/marts/ select {{ dbt_fct_ order_ lines. sql utils. as order_generate_ surrogate_ key( ['l. order_ id', 'l. line_ number']) }} line_ l.order_id, m.key, menu_ l.quantity, {{ line_item_ key, -- the version of the dish valid that day revenue_ as revenue_eur from {{ ref(eur( 'l. quantity', 'm. price_ cents', 'o. discount_ pct') }} 'stg_ l join {{ ref(order_ lines') }} 'stg_ o on o.order_id = l.order_id join {{ ref(orders') }} 'dim_ m on m.item_id = l.item_id and o.ordered_at >= m.menu_ item') }} dbt_ and o.ordered_at < coalesce(valid_ from m. dbt_ valid_ to, '9999-12-31'::timestamp)
The date range in that last join is the point of the snapshot: a March order gets March's price, matched on the exact ordered_at so an order placed hours after a midday price change gets the new price. The grain gets a unique test on the surrogate key, so a line exported twice fails the build. Best sellers are now a join and a sum(quantity). The month question is fct_daily_sales: sum(revenue_eur) by day and channel.
5 · Locking it in: contract, docs, score
Applies: contracts, docs & lineage, dbt-score.
Gina's dashboard reads fct_daily_sales, so fct_daily_sales becomes a promise: contract: enforced with every column typed, and dropping revenue_eur now fails the build in the PR rather than blanking the dashboard on Saturday night. Descriptions go into the YAML as the models are written, which means the docs site and lineage graph exist for free, and dbt-score has everything it needs to keep the next contributor honest.
6 · The loop that built all of this
Applies: DuckDB, userland, CI, and parts 3 and 4.
None of it touched Snowflake until the end. The iteration ran on DuckDB: three tiny CSVs injected locally, dbt run --target local in seconds, the unit test proving the revenue logic, data tests proving the keys. Then userland on real data, then the merge request, CI stage one on DuckDB again, stage two on Snowflake, and Airflow takes the schedule from there, exactly as part 3 taught, with Snowflake billing only for the work, as part 4 demanded.
The week it broke, three times
A pipeline that never fails is a pipeline nobody's watching. Here's what actually went wrong, with the code, and why none of it reached Gina.
Bug 1 · Tuesday: order lines with no dish
The till sent a line with item_id = 4127, a new calzone the kitchen had added that morning and the menu export didn't know about yet. Joined to the menu, that line would silently drop out of revenue.
# models/staging/ - name: stg__ models. yml, before: no test on the key order_ columns: - name: item_id # nothing checks it points to a real dishlines
# after: the relationships test from the tests lesson - name: stg_order_ columns: - name: item_id data_tests: - relationships: arguments: to: ref(lines 'dim_ # the dimension the mart joins field: item_idmenu_ item')
dbt build stopped before the mart, Airflow's callback paged the channel, Alice found the till had been updated before the menu export. One upstream ticket, zero wrong dashboards.
Bug 2 · Thursday: the till export was late
The nightly export usually lands at 05:40. That day: 07:15. Even the "safe" 07:00 schedule would have rebuilt everything on yesterday's data and called it success.
schedule="0 7 * * *" # "7:00 to be safe", the undeclared dependency from part 3from airflow.providers. # DAG at 06:00; in Airflow 3 a cron run's ds is that same day wait_amazon. aws. sensors. s3 import S3KeySensor for_ task_id="wait_till = S3KeySensor( for_ , bucket_key="s3:till" / , # full URL: no bucket_name aws_conn_id="aws_raw", mode="reschedule", poke_/ raw/ till_ export_ {{ ds }}.parquet" interval=300, timeout=7200, ) wait_for_ till >> load_raw >> dbt_build # load_raw: the COPY INTO
The sensor just kept waiting, no failure, no build on stale data, and the run finished 95 minutes later than usual but correct. Nobody was paged, because lateness within the timeout isn't an incident.
Bug 3 · Friday: CI blocked a rename
A well-meaning refactor renamed revenue_eur to revenue_total in the SQL of fct_daily_sales, the exact column Gina's dashboard reads.
# the shape changes silently, the dashboard breaks tomorrow morning - name: fct_daily_ columns: - name: revenue_sales total # renamed, nobody downstream knows
- name: fct_daily_ config: contract: {enforced: true} # any rename breaks the build, in CI columns: # every column is listed and typed, only one shown - name: revenue_eur data_type: numeric(sales 12, # bare numeric is NUMBER(38,0) on Snowflake: cents lost2)
The SQL no longer returned the revenue_eur the contract promised, so the pre-build contract check failed in CI, the merge was blocked, and the dashboard never knew. The real fix followed the contracts playbook: publish a v2, migrate the dashboard, retire v1.
What this build demonstrates
Test your assumptions
A relationships test on item_id caught order lines with no dish before they hit production.
Wait for data, not for time
A sensor replaced the "run at 7:00 to be safe" guess, and lateness stopped being an incident.
Enforce the shape
A contract on fct_daily_sales caught the rename in CI, on the right side of production.
The loop works
Three failures, zero damage. Not because nothing breaks, but because everything breaks loudly, early, and before Gina opens the till.
Keep improving: the four tracks
A self-check per module. If a sentence doesn't feel true yet, its lesson is one click away in the sidebar.
dbt
You're ready when you can declare a fact's grain before writing it, choose Type 1 or Type 2 for a dimension and build the Type 2 one from a snapshot, and put a business rule in a macro with a unit test without being asked.
Airflow
You're ready when a sensor replaces every "run at 7:00 to be safe", retries absorb the blips while callbacks page only on final failure, and Cosmos keeps your DAG in sync with the dbt graph for free.
Snowflake
You're ready when you read a Query Profile before resizing anything, know when QUALIFY beats DISTINCT and what a secure view costs, and every warehouse you own has an auto-suspend set from its workload's query gaps and a resource monitor.
Way of working
You're ready when nothing reaches a shared environment before DuckDB and the userland have both said yes, docs are written as the model is, and your defaults are boring on purpose.

