Features
A dbt macro is a reusable Jinja-powered function that generates SQL or YAML at compile time, enabling DRY principles and consistent logic across dbt projects.
A dbt macro is a named block of Jinja code that renders SQL or YAML during dbt’s compile phase, letting teams reuse logic and reduce duplication.
During compilation, dbt evaluates Jinja expressions inside {{ }}. When a macro is referenced with {{ macro_name(arg) }}, dbt executes the macro’s Python-like template and inserts the rendered text into the final SQL.
Macros centralize business logic, enforce consistency, and speed development by turning common SQL fragments—date buckets, SCD joins, pivot clauses—into single callable functions.
Create a macros directory, add a .sql file, and define your macro with Jinja tags:
{% macro first_day(date_col) %}
date_trunc('day', {{ date_col }})
{% endmacro %}
1) Save the macro above.
2) In a model, call {{ first_day('orders.created_at') }}.
3) Run dbt run; the compiled SQL now contains date_trunc('day', orders.created_at).
Start macros with a verb, document inputs/outputs, keep logic idempotent, and unit-test with dbt’s built-in run-operation plus assertions.
Overusing macros for simple SELECTs can hide intent. Limit usage to logic that truly benefits from abstraction and reuse.
Galaxy’s AI copilot auto-completes Jinja syntax, suggests macro calls, and explains macro outputs directly in its desktop SQL editor—accelerating macro authoring without switching tools.
dbt macros embody the DRY principle in data engineering. By templating repetitive SQL and YAML patterns, teams cut maintenance time, standardize calculations, and reduce human error. In fast-moving startups, this consistency keeps analytics aligned with product changes while letting engineers ship models faster.
Use positional or named arguments: {{ my_macro(col='orders.id') }}. Inside the macro, reference {{ col }}.
Yes. Call another macro inside Jinja: {{ other_macro(arg) }}, but avoid deep nesting for readability.
Run dbt run-operation my_macro --args '{"arg": "value"}' and assert the rendered SQL matches expectations.
Galaxy’s SQL editor recognizes Jinja syntax, offers IntelliSense for macro names, and its AI copilot can generate new macros or refactor existing ones.
.avif){kind=logo}
