Project: The Shop in dbt

This lesson puts the whole shop into one dbt project. Most of it is given: a source that says where the raw tables live, two staging models that each clean one source, and the tests that check their rows. You write the last piece, the mart in models/marts/daily_revenue.sql. It answers two questions: how much money did the shop make each day, and how much of it came from customers who get the newsletter? The file starts with the day and orders columns and GROUP BY day, but it has no FROM, so dbt build stops on it: Binder Error: … the FROM clause is missing.

Here are the pieces again. A model is one SELECT in a file, and dbt makes a view or a table named after the file. {{ ref('stg_orders') }} reads another model: dbt fills in its real table name, then builds that model and runs its tests before yours. The materialization, set with {{ config(materialized='…') }}, picks what dbt makes. A view (the default) runs its SELECT each time someone reads it. A table stores the rows at build time. One dbt build builds every model in the order the refs give, runs each model's tests right after it, and skips anything built on top of a failure. Data teams work this way because dbt works out the order, the table names and the checks from the files, so nobody has to run SQL scripts by hand in the right order. At work you type dbt build in a terminal, and the models go into a warehouse such as Snowflake or BigQuery, not the DuckDB file shop.duckdb.

You can't edit the given files. The source points at the schema raw, where the loader left raw_orders and raw_customers with every column as text. stg_orders gives the columns their types (the quantity "two" becomes NULL). It also keeps one row per order_id, the one from the latest file, so the extra copies of orders 5027 and 5040 are gone and order 5062 is cancelled, as the 05-07 file says. stg_customers turns newsletter into true or false. schema.yml tests both staging models and the day column of your mart.

models/sources.yml

version: 2

sources:
  - name: shop
    schema: raw
    tables:
      - name: raw_orders
      - name: raw_customers

models/staging/stg_orders.sql

SELECT
    order_id,
    customer_id,
    product,
    TRY_CAST(quantity AS INTEGER) AS quantity,
    CAST(unit_price AS INTEGER) AS unit_price,
    status,
    CAST(ordered_at AS TIMESTAMP) AS ordered_at
FROM {{ source('shop', 'raw_orders') }}
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY filename DESC) = 1

models/staging/stg_customers.sql

SELECT
    customer_id,
    LOWER(TRIM(city)) AS city,
    CAST(joined AS DATE) AS joined_on,
    newsletter = 'yes' AS newsletter
FROM {{ source('shop', 'raw_customers') }}

models/schema.yml

version: 2

models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: status
        tests:
          - accepted_values: {values: ['paid', 'cancelled']}
      - name: customer_id
        tests:
          - relationships: {to: ref('stg_customers'), field: customer_id}
  - name: stg_customers
    columns:
      - name: customer_id
        tests: [unique, not_null]
  - name: daily_revenue
    columns:
      - name: day
        tests: [unique, not_null]

Here is a mart that answers a different question: the units sold and the cancelled orders for each product. It reads the staging model through ref, and a CASE inside SUM adds 1 only for the rows that match:

{{ config(materialized='table') }}

SELECT
    product,
    SUM(quantity) AS units,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM {{ ref('stg_orders') }}
GROUP BY product

A model can also ref two models and join them like any two tables, and dbt builds both of them first. A LEFT JOIN keeps every row of the left table, even one with no match; the other table's columns are NULL in that row. When your mart is right, the check runs dbt build and prints:

model stg_customers (view): OK
model stg_orders (view): OK
test stg_customers.customer_id not_null: PASS
test stg_customers.customer_id unique: PASS
test stg_orders.status accepted_values: PASS
test stg_orders.order_id not_null: PASS
test stg_orders.customer_id relationships: PASS
test stg_orders.order_id unique: PASS
model daily_revenue (table): OK
test daily_revenue.day not_null: PASS
test daily_revenue.day unique: PASS
daily_revenue (table), 7 rows:
day | orders | revenue | newsletter_revenue
2026-05-01 | 10 | 274 | 138
2026-05-02 | 11 | 384 | 203
2026-05-03 | 9 | 245 | 195
2026-05-04 | 6 | 122 | 50
2026-05-05 | 12 | 436 | 130
2026-05-06 | 7 | 170 | 38
2026-05-07 | 9 | 595 | 520

Your task: in models/marts/daily_revenue.sql, read stg_orders through ref (named o) and keep the paid orders. Add the revenue column. LEFT JOIN stg_customers through ref (named c) on customer_id, and add newsletter_revenue. Last, make the mart a table.

Assignment
Follow the Coding Tutorial to finish the mart models/marts/daily_revenue.sql: its FROM and its LEFT JOIN with ref, the WHERE that keeps paid orders, the revenue and newsletter_revenue columns, and the table config.

Hint
A mart reads only staging models, through ref. That is how dbt knows to build stg_orders and stg_customers first. Use a LEFT JOIN so an order without a customer still counts, and give the CASE an ELSE 0.