Advanced DuckDB Features and a First Analytical Mart

17.1 ASOF Joins (25 min)

Economic data rarely lines up perfectly. A policy decision on 2024-06-05 needs the most recent 2-year yield on or before that date. There may be no yield observation on the exact day (weekends, holidays). An ASOF join matches each row to the latest row that doesn't come after it.

decisions                    yields (daily)                    ASOF result
2024-06-05  ───────────────► 2024-06-04  3.92  (latest ≤ date) 2024-06-05 → 3.92
2024-07-24  ───────────────► 2024-07-24  3.60                  2024-07-24 → 3.60

Create day17/asof.sql:

Example 1 — Market yields at each decision

WITH yields AS (
    SELECT CAST(date AS DATE) AS date, value AS yield_2yr
    FROM raw.observations
    WHERE name = 'yield_2yr'
)
SELECT d.decision_date, d.rate, d.change_bp,
       y.date AS yield_obs_date, y.yield_2yr
FROM raw.boc_decisions AS d
ASOF JOIN yields AS y
     ON d.decision_date >= y.date
ORDER BY d.decision_date;

The inequality in ON says "the yield date must not be after the decision date"; ASOF then picks the closest such row.

Example 2 — How did markets move around decisions?

Compare the 2-year yield the day before a decision with the yield a week later:

WITH yields AS (
    SELECT CAST(date AS DATE) AS date, value AS y2
    FROM raw.observations
    WHERE name = 'yield_2yr'
),
before AS (
    SELECT d.decision_date, d.change_bp, y.y2 AS y2_before
    FROM raw.boc_decisions AS d
    ASOF JOIN yields AS y ON d.decision_date - INTERVAL 1 DAY >= y.date
),
after AS (
    SELECT d.decision_date, y.y2 AS y2_after
    FROM raw.boc_decisions AS d
    ASOF JOIN yields AS y ON d.decision_date + INTERVAL 7 DAY >= y.date
)
SELECT decision_date, change_bp,
       ROUND(y2_before, 2) AS y2_before,
       ROUND(y2_after, 2)  AS y2_week_after,
       ROUND((y2_after - y2_before) * 100) AS move_bp
FROM before JOIN after USING (decision_date)
ORDER BY decision_date;

Economist's caution: a one-week window mixes the decision with other news. This is an event-study flavour, not causal identification. Causal methods come in Module 8 (Days 71–80).

Example 3 — The CPI reading known at each decision (with a publication lag)

CPI for month m is published during month m+1. A rough "what was known" rule is to use the CPI of two months before the decision month:

WITH cpi AS (
    SELECT month,
           100 * (cpi / LAG(cpi, 12) OVER (ORDER BY month) - 1) AS yoy
    FROM raw.statcan_cpi
    WHERE geo = 'Canada' AND component = 'All-items'
)
SELECT d.decision_date, d.rate,
       c.month AS cpi_month_used,
       ROUND(c.yoy, 1) AS cpi_yoy,
       ROUND(d.rate - c.yoy, 2) AS real_rate_at_decision
FROM raw.boc_decisions AS d
ASOF JOIN cpi AS c
     ON CAST(date_trunc('month', d.decision_date) - INTERVAL 2 MONTH AS DATE) >= c.month
ORDER BY d.decision_date;

Thinking about what was known when (data vintages and publication lags) is essential for honest forecasting on Days 61–70.

17.2 SQL Macros: Reusable Functions (20 min)

You've typed 100 * (x / LAG(x, 12) OVER (...) - 1) many times. DuckDB macros are SQL's version of Day 3 functions.

Scalar macros

CREATE OR REPLACE MACRO pct_change(new_value, old_value) AS
    100 * (new_value / old_value - 1);

CREATE OR REPLACE MACRO annualise(ratio, periods) AS
    100 * (POWER(ratio, periods) - 1);

CREATE OR REPLACE MACRO stance(rate) AS
    CASE WHEN rate < 2.25 THEN 'Stimulative'
         WHEN rate <= 2.75 THEN 'Neutral'
         ELSE 'Restrictive' END;

SELECT pct_change(169.0, 164.1)      AS yoy,
       annualise(1.008, 4)           AS ann_growth,
       stance(2.25)                  AS current_stance;

Macros can take a window expression as an argument:

SELECT month, cpi,
       ROUND(pct_change(cpi, LAG(cpi, 12) OVER (ORDER BY month)), 1) AS yoy
FROM raw.statcan_cpi
WHERE geo = 'Canada' AND component = 'All-items'
ORDER BY month DESC
LIMIT 3;

Table macros: parameterised queries

CREATE OR REPLACE MACRO series(series_name) AS TABLE
    SELECT month, value
    FROM scratch.monthly_long
    WHERE name = series_name;

SELECT * FROM series('fed_funds') ORDER BY month DESC LIMIT 3;
SELECT * FROM series('wti')       ORDER BY month DESC LIMIT 3;

Macros created in a database file are saved with it. dbt has its own, more powerful macro system (Jinja, Days 34–37), but the idea of write once, reuse everywhere is the same.

17.3 "Friendly SQL" in DuckDB (20 min)

DuckDB adds conveniences to standard SQL. They save a lot of typing, but they may not exist in other databases, so know which parts of your SQL are portable.

-- FROM-first: quick look at a table
FROM raw.observations LIMIT 3;

-- EXCLUDE: all columns except some
SELECT * EXCLUDE (fetched_at, source) FROM raw.observations LIMIT 3;

-- REPLACE: modify a column in place
SELECT * REPLACE (ROUND(value, 2) AS value) FROM scratch.monthly_long LIMIT 3;

-- COLUMNS: apply an expression to every matching column (regex)
SELECT month, ROUND(COLUMNS('yield_.*|overnight_rate|fed_funds'), 2)
FROM scratch.monthly_wide
ORDER BY month DESC
LIMIT 3;

-- GROUP BY ALL / ORDER BY ALL
SELECT name, year(month) AS yr, AVG(value)
FROM scratch.monthly_long
GROUP BY ALL
ORDER BY ALL
LIMIT 5;

-- Column aliases reused in the same SELECT
SELECT overnight_rate - fed_funds AS diff,
       diff * 100                  AS diff_bp
FROM scratch.monthly_wide
ORDER BY month DESC
LIMIT 3;

17.4 Performance Habits (13 min)

DuckDB is fast, but some habits matter as data grows.

See what DuckDB actually did

EXPLAIN ANALYZE
SELECT geo, AVG(cpi)
FROM raw.statcan_cpi
WHERE component = 'All-items' AND month >= DATE '2020-01-01'
GROUP BY geo;

The output shows each operator, how many rows flowed through it and how long it took. Look for the filter happening early, near the scan.

Habits

1. Filter early. Put WHERE conditions in the first CTE, so later steps process fewer rows.
2. Select only the columns you need. With Parquet, unused columns are never read (Day 13).
3. Materialise expensive, reused results as tables instead of recomputing views, which is dbt's table materialisation.
4. Don't worry about indexes. DuckDB's columnar engine rarely needs them for analytics.
5. Beware fan-out joins (Day 12): a row explosion is the most common cause of slow queries.

17.5 A Short SQL Style Guide (8 min)

Readable SQL is maintainable SQL. Adopt these conventions for the rest of the course:

-- 1. A header comment saying what the query produces and its grain
-- Output: one row per policy decision, with market context
WITH decisions AS (                              -- 2. CTE names are nouns: what they hold
    SELECT decision_date,                        -- 3. one column per line
           rate,
           change_bp
    FROM raw.boc_decisions
    WHERE decision_date >= DATE '2022-01-01'     -- 4. filter early
),

yields AS (
    SELECT CAST(date AS DATE) AS date,
           value              AS yield_2yr       -- 5. explicit, lowercase snake_case aliases
    FROM raw.observations
    WHERE name = 'yield_2yr'
)

SELECT d.decision_date,                          -- 6. qualify columns when joining
       d.change_bp,
       y.yield_2yr
FROM decisions AS d
ASOF JOIN yields AS y
     ON d.decision_date >= y.date
ORDER BY d.decision_date;

• Grain: always know (and write down) what one row represents.
• Keywords in CAPITALS, names in snake_case.
• One idea per CTE.
• An optional linter, pip install sqlfluff, can check style automatically. dbt projects often use it.

17.6 Capstone: marts.policy_cycles (40 min)

Build your first mart: a clean, analysis-ready table that answers a real question.

For each Bank of Canada tightening or easing cycle since 2022: when did it start and end, how many moves did it take, how large was it in total, and what were inflation, the real rate and the 2-year yield at the start and the end?

Definition of a cycle

• Only actual moves count (holds are ignored, so a pause doesn't end a cycle).
• A new cycle begins whenever a move's direction differs from the previous move's direction.

This is gaps-and-islands (Day 16) on the sequence of moves.

Requirements

Create sql/04_policy_cycles.sql that:

1. Builds marts.policy_cycles with one row per cycle and these columns: cycle_id, direction ('Tightening' / 'Easing'), start_date, end_date, n_moves, total_bp, rate_before, rate_after, duration_days, cpi_yoy_start, cpi_yoy_end, real_rate_start, real_rate_end, yield_2yr_start, yield_2yr_end.
2. Uses CPI with a 2-month publication lag (17.1, Example 3) and the 2-year yield from the day before the first move and the day of the last move (ASOF joins).
3. Includes a header comment stating the table's grain.

Check your result against the notebook's decision log: the 2022–23 tightening should total +475bp (0.25% → 5.00%) and the 2024–25 easing −275bp (5.00% → 2.25%).

-- marts.policy_cycles
-- Grain: one row per BoC policy cycle (a run of moves in the same direction).
-- Holds are ignored when defining cycles. Source: raw.boc_decisions,
-- raw.statcan_cpi (Canada, All-items), raw.observations (yield_2yr).

CREATE SCHEMA IF NOT EXISTS marts;

CREATE OR REPLACE TABLE marts.policy_cycles AS
WITH moves AS (
    SELECT decision_date, rate, change_bp,
           CASE WHEN change_bp > 0 THEN 'Tightening' ELSE 'Easing' END AS direction
    FROM raw.boc_decisions
    WHERE change_bp <> 0
),

marked AS (
    SELECT *,
           CASE WHEN direction = LAG(direction) OVER (ORDER BY decision_date)
                THEN 0 ELSE 1 END AS new_cycle
    FROM moves
),

numbered AS (
    SELECT *, SUM(new_cycle) OVER (ORDER BY decision_date) AS cycle_id
    FROM marked
),

cycles AS (
    SELECT cycle_id,
           ANY_VALUE(direction)                                 AS direction,
           MIN(decision_date)                                   AS start_date,
           MAX(decision_date)                                   AS end_date,
           COUNT(*)                                             AS n_moves,
           SUM(change_bp)                                       AS total_bp,
           arg_min(rate, decision_date)
             - arg_min(change_bp, decision_date) / 100.0        AS rate_before,
           arg_max(rate, decision_date)                         AS rate_after
    FROM numbered
    GROUP BY cycle_id
),

cpi AS (
    SELECT month,
           100 * (cpi / LAG(cpi, 12) OVER (ORDER BY month) - 1) AS yoy
    FROM raw.statcan_cpi
    WHERE geo = 'Canada' AND component = 'All-items'
),

yields AS (
    SELECT CAST(date AS DATE) AS date, value AS yield_2yr
    FROM raw.observations
    WHERE name = 'yield_2yr'
),

with_cpi_start AS (
    SELECT c.*, p.yoy AS cpi_yoy_start
    FROM cycles AS c
    ASOF JOIN cpi AS p
         ON CAST(date_trunc('month', c.start_date) - INTERVAL 2 MONTH AS DATE) >= p.month
),

with_cpi_end AS (
    SELECT c.*, p.yoy AS cpi_yoy_end
    FROM with_cpi_start AS c
    ASOF JOIN cpi AS p
         ON CAST(date_trunc('month', c.end_date) - INTERVAL 2 MONTH AS DATE) >= p.month
),

with_yield_start AS (
    SELECT c.*, y.yield_2yr AS yield_2yr_start
    FROM with_cpi_end AS c
    ASOF JOIN yields AS y ON c.start_date - INTERVAL 1 DAY >= y.date
),

with_yield_end AS (
    SELECT c.*, y.yield_2yr AS yield_2yr_end
    FROM with_yield_start AS c
    ASOF JOIN yields AS y ON c.end_date >= y.date
)

SELECT cycle_id,
       direction,
       start_date,
       end_date,
       n_moves,
       total_bp,
       rate_before,
       rate_after,
       end_date - start_date                   AS duration_days,
       ROUND(cpi_yoy_start, 1)                 AS cpi_yoy_start,
       ROUND(cpi_yoy_end, 1)                   AS cpi_yoy_end,
       ROUND(rate_before - cpi_yoy_start, 2)   AS real_rate_start,
       ROUND(rate_after  - cpi_yoy_end, 2)     AS real_rate_end,
       ROUND(yield_2yr_start, 2)               AS yield_2yr_start,
       ROUND(yield_2yr_end, 2)                 AS yield_2yr_end
FROM with_yield_end
ORDER BY start_date;

SELECT * FROM marts.policy_cycles;

Discussion questions

1. The real rate at the start of the 2022 tightening was deeply negative. What does that say about the stance when hiking began?
2. Did the 2-year yield move more or less than the policy rate over each cycle? What does that suggest about how much the market anticipated?
3. How would the table change if a hold ended a cycle? Which definition is more useful for your research question?

17.7 Wiring It into the Warehouse Build (10 min)

Make the decision log and the mart part of the reproducible pipeline.

1. Add the decision log to the raw layer. Create sql/02b_boc_decisions.sql by copying day11/decisions.sql and changing the table name to raw.boc_decisions.

2. Update load_warehouse.py so the SQL files run in order:

for sql_file in ["01_schemas.sql", "02_raw_tables.sql",
                 "02b_boc_decisions.sql", "04_policy_cycles.sql"]:
    run_sql_file(con, SQL_DIR / sql_file)

Put the 04_policy_cycles.sql run after raw.dim_series is created and before the checks.

3. Add checks for the mart to the CHECKS dictionary:

"policy cycles: moves don't add up": """
    SELECT COUNT(*) FROM marts.policy_cycles
    WHERE ROUND((rate_after - rate_before) * 100) <> total_bp
""",
"policy cycles: empty mart": """
    SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END FROM marts.policy_cycles
""",

4. Rebuild and export:

python load_warehouse.py
duckdb macro.duckdb -readonly -c "COPY marts.policy_cycles TO 'data/processed/policy_cycles.csv' (HEADER)"

The -c flag runs a single command and exits, which is handy in scripts.

Recap: Module 2 so far

Looking ahead: Days 18–20

You'll formalise everything into a proper staging layer for CPI and GDP. That means typed, gap-checked, documented views computing year-over-year changes and moving averages directly in SQL, feeding the macro data mart that dbt will manage from Day 31.

Day 17 checklist

• I can align data with different timing using ASOF JOIN, including publication lags
• I can write scalar and table macros
• I know DuckDB's friendly-SQL shortcuts and which features are portable
• I can read an EXPLAIN ANALYZE plan and apply basic performance habits
• I write SQL in a consistent, documented style with a stated grain
• I built, validated and exported marts.policy_cycles as part of the warehouse build

git add .
git commit -m "Day 17: ASOF joins, macros, policy_cycles mart"
git push