Modular Modeling

Time	Block
0:00 – 0:23	34.1 The rules of each layer
0:23 – 0:50	34.2 Staging: one model per source table
0:50 – 0:58	Break
0:58 – 1:20	34.3 Intermediate: organised by economic concept
1:20 – 1:43	34.4 Marts: facts, dimensions and one-big-tables
1:43 – 1:50	Break
1:50 – 2:08	34.5 DAG hygiene: rules that keep a project healthy
2:08 – 2:30	34.6 Mini-project: restructure macro_dbt

---

34.1 The Rules of Each Layer (23 min)

Modular modeling means breaking transformations into small, single-purpose models, each in the layer where it belongs. You already met the layers in Module 2. Here are the rules the dbt community uses, adapted to our project:

Layer	Purpose	May do	May not do	Materialisation
staging stg_<source>__<entity>	Clean, faithful copy of one source table	rename, cast, standardise units, light filtering of junk	join other sources, aggregate, apply economics	view
intermediate int_<concept>__<what>	Reusable building blocks	join, aggregate, reshape, compute growth rates	read source() directly; serve dashboards	view (table if heavy)
marts fct_…, dim_…, panels	Finished products for consumers	combine intermediates, final naming	contain logic other marts need to repeat	table

Three principles lie behind the rules:

1. Single responsibility. A model does one thing. If you describe it with "and", consider splitting it.
2. Staging is the only door to raw data. Only staging models call source(). When dlt renames a column, you fix one file.
3. Downstream reuse. If two marts need the same logic, it belongs upstream in an intermediate model (or a macro, Day 35), not copied into both.

Economist's analogy: staging is data cleaning, intermediate is constructing variables, and marts are the estimation samples handed to different projects. You wouldn't compute inflation separately inside every regression script. You construct it once and merge it in.

---

34.2 Staging: One Model per Source Table (28 min)

stg_macro__observations breaks rule 2's spirit: it unions three source tables and joins a seed. Let's split it.

New folder structure

cd macro_dbt
mkdir -p models/staging/{boc,fred,statcan,dlt}
git mv models/staging/stg_statcan__cpi.sql models/staging/statcan/
git mv models/staging/stg_statcan__gdp.sql models/staging/statcan/

Each provider folder gets its own sources file.

models/staging/boc/_boc__sources.yml

version: 2

sources:
  - name: boc
    description: "Bank of Canada Valet data loaded by macro_ingest (dlt)."
    schema: raw_dlt
    tables:
      - name: observations
        identifier: boc_observations      # real table name in raw_dlt
        description: "Valet observations, long format, merged incrementally."
      - name: series_metadata
        identifier: boc_series_metadata

identifier lets the dbt name (source('boc', 'observations')) differ from the physical table name (raw_dlt.boc_observations). The code reads naturally, and the physical name lives in one place.

Create the equivalents:

• models/staging/fred/_fred__sources.yml: source fred, table observations → fred_observations.
• models/staging/statcan/_statcan__sources.yml: source statcan, tables observations → statcan_observations, cpi_full → statcan_cpi_full, gdp_full → statcan_gdp_full, series_metadata → statcan_series_metadata.
• models/staging/dlt/_dlt__sources.yml: source dlt, table loads → _dlt_loads.

Delete the old models/staging/_sources.yml, then update the two StatCan models: source('raw_dlt', 'statcan_cpi_full') → source('statcan', 'cpi_full'), and likewise for GDP.

One staging model per source table

models/staging/boc/stg_boc__observations.sql

-- Grain: one row per BoC series × observation date
with source as (

    select * from {{ source('boc', 'observations') }}

)

select
    cast(date as date)  as obs_date,
    series_id,
    value,
    'boc'               as provider,
    _dlt_load_id        as load_id
from source

Create stg_fred__observations.sql and stg_statcan__observations.sql the same way (only the source and provider change; tomorrow a macro removes even that repetition).

models/staging/dlt/stg_dlt__loads.sql

-- Grain: one row per successful dlt load
select
    load_id,
    inserted_at    as loaded_at,
    status
from {{ source('dlt', 'loads') }}

Now delete models/staging/stg_macro__observations.sql. The union and the seed join move to the intermediate layer, where joining is allowed.

dbt ls --select staging

---

34.3 Intermediate: Organised by Economic Concept (23 min)

Group intermediate models by concept, not by source:

mkdir -p models/intermediate/{macro,cpi,gdp}
git mv models/intermediate/int_cpi__*.sql models/intermediate/cpi/
git mv models/intermediate/int_gdp__*.sql models/intermediate/gdp/
git mv models/intermediate/int_rates__*.sql models/intermediate/macro/

The union moves here

models/intermediate/macro/int_macro__observations.sql

-- Grain: one row per series × observation date, all providers, labelled with seed metadata
with unioned as (

    select * from {{ ref('stg_boc__observations') }}
    union all
    select * from {{ ref('stg_fred__observations') }}
    union all
    select * from {{ ref('stg_statcan__observations') }}

)

select
    u.obs_date,
    u.series_id,
    s.series_name,
    u.provider,
    u.value,
    s.frequency,
    s.monthly_agg,
    l.loaded_at
from unioned as u
join {{ ref('seed_series') }} as s using (series_id)
left join {{ ref('stg_dlt__loads') }} as l using (load_id)

Update int_rates__monthly_long to read {{ ref('int_macro__observations') }} instead of the deleted staging model.

When to create a new intermediate model

Split it out when…	Keep it as a CTE when…
two or more models need the same result	only one model uses it
the step is expensive and worth materialising	it's cheap
it has a clear economic meaning (e.g. "CPI growth rates")	it's a mechanical sub-step
testing it separately would catch real errors	testing it adds nothing

Naming: say what the model is

✗ Vague	✓ Descriptive
int_cpi_2	int_cpi__growth
int_temp_rates	int_rates__monthly_long
int_joined	int_macro__observations

Ephemeral helpers

A helper that just filters is a good ephemeral model (Day 32). Create models/intermediate/cpi/int_cpi__active.sql:

{{ config(materialized='ephemeral') }}
-- Grain: month × geography × component; non-terminated CPI series only
select month, geo, geo_code, geo_level, component, cpi
from {{ ref('stg_statcan__cpi') }}
where not is_terminated

Tomorrow's growth macro will read from it.

---

34.4 Marts: Facts, Dimensions and One-Big-Tables (23 min)

Marts are built for consumers: dashboards, forecasting models (Days 61–70), causal models (Days 71–80). Two design styles dominate.

Dimensional (Kimball) style

Type	Prefix	Contains	Our examples
Fact	fct_	measurements or events: many rows, numeric	fct_observations (every value), fct_policy_decisions
Dimension	dim_	descriptive entities: few rows, text	dim_series, dim_geo, dim_date

Facts are joined to dimensions by keys: the star schema you built by hand on Day 12.

One-big-table (OBT) style

A single wide table with everything a consumer needs: our macro_monthly and macro_quarterly panels from Day 20. Dashboards and econometric software love these, because no joins are needed.

In practice you use both: dimensional models as the reusable core, and OBT panels built on top for specific consumers.

mkdir -p models/marts/{core,monetary}

dim_date: a calendar dimension

Every time-series project needs a calendar. Create models/marts/core/dim_date.sql:

-- Grain: one row per calendar day, 1990-01-01 to 2030-12-31
with spine as (

    select cast(range as date) as date_day
    from range(date '1990-01-01', date '2031-01-01', interval 1 day)

)

select
    date_day,
    year(date_day)                                    as year,
    quarter(date_day)                                 as quarter,
    month(date_day)                                   as month,
    cast(date_trunc('month', date_day) as date)       as month_start,
    cast(date_trunc('quarter', date_day) as date)     as quarter_start,
    dayofweek(date_day)                               as day_of_week,     -- 0 = Sunday
    dayofweek(date_day) in (0, 6)                     as is_weekend,
    exists (
        select 1 from {{ ref('seed_recession_bands') }} as b
        where date_day between b.start_date and b.end_date
    )                                                 as in_dated_recession
from spine

dim_series: every series, described once

models/marts/core/dim_series.sql:

-- Grain: one row per ingested series
select
    s.series_id,
    s.series_name,
    s.source           as provider,
    s.frequency,
    s.monthly_agg,
    s.max_staleness_days,
    coalesce(b.label, st.title)   as official_label
from {{ ref('seed_series') }} as s
left join {{ ref('stg_boc__series_metadata') }}     as b  using (series_id)
left join {{ ref('stg_statcan__series_metadata') }} as st on st.vector_id = s.series_id

(Create the two small stg_*__series_metadata staging models from their sources first. They're simple selects with renames.)

Move the Day 17 mart

Port sql/32_policy_cycles.sql to models/marts/monetary/policy_cycles.sql. Replace raw.boc_decisions with {{ ref('seed_boc_decisions') }}, CPI with {{ ref('int_cpi__growth') }} (national All-items yoy), and yields with {{ ref('int_macro__observations') }} where series_name = 'yield_2yr'.

---

34.5 DAG Hygiene (18 min)

Good projects follow a few structural rules. You can check each with dbt ls or the lineage graph.

Rule	Why	Check
Only staging reads source()	one place to absorb raw changes	grep -rn "source(" models/intermediate models/marts should find nothing
Marts don't ref() staging directly	marts should be built from reusable intermediates	inspect dbt ls --select +marts
No model reads a mart to build an intermediate	layers flow one way	lineage graph: no arrows pointing "backwards"
Every model has a documented grain	prevents fan-outs and double counting	YAML descriptions (Day 36 tests enforce keys)
No long "daisy chains" of trivial models	each model must earn its place	lineage graph

grep -rn "source(" models/intermediate models/marts || echo "✓ only staging reads sources"
dbt ls --select +marts --resource-type model

The dbt_project_evaluator package automates dozens of such checks. It's worth exploring once your project grows. The table above covers what matters most.

---

34.6 Mini-Project: Restructure macro_dbt (23 min)

1. Finish the moves from 34.2–34.4 so the tree looks like this:

models/
├── staging/
│   ├── boc/      _boc__sources.yml, _boc__models.yml, stg_boc__observations.sql, stg_boc__series_metadata.sql
│   ├── fred/     _fred__sources.yml, _fred__models.yml, stg_fred__observations.sql
│   ├── statcan/  _statcan__sources.yml, _statcan__models.yml, stg_statcan__observations.sql,
│   │             stg_statcan__cpi.sql, stg_statcan__gdp.sql, stg_statcan__series_metadata.sql
│   └── dlt/      _dlt__sources.yml, stg_dlt__loads.sql
├── intermediate/
│   ├── macro/    int_macro__observations.sql, int_rates__monthly_long.sql, int_rates__monthly.sql
│   ├── cpi/      int_cpi__active.sql, int_cpi__growth.sql, int_cpi__smoothed.sql
│   └── gdp/      int_gdp__growth.sql
└── marts/
    ├── core/     dim_date.sql, dim_series.sql
    └── monetary/ policy_cycles.sql

2. Split the YAML documentation into one _<folder>__models.yml per folder.
3. dbt build: everything must succeed.
4. Re-run Day 33's analyses/regression_vs_module2.sql: zero differences (apart from explained dev filters).
5. Open the docs lineage graph and check the hygiene rules in 34.5.

Day 34 checklist

• I can state what each layer may and may not do
• Each source table has exactly one staging model, and only staging reads sources
• Intermediate models are organised by economic concept, with descriptive names
• I understand facts vs dimensions vs one-big-tables, and built dim_date and dim_series
• The project passes the DAG hygiene checks

dbt build && cd .. && git add . && git commit -m "Day 34: modular restructure, dims, policy_cycles mart" && git push