Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 41 ยท Jinja and Macros

Writing Macros

Create dbt macros to reuse SQL logic: arguments, default values, calling macros from models, macro files, docs, tests with macros, run-operation and common mistakes.

A macro is a Jinja function that returns text, usually SQL. If you find yourself writing the same expression in five models (convert cents to dollars, a standard date truncation, a company-specific hash of columns), put it in a macro once and call it everywhere. Macros are dbt's mechanism for DRY code ("don't repeat yourself").

Your first macro

Macros live in .sql files in the macros/ folder. The file name does not matter, but the macro name does:

SQL
-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, decimal_places=2) -%}
    round({{ column_name }} / 100.0, {{ decimal_places }})
{%- endmacro %}

Call it from any model:

SQL
select
    order_id,
    {{ cents_to_dollars('amount_cents') }} as amount_usd,
    {{ cents_to_dollars('tax_cents', 4) }} as tax_usd
from {{ ref('stg_payments') }}

At compile time dbt replaces each call by the text the macro prints. To see exactly this, here is the macro in real Jinja, along with the model compiled:

Python Define and call a macro

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Note the whitespace control (-%} and {%-) inside the macro. Without it, the output would contain newlines and indentation in the middle of the expression. Compare:

Python Why whitespace control matters in macros

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The loose version leaks newlines and spaces; the tight one prints select upper(name) as n. SQL does not mind, but readable compiled output makes debugging far easier.

Arguments

Macros accept positional arguments, keyword arguments and defaults, like Python functions:

SQL
{% macro safe_divide(numerator, denominator, default=0) -%}
    case
        when {{ denominator }} = 0 or {{ denominator }} is null then {{ default }}
        else {{ numerator }} * 1.0 / {{ denominator }}
    end
{%- endmacro %}
SQL
select
    order_id,
    {{ safe_divide('items_total', 'item_count') }} as avg_item_price,
    {{ safe_divide('items_total', 'item_count', default='null') }} as avg_or_null
from {{ ref('fct_orders') }}

Arguments passed as strings, such as 'items_total', are text pasted into the SQL: a macro operates on SQL fragments, not on values. That is the mental model: macros are text substitution with logic.

Test the macro idea on sample data. Here is the rendered SQL, run against the shop database:

SQL The safe_divide macro, rendered and run ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Products with zero stock return 0 instead of a division error, which is exactly what the macro guarantees for every model that uses it.

Macros that generate more than an expression

Macros can print entire clauses or statements. A common one builds a stable surrogate key by hashing columns. This version uses a loop and loop.last (from the previous lesson):

Python A macro that loops over its argument

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In a real project you would normally reach for dbt_utils.generate_surrogate_key, which produces an MD5 hash and handles nulls consistently across warehouses; the shape of the macro is the same.

Macros calling macros

A macro can call other macros, which lets you build small pieces and combine them:

SQL
{% macro dollars(column_name) -%}
    {{ cents_to_dollars(column_name) }}
{%- endmacro %}

All macros in the project, in installed packages and in dbt itself are visible globally. If two have the same name, the project's version wins over a package's, which is how you override package behavior (see the context lesson).

Where macros go and how to document them

Put macros in macros/, one concept per file, with the file named after the macro. Document them in YAML:

YAML
# macros/_macros.yml
version: 2

macros:
  - name: cents_to_dollars
    description: "Converts an integer amount in cents to a rounded decimal."
    arguments:
      - name: column_name
        type: string
        description: "Name of the column holding the amount."
      - name: decimal_places
        type: integer
        description: "Rounding precision. Default 2."

Documented macros show up in dbt docs alongside models.

Running a macro from the command line

A macro can do work without any model: dbt run-operation calls it directly. This is how teams write housekeeping tasks (drop old schemas, grant permissions, clone production):

SQL
-- macros/drop_dev_schema.sql
{% macro drop_schema(schema_name) %}
    {% do run_query('drop schema if exists ' ~ schema_name ~ ' cascade') %}
    {{ log('Dropped schema ' ~ schema_name, info=True) }}
{% endmacro %}
Terminal
dbt run-operation drop_schema --args '{schema_name: dbt_maria}'

run_query executes SQL during a run. These special functions are explained in the context functions lesson. Be careful: a macro that drops things is code with real consequences. Test it against a dev target first.

Macros as tests

A custom generic test is a macro whose name starts with test_ (in tests/generic/ or macros/). It receives a model and column and returns a query that selects failing rows. Tests get their own chapter; for now, notice it is the same macro mechanism.

Common mistakes

  • Forgetting that arguments are text. {{ safe_divide(price, stock) }} without quotes tries to use Jinja variables named price and stock, which do not exist, and prints empty strings. Quote column names: 'price'.
  • Whitespace leaks. A macro used inside an expression needs -%} / {%- to avoid stray newlines in odd places, such as inside a string literal.
  • Over-abstracting. A macro with eight arguments and three branches is harder to read than SQL. Extract a macro when the same logic appears in three or more places.
  • Hidden dependencies. A macro that hard-codes a table name hides a dependency from dbt. Pass ref() results in as arguments instead.
  • Name clashes with packages. Choose distinctive names, or use a project prefix for internal macros.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where do macro files go?

    dbt loads macros from the macros folder (the macro-paths setting).
  2. 2. What does a macro return?

    Macros are Jinja functions that print text, so a call is replaced by that text at compile time.
  3. 3. Which call correctly passes a column name to safe_divide?

    Column names are passed as strings; the macro pastes them into the SQL.
  4. 4. How do you run a macro that does housekeeping, without any model?

    dbt run-operation calls a macro directly, with --args for its arguments.

Summary

  • A macro is a Jinja function in macros/ that returns text, usually SQL; call it with {{ my_macro(...) }}.
  • Arguments can have defaults; column names are passed as strings and pasted into the SQL.
  • Use {%- -%} inside macros to keep compiled SQL clean.
  • Document macros in YAML, run them with dbt run-operation, and extract a macro only when logic repeats.

Next: dbt Context Functions: target, var, env_var and Friends