Designing the Mart and Building Its Foundations

38.1 Start from the Consumer (25 min)

A mart is a product. Before writing SQL, write down who uses it and what they ask. Our consumers are the course notebook's Module 02 (Rates & FX) and Module 06 (Open Economy) dashboards, and later the forecasting (Days 61–70) and causal (Days 71–80) modules.

The questions

From questions to schema

                    dim_date          dim_series (country, instrument, tenor)
                        │                   │
   ┌────────────────────┼───────────────────┼─────────────────────┐
   │                    │                   │                     │
fct_yield_curve     fct_open_economy_daily   macro_monthly     macro_quarterly
(date × country     (date: FX, oil, yields,  (month: OBT)      (quarter: OBT)
 × tenor)            spreads, returns,              │
                     vol, rolling corr)       macro_kpis (latest values + signals)

The grain decision is the most important one. Markets are daily, while inflation and output are monthly/quarterly. We keep a daily fact for market dynamics (returns, volatility, correlations need daily data) and aggregate up for the monthly and quarterly panels. We never disaggregate down: monthly CPI must not be spread across days.

Try it 38.1

For each question Q1–Q8, name one pitfall that would make the answer misleading. (Example: Q4 with levels instead of changes: two trending series look correlated even when they're unrelated.)

38.2 Extending Ingestion: 5-Year and US Treasury Yields (23 min)

The curve needs a middle tenor, and the open-economy questions need US yields.

Step 1 — Configuration (.dlt/config.toml)

[sources.boc]
series_ids = ["V39079", "FXCADUSD", "BD.CDN.2YR.DQ.YLD", "BD.CDN.5YR.DQ.YLD", "BD.CDN.10YR.DQ.YLD"]
start_date = "2006-01-01"
lookback_days = 14

[sources.fred]
series_ids = ["FEDFUNDS", "DFF", "DGS2", "DGS5", "DGS10", "DCOILWTICO", "CANLOLITOAASTSAM"]
start_date = "2006-01-01"
lookback_days = 180

Step 2 — Ingest

cd /workspaces/macro-data-course
source .venv/bin/activate
python ingest.py

Thanks to the per-series cursors from Day 27, the new series have no cursor yet, so they load their full history from start_date, while existing series load only their recent window. Adding a series needs no code change. That's the payoff of Modules 3's design.

If dbt build now fails the relationships test from Day 36 ("series_id not in seed_series"), good: the test is doing its job. The next block adds the seed rows.

38.3 A Richer Series Dimension (23 min)

The mart needs to know what kind of thing each series is. Extend macro_dbt/seeds/seed_series.csv:

series_id,series_name,source,frequency,monthly_agg,max_staleness_days,in_rates_panel,country,instrument_type,tenor_years
V39079,overnight_rate,boc,daily,last,10,true,CA,policy_rate,
FXCADUSD,cad_usd,boc,daily,mean,10,true,CA,fx,
BD.CDN.2YR.DQ.YLD,yield_2yr,boc,daily,mean,10,true,CA,govt_yield,2
BD.CDN.5YR.DQ.YLD,yield_5yr,boc,daily,mean,10,true,CA,govt_yield,5
BD.CDN.10YR.DQ.YLD,yield_10yr,boc,daily,mean,10,true,CA,govt_yield,10
FEDFUNDS,fed_funds,fred,monthly,last,75,true,US,policy_rate,
DFF,fed_funds_daily,fred,daily,last,10,false,US,policy_rate,
DGS2,us_yield_2yr,fred,daily,mean,10,true,US,govt_yield,2
DGS5,us_yield_5yr,fred,daily,mean,10,true,US,govt_yield,5
DGS10,us_yield_10yr,fred,daily,mean,10,true,US,govt_yield,10
DCOILWTICO,wti,fred,daily,mean,15,true,WORLD,commodity,
CANLOLITOAASTSAM,oecd_cli,fred,monthly,mean,120,true,CA,leading_indicator,
v41690973,cpi_all_items,statcan,monthly,last,75,false,CA,price_index,
v2062815,unemployment_rate,statcan,monthly,last,60,true,CA,labour_market,

Update the types and tests in seeds/_seeds.yml:

  - name: seed_series
    config:
      column_types:
        series_id: varchar
        max_staleness_days: integer
        in_rates_panel: boolean
        tenor_years: integer
    columns:
      - name: series_id
        data_tests: [unique, not_null]
      - name: country
        data_tests:
          - accepted_values: {values: ['CA', 'US', 'WORLD']}
      - name: instrument_type
        data_tests:
          - accepted_values:
              values: ['policy_rate', 'fx', 'govt_yield', 'commodity',
                       'leading_indicator', 'price_index', 'labour_market']
    data_tests:
      - dbt_utils.expression_is_true:
          expression: "(instrument_type = 'govt_yield') = (tenor_years is not null)"

The model-level expression_is_true test encodes a rule: a series has a tenor if and only if it's a government yield. It's a small data contract on your own reference data.

Then carry the new columns through dim_series:

-- 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,
    s.country,
    s.instrument_type,
    s.tenor_years,
    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

cd macro_dbt
dbt build --select seed_series+

Because int_rates__monthly builds its columns from the seed (Day 35's run_query), the monthly rates panel automatically gains yield_5yr, us_yield_2yr, us_yield_5yr and us_yield_10yr. Check with dbt show --select int_rates__monthly --limit 3.

38.4 fct_yield_curve: The Curve as Data (23 min)

A yield curve is naturally long: for each date and country, a set of (tenor, yield) points. Long format makes curve charts and cross-country comparisons trivial.

Create models/marts/rates/fct_yield_curve.sql (make the rates folder):

-- Grain: one row per observation date × country × tenor (government bond yields only)
with yields as (

    select
        o.obs_date,
        s.country,
        s.tenor_years,
        o.value         as yield_pct,
        o.series_id
    from {{ ref('int_macro__observations') }} as o
    join {{ ref('seed_series') }}             as s using (series_id)
    where s.instrument_type = 'govt_yield'

)

select
    {{ dbt_utils.generate_surrogate_key(['obs_date', 'country', 'tenor_years']) }} as curve_point_key,
    obs_date,
    country,
    tenor_years,
    yield_pct,
    series_id
from yields

Document and test it in models/marts/rates/_rates__models.yml:

version: 2

models:
  - name: fct_yield_curve
    description: >
      Government bond yield curve points. Grain: date × country × tenor.
      Canada: BoC benchmark yields; US: Treasury constant-maturity yields (FRED).
    columns:
      - name: curve_point_key
        data_tests: [unique, not_null]
      - name: tenor_years
        data_tests:
          - accepted_values: {values: [2, 5, 10], quote: false}
      - name: yield_pct
        data_tests:
          - within_range: {min_value: -2, max_value: 20}

(quote: false compares numbers as numbers rather than text.)

Using it: Q1 in one query

Save as analyses/yield_curve_snapshots.sql:

-- Canadian curve today, one year ago, and at the 2023 tightening peak
with latest as (
    select max(obs_date) as d from {{ ref('fct_yield_curve') }} where country = 'CA'
),
dates as (
    select 'latest'      as label, d                                 as target_date from latest
    union all
    select 'one year ago', cast(d - interval 1 year as date)                            from latest
    union all
    select 'mid-2023',     date '2023-07-12'
),
points as (
    select dt.label, c.tenor_years, c.yield_pct, c.obs_date
    from dates as dt
    asof join (select * from {{ ref('fct_yield_curve') }} where country = 'CA') as c
         on dt.target_date >= c.obs_date                -- first attempt: see below
)
select label, tenor_years, round(yield_pct, 2) as yield_pct, obs_date
from points
order by label, tenor_years

Run this first attempt and look closely: each date returns only one point, not a curve. An ASOF join matches exactly one row per left row, but each date needs three tenors. The fix is to ASOF-join per tenor, which means adding the tenor to the join's equality condition:

points as (
    select dt.label, t.tenor_years, c.yield_pct, c.obs_date
    from dates as dt
    cross join (select distinct tenor_years from {{ ref('fct_yield_curve') }}) as t
    asof join (select * from {{ ref('fct_yield_curve') }} where country = 'CA') as c
         on c.tenor_years = t.tenor_years and dt.target_date >= c.obs_date
)

ASOF JOIN … ON <equality conditions> AND <one inequality>: the equalities pick the right tenor, and the inequality finds the latest date on or before the target. This is exactly how you'd build a curve "as of" any date, holidays included.

dbt compile --select yield_curve_snapshots
dbt show --inline "$(cat target/compiled/macro_dbt/analyses/yield_curve_snapshots.sql)"

38.5 Aligning Daily Markets: a Business-Day Panel (40 min)

Daily series don't share a calendar: Canadian and US holidays differ, FRED sometimes lags by a day, and oil trades on different days. To compute cross-market returns and correlations we need one row per business day with every series aligned.

Design

1. A spine of weekdays from dim_date.
2. For each daily series, an ASOF join attaches the latest value on or before that day.
3. A staleness cap: if the latest value is more than max_ffill_days old, treat it as missing. Forward-filling across a long outage would invent data (the Day 5 ffill rule).
4. Columns are generated from the seed (frequency = 'daily'), so new daily series appear automatically.

Add to dbt_project.yml vars:

  max_ffill_days: 5            # forward-fill daily market data at most 5 calendar days

Create models/intermediate/markets/int_market__daily.sql:

-- depends_on: {{ ref('seed_series') }}
-- Grain: one row per weekday. Every daily series aligned by ASOF join,
-- forward-filled at most var('max_ffill_days') calendar days, NULL beyond that.
{{ config(materialized='table') }}

{% set daily_series = get_series_names(where="frequency = 'daily'") %}

with spine as (

    select date_day
    from {{ ref('dim_date') }}
    where not is_weekend
      and date_day between date '{{ var("panel_start_date") }}' and current_date

),

{% for s in daily_series %}
s_{{ s }} as (

    select obs_date, value
    from {{ ref('int_macro__observations') }}
    where series_name = '{{ s }}'

),
{% endfor %}

aligned as (

    select
        sp.date_day
        {%- for s in daily_series %},
        s_{{ s }}.value                     as {{ s }}_raw,
        sp.date_day - s_{{ s }}.obs_date    as {{ s }}_age_days
        {%- endfor %}
    from spine as sp
    {%- for s in daily_series %}
    asof left join s_{{ s }} on sp.date_day >= s_{{ s }}.obs_date
    {%- endfor %}

)

select
    date_day
    {%- for s in daily_series %},
    case when {{ s }}_age_days <= {{ var('max_ffill_days') }} then {{ s }}_raw end  as {{ s }},
    {{ s }}_age_days
    {%- endfor %}
from aligned

Notes:

• asof left join keeps every spine day even before a series starts (the value is NULL).
• Subtracting two dates gives a number of days in DuckDB, so {{ s }}_age_days records how stale each value is. Keeping it lets dashboards grey out stale points.
• This model reads dim_date, a mart. Calendar dimensions are the accepted exception to "intermediate doesn't read marts": they're conformed reference data used everywhere. Record exceptions like this in CONVENTIONS.md (Day 37).

dbt build --select int_market__daily
dbt show --inline "
select date_day, cad_usd, cad_usd_age_days, yield_2yr, us_yield_2yr, wti, wti_age_days
from {{ ref('int_market__daily') }}
order by date_day desc limit 10"

Tests

# models/intermediate/markets/_markets__models.yml
version: 2

models:
  - name: int_market__daily
    description: >
      Business-day panel of all daily series (seed frequency = 'daily'), aligned by ASOF join.
      Values older than var('max_ffill_days') are set to NULL; *_age_days shows staleness.
    columns:
      - name: date_day
        data_tests: [unique, not_null]
    data_tests:
      - dbt_utils.expression_is_true:
          expression: "cad_usd_age_days is null or cad_usd_age_days >= 0"

Try it 38.5

How many weekdays since 2020 had a stale (≥ 1 day old) US 10-year yield but a fresh (0 days) Canadian 10-year yield? What calendar events explain them?

dbt show --inline "
select year(date_day) as yr, count(*) as n_days
from {{ ref('int_market__daily') }}
where date_day >= date '2020-01-01'
  and us_yield_10yr_age_days >= 1 and yield_10yr_age_days = 0
group by 1 order by 1"

Day 38 checklist

• I designed the mart from consumer questions, with an explicit grain per model
• New series were added through configuration and seeds only, with no code changes
• dim_series describes country, instrument type and tenor, with tests enforcing consistency
• fct_yield_curve stores the curve in long format, and I can build curves "as of" any date
• int_market__daily aligns all daily series on business days with a staleness cap

dbt build && cd .. && git add . && git commit -m "Day 38: mart design, new yields, fct_yield_curve, aligned daily panel" && git push