The Open-Economy Mart

39.1 A Short Open-Economy Primer for the Data (18 min)

A few relationships motivate the columns we'll build. We treat them as descriptive patterns to monitor, not causal claims. Causal questions come in Module 8.

The notebook already charts several of these (BoC–Fed differential vs USD/CAD; CAD/USD vs WTI; rolling correlation). The mart makes them consistent, tested and reusable.

39.2 int_open_economy__daily: Spreads, Slopes, Returns (33 min)

Create models/intermediate/markets/int_open_economy__daily.sql:

-- Grain: one row per weekday. Open-economy derived measures from int_market__daily.
-- Spreads in basis points; returns are daily log changes (non-positive prices → NULL).
{{ config(materialized='table') }}

with m as (

    select * from {{ ref('int_market__daily') }}

),

levels as (

    select
        date_day,

        -- FX: both quotes (the notebook uses both)
        cad_usd,
        1 / cad_usd                                         as usd_cad,

        -- Policy rates
        overnight_rate,
        fed_funds_daily,
        {{ bp('overnight_rate - fed_funds_daily') }}        as policy_diff_bp,

        -- Curves
        yield_2yr   as ca_2y,  yield_5yr   as ca_5y,  yield_10yr   as ca_10y,
        us_yield_2yr as us_2y, us_yield_5yr as us_5y, us_yield_10yr as us_10y,
        {{ bp('yield_10yr - yield_2yr') }}                  as ca_slope_bp,
        {{ bp('us_yield_10yr - us_yield_2yr') }}            as us_slope_bp,
        {{ bp('2 * yield_5yr - yield_2yr - yield_10yr') }}  as ca_butterfly_bp,

        -- Cross-border spreads
        {{ bp('yield_2yr - us_yield_2yr') }}                as ca_us_2y_spread_bp,
        {{ bp('yield_10yr - us_yield_10yr') }}              as ca_us_10y_spread_bp,

        -- Oil
        wti
    from m

)

select
    *,
    case when usd_cad > 0 and lag(usd_cad) over w > 0
         then ln(usd_cad / lag(usd_cad) over w) end         as usd_cad_logret,
    case when wti > 0 and lag(wti) over w > 0
         then ln(wti / lag(wti) over w) end                 as wti_logret,
    ca_us_2y_spread_bp  - lag(ca_us_2y_spread_bp)  over w   as d_ca_us_2y_spread_bp,
    ca_us_10y_spread_bp - lag(ca_us_10y_spread_bp) over w   as d_ca_us_10y_spread_bp
from levels
window w as (order by date_day)

Why case when … > 0? On 20 April 2020, WTI futures settled below zero, a historic anomaly caused by storage constraints. The logarithm of a negative number is undefined, so without the guard this model would fail or produce garbage. It's a reminder that economic data contains genuine extremes, and robust code anticipates them.

Why log returns? They add up over time (the log change over a month is the sum of daily log changes) and are roughly the percentage change for small moves. That's the standard in finance and international macro.

39.3 Rolling Volatility, Correlations and Betas (23 min)

Now the dynamics. We add rolling statistics over a 60-business-day window (about three months), producing NULL until the window is full, as rolling_avg did on Day 37.

First, a small generic macro for "rolling statistic with a minimum count". Create macros/rolling_stat.sql:

{#
  rolling_stat(): any aggregate over the last n rows, NULL unless all n rows have both inputs.
  expr        the aggregate expression, e.g. 'corr(a, b)' or 'stddev_samp(a)'
  count_expr  expression counted for completeness, e.g. 'a + b' (NULL if either is NULL)
#}
{% macro rolling_stat(expr, count_expr, n, order_by) -%}
    case
        when count({{ count_expr }}) over (order by {{ order_by }}
             rows between {{ n - 1 }} preceding and current row) = {{ n }}
        then {{ expr }} over (order by {{ order_by }}
             rows between {{ n - 1 }} preceding and current row)
    end
{%- endmacro %}

Then the final daily fact, models/marts/open_economy/fct_open_economy_daily.sql:

-- Grain: one row per weekday. Open-economy dashboard fact (notebook Modules 02 & 06).
{% set n = var('rolling_window_days', 60) %}

with d as (

    select * from {{ ref('int_open_economy__daily') }}

)

select
    *,

    -- Realised volatility of USD/CAD, annualised (≈252 trading days), in %
    {{ rolling_stat('stddev_samp(usd_cad_logret)', 'usd_cad_logret', n, 'date_day') }}
        * sqrt(252) * 100                                                   as usd_cad_vol_ann_pct,

    -- Rolling correlations of daily changes
    {{ rolling_stat('corr(usd_cad_logret, wti_logret)',
                    'usd_cad_logret + wti_logret', n, 'date_day') }}         as corr_usdcad_wti,
    {{ rolling_stat('corr(usd_cad_logret, d_ca_us_2y_spread_bp)',
                    'usd_cad_logret + d_ca_us_2y_spread_bp', n, 'date_day') }} as corr_usdcad_2y_spread,

    -- Rolling beta: % change in USD/CAD per 10bp widening of the CA–US 2y spread
    {{ rolling_stat('regr_slope(usd_cad_logret, d_ca_us_2y_spread_bp)',
                    'usd_cad_logret + d_ca_us_2y_spread_bp', n, 'date_day') }}
        * 10 * 100                                                          as beta_usdcad_per_10bp
from d

Add rolling_window_days: 60 to vars.

A negative corr_usdcad_2y_spread means: when Canadian 2-year yields rise relative to US ones, USD/CAD tends to fall (CAD strengthens), consistent with the differential story in 39.1. Check the sign in your data, and how it changes over time.

Tests and docs

# models/marts/open_economy/_open_economy__models.yml
version: 2

models:
  - name: fct_open_economy_daily
    description: >
      Daily open-economy fact for the Rates & FX and Open Economy dashboards.
      Grain: weekday. Rolling statistics use var('rolling_window_days') business days
      and are NULL until the window is complete.
    columns:
      - name: date_day
        data_tests: [unique, not_null]
      - name: corr_usdcad_wti
        data_tests:
          - within_range: {min_value: -1, max_value: 1}
      - name: corr_usdcad_2y_spread
        data_tests:
          - within_range: {min_value: -1, max_value: 1}
      - name: usd_cad_vol_ann_pct
        data_tests:
          - within_range: {min_value: 0, max_value: 50}

Correlations outside [−1, 1] would mean a bug. Testing mathematical bounds is cheap insurance.

dbt build --select +fct_open_economy_daily
dbt show --inline "
select date_day, round(usd_cad, 4) as usd_cad, round(ca_us_2y_spread_bp) as spread_2y,
       round(corr_usdcad_2y_spread, 2) as corr_spread, round(corr_usdcad_wti, 2) as corr_wti,
       round(usd_cad_vol_ann_pct, 1) as vol
from {{ ref('fct_open_economy_daily') }} order by date_day desc limit 10"

39.4 Monthly and Quarterly One-Big-Tables (23 min)

Monthly open-economy aggregates

models/intermediate/markets/int_open_economy__monthly.sql:

-- Grain: one row per month. Month-end FX, monthly averages of spreads and slopes,
-- monthly log changes, and how many business days contributed.
select
    cast(date_trunc('month', date_day) as date)              as month,
    arg_max(usd_cad, date_day)                               as usd_cad_eom,
    arg_max(wti, date_day)                                   as wti_eom,
    avg(ca_us_2y_spread_bp)                                  as ca_us_2y_spread_bp_avg,
    avg(ca_us_10y_spread_bp)                                 as ca_us_10y_spread_bp_avg,
    avg(policy_diff_bp)                                      as policy_diff_bp_avg,
    avg(ca_slope_bp)                                         as ca_slope_bp_avg,
    avg(us_slope_bp)                                         as us_slope_bp_avg,
    sum(usd_cad_logret)                                      as usd_cad_logret_month,
    sum(wti_logret)                                          as wti_logret_month,
    count(usd_cad)                                           as fx_days
from {{ ref('int_open_economy__daily') }}
group by 1

sum(usd_cad_logret) is the monthly log change, the additivity of log returns in action.

marts/macro/macro_monthly.sql: the monthly one-big-table

Port Day 20's macro_monthly, now with open-economy columns. Requirements:

• Grain: month from var('panel_start_date').
• From int_rates__monthly: all panel columns (generated from the seed, so US yields are included).
• From int_cpi__national_measures: headline_yoy, core_ex_fe_yoy, headline_ann_3m, median_major_yoy, diffusion_above_3, next_yoy_if_flat.
• From int_open_economy__monthly: all columns.
• Derived: real_rate, real_rate_core (use the Day 35 real_rate macro), usd_cad_12m_pct (12-month % change in month-end USD/CAD, gap-safe), in_dated_recession (overlaps_recession).

-- models/marts/macro/macro_monthly.sql
-- Grain: one row per month, Canada. Rates, FX, oil, CPI measures, real rates, open-economy aggregates.
with rates as (
    select * from {{ ref('int_rates__monthly') }}
    where month >= date '{{ var("panel_start_date") }}'
),

cpi as (
    select month, headline_yoy, core_ex_fe_yoy, headline_ann_3m,
           median_major_yoy, diffusion_above_3, next_yoy_if_flat
    from {{ ref('int_cpi__national_measures') }}
),

oe as (
    select * from {{ ref('int_open_economy__monthly') }}
),

joined as (
    select r.*, c.* exclude (month), o.* exclude (month)
    from rates as r
    left join cpi as c using (month)
    left join oe  as o using (month)
),

with_lags as (
    select *,
           lag(usd_cad_eom, 12) over (order by month) as _usd_cad_l12,
           lag(month, 12)       over (order by month) as _month_l12
    from joined
)

select
    * exclude (_usd_cad_l12, _month_l12),
    {{ real_rate('overnight_rate', 'headline_yoy') }}      as real_rate,
    {{ real_rate('overnight_rate', 'core_ex_fe_yoy') }}    as real_rate_core,
    case when _month_l12 = cast(month - interval 12 month as date)
         then {{ pct_change('usd_cad_eom', '_usd_cad_l12') }} end    as usd_cad_12m_pct,
    {{ overlaps_recession('month', "cast(month + interval 1 month - interval 1 day as date)") }}
                                                           as in_dated_recession
from with_lags

marts/macro/macro_quarterly.sql

Port Day 20's macro_quarterly with the same pattern: GDP from int_gdp__growth (plus deflator and cycle flags), complete-quarter averages from macro_monthly (require 3 months of data), and quarterly averages of the CA–US spreads. Because macro_quarterly reads macro_monthly, it is a mart reading a mart. Either accept this and document it (the quarterly table is explicitly a roll-up of the monthly one), or move the monthly logic into an intermediate model both read from. Discuss which you prefer, then implement it.

39.5 Levels vs Changes: a Spurious-Correlation Lesson (20 min)

The notebook's Module 06 computes a 12-month rolling correlation between USD/CAD levels and WTI levels. Let's compare that with correlations of monthly changes.

Create analyses/levels_vs_changes.sql:

with m as (
    select month, usd_cad_eom, wti_eom, usd_cad_logret_month, wti_logret_month
    from {{ ref('macro_monthly') }}
    where usd_cad_eom is not null and wti_eom is not null
),
rolled as (
    select
        month,
        {{ rolling_stat('corr(usd_cad_eom, wti_eom)', 'usd_cad_eom + wti_eom', 12, 'month') }}
            as corr_levels_12m,
        {{ rolling_stat('corr(usd_cad_logret_month, wti_logret_month)',
                        'usd_cad_logret_month + wti_logret_month', 12, 'month') }}
            as corr_changes_12m
    from m
)
select
    round(avg(abs(corr_levels_12m)), 2)                       as avg_abs_corr_levels,
    round(avg(abs(corr_changes_12m)), 2)                      as avg_abs_corr_changes,
    round(avg(case when abs(corr_levels_12m)  > 0.8 then 1.0 else 0 end), 2) as share_levels_above_08,
    round(avg(case when abs(corr_changes_12m) > 0.8 then 1.0 else 0 end), 2) as share_changes_above_08
from rolled

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

Typically, level correlations are much larger in absolute value and swing violently between +1 and −1. Over 12 points, any two trending series look strongly related, in one direction or the other. Correlations of changes are smaller but far more meaningful. This is the classic spurious regression problem (Granger and Newbold), and the reason the daily fact uses returns.

Dashboard design consequence: keep the levels chart (it's intuitive), but base any "relationship strength" indicator on changes. Put this reasoning in the column descriptions so dashboard users see it.

39.6 macro_kpis: the One-Screen Status Table (20 min)

The notebook's Module 07 shows a KPI grid with a coloured "signal" per indicator. The dbt version makes each value, its as-of date, and the rule behind its colour explicit.

Create models/marts/macro/macro_kpis.sql:

-- Grain: one row per KPI. Latest available value, its date, and a rule-based signal.
with m as (select * from {{ ref('macro_monthly') }}),
q as (select * from {{ ref('macro_quarterly') }}),
d as (select * from {{ ref('fct_open_economy_daily') }}),

{% set monthly_kpis = [
    {"pillar": "P1 Economy",  "kpi": "CPI headline YoY (%)",        "col": "headline_yoy"},
    {"pillar": "P1 Economy",  "kpi": "Unemployment rate (%)",       "col": "unemployment_rate"},
    {"pillar": "P3 Monetary", "kpi": "Overnight rate (%)",          "col": "overnight_rate"},
    {"pillar": "P3 Monetary", "kpi": "Real policy rate (%)",        "col": "real_rate"},
    {"pillar": "P4 Cycle",    "kpi": "OECD CLI (100 = trend)",      "col": "oecd_cli"},
] %}
{% set daily_kpis = [
    {"pillar": "P3 Monetary", "kpi": "CA 10y–2y slope (bp)",        "col": "ca_slope_bp"},
    {"pillar": "P5 Open",     "kpi": "USD/CAD",                     "col": "usd_cad"},
    {"pillar": "P5 Open",     "kpi": "CA–US 2y spread (bp)",        "col": "ca_us_2y_spread_bp"},
    {"pillar": "P5 Open",     "kpi": "USD/CAD vol, 60d ann. (%)",   "col": "usd_cad_vol_ann_pct"},
] %}

latest as (
    {% for k in monthly_kpis %}
    select '{{ k.pillar }}' as pillar, '{{ k.kpi }}' as kpi, '{{ k.col }}' as kpi_code,
           arg_max({{ k.col }}, month) as value, max(month) as as_of
    from m where {{ k.col }} is not null
    union all
    {% endfor %}
    {% for k in daily_kpis %}
    select '{{ k.pillar }}', '{{ k.kpi }}', '{{ k.col }}',
           arg_max({{ k.col }}, date_day), max(date_day)
    from d where {{ k.col }} is not null
    union all
    {% endfor %}
    select 'P1 Economy', 'Real GDP growth, q/q ann. (%)', 'real_qoq_ann',
           arg_max(real_qoq_ann, quarter), max(quarter)
    from q where real_qoq_ann is not null
)

select
    pillar,
    kpi,
    kpi_code,
    value,
    as_of,
    case kpi_code
        when 'headline_yoy'       then case when value between 1 and 3 then 'neutral'
                                            when value > 3 then 'warning' else 'warning' end
        when 'real_rate'          then case when value < 0 then 'stimulative' else 'restrictive' end
        when 'ca_slope_bp'        then case when value < 0 then 'negative' else 'neutral' end
        when 'real_qoq_ann'       then case when value < 0 then 'negative'
                                            when value < 1 then 'warning' else 'positive' end
        when 'unemployment_rate'  then 'neutral'
        else 'neutral'
    end as signal
from latest
order by pillar, kpi

• The KPI lists are data (lists of dicts), so adding a KPI is one line.
• Every KPI carries its as_of date, which fixes pitfall Q8 (a stale value presented as current).
• The signal rules are simple, visible and reviewable. On Days 41–50, Great Expectations can profile what "unusual" means statistically.

Add tests: kpi_code unique; as_of not null; signal in an accepted list.

Day 39 checklist

• I can explain the open-economy measures (differentials, slope, curvature, oil) and their caveats
• int_open_economy__daily computes spreads and log returns, robust to negative oil prices
• fct_open_economy_daily provides rolling volatility, correlations and betas, NULL until complete
• macro_monthly and macro_quarterly combine rates, CPI, GDP and open-economy measures without leaking incomplete periods
• I can demonstrate why correlations of levels mislead and changes don't
• macro_kpis shows each KPI with value, date and a transparent signal rule

dbt build && cd .. && git add . && git commit -m "Day 39: open-economy mart, monthly/quarterly OBTs, KPIs" && git push