How do you define a reusable macro in a dbt project and call it from a model?

## What a macro actually is A dbt macro is a Jinja macro: a named block of template text with parameters. Calling it renders its body with the arguments substituted, and the resulting text is pasted into whatever was being compiled. It is closer to a text function than to a database function — nothing about it exists in the warehouse, and the warehouse only ever sees the expanded result. ```sql -- macros/cents_to_dollars.sql {% macro cents_to_dollars(column_name, precision=2) %} round({{ column_name }} / 100.0, {{ precision }}) {% endmacro %} ``` ```sql -- models/stg_payments.sql select payment_id, {{ cents_to_dollars('amount_cents') }} as amount, {{ cents_to_dollars('fee_cents', 4) }} as fee from {{ source('stripe', 'payments') }} ``` Compiled, the second file contains `round(amount_cents / 100.0, 2) as amount` — ordinary SQL. ## Where the files live Macros go in `.sql` files under the `macros/` directory. The path is configurable through `macro-paths` in `dbt_project.yml`, but the default is what almost every project uses. Three details trip people up: - **The file name is irrelevant.** dbt indexes macros by macro name, not by file. `macros/utils.sql` may define five macros and all five are callable. - **Two macros with the same name in your project collide.** dbt will raise a duplicate-resource error at parse time; names are a flat namespace within a project. - **Macros are global to the project.** There is no import statement; once a macro is parsed, every model, test, hook and other macro can call it. ## Arguments, defaults and return values Parameters may have defaults (`precision=2` above), and callers may pass positionally or by keyword. Because a macro renders text, its value is a string by default. When you want a macro to hand back a real object — a list of column names, a boolean, a number to loop over — use `{{ return(...) }}`: ```sql {% macro active_regions() %} {{ return(['emea', 'amer', 'apac']) }} {% endmacro %} ``` Now `{% for region in active_regions() %}` iterates over a genuine list instead of trying to iterate the characters of a string. ## Calling a macro outside a model Some macros do not produce a fragment of a SELECT; they do an operational job. `dbt run-operation` executes such a macro on its own against the configured target: ```bash dbt run-operation grant_select --args '{role: reporter, schema: analytics}' ``` The macro body typically wraps its SQL in a `statement` block so it actually executes rather than being rendered into nowhere: ```sql {% macro grant_select(role, schema) %} {% set sql %} grant select on all tables in schema {{ schema }} to role {{ role }} {% endset %} {% do run_query(sql) %} {% endmacro %} ``` `{% do ... %}` evaluates an expression and discards its output, which is what you want when the point is the side effect. This is how teams script grants, warehouse maintenance and ad-hoc DDL without leaving the dbt project. ## Where else macros show up Hooks (`pre_hook`, `post_hook`, `on-run-end`) call macros, so a project can grant privileges after every build. Materializations themselves are macros, which is how custom materializations are written. And dbt's own behaviour is customisable by *overriding* specific built-in macros: defining `generate_schema_name` in your own `macros/` directory replaces dbt's default schema-naming logic project-wide. That override-by-name mechanism is powerful and easy to trip over — a macro named after a dbt internal is not a private helper. ## When a macro is the wrong tool A macro earns its place when the same SQL idiom appears in many models and would drift if copied: a currency conversion, a surrogate-key expression, a standard timestamp cast, a grant statement. It is the wrong tool when it hides the shape of a model — a macro that emits the whole FROM clause, or picks the grain, makes the model unreadable and the compiled output the only source of truth. Names matter for the same reason: `cents_to_dollars` reads at the call site, `fix_amount` does not. Finally, remember that macros are not tested by anything automatically. A macro used by forty models is a forty-model blast radius, so changes to one deserve the same care as a schema change.