Macros

SQL functions, written in Jinja

A macro is a reusable snippet of SQL with parameters. Anything between {% … %} or {{ … }} in a model is Jinja — dbt renders it to plain SQL before Snowflake sees it. You have been using macros from the start: ref() and source() are macros. The project keeps shared ones in macros/.

A familiar pain point: organisation codes

Provider, practice and ICB codes arrive in inconsistent forms. Feeds often carry site-level codes with suffixes (RRP00) where reference data holds the parent organisation (RRP) — so joins to the organisation dictionary quietly return nothing. The project handles this once, in a macro:

select
    {{ clean_organisation_id('provider_code') }} as provider_code,
    ...
from {{ ref('stg_some_activity_feed') }}

clean_organisation_id() keeps the code if it is a recognised organisation, and otherwise falls back to the 3-character parent code. Every model that uses it resolves codes the same way — and when the rule needs to change, it changes in one place.

Another example: age attributes

The same principle applies to derivations. Instead of each model defining its own age bands:

select
    person_id,
    {{ calculate_age_attributes('birth_date', 'current_date()') }}
from {{ ref('stg_olids_patient') }}

One call expands into age, age_band_5y, age_band_10y, age_band_nhs, age_band_ons, life_stage and school-age flags — consistent across every model, with the band boundaries defined once.

The ones you will reach for

Writing your own

A macro definition is the SQL you would have written, with the varying parts as parameters. A realistic small example — a standard “not yet ended” filter used across effective-dated reference tables:

{% macro is_active_record(start_date_col, end_date_col, as_of='current_date()') %}
    {{ start_date_col }} <= {{ as_of }}
    and ({{ end_date_col }} is null or {{ end_date_col }} > {{ as_of }})
{% endmacro %}

select organisation_code, organisation_name
from {{ ref('stg_dictionary_dbo_organisation') }}
where {{ is_active_record('open_date', 'close_date') }}

Practical notes when writing one:

• Parameters arrive as text. start_date_col is the string open_date pasted into the SQL — the macro never sees data, only names. Defaults (like as_of above) keep the common case short.
• Debug with dbt compile. If a macro misbehaves, read the rendered SQL — the mistake is usually visible immediately in the expansion.
• Navigate with the editor. In VS Code, go-to-definition on any macro call opens its source — the fastest way to learn what the existing macros actually do.
• Place it with its peers. Shared macros live in macros/, grouped by purpose (transformations/, governance/, qof_registers/…). A macro used by one model probably should not exist yet — inline it until the rule of three says otherwise.

The two mistakes everyone makes first

Nesting curlies. Once you are inside {{ … }} or {% … %}, you are already in Jinja — opening another pair does not evaluate anything, it passes the literal text. So a macro that takes a model as an argument is called with ref() bare:

{{ deduplicate_table(ref('stg_olids_patient'), 'person_id') }}   -- right
{{ deduplicate_table("{{ ref('stg_olids_patient') }}", ...) }}   -- passes literal text

Missing quotes. Column names passed to a macro are strings and need quoting: {{ calculate_age_attributes('birth_date') }}. Without the quotes, Jinja looks for a variable called birth_date, finds nothing, and renders nothing — which surfaces later as baffling SQL rather than a helpful error. When either mistake has you stuck, dbt compile and read what was actually rendered.