dbt Marts: The Panel as a Model

7.1 Layers, ref() and dbt's own DAG (40 min)

Concept. Models refer to each other with {{ ref('model_name') }}. From these references dbt draws a graph of dependencies and runs models in the right order. dbt calls this graph a DAG, a directed acyclic graph: the same mathematical object as the causal DAGs of Module 2. Here arrows mean "is built from"; there they will mean "causes". In both, a cycle would be nonsense.

Today's graph:

raw.customers ─────────► stg_customers ──────────────┐
raw.orders ────────────► stg_orders ──► int_customer_weekly_orders ──┤
raw.campaign_recipients ► stg_campaign_recipients ───┼──► panel_customer_week
                          int_weeks (calendar) ──────┘

dbt's --select syntax follows the graph: +panel_customer_week means "the panel and everything upstream", stg_orders+ means "stg_orders and everything downstream".

dbt ls --profiles-dir . --select +panel_customer_week --resource-type model --resource-type source

northmart.intermediate.int_customer_weekly_orders
northmart.intermediate.int_weeks
northmart.marts.panel_customer_week
northmart.staging.stg_campaign_recipients
northmart.staging.stg_customers
northmart.staging.stg_orders
source:northmart.raw.campaign_recipients
source:northmart.raw.customers
source:northmart.raw.orders

stg_products isn't listed: the panel doesn't use it. Without --resource-type, the list also includes every test attached to these models.

7.2 Variables and the calendar spine model (50 min)

Concept. The email date, the first week and the campaign ID are study design choices. Hard-coding them inside SQL hides them. dbt variables (vars: in dbt_project.yml) put them in one visible place, and {{ var("campaign_week") }} reads them. Changing the analysis window becomes a one-line, reviewable change.

Example 1 — the calendar. transform/models/intermediate/int_weeks.sql:

-- The calendar spine: one row per Monday, numbered 1..16.
select
    cast(week_start as date)                          as week_start,
    row_number() over (order by week_start)           as week_no
from generate_series(
    date '{{ var("first_week") }}',
    date '{{ var("last_week") }}',
    interval 7 day
) as g(week_start)

Example 2 — weekly outcomes where orders exist. transform/models/intermediate/int_customer_weekly_orders.sql:

-- Outcomes per customer-week, only where at least one order exists.
select
    customer_id,
    cast(date_trunc('week', order_date) as date)      as week_start,
    count(distinct order_id)                          as n_orders,
    sum(revenue)                                      as revenue
from {{ ref('stg_orders') }}
group by all

group by all groups by every column that isn't aggregated: a DuckDB convenience that saves retyping.

dbt build --profiles-dir . --select intermediate
dbt show --profiles-dir . --select int_weeks --limit 16

Variables can be overridden at run time without editing files, for example to rebuild with a shorter window:

dbt show --profiles-dir . --select int_weeks --vars '{"last_week": "2026-02-23"}'

7.3 The customer × week panel as a dbt model (60 min)

Concept. The panel is Day 3's SQL, reorganised: each WITH step became either its own model (int_weeks, int_customer_weekly_orders) or a short CTE, every table name became a ref(), and every design choice became a var(). dbt writes the CREATE TABLE; the file contains only the SELECT.

Create transform/models/marts/panel_customer_week.sql:

-- The Module 1 deliverable: one row per customer per week, ready for causal analysis.
-- Grain: customer_id x week_start. Treatment: the {{ var("campaign_id") }} email.

with spine as (
    select c.customer_id, w.week_start, w.week_no
    from {{ ref('stg_customers') }} as c
    cross join {{ ref('int_weeks') }} as w
),

filled as (
    select
        s.*,
        coalesce(o.n_orders, 0)  as n_orders,
        coalesce(o.revenue, 0)   as revenue
    from spine as s
    left join {{ ref('int_customer_weekly_orders') }} as o using (customer_id, week_start)
),

recipients as (
    select customer_id, sent_date
    from {{ ref('stg_campaign_recipients') }}
    where campaign_id = '{{ var("campaign_id") }}'
)

select
    f.customer_id,
    f.week_start,
    f.week_no,
    f.week_no - {{ var("campaign_week") }}                         as event_week,
    cast(r.customer_id is not null as integer)                     as treated,
    cast(f.week_no >= {{ var("campaign_week") }} as integer)       as post,
    cast(r.customer_id is not null
         and f.week_start >= r.sent_date as integer)               as treated_post,
    f.n_orders,
    round(f.revenue, 2)                                            as revenue,
    c.region,
    c.age,
    cast(c.is_loyalty as integer)                                  as is_loyalty,
    sum(f.n_orders) filter (where f.week_no < {{ var("campaign_week") }})
        over (partition by f.customer_id)                          as pre_orders,
    round(sum(f.revenue) filter (where f.week_no < {{ var("campaign_week") }})
        over (partition by f.customer_id), 2)                      as pre_revenue
from filled as f
join {{ ref('stg_customers') }} as c using (customer_id)
left join recipients as r using (customer_id)

Note that pre_orders uses week_no < campaign_week, so if the campaign date variable changes, the pre-period follows it automatically. On Day 3 the "8" was typed by hand.

Because marts is configured as +materialized: table, the panel is stored, not recomputed on every query: important for a table read by every estimation in Modules 2–8.

7.4 Custom tests: generic and singular (45 min)

Concept. The four built-in tests can't express everything. dbt offers two ways to write your own:

• A generic test is a reusable macro with parameters, applied in YAML like unique.
• A singular test is a one-off .sql file in tests/ that returns the bad rows.

Example 1 — a generic test. dbt's unique checks one column; a panel's key is two columns. Create transform/tests/generic/unique_combination.sql:

-- A reusable test: no two rows share the same combination of columns.
{% test unique_combination(model, columns) %}
select {{ columns | join(', ') }}, count(*) as n
from {{ model }}
group by {{ columns | join(', ') }}
having count(*) > 1
{% endtest %}

Example 2 — the panel's YAML. transform/models/marts/_marts.yml applies it, plus column tests:

version: 2

models:
  - name: panel_customer_week
    description: "One row per customer per week. Outcomes are 0 (not NULL) in weeks without orders."
    data_tests:
      - unique_combination:
          arguments:
            columns: ["customer_id", "week_start"]
    columns:
      - name: customer_id
        data_tests: [not_null]
      - name: treated
        data_tests:
          - accepted_values:
              arguments:
                values: [0, 1]
                quote: false
      - name: post
        data_tests:
          - accepted_values:
              arguments:
                values: [0, 1]
                quote: false
      - name: n_orders
        data_tests: [not_null]
      - name: revenue
        data_tests: [not_null]
      - name: pre_orders
        description: "Orders in weeks 1-8. Fixed per customer: a pre-treatment covariate."
        data_tests: [not_null]

Example 3 — singular tests. These are Day 3's Python checks, now part of the build. Create three files in transform/tests/:

-- Every customer must appear in every week. Returns rows (= failure) if the count is off.
select count(*) as panel_rows
from {{ ref('panel_customer_week') }}
having count(*) != (select count(*) from {{ ref('stg_customers') }})
                  * (select count(*) from {{ ref('int_weeks') }})

-- Panel revenue must equal order revenue (to the cent, allowing for rounding).
select
    (select sum(revenue) from {{ ref('panel_customer_week') }}) as panel_revenue,
    (select sum(revenue) from {{ ref('stg_orders') }})          as order_revenue
where abs(panel_revenue - order_revenue) >= 1

-- Causal validity: nobody can be "treated" before the email was sent.
select customer_id, week_start
from {{ ref('panel_customer_week') }}
where treated_post = 1 and post = 0

Build everything:

dbt build --profiles-dir .

Done. PASS=30 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=30

7 models and 23 tests. If a staging test fails, dbt skips the panel rather than building it on bad data.

Try it. Day 3 checked that pre_orders is constant within each customer. Write that as a singular test, tests/assert_pre_orders_fixed.sql.

-- tests/assert_pre_orders_fixed.sql
-- A pre-treatment covariate cannot change over time within a customer.
select customer_id
from {{ ref('panel_customer_week') }}
group by customer_id
having count(distinct pre_orders) > 1

7.5 Day 7 mini-project: prove the two pipelines agree (55 min)

Goal. We now have two independent routes to the panel:

If both are right, they must produce the same 64,000 rows to the cent. Write day07/compare_panels.py to prove it.

Day 3's build_panel.py and dbt both write a table called marts.panel_customer_week, and dbt has just replaced Day 3's version. That's why we compare against the Parquet file Day 3 exported.

# day07/compare_panels.py
"""Mini-project check: the dbt panel must match the Day 3 panel exactly."""
import duckdb
import polars as pl

KEY = ["customer_id", "week_start"]

con = duckdb.connect("data/northmart.duckdb", read_only=True)
dbt_panel = con.sql("SELECT * FROM marts.panel_customer_week").pl().sort(KEY)
con.close()
day3_panel = pl.read_parquet("data/marts/panel_customer_week.parquet").sort(KEY)

print("rows    dbt:", dbt_panel.height, "| Day 3:", day3_panel.height)
print("columns match:", dbt_panel.columns == day3_panel.columns)

# Compare column by column, ignoring integer width (INT32 vs INT64)
problems = []
for col in day3_panel.columns:
    a, b = dbt_panel[col], day3_panel[col]
    if a.dtype.is_numeric():
        same = ((a.cast(pl.Float64) - b.cast(pl.Float64)).abs() < 1e-6).all()
    else:
        same = (a == b).all()
    if not same:
        problems.append(col)

print("columns that differ:", problems or "none")
assert not problems, "The dbt panel does not reproduce Day 3. Investigate before moving on."
print("The dbt model reproduces the Day 3 panel exactly.")

cd ..
python day07/compare_panels.py

rows    dbt: 64000 | Day 3: 64000
columns match: True
columns that differ: none
The dbt model reproduces the Day 3 panel exactly.

This is the strongest test in the module. Two pipelines written in different tools, from differently shaped data, agree on every cell. From now on, the dbt version is the official panel.

Extension — a balance table. Add a mart models/marts/campaign_balance.sql that compares treated and control customers before the email: number of customers, average pre-period orders and revenue, loyalty share and age. (Hint: filter to week_no = 1 to get one row per customer.)

-- models/marts/campaign_balance.sql
-- Were emailed and non-emailed customers comparable BEFORE the email?
select
    treated,
    count(*)                         as customers,
    round(avg(pre_orders), 2)        as pre_orders,
    round(avg(pre_revenue), 0)       as pre_revenue,
    round(avg(is_loyalty), 3)        as loyalty_share,
    round(avg(age), 1)               as age
from {{ ref('panel_customer_week') }}
where week_no = 1                    -- one row per customer
group by treated
order by treated

dbt build --profiles-dir . --select campaign_balance
dbt show --profiles-dir . --select campaign_balance

| treated | customers | pre_orders | pre_revenue | loyalty_share |  age |
| ------- | --------- | ---------- | ----------- | ------------- | ---- |
|       0 |      2721 |       1.85 |         288 |         0.208 | 46.7 |
|       1 |      1279 |       3.23 |         506 |         0.491 | 47.3 |

7.6 Documentation, a full rebuild, and Git (20 min)

Documentation. dbt builds a website from your YAML descriptions and the graph:

cd transform
dbt docs generate --profiles-dir .
dbt docs serve --profiles-dir . --port 8080

Open port 8080 when Codespaces offers it. Click panel_customer_week, then the graph icon (bottom right) to see the lineage from raw to the panel. Stop the server with Ctrl+C.

Full rebuild from scratch. With the API running in the other terminal:

cd ..
rm -rf data/northmart.duckdb .dlt_pipelines
python api/clock.py 2026-04-26
python day05/northmart_pipeline.py
cd transform && dbt build --profiles-dir . && cd ..
python day07/compare_panels.py

Four commands in the right order still need a human who remembers them. Day 9 hands that job to an orchestrator.

git add .
git commit -m "Day 7: dbt marts, custom tests, panel reproduces Day 3"
git push

Rebuilding the Day 3 comparison file. compare_panels.py needs data/marts/panel_customer_week.parquet. If you deleted it, rerun Day 2's clean.py and Day 3's build_warehouse.py, calendar_spine.py and build_panel.py first.

Coming next (Days 8–10). Day 8 turns the causal checks into data contracts that fail loudly on leakage and treatment-timing errors. Day 9 orchestrates the whole pipeline with Prefect. Day 10 is the Module 1 capstone: the Causal Data Mart.

Day 7 checklist

• I can explain how ref() builds dbt's dependency graph and use +model selection
• I keep study design choices (dates, campaign) in dbt variables
• I can build the customer × week panel as a layered dbt project
• I can write generic and singular tests, including causal-validity checks
• I have proved that the dbt panel reproduces the Day 3 panel exactly