DRY Principles and dbt Macros

35.1 DRY: Spotting Repetition Worth Removing (15 min)

DRY means Don't Repeat Yourself: every piece of knowledge should have one authoritative home. In our project, repeated knowledge includes:

But DRY has a limit. Don't abstract two things just because their SQL looks similar. Abstract when they share meaning, so that a change to one should always change the other. A macro that hides simple SQL behind ten parameters is worse than the repetition it removed.

Test for a good macro: could you explain what it does to an economist in one sentence, without mentioning SQL?

35.2 Macro Basics: Small Scalar Macros (25 min)

A macro is a reusable piece of Jinja that returns text, usually a SQL fragment. You wrote one on Day 32 (generate_schema_name). Macros live in macros/.

Syntax

{% macro macro_name(arg1, arg2='default') %}
    ... Jinja + SQL text ...
{% endmacro %}

Call it anywhere with {{ macro_name(...) }}.

Example 1 — Percentage change

Create macros/economics.sql:

{% macro pct_change(new_value, old_value) -%}
    (100.0 * ({{ new_value }} / nullif({{ old_value }}, 0) - 1))
{%- endmacro %}


{% macro annualise(ratio, periods_per_year) -%}
    (100.0 * (power({{ ratio }}, {{ periods_per_year }}) - 1))
{%- endmacro %}


{% macro bp(pp_expression) -%}
    ({{ pp_expression }} * 100)
{%- endmacro %}

Use them:

select
    month,
    {{ pct_change('cpi', 'lag(cpi, 12) over (order by month)') }}  as yoy,
    {{ bp('yield_10yr - yield_2yr') }}                             as spread_bp

The arguments are strings of SQL, pasted into the template. The macro doesn't see data, only text (Day 33's "Jinja runs first").

nullif(x, 0) returns NULL when x is zero, so a zero base yields NULL instead of an error. It's a small robustness bonus now written once.

Example 2 — Monthly aggregation rule

{% macro monthly_value(value_col, date_col, agg_col) -%}
    case
        when {{ agg_col }} = 'last' then arg_max({{ value_col }}, {{ date_col }})
        else avg({{ value_col }})
    end
{%- endmacro %}

Example 3 — Recession overlap

Create macros/calendar.sql:

{% macro overlaps_recession(period_start, period_end) -%}
    exists (
        select 1
        from {{ ref('seed_recession_bands') }} as _rb
        where {{ period_start }} <= _rb.end_date
          and {{ period_end }}   >= _rb.start_date
    )
{%- endmacro %}

In dim_date:

    {{ overlaps_recession('date_day', 'date_day') }}   as in_dated_recession

And for a month (used on Days 38–40):

    {{ overlaps_recession('month', "cast(month + interval 1 month - interval 1 day as date)") }}

Because the macro calls ref('seed_recession_bands'), dbt records the dependency in the model that calls it, so the lineage graph stays correct.

Try it 35.2

Write a macro real_rate(nominal_col, inflation_col) returning the approximate real rate, and a second one real_rate_exact(...) using the Fisher equation (Day 1). Test both with dbt show --inline "select {{ real_rate('2.25', '3.0') }} as r, {{ real_rate_exact('2.25', '3.0') }} as r_exact".

{% macro real_rate(nominal_col, inflation_col) -%}
    ({{ nominal_col }} - {{ inflation_col }})
{%- endmacro %}

{% macro real_rate_exact(nominal_col, inflation_col) -%}
    (100.0 * ((1 + {{ nominal_col }} / 100.0) / (1 + {{ inflation_col }} / 100.0) - 1))
{%- endmacro %}

35.3 A Model-Generating Macro: growth_rates() (33 min)

Day 33's horizons loop lived inside int_cpi__growth. GDP needs the same logic with quarters. Let's write it once.

Create macros/growth_rates.sql:

{#
  growth_rates(): gap-safe growth measures for any time series.

  relation      model/ref to read from
  value_col     column holding the level (e.g. 'cpi', 'real_gdp')
  date_col      period column (first day of month or quarter)
  partition_by  list of columns identifying each series (may be empty)
  horizons      list of {"name", "lag", "power"} (power = annualisation exponent)
  step_months   months per period: 1 for monthly, 3 for quarterly
  keep_cols     extra columns to carry through unchanged

  A measure is NULL unless the comparison period is exactly lag × step_months earlier.
#}
{% macro growth_rates(relation, value_col, date_col, partition_by=[], horizons=[],
                      step_months=1, keep_cols=[]) %}

with _base as (

    select * from {{ relation }}

),

_lagged as (

    select
        *,
        {%- for l in horizons | map(attribute='lag') | unique %}
        lag({{ value_col }}, {{ l }}) over _w  as _{{ value_col }}_l{{ l }},
        lag({{ date_col }}, {{ l }})  over _w  as _{{ date_col }}_l{{ l }}{{ ',' if not loop.last }}
        {%- endfor %}
    from _base
    window _w as (
        {% if partition_by %}partition by {{ partition_by | join(', ') }}{% endif %}
        order by {{ date_col }}
    )

)

select
    {{ date_col }},
    {%- for c in partition_by + keep_cols %}
    {{ c }},
    {%- endfor %}
    {{ value_col }},
    {%- for h in horizons %}
    case
        when _{{ date_col }}_l{{ h.lag }}
             = cast({{ date_col }} - interval {{ h.lag * step_months }} month as date)
        then {{ annualise(value_col ~ ' / _' ~ value_col ~ '_l' ~ h.lag, h.power) }}
    end as {{ h.name }}{{ ',' if not loop.last }}
    {%- endfor %}
from _lagged

{% endmacro %}

New Jinja features here:

• {# … #} at the top documents the macro for the next reader.
• partition_by + keep_cols: lists can be concatenated, as in Python.
• value_col ~ ' / _' ~ …: ~ is Jinja's string concatenation, used to build an argument for another macro (annualise). Macros can call macros.
• Internal names start with _ (_base, _w, _cpi_l12) to avoid clashing with the caller's columns.

Put the horizons in vars

In dbt_project.yml:

vars:
  monthly_horizons:
    - {name: mom,    lag: 1,  power: 1}
    - {name: ann_3m, lag: 3,  power: 4}
    - {name: ann_6m, lag: 6,  power: 2}
    - {name: yoy,    lag: 12, power: 1}
  quarterly_horizons:
    - {name: real_qoq,     lag: 1, power: 1}
    - {name: real_qoq_ann, lag: 1, power: 4}
    - {name: real_yoy,     lag: 4, power: 1}

The CPI growth model becomes six lines

-- models/intermediate/cpi/int_cpi__growth.sql
-- Grain: month × geography × component. Gap-safe growth rates (%).
{{ growth_rates(
    relation     = ref('int_cpi__active'),
    value_col    = 'cpi',
    date_col     = 'month',
    partition_by = ['geo', 'component'],
    keep_cols    = ['geo_code', 'geo_level'],
    horizons     = var('monthly_horizons'),
    step_months  = 1
) }}

dbt compile --select int_cpi__growth
dbt build --select int_cpi__growth+

Read the compiled SQL: it's the familiar Day 19 logic. Then re-run the regression analysis. Zero differences means the refactor preserved the numbers.

Column order note: the macro outputs date, partitions, keep_cols, value, measures. If a downstream model used select * and relied on the old column order, it may break. That's one reason to prefer explicit column lists downstream.

35.4 run_query: Configuration from Seeds (23 min)

Day 33 left a known duplication: vars.rate_series repeats seed_series.csv. Jinja can run a query at compile time with run_query and use the result to write SQL.

Step 1 — Mark panel membership in the seed

Add a column to seeds/seed_series.csv:

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

and type it in seeds/_seeds.yml (in_rates_panel: boolean). Run dbt seed --select seed_series.

Step 2 — A macro that reads the seed

Create macros/get_series_names.sql:

{% macro get_series_names(where=none) %}

    {% set query %}
        select series_name
        from {{ ref('seed_series') }}
        {% if where %}where {{ where }}{% endif %}
        order by series_name
    {% endset %}

    {% if execute %}
        {% set results = run_query(query) %}
        {% set names = results.columns[0].values() %}
    {% else %}
        {% set names = [] %}
    {% endif %}

    {{ return(names) }}

{% endmacro %}

New ideas:

• {% set query %} … {% endset %} captures a block of text into a variable.
• run_query(query) runs SQL against the target and returns a table. .columns[0].values() extracts the first column as a list.
• execute is false while dbt is parsing the project (building the DAG, when the database may not even be ready) and true when it actually runs. Always guard run_query with {% if execute %}.
• {{ return(names) }} makes the macro return a Python-like value (a list) instead of text.

Step 3 — Use it

-- models/intermediate/macro/int_rates__monthly.sql
-- depends_on: {{ ref('seed_series') }}
-- Grain: one row per month. Columns generated from seed_series (in_rates_panel = true).
{% set panel_series = get_series_names(where="in_rates_panel") %}

with wide as (

    select
        month,
        {%- for s in panel_series %}
        max(value) filter (where series_name = '{{ s }}') as {{ s }}{{ ',' if not loop.last }}
        {%- endfor %}
    from {{ ref('int_rates__monthly_long') }}
    group by month

)

select
    *,
    overnight_rate - fed_funds        as boc_fed_diff,
    {{ bp('yield_10yr - yield_2yr') }} as spread_10_2_bp,
    1 / cad_usd                       as usd_cad
from wide

The -- depends_on: comment is a recommended dbt pattern: it guarantees dbt registers the dependency on seed_series, so the seed is always built before this model.

Now remove rate_series from vars. Adding a series to the panel means one CSV row, the same file that already drives ingestion checks.

Trade-off: run_query makes compilation depend on the database. A model can't compile until the seed exists, and every compile runs a small query. It's worth it for configuration tables like this; don't use it for anything large.

Try it 35.4

Use get_series_names(where="frequency = 'daily'") inside dbt show --inline to print the daily series as a single comma-separated string.

dbt show --inline "select '{{ get_series_names(where=\"frequency = 'daily'\") | join(', ') }}' as daily_series"

35.5 Debugging and Documenting Macros (18 min)

Seeing what a macro produced

• dbt compile --select model and read target/compiled/...: always the first step.
• {{ log("panel series: " ~ panel_series, info=True) }} prints to the terminal while dbt runs. Remove it once things work.
• dbt run-operation <macro> --args '{...}' runs a macro on its own (Day 37 uses this).

Common errors

Documenting macros

Create macros/_macros.yml:

version: 2

macros:
  - name: growth_rates
    description: >
      Gap-safe growth measures for any time series. A measure is NULL unless the
      comparison period is exactly lag × step_months earlier. Annualisation via `power`.
    arguments:
      - name: relation
        type: relation
        description: "Model to read from (use ref())."
      - name: horizons
        type: list
        description: "List of {name, lag, power}; see vars.monthly_horizons."
      - name: step_months
        type: integer
        description: "1 for monthly data, 3 for quarterly."

  - name: get_series_names
    description: "Series names from seed_series, optionally filtered by a SQL condition."

  - name: overlaps_recession
    description: "True if [period_start, period_end] overlaps any band in seed_recession_bands."

Macro documentation appears on the docs site (dbt docs generate), next to the models that use it.

35.6 Mini-Project: DRY the CPI, GDP and Rates Models (23 min)

1. GDP. Split int_gdp__growth into:
   • int_gdp__levels (view): quarter, real_gdp, nominal_gdp, gdp_deflator (Day 20's first CTEs);
   • int_gdp__growth: growth_rates(ref('int_gdp__levels'), 'real_gdp', 'quarter', horizons=var('quarterly_horizons'), step_months=3, keep_cols=['nominal_gdp', 'gdp_deflator']).
2. Rates. int_rates__monthly_long uses the monthly_value macro; int_rates__monthly uses get_series_names.
3. Calendar. dim_date uses overlaps_recession.
4. Everywhere: replace hand-written 100 * (a / b - 1) with pct_change.
5. dbt build succeeds and the regression analysis shows no differences.
6. Count lines of SQL before and after:

git stash && find models -name "*.sql" | xargs cat | wc -l && git stash pop
find models macros -name "*.sql" | xargs cat | wc -l

-- models/intermediate/gdp/int_gdp__levels.sql
-- Grain: one row per quarter (Canada, SAAR). Levels in $ millions; deflator 100 = base year.
select
    quarter,
    max(value) filter (where prices like 'Chained%')       as real_gdp,
    max(value) filter (where prices = 'Current prices')    as nominal_gdp,
    100 * nominal_gdp / real_gdp                           as gdp_deflator
from {{ ref('stg_statcan__gdp') }}
where estimate = 'Gross domestic product at market prices'
  and seasonal_adjustment = 'Seasonally adjusted at annual rates'
group by quarter

-- models/intermediate/gdp/int_gdp__growth.sql
-- Grain: one row per quarter. Gap-safe real GDP growth (%).
{{ growth_rates(
    relation    = ref('int_gdp__levels'),
    value_col   = 'real_gdp',
    date_col    = 'quarter',
    keep_cols   = ['nominal_gdp', 'gdp_deflator'],
    horizons    = var('quarterly_horizons'),
    step_months = 3
) }}

Day 35 checklist

• I can tell repetition of knowledge (worth a macro) from coincidental similarity (not worth it)
• I write scalar macros with arguments and defaults, and macros that call macros
• growth_rates() generates gap-safe measures for monthly and quarterly data from vars
• I use run_query safely (execute, depends_on) to drive SQL from seeds
• I debug macros with compiled SQL and log(), and document them in YAML

dbt build && cd .. && git add . && git commit -m "Day 35: macros, growth_rates, run_query from seeds" && git push