Course contents

The Analytics Engineer’s Field GuideCapstoneCase study: IsmaFrost pizzeria

Case study: IsmaFrost pizzeria

Capstone · end-to-end

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.

3 raw feeds
sources + freshness
→
Staging
clean + tested keys
→
The star
macro · snapshot · grain
→
Contract
Gina's promise
→
The loop
DuckDB · userland · CI · Airflow

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.yml
sources:
  - name: raw
    database: "{{ target.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_field: loaded_at
          freshness:
            warn_after: {count: 2, period: day}   # closed Mondays, so two days
      - name: menu_items
      - name: order_lines

2 · Staging: clean, rename, cast, nothing else

Applies: models & refs, data tests.

-- models/staging/stg_orders.sql, one row per order, tidied
select
    order_id,
    ordered_at,                     -- kept: the price join needs the exact time
    ordered_at::date                as order_date,
    lower(trim(channel))          as channel,
    table_number::int               as table_number,
    amount_cents::int               as amount_cents,
    discount_pct::float             as discount_pct
from {{ source('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_revenue_eur.sql
{% macro line_revenue_eur(quantity, price_cents, discount_pct) %}
    round(
        {{ quantity }} * {{ price_cents }} / 100.0
        * (1 - coalesce({{ discount_pct }}, 0)),
    2)
{% endmacro %}

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_menu_items.yml (dbt 1.9+)
snapshots:
  - name: snap_menu_items
    relation: source('raw', 'menu_items')
    config:
      schema: snapshots
      unique_key: item_id
      strategy: check      # the export has no updated_at, so compare columns
      check_cols: [price_cents, category, is_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 · SCD2
fct_order_lines1 row = 1 order linequantity, revenue, two keys
dim_orderdate, channel, table
"best sellers" = fct_order_lines × dim_menu_item · "how is the month going" = fct_order_lines × dim_order, by day
-- models/marts/fct_order_lines.sql
select
    {{ dbt_utils.generate_surrogate_key(['l.order_id', 'l.line_number']) }} as order_line_key,
    l.order_id,
    m.menu_item_key,                     -- the version of the dish valid that day
    l.quantity,
    {{ line_revenue_eur('l.quantity', 'm.price_cents', 'o.discount_pct') }} as revenue_eur
from {{ ref('stg_order_lines') }} l
join {{ ref('stg_orders') }} o on o.order_id = l.order_id
join {{ ref('dim_menu_item') }} m
  on  m.item_id = l.item_id
  and o.ordered_at >= m.dbt_valid_from
  and o.ordered_at <  coalesce(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.

Lesson from the guide
Where it showed up at IsmaFrost
Sources & staging
Three feeds declared, freshness on orders, 1:1 staging models
Modeling
A small star, grain declared, dim_menu_item from a snapshot
Tests
Keys, accepted categories, relationships on order lines, a unit test on revenue
Macros & packages
line_revenue_eur written once, surrogate keys from dbt_utils
Contracts & docs
fct_daily_sales enforced for Gina's dashboard, YAML feeding docs and dbt-score
Userland & DuckDB
Iterated free and local, promoted through userland, CI, then the schedule
· · ·

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.

The exposure
# models/staging/_models.yml, before: no test on the key
  - name: stg_order_lines
    columns:
      - name: item_id       # nothing checks it points to a real dish
The fix
# after: the relationships test from the tests lesson
  - name: stg_order_lines
    columns:
      - name: item_id
        data_tests:
          - relationships:
              arguments:
                to: ref('dim_menu_item')   # the dimension the mart joins
                field: item_id

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.

The guess it replaced
schedule="0 7 * * *"   # "7:00 to be safe", the undeclared dependency from part 3
The fix
from airflow.providers.amazon.aws.sensors.s3 import S3KeySensor

# DAG at 06:00; in Airflow 3 a cron run's ds is that same day
wait_for_till = S3KeySensor(
    task_id="wait_for_till",
    bucket_key="s3://raw/till_export_{{ ds }}.parquet",  # full URL: no bucket_name
    aws_conn_id="aws_raw",
    mode="reschedule", poke_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.

Without a contract
# the shape changes silently, the dashboard breaks tomorrow morning
  - name: fct_daily_sales
    columns:
      - name: revenue_total   # renamed, nobody downstream knows
With the contract from the contracts lesson
  - name: fct_daily_sales
    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(12,2)  # bare numeric is NUMBER(38,0) on Snowflake: cents lost

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.

The point of the exercise
Nothing here was clever. Every choice was the default this part taught, applied in order, on a problem small enough to see whole. That's what a good platform feels like day to day: boring decisions, compounding.

Lesson quiz

  1. Why does the pizzeria's revenue rule (quantity × price, minus discount) live in a macro with a unit test?

    1. Unit tests can only be attached to macros, never directly to the dbt models themselves
    2. It's the rule everyone must share: written once, its logic proven on fake rows
    3. Keeping it in a macro hides the rule from the lineage graph and docs site
    4. A macro makes Snowflake compute the revenue faster than inline SQL would
  2. The till sent order lines with item_id 4127, a dish the menu export didn't know yet. What stopped it reaching Gina's dashboard?

    1. The contract on fct_daily_sales, rejecting the unknown dish
    2. A relationships test on item_id, failing dbt build before the mart
    3. The sensor, waiting until the menu export had finished landing
    4. The unit test on revenue, flagging the missing price for the dish
  3. The case study's DAG starts at 06:00, and wait_for_till has mode="reschedule" and timeout=7200. Next week the till export lands at 09:30. What happens to that morning's run?

    1. The sensor fails at its two-hour timeout, and dbt never runs
    2. The sensor waits until 09:30, then the run builds as usual
    3. The sensor holds a worker slot until the file arrives
    4. dbt runs at 08:00 on yesterday's data, then again at 09:30