GDP, Mixed Frequencies and the Module 2 Capstone

20.1 Profiling and Staging GDP (23 min)

Profile first

SELECT DISTINCT "Estimates" FROM raw.statcan_gdp_full ORDER BY 1;
SELECT DISTINCT "Prices" FROM raw.statcan_gdp_full ORDER BY 1;
SELECT DISTINCT "Seasonal adjustment" FROM raw.statcan_gdp_full ORDER BY 1;
SELECT DISTINCT UOM, SCALAR_FACTOR FROM raw.statcan_gdp_full;
SELECT MIN(REF_DATE), MAX(REF_DATE) FROM raw.statcan_gdp_full;

Things to notice:

• Estimates lists the expenditure components: household consumption, government spending, business investment, exports, imports, and GDP itself.
• Prices distinguishes current prices (nominal), chained dollars (real) and price indexes. Note the exact labels; the chained-dollar reference year changes when StatCan rebases.
• SCALAR_FACTOR tells you values are in millions of dollars.
• Quarterly REF_DATE looks like 2026-04, the first month of the quarter (Day 7).

sql/11_stg_statcan__gdp.sql

-- staging.stg_statcan__gdp
-- Source:  raw.statcan_gdp_full (StatCan 36-10-0104-01, GDP expenditure-based, quarterly)
-- Grain:   one row per quarter × estimate × prices × seasonal adjustment
-- Units:   value_millions (dollars, millions) or index, see `unit`

CREATE OR REPLACE VIEW staging.stg_statcan__gdp AS
WITH source AS (
    SELECT * FROM raw.statcan_gdp_full
),

renamed AS (
    SELECT
        CAST(REF_DATE || '-01' AS DATE)      AS quarter,
        TRIM("Estimates")                    AS estimate,
        TRIM("Prices")                       AS prices,
        TRIM("Seasonal adjustment")          AS seasonal_adjustment,
        UOM                                  AS unit,
        SCALAR_FACTOR                        AS scalar_factor,
        VECTOR                               AS vector_id,
        TRY_CAST(VALUE AS DOUBLE)            AS value,
        NULLIF(TRIM(STATUS), '')             AS status_flag
    FROM source
    WHERE GEO = 'Canada'
)

SELECT * FROM renamed WHERE value IS NOT NULL;

Add tests:

-- sql/tests/gdp_unique_keys.sql
-- severity: error
SELECT quarter, estimate, prices, seasonal_adjustment, COUNT(*)
FROM staging.stg_statcan__gdp
GROUP BY ALL HAVING COUNT(*) > 1

-- sql/tests/gdp_quarter_starts.sql
-- severity: error
-- Quarterly dates must be Jan/Apr/Jul/Oct 1st
SELECT DISTINCT quarter FROM staging.stg_statcan__gdp
WHERE month(quarter) NOT IN (1, 4, 7, 10) OR day(quarter) <> 1

20.2 int_gdp__growth: Quarterly Growth Done Right (28 min)

Create sql/22_int_gdp__growth.sql. Check the exact prices labels from your profiling and adjust the two constants if needed.

-- intermediate.int_gdp__growth
-- Grain:  one row per quarter (Canada, seasonally adjusted at annual rates)
-- Adds:   real GDP growth (qoq, annualised compound & simple, yoy),
--         nominal growth, implicit GDP deflator and its inflation.

CREATE OR REPLACE VIEW intermediate.int_gdp__growth AS
WITH gdp AS (
    SELECT quarter,
           MAX(value) FILTER (WHERE prices LIKE 'Chained%')      AS real_gdp,
           MAX(value) FILTER (WHERE prices = 'Current prices')   AS nominal_gdp
    FROM staging.stg_statcan__gdp
    WHERE estimate = 'Gross domestic product at market prices'
      AND seasonal_adjustment = 'Seasonally adjusted at annual rates'
    GROUP BY quarter
),

with_deflator AS (
    SELECT *, 100 * nominal_gdp / real_gdp AS gdp_deflator
    FROM gdp
),

lagged AS (
    SELECT *,
           LAG(real_gdp, 1)     OVER w AS real_l1,
           LAG(real_gdp, 4)     OVER w AS real_l4,
           LAG(nominal_gdp, 4)  OVER w AS nominal_l4,
           LAG(gdp_deflator, 4) OVER w AS deflator_l4,
           LAG(quarter, 1)      OVER w AS quarter_l1,
           LAG(quarter, 4)      OVER w AS quarter_l4
    FROM with_deflator
    WINDOW w AS (ORDER BY quarter)
),

growth AS (
    SELECT quarter, real_gdp, nominal_gdp, gdp_deflator,
           CASE WHEN quarter_l1 = CAST(quarter - INTERVAL 3 MONTH AS DATE)
                THEN 100 * (real_gdp / real_l1 - 1) END                   AS real_qoq,
           CASE WHEN quarter_l1 = CAST(quarter - INTERVAL 3 MONTH AS DATE)
                THEN 100 * (POWER(real_gdp / real_l1, 4) - 1) END         AS real_qoq_ann,
           CASE WHEN quarter_l1 = CAST(quarter - INTERVAL 3 MONTH AS DATE)
                THEN 400 * (real_gdp / real_l1 - 1) END                   AS real_qoq_ann_simple,
           CASE WHEN quarter_l4 = CAST(quarter - INTERVAL 12 MONTH AS DATE)
                THEN 100 * (real_gdp / real_l4 - 1) END                   AS real_yoy,
           CASE WHEN quarter_l4 = CAST(quarter - INTERVAL 12 MONTH AS DATE)
                THEN 100 * (nominal_gdp / nominal_l4 - 1) END             AS nominal_yoy,
           CASE WHEN quarter_l4 = CAST(quarter - INTERVAL 12 MONTH AS DATE)
                THEN 100 * (gdp_deflator / deflator_l4 - 1) END           AS deflator_yoy
    FROM lagged
)

SELECT *,
       CASE WHEN COUNT(real_qoq_ann) OVER w4 = 4
            THEN AVG(real_qoq_ann) OVER w4 END                            AS real_qoq_ann_ma4
FROM growth
WINDOW w4 AS (ORDER BY quarter RANGE BETWEEN INTERVAL 9 MONTH PRECEDING AND CURRENT ROW);

Notes on the economics:

• SAAR: StatCan publishes quarterly GDP "seasonally adjusted at annual rates", meaning the quarterly level is multiplied by 4. Growth rates are unaffected by that scaling.
• Compound vs simple annualisation: the notebook uses pct_change() * 400 (simple). We keep both, so you can see the difference, which is largest in extreme quarters such as 2020.
• Implicit deflator = nominal ÷ real × 100. Its growth is economy-wide inflation (including investment and exports), a useful comparison with CPI.
• The 4-quarter window uses RANGE … 9 MONTH PRECEDING: four quarter-start dates span 9 months.

SELECT quarter,
       ROUND(real_qoq_ann, 1)        AS qoq_ann,
       ROUND(real_qoq_ann_simple, 1) AS qoq_ann_simple,
       ROUND(real_yoy, 1)            AS yoy,
       ROUND(deflator_yoy, 1)        AS deflator_yoy
FROM intermediate.int_gdp__growth
ORDER BY quarter DESC
LIMIT 6;

A word on chained dollars

Real GDP components in chained dollars are not additive: consumption + investment + … doesn't exactly equal chained real GDP, except in the reference year. That's why StatCan publishes separate contributions to percent change. If you ever compute growth contributions from chained components yourself, you'll get a residual. Report it, just as with CPI (19.5).

Try it 20.2

Which five quarters since 2000 had the largest gap between compound and simple annualised growth? What do they have in common?

SELECT quarter,
       ROUND(real_qoq_ann, 1)                               AS compound,
       ROUND(real_qoq_ann_simple, 1)                        AS simple,
       ROUND(ABS(real_qoq_ann - real_qoq_ann_simple), 2)    AS gap
FROM intermediate.int_gdp__growth
WHERE quarter >= DATE '2000-01-01'
ORDER BY gap DESC NULLS LAST
LIMIT 5;

20.3 Recession Flags: Rules of Thumb vs Dated Cycles (18 min)

A popular rule of thumb calls a technical recession two consecutive quarters of negative real GDP growth. Economists who date business cycles (the C.D. Howe Institute's Business Cycle Council in Canada, used by the notebook) look at depth, duration and breadth, so the two can disagree.

Add to sql/22_int_gdp__growth.sql:

-- intermediate.int_gdp__cycle_flags
-- Grain: one row per quarter
CREATE OR REPLACE VIEW intermediate.int_gdp__cycle_flags AS
WITH g AS (
    SELECT quarter, real_qoq,
           real_qoq < 0                               AS is_negative,
           LAG(real_qoq)  OVER (ORDER BY quarter) < 0 AS prev_negative,
           LEAD(real_qoq) OVER (ORDER BY quarter) < 0 AS next_negative
    FROM intermediate.int_gdp__growth
)
SELECT g.quarter,
       g.real_qoq,
       COALESCE(g.is_negative AND (g.prev_negative OR g.next_negative), false)
                                                   AS technical_recession,
       EXISTS (
           SELECT 1 FROM seeds.seed_recession_bands AS b
           WHERE g.quarter <= b.end_date
             AND CAST(g.quarter + INTERVAL 3 MONTH - INTERVAL 1 DAY AS DATE) >= b.start_date
       )                                           AS in_dated_recession
FROM g;

• A quarter is flagged technical_recession if it's negative and its neighbour before or after is also negative, so both quarters of each pair are flagged.
• in_dated_recession checks whether the quarter overlaps a band in the seed table. Two date ranges overlap when each starts before the other ends.

Compare the two definitions:

SELECT quarter, ROUND(real_qoq, 2) AS qoq, technical_recession, in_dated_recession
FROM intermediate.int_gdp__cycle_flags
WHERE technical_recession OR in_dated_recession
ORDER BY quarter;

Discussion: where do they disagree, and why? A recession can be dated without two negative quarters, and a technical recession may be too shallow or brief to be dated. The notebook's "Oil shock" band of 2015 is a good case to look at. Definitions are choices, so seed them, document them and test them.

20.4 Formalising the Rates Panel (20 min)

The scratch.monthly_* views from Day 14 did useful work, but scratch is for experiments. Let's promote them properly.

sql/12_stg_macro__observations.sql: staging for the Day 10 pipeline output:

-- staging.stg_macro__observations
-- Source: raw.observations (macro_extract pipeline: BoC, FRED, StatCan vectors)
-- Grain:  one row per series × observation date
CREATE OR REPLACE VIEW staging.stg_macro__observations AS
SELECT CAST(date AS DATE)  AS obs_date,
       series_id,
       name                AS series_name,
       source,
       value,
       fetched_at
FROM raw.observations;

sql/23_int_rates__monthly.sql: monthly aggregation with explicit rules, then wide:

-- intermediate.int_rates__monthly
-- Grain: one row per month. Policy rates = last value in month; others = monthly mean.
CREATE OR REPLACE VIEW intermediate.int_rates__monthly AS
WITH monthly_long AS (
    SELECT CAST(date_trunc('month', obs_date) AS DATE) AS month,
           series_name,
           CASE WHEN series_name IN ('overnight_rate', 'fed_funds')
                THEN arg_max(value, obs_date)
                ELSE AVG(value) END                    AS value
    FROM staging.stg_macro__observations
    GROUP BY ALL
),

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 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;

Could the aggregation rule live in a seed (seed_series_rules with series_name, monthly_agg) instead of a CASE? Yes, and that's the better design once you have dozens of series. Try it as a stretch goal.

20.5 Capstone: The Macro Marts (33 min)

Marts are tables (materialised), built for consumers: the dashboard layer (as in the notebook), forecasting (Days 61–70) and causal models (Days 71–80).

sql/30_macro_monthly.sql

Requirements:

• Grain: one row per month from 2006-01-01.
• Columns: everything from int_rates__monthly; the key CPI measures from int_cpi__national_measures; real_rate (overnight − headline YoY) and real_rate_core (overnight − core ex food & energy YoY); in_dated_recession (month overlaps a seed band).
• A header comment and COMMENT ON TABLE.

-- marts.macro_monthly
-- Grain:  one row per month, Canada, from 2006-01.
-- Use:    dashboards (notebook Modules 01–06), forecasting features.
-- Notes:  CPI momentum measures are NSA-based. Real rates use contemporaneous CPI
--         (ignores the publication lag — see marts.policy_cycles for a lag-aware version).

CREATE OR REPLACE TABLE marts.macro_monthly AS
WITH rates AS (
    SELECT * FROM intermediate.int_rates__monthly
    WHERE month >= DATE '2006-01-01'
),

cpi AS (
    SELECT month, headline_yoy, headline_ann_3m, headline_yoy_ma3, headline_yoy_ma12,
           core_ex_fe_yoy, shelter_yoy, energy_yoy, food_yoy,
           median_major_yoy, diffusion_above_3, next_yoy_if_flat
    FROM intermediate.int_cpi__national_measures
),

joined AS (
    SELECT r.*, c.* EXCLUDE (month)
    FROM rates AS r
    LEFT JOIN cpi AS c USING (month)
)

SELECT j.*,
       j.overnight_rate - j.headline_yoy    AS real_rate,
       j.overnight_rate - j.core_ex_fe_yoy  AS real_rate_core,
       EXISTS (
           SELECT 1 FROM seeds.seed_recession_bands AS b
           WHERE j.month <= b.end_date
             AND CAST(j.month + INTERVAL 1 MONTH - INTERVAL 1 DAY AS DATE) >= b.start_date
       )                                    AS in_dated_recession
FROM joined AS j
ORDER BY month;

COMMENT ON TABLE marts.macro_monthly IS
    'Monthly Canadian macro panel: rates, FX, oil, CPI measures, real rates. Grain: month.';

sql/31_macro_quarterly.sql

Requirements:

• Grain: one row per quarter from 2006.
• All columns from int_gdp__growth and int_gdp__cycle_flags.
• Quarterly averages of overnight_rate, headline_yoy, core_ex_fe_yoy, real_rate, unemployment_rate, usd_cad and spread_10_2_bp from marts.macro_monthly, only for complete quarters (3 months of data); otherwise NULL.
• real_gdp_trillions: real GDP converted from millions for readability.

-- marts.macro_quarterly
-- Grain:  one row per quarter, Canada, from 2006-Q1.
-- Monthly indicators are quarterly averages over complete quarters only.

CREATE OR REPLACE TABLE marts.macro_quarterly AS
WITH monthly_to_quarter AS (
    SELECT CAST(date_trunc('quarter', month) AS DATE) AS quarter,
           COUNT(headline_yoy)                         AS months_with_cpi,
           AVG(overnight_rate)                         AS overnight_rate_avg,
           AVG(headline_yoy)                           AS headline_yoy_avg,
           AVG(core_ex_fe_yoy)                         AS core_ex_fe_yoy_avg,
           AVG(real_rate)                              AS real_rate_avg,
           AVG(unemployment_rate)                      AS unemployment_rate_avg,
           AVG(usd_cad)                                AS usd_cad_avg,
           AVG(spread_10_2_bp)                         AS spread_10_2_bp_avg
    FROM marts.macro_monthly
    GROUP BY 1
),

complete AS (
    SELECT quarter,
           COLUMNS(* EXCLUDE (quarter, months_with_cpi))
    FROM monthly_to_quarter
    WHERE months_with_cpi = 3
)

SELECT g.quarter,
       year(g.quarter)                    AS yr,
       quarter(g.quarter)                 AS qtr,
       g.real_gdp / 1e6                   AS real_gdp_trillions,
       g.* EXCLUDE (quarter),
       f.technical_recession,
       f.in_dated_recession,
       c.* EXCLUDE (quarter)
FROM intermediate.int_gdp__growth       AS g
LEFT JOIN intermediate.int_gdp__cycle_flags AS f USING (quarter)
LEFT JOIN complete                      AS c USING (quarter)
WHERE g.quarter >= DATE '2006-01-01'
ORDER BY g.quarter;

COMMENT ON TABLE marts.macro_quarterly IS
    'Quarterly Canadian macro panel: GDP growth, deflator, recession flags, quarterly averages.';

Mart tests

-- sql/tests/macro_monthly_unique.sql
-- severity: error
SELECT month, COUNT(*) FROM marts.macro_monthly GROUP BY month HAVING COUNT(*) > 1

-- sql/tests/macro_monthly_no_gaps.sql
-- severity: error
SELECT MIN(month), MAX(month), COUNT(*)
FROM marts.macro_monthly
HAVING date_diff('month', MIN(month), MAX(month)) + 1 <> COUNT(*)

-- sql/tests/macro_quarterly_growth_range.sql
-- severity: error
-- Even 2020's extremes fall well inside ±60% annualised
SELECT quarter, real_qoq_ann FROM marts.macro_quarterly
WHERE real_qoq_ann IS NOT NULL AND ABS(real_qoq_ann) > 60

-- sql/tests/macro_monthly_real_rate_range.sql
-- severity: warn
SELECT month, real_rate FROM marts.macro_monthly
WHERE real_rate IS NOT NULL AND ABS(real_rate) > 15

20.6 One-Command Pipeline · Module 2 Recap (15 min)

Update load_warehouse.py to run every SQL file in order

Replace the hard-coded list from Day 17 with:

for path in sorted(SQL_DIR.glob("*.sql")):
    run_sql_file(con, path)

Only files directly in sql/ match (not sql/tests/), and the numeric prefixes guarantee the order: schemas → raw → seeds → staging → intermediate → marts. Keep the raw.dim_series creation from Day 13 before this loop, and move the Python CHECKS from Days 13 and 17 into sql/tests/ files so all tests live in one place.

Then export the marts for the dashboard notebook:

import os
os.makedirs("data/marts", exist_ok=True)
for table in ["macro_monthly", "macro_quarterly", "policy_cycles"]:
    con.execute(f"COPY marts.{table} TO 'data/marts/{table}.parquet' (FORMAT parquet)")

pipeline.sh: extract → load → test

#!/usr/bin/env bash
# pipeline.sh — rebuild everything. Stops at the first failing step.
set -e
source .venv/bin/activate
python run_extract.py
python load_warehouse.py
python run_tests.py
echo "✓ Pipeline complete: data/marts/ is up to date"

chmod +x pipeline.sh
./pipeline.sh

set -e makes the script stop as soon as any command fails. This works because every Python script returns a meaningful exit code (Day 10).

Note: run_extract.py exits with code 2 when some series fail. Under set -e that stops the pipeline. Is that what you want? Discuss, then decide (for example, python run_extract.py || [ $? -eq 2 ] tolerates partial failures). Prefect (Days 51–60) gives you much finer control over exactly these decisions.

Using the marts in the notebook

In the course notebook (or a new one), Cells 3–5 can now be replaced by:

import pandas as pd
monthly = pd.read_parquet("data/marts/macro_monthly.parquet")
quarterly = pd.read_parquet("data/marts/macro_quarterly.parquet")

Every chart in Modules 01–06 then draws from tested, documented tables instead of ad-hoc transformations.

Module 2 recap

APIs ──► macro_extract (Days 8–10)
          │
          ▼
      raw.*        all columns, as text                      Day 13, 18
          │
      seeds.*      geo codes, recession bands, weights       Day 18
          │
      staging.*    stg_statcan__cpi, stg_statcan__gdp,        Days 18, 20
                   stg_macro__observations
          │
      intermediate.*  int_cpi__growth / __smoothed /          Days 19–20
                      __national_measures, int_gdp__growth /
                      __cycle_flags, int_rates__monthly
          │
      marts.*      macro_monthly, macro_quarterly,            Days 17, 20
                   policy_cycles
          │
      sql/tests/*  uniqueness, gaps, ranges, freshness        Days 18–20

What still hurts, and what fixes it next

Day 20 checklist

• I profiled and staged the GDP table, including units and labels
• I compute quarterly growth (qoq, annualised compound and simple, yoy), the deflator and 4-quarter averages, gap-safely
• I can compare rule-of-thumb and dated recession definitions
• I promoted scratch work into proper staging and intermediate models
• I built monthly and quarterly marts, avoiding incomplete-quarter averages
• One command extracts, builds, tests and exports everything

./pipeline.sh
git add .
git commit -m "Day 20: GDP staging, macro marts, one-command pipeline — Module 2 complete"
git push