Course contents

The Analytics Engineer’s Field Guidedbt 2: make it trustworthyStep 5: Macros

Macros

Part 2 · dbt · step 5

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:

what you write · stg_orders.sql
select order_id,
  {{ to_euros('amount_cents') }}a call
from {{ source(…) }}
→
dbt compiles
what Snowflake runs
select order_id,
  amount_cents / 100.0plain SQL
from RAW_DB.PUBLIC.ORDERS

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_euros.sql
{% macro to_euros(cents_column) %}
    {{ cents_column }} / 100.0
{% endmacro %}

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/stg_orders.sql
SELECT
    order_id,
    {{ to_euros('amount_cents') }} AS amount_eur
FROM {{ source('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.sql
stg_refunds.sql
fct_orders.sql
macros/to_euros.sqlthe rule, defined once
change the rule here · all three models change together

A macro can call another macro

Macros compose, so shared logic stays in one place:

-- macros/net_eur.sql
{% macro net_eur(cents_column, vat_rate) %}
    ({{ to_euros(cents_column) }}) / (1 + {{ vat_rate }})
{% endmacro %}

A model calls {{ net_eur('amount_cents', 0.10) }}. 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, or analytics_staging in 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_database_name.sql
{% macro generate_database_name(custom_database_name, node) -%}
    {%- if target.name == 'pro' -%}          PROD_DB
    {%- elif target.name == 'pre_prod' -%}   PRE_PROD_DB
    {%- elif target.type == 'duckdb' -%}     {{ target.database }}   {# the local file, step 15 #}
    {%- else -%}                             USERLAND   {# every personal target #}
    {%- endif -%}
{%- endmacro %}
  • target holds your target's settings: target.name is the target you ran with, target.type the adapter. pre_prod is CI's target, in CI's own profile. The duckdb line is for step 15: ignore it for now.
  • The two arguments are what dbt passes in: a model's own database setting, 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 clean PROD_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

userland
pre_prod
pro
generate_database_name()runs for every modelswitches on target.name

writes into

USERLAND
PRE_PROD_DB
PROD_DB
same colour, same route: userland → USERLAND · pre_prod → PRE_PROD_DB · pro → PROD_DB

Same project, same models, three destinations, and nobody ever types a database name in a model.

Don't macro everything
A macro you call once just hides the SQL. Reach for one when a snippet shows up in three or more places, or encodes a rule the whole team must apply the same way. Otherwise, plain SQL reads better.

Next: most of the macros you'll want, someone already wrote. That's what packages are for.

Lesson quiz

  1. At what point does Snowflake see your to_euros macro?

    1. Never: dbt expands it into plain SQL before sending the query
    2. At compile time in Snowflake, which then caches it for later runs
    3. At run time, as a function dbt creates in the schema first
    4. Only in models materialized as tables, never in views
  2. A snippet appears in exactly one model. Should it become a macro?

    1. Yes: every snippet that might be reused belongs in macros/
    2. No: macros may only be called from staging models, not marts
    3. Yes: macros make the query run faster in Snowflake itself
    4. No: a macro called once is indirection that hides the SQL
  3. Thomas adds a target named staging_test to his own profile and runs dbt build --target staging_test on Snowflake. With the lesson's generate_database_name, which database do his models land in?

    1. USERLAND, like every personal target
    2. The database named in his profile target
    3. PROD_DB, since it isn't a userland target
    4. PRE_PROD_DB, the test copy CI builds