Materializations, Configuration, Seeds and Documentation

All commands today run from inside macro_dbt/.

32.1 Materializations (25 min)

A materialization decides how a model's SELECT is persisted. You met the concept on Day 13 (tables vs views). dbt makes it a one-word setting.

See the difference yourself

Change one model to a table inside the file, using a config() block at the top:

-- models/intermediate/int_rates__monthly_long.sql
{{ config(materialized='table') }}

select
    cast(date_trunc('month', obs_date) as date)  as month,
    ...

dbt run --select int_rates__monthly_long
cat target/run/macro_dbt/models/intermediate/int_rates__monthly_long.sql

The executed SQL is now a create table … as. Check the object type in DuckDB:

dbt show --inline "select table_schema, table_name, table_type from information_schema.tables where table_name like 'int_rates%'"

Ephemeral: a model that is only a CTE

-- models/intermediate/int_cpi__national_all_items.sql
{{ config(materialized='ephemeral') }}

-- Grain: one row per month; national All-items CPI only
select month, cpi
from {{ ref('stg_statcan__cpi') }}
where geo_code = 'CA' and component = 'All-items'

Nothing is created in the database. Any model that ref()s it gets its SQL pasted in as a CTE. Try it:

dbt show --inline "select count(*) from {{ ref('int_cpi__national_all_items') }}"

Use ephemeral models for small, reused building blocks that aren't worth a database object. Don't overuse them: they can't be queried directly when debugging.

A look ahead: incremental

An incremental model inserts or merges only new rows on each run, much like dlt's merge on Day 27, but inside the warehouse. It's powerful for huge tables and needs care (what counts as "new"? what about revisions?). We'll use it on Days 34–37 once tests are in place. For macro data of our size, view and table cover almost everything.

32.2 Where Configuration Lives (23 min)

Configurations can be set in three places. The most specific wins:

1. config() block inside the model file     ← highest priority
2. properties file (models/…/_staging.yml)
3. dbt_project.yml (folder-level defaults)  ← lowest priority

Folder-level defaults in dbt_project.yml

Replace the models: section:

models:
  macro_dbt:
    staging:
      +materialized: view
    intermediate:
      +materialized: view
    marts:
      +materialized: table

Each key under macro_dbt: is a folder in models/. Every model in models/marts/ is now a table by default, with no per-file configuration needed.

Model-level override in a properties file

Create models/intermediate/_intermediate.yml:

version: 2

models:
  - name: int_rates__monthly_long
    description: "Monthly aggregation of all ingested series, using each series' monthly_agg rule."
    config:
      materialized: table

Now remove the {{ config(materialized='table') }} line from the SQL file and rebuild: it's still a table, because the YAML setting applies.

Convention: set defaults by folder in dbt_project.yml, and override exceptions in YAML or config(). Use config() in the SQL file only when the setting is essential to understanding the model (e.g. an incremental strategy).

Checking what dbt resolved

dbt ls --select intermediate --output json --output-keys "name config"

This prints each model's final configuration after all three levels are combined.

32.3 Custom Schemas (20 min)

Right now everything lands in one schema, dev. We want dbt's layers separated just like Module 2's (staging, intermediate, marts, seeds), and we want dev builds kept away from the Module 2 objects the dashboards currently use.

Add +schema per folder in dbt_project.yml:

models:
  macro_dbt:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: view
      +schema: intermediate
    marts:
      +materialized: table
      +schema: marts

seeds:
  macro_dbt:
    +schema: seeds

dbt run
dbt show --inline "select distinct table_schema from information_schema.tables order by 1"

You'll see dev_staging and dev_intermediate. dbt's default rule is <target schema>_<custom schema>, so dev builds can never overwrite staging.*.

Making prod use the clean names

For production we want staging, not main_staging. dbt decides schema names with a built-in macro, generate_schema_name, and you can override it. Create macros/generate_schema_name.sql:

{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- if target.name == 'prod' and custom_schema_name is not none -%}
        {{ custom_schema_name | trim }}
    {%- elif custom_schema_name is none -%}
        {{ target.schema }}
    {%- else -%}
        {{ target.schema }}_{{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

This is your first macro, a reusable piece of Jinja. Macros get a full treatment on Days 34–37; for now, read it as an if/elif/else (Day 2) that returns a schema name.

⚠️ Do not run dbt run --target prod yet. In prod, dbt would replace the Module 2 views in staging.* and intermediate.*. We'll do that deliberately, with a regression test, at the cut-over on Days 38–40. Until then, dev only.

32.4 Seeds: Reference Data as CSV (25 min)

In Module 2, seeds were INSERT INTO … VALUES statements in sql/03_seeds.sql. In dbt, a seed is simply a CSV file in seeds/, loaded with dbt seed and referenced with ref().

Create the CSVs

seeds/seed_geo.csv

geo,geo_code,geo_level,region
Canada,CA,national,
Newfoundland and Labrador,NL,province,Atlantic
Prince Edward Island,PE,province,Atlantic
Nova Scotia,NS,province,Atlantic
New Brunswick,NB,province,Atlantic
Quebec,QC,province,Central
Ontario,ON,province,Central
Manitoba,MB,province,Prairies
Saskatchewan,SK,province,Prairies
Alberta,AB,province,West
British Columbia,BC,province,West

seeds/seed_series.csv

series_id,series_name,source,frequency,monthly_agg,max_staleness_days
V39079,overnight_rate,boc,daily,last,10
FXCADUSD,cad_usd,boc,daily,mean,10
BD.CDN.2YR.DQ.YLD,yield_2yr,boc,daily,mean,10
BD.CDN.10YR.DQ.YLD,yield_10yr,boc,daily,mean,10
FEDFUNDS,fed_funds,fred,monthly,last,75
DCOILWTICO,wti,fred,daily,mean,15
CANLOLITOAASTSAM,oecd_cli,fred,monthly,mean,120
v41690973,cpi_all_items,statcan,monthly,last,75
v2062815,unemployment_rate,statcan,monthly,last,60

seeds/seed_recession_bands.csv

label,start_date,end_date
GFC,2008-10-01,2009-06-30
Oil shock,2015-01-01,2015-09-30
COVID,2020-02-01,2020-06-30
Tariff recession,2025-10-01,2026-03-31

seeds/seed_cpi_weights.csv (illustrative weights from the course notebook; see Day 18)

component,weight_pct
Shelter,29.0
Transportation,16.0
Food,15.9
"Household operations, furnishings and equipment",14.0
"Recreation, education and reading",10.4
Health and personal care,5.4
Clothing and footwear,5.0

Note the double quotes around names containing commas. That's standard CSV quoting.

seeds/seed_boc_decisions.csv: the Day 11 decision log, which is hand-maintained reference data and so a seed:

decision_date,rate,change_bp,note
2022-03-02,0.50,25,First 2022 hike
2022-04-13,1.00,50,Accelerating
2022-06-01,1.50,50,
2022-07-13,2.50,100,Jumbo hike
2022-09-07,3.25,75,
2022-10-26,3.75,50,
2022-12-07,4.25,50,
2023-01-25,4.25,0,Conditional pause
2023-03-08,4.25,0,
2023-04-12,4.25,0,
2023-06-07,4.75,50,Resumed hiking
2023-07-12,5.00,25,Peak rate
2023-09-06,5.00,0,
2024-06-05,4.75,-25,First cut
2024-07-24,4.50,-25,
2024-09-04,4.25,-25,
2024-10-23,3.75,-50,Accelerating cuts
2024-12-11,3.25,-50,
2025-01-29,3.00,-25,
2025-03-12,2.75,-25,
2025-04-16,2.25,-50,Tariff shock
2025-06-04,2.25,0,
2025-07-30,2.25,0,
2025-09-10,2.25,0,
2025-10-29,2.25,0,Current hold

Tell dbt the column types

dbt infers seed types, but, as on Day 24, don't let inference decide for important columns. Create seeds/_seeds.yml:

version: 2

seeds:
  - name: seed_geo
    description: "Canada and provinces: codes, level and region."
    config:
      column_types: {geo: varchar, geo_code: varchar, geo_level: varchar, region: varchar}

  - name: seed_series
    description: "Every ingested series: name, provider, frequency, monthly aggregation rule, staleness tolerance."
    config:
      column_types: {series_id: varchar, max_staleness_days: integer}

  - name: seed_recession_bands
    description: "Recession bands as used in the course notebook."
    config:
      column_types: {start_date: date, end_date: date}

  - name: seed_cpi_weights
    description: "Illustrative CPI weights from the notebook; official weights: StatCan 18-10-0007-01."
    config:
      column_types: {weight_pct: double}

  - name: seed_boc_decisions
    description: "BoC policy decisions (course notebook), with change in basis points."
    config:
      column_types: {decision_date: date, rate: double, change_bp: integer, note: varchar}

dbt seed
dbt show --select seed_boc_decisions --limit 3

Switch the staging models to the dbt seeds

In stg_macro__observations.sql and stg_statcan__cpi.sql, replace the temporary Module 2 references:

join {{ ref('seed_series') }} as s using (series_id)
...
left join {{ ref('seed_geo') }} as g using (geo)

dbt build --select +stg_macro__observations +stg_statcan__cpi

dbt build runs seeds, models (and tests, once we have them) in DAG order: one command for everything. The seeds are now part of the graph, so changing a CSV and running dbt build --select seed_series+ rebuilds exactly what depends on it.

When is a CSV seed appropriate? Small (hundreds of rows, not millions), hand-maintained reference data that changes rarely and should be reviewed in Git: codes, mappings, thresholds, recession dates, decision logs. Never use seeds to load real datasets. That's dlt's job.

32.5 Choosing Materializations with Evidence (20 min)

Port two intermediate models from Day 19. We'll measure the cost of a view vs a table rather than guessing.

models/intermediate/int_cpi__growth.sql (as on Day 19; tomorrow we'll shorten it with Jinja):

-- Grain: month × geography × component. Gap-safe growth rates in %.
with base as (

    select month, geo, geo_code, geo_level, component, cpi
    from {{ ref('stg_statcan__cpi') }}
    where not is_terminated

),

lagged as (

    select
        *,
        lag(cpi, 1)    over w as cpi_l1,   lag(month, 1)  over w as month_l1,
        lag(cpi, 3)    over w as cpi_l3,   lag(month, 3)  over w as month_l3,
        lag(cpi, 6)    over w as cpi_l6,   lag(month, 6)  over w as month_l6,
        lag(cpi, 12)   over w as cpi_l12,  lag(month, 12) over w as month_l12
    from base
    window w as (partition by geo, component order by month)

)

select
    month, geo, geo_code, geo_level, component, cpi,
    case when month_l1 = cast(month - interval 1 month as date)
         then 100 * (cpi / cpi_l1 - 1) end                     as mom,
    case when month_l3 = cast(month - interval 3 month as date)
         then 100 * (power(cpi / cpi_l3, 4) - 1) end           as ann_3m,
    case when month_l6 = cast(month - interval 6 month as date)
         then 100 * (power(cpi / cpi_l6, 2) - 1) end           as ann_6m,
    case when month_l12 = cast(month - interval 12 month as date)
         then 100 * (cpi / cpi_l12 - 1) end                    as yoy
from lagged

models/intermediate/int_cpi__smoothed.sql: port Day 19's int_cpi__smoothed, replacing intermediate.int_cpi__growth with {{ ref('int_cpi__growth') }}.

The experiment

dbt run --select int_cpi__growth int_cpi__smoothed        # both as views (folder default)
time dbt show --inline "select count(*) from {{ ref('int_cpi__smoothed') }} where yoy_ma12 is not null"

Now make int_cpi__growth a table in _intermediate.yml:

  - name: int_cpi__growth
    description: "Gap-safe CPI growth rates (mom, ann_3m, ann_6m, yoy) for every geography and component."
    config:
      materialized: table

dbt run --select int_cpi__growth int_cpi__smoothed
time dbt show --inline "select count(*) from {{ ref('int_cpi__smoothed') }} where yoy_ma12 is not null"

Compare the two timings, and the run times dbt prints for each model.

A decision rule

For our project: staging = views, heavy intermediates (CPI growth over the full table) = tables, light intermediates = views, marts = tables. Record the reason for each exception in the model's description.

Try it 32.5

Port Day 20's int_gdp__growth into models/intermediate/. It reads a small table (a few hundred quarters). Which materialisation should it use, and why? Configure it accordingly.

32.6 Documentation and the Lineage Graph · Mini-Project (23 min)

Descriptions you write in YAML become a documentation website, generated from the project itself.

Document the staging models

Create models/staging/_staging.yml:

version: 2

models:
  - name: stg_statcan__cpi
    description: >
      Typed StatCan CPI (18-10-0004-01), not seasonally adjusted.
      Grain: one row per month × geography × product group.
      Rows without a numeric value are dropped; terminated series are flagged.
    columns:
      - name: month
        description: "First day of the reference month."
      - name: geo_level
        description: "'national', 'province' or 'city' (from seed_geo)."
      - name: cpi
        description: "Index level; base period in index_base (e.g. 2002=100)."

  - name: stg_macro__observations
    description: >
      All ingested series (BoC, FRED, StatCan vectors) in long format, labelled via seed_series.
      Grain: one row per series × observation date.
    columns:
      - name: monthly_agg
        description: "How to collapse to monthly: 'last' (policy rates) or 'mean' (prices, yields)."
      - name: loaded_at
        description: "When dlt loaded the row (from raw_dlt._dlt_loads)."

Generate and browse

dbt docs generate
dbt docs serve --port 8080

Codespaces will offer to open the forwarded port in your browser. Explore:

• each model's description, columns, compiled SQL and materialization;
• the lineage graph (the blue icon, bottom right): sources → staging → intermediate, seeds included.

Press Ctrl+C in the terminal to stop the server.

Mini-project

1. Port Day 20's int_rates__monthly (the wide version with derived columns boc_fed_diff, spread_10_2_bp, usd_cad) into models/intermediate/int_rates__monthly.sql, reading from int_rates__monthly_long.
2. Document it in _intermediate.yml: grain, the aggregation rule, and each derived column.
3. Run dbt build --select +int_rates__monthly.
4. Regression-check it against the Module 2 view intermediate.int_rates__monthly using except in both directions (as in 31.6). Round numeric columns to 8 decimals in both sides of the comparison if tiny floating-point differences appear.

-- models/intermediate/int_rates__monthly.sql
-- Grain: one row per month. Derived spreads and FX inversion.
with wide as (

    select
        month,
        max(value) filter (where series_name = 'overnight_rate')    as overnight_rate,
        max(value) filter (where series_name = 'fed_funds')         as fed_funds,
        max(value) filter (where series_name = 'yield_2yr')         as yield_2yr,
        max(value) filter (where series_name = 'yield_10yr')        as yield_10yr,
        max(value) filter (where series_name = 'cad_usd')           as cad_usd,
        max(value) filter (where series_name = 'wti')               as wti,
        max(value) filter (where series_name = 'oecd_cli')          as oecd_cli,
        max(value) filter (where series_name = 'unemployment_rate') as unemployment_rate
    from {{ ref('int_rates__monthly_long') }}
    group by month

)

select
    *,
    overnight_rate - fed_funds        as boc_fed_diff,
    (yield_10yr - yield_2yr) * 100    as spread_10_2_bp,
    1 / cad_usd                       as usd_cad
from wide

Day 32 checklist

• I can explain view, table and ephemeral materialisations and when to use each
• I set folder defaults in dbt_project.yml and exceptions in YAML or config(), and I know which wins
• My dev builds land in dev_* schemas; prod will use clean names, and I won't run prod until the cut-over
• My reference data (geo, series, recession bands, CPI weights, BoC decisions) lives in CSV seeds with typed columns
• I chose materialisations using measured timings
• I document models in YAML and can browse the docs site and lineage graph

cd ..
git add .
git commit -m "Day 32: materializations, custom schemas, seeds, docs"
git push