Macros: write once, reuse everywhere
Alice keeps pasting the same snippet into every model. A macro turns it into one function she calls.
By her third staging model, Alice is writing amount_cents / 100.0 for the fourth time. Copy-paste works fine, right up until the rule changes and she has to find it in nine files.
A macro is a function that writes SQL for you. It's Jinja, from step 2: {{ }} prints a value, {% %} runs logic (an if, a macro definition), {# #} is a comment. You define a macro once in macros/, call it with {{ }}, and dbt swaps the call for real SQL before the query reaches Snowflake:
Snowflake never sees a macro. It only receives the plain SQL the macro produced, and dbt compile shows you that SQL.
Writing one: to_euros
-- macros/to_ {% macro to_euros. sql euros( {{ cents_column }} / 100.0 {% endmacro %}cents_ column) %}
The rule: {% macro %} … {% endmacro %} is logic, so it defines the macro and prints nothing itself. Inside, {{ cents_column }} prints the argument's value. The text between the two tags is what the macro returns. One file can hold several macros under any name, but one per file, named after it, is easiest to find.
Any model calls it instead of repeating the maths:
-- models/staging/ SELECT order_id, {{ to_stg_ orders. sql euros( AS amount_eur FROM {{ source('amount_ cents') }} 'raw', 'orders') }}
Note the quotes: you pass the column name as a string. Every model calls the same function, and the rule lives in one file:
models that call it
stg_orders.sqlstg_refunds.sqlfct_orders.sqlmacros/to_euros.sqlthe rule, defined onceA macro can call another macro
Macros compose, so shared logic stays in one place:
-- macros/net_ {% macro net_eur. sql eur( ({{ to_cents_ column, vat_rate) %} euros( ) / (1 + {{ vat_rate }}) {% endmacro %}cents_ column) }}
A model calls {{ net_. Quote names, not numbers. Inside net_eur, cents_column is a variable already holding 'amount_cents', so it's passed on without quotes.
The brackets matter because a macro pastes raw text. Write / 1 + {{ vat_rate }} and it compiles to / 1 + 0.10: divide by 1, then add 0.10. Wrong number, no error.
The macro that tells dbt where to write
dbt runs its own macros too. Two decide where each model lands, generate_database_name and generate_schema_name. The built-in versions:
- Database: the target's
database, unless a model sets its own. - Schema: the target's
schema. If a model sets one (like step 4's+schema: staging), dbt appends it:dbt_alice_staging, oranalytics_stagingin production.
Put a macro with the same name in macros/ and dbt uses yours. Step 14 overrides generate_schema_name: production gets plain STAGING, and your runs go back to dbt_alice. Here's a database one, branching on target.name:
-- macros/generate_ {% macro generate_database_ name. sql database_ {%- if target.name == 'pro' -%} PROD_DB {%- elif target.name == 'pre_prod' -%} PRE_PROD_DB {%- elif target.type == 'duckdb' -%} {{ target.name( custom_ database_ name, node) -%} database }} {# the local file, step 15 #} {%- else -%} USERLAND {# every personal target #} {%- endif -%} {%- endmacro %}
targetholds your target's settings:target.nameis the target you ran with,target.typethe adapter.pre_prodis CI's target, in CI's own profile. Theduckdbline is for step 15: ignore it for now.- The two arguments are what dbt passes in: a model's own
databasesetting, and the model. Keep them. The macro never uses the first, so no model can pick its own database. - The dashes in
{%-and-%}trim the whitespace around a tag, so the macro returns a cleanPROD_DB. - The comments are Jinja
{# #}: a--comment is plain text to Jinja, so it would end up in the returned name.
Each profile already names a database, so the gain is that guarantee, in one reviewed file.
target.name
generate_database_name()runs for every modelswitches on target.namewrites into
Same project, same models, three destinations, and nobody ever types a database name in a model.
Next: most of the macros you'll want, someone already wrote. That's what packages are for.

