Staging and Marts

As a dbt project grows, a data team sorts its models into two layers. A staging model cleans one raw table: it fixes types and names, and nothing more. A mart answers a business question, such as revenue per day. It reads staging models only, through ref, never the raw tables.

Why: every cleaning rule lives in one place. stg_customers trims and lowercases the city once, and every mart that groups by city gets the same clean value. The layers are two folders, models/staging/ and models/marts/. dbt finds models in every folder under models/ and still names each one after its file, so ref('stg_orders') doesn't change.

The mart daily_revenue is already there as an example: it answers "how much did we earn each day?" from stg_orders alone. The given files:

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 raw_orders

models/staging/stg_customers.sql

SELECT
    customer_id,
    LOWER(TRIM(city)) AS city,
    CAST(joined AS DATE) AS joined_on
FROM raw_customers

models/marts/daily_revenue.sql

SELECT
    CAST(ordered_at AS DATE) AS day,
    SUM(quantity * unit_price) AS revenue
FROM {{ ref('stg_orders') }}
WHERE status = 'paid'
GROUP BY day

You edit models/marts/city_revenue.sql, a second mart: revenue per customer city. It starts with the columns c.city and revenue. Then dbt build must print:

model stg_customers (view): OK
model stg_orders (view): OK
model city_revenue (view): OK
model daily_revenue (view): OK
city_revenue (view), 7 rows:
city | revenue
berlin | 452
cluj | 537
lyon | 117
madrid | 552
porto | 247
rome | 355
NULL | 22

At work you type dbt build in a terminal, and both layers live in a warehouse such as Snowflake or BigQuery. Here the grader builds them into the DuckDB file shop.duckdb.

Your task: in models/marts/city_revenue.sql, read stg_orders as o and join stg_customers as c on customer_id, both through ref. Keep only the paid orders and group the rows by the customer's city.

Assignment
Follow the Coding Tutorial to build the mart city_revenue from the two staging models.

Hint
ref works in a JOIN just as in a FROM, and the alias comes after the closing }}. A mart never reads raw_orders or raw_customers.