dbt Macros: Reusable Jinja Templating for Analytics Engineering

dbt macros are Jinja-templated SQL functions that generate SQL programmatically. They are the mechanism for eliminating repetitive SQL patterns from dbt projects — abstracting logic that would otherwise be copy-pasted across many models into a reusable, testable function. Understanding when to write macros and how to write them effectively is one of the key skills that separates maintainable dbt projects from ones that become technical debt.

What Macros Are and Are Not

A macro is a Jinja function defined in a .sql file in the macros/ directory. When dbt compiles a model that calls the macro, Jinja renders the macro's output as SQL text and inlines it into the model's compiled SQL.

Macros are SQL generation functions — they produce SQL strings. They are not callable at query runtime; they run at compile time, before the SQL is sent to the database. This distinction matters: a macro cannot make decisions based on query-time data (current row values, query parameters). It can make decisions based on compile-time context: environment variables, dbt variables, model configuration, and other macros.

A Simple Macro Example

A macro that generates a COALESCE expression for handling nulls with a custom default:

{% macro coalesce_with_default(column_name, default_value='unknown') %}

COALESCE({{ column_name }}, '{{ default_value }}')

{% endmacro %}

Usage in a model:

SELECT

order_id,

{{ coalesce_with_default('customer_segment') }} as customer_segment,

{{ coalesce_with_default('region', 'unassigned') }} as region

FROM source_orders

Compiled output:

SELECT

order_id,

COALESCE(customer_segment, 'unknown') as customer_segment,

COALESCE(region, 'unassigned') as region

FROM source_orders

This is a trivial example — the value of macros grows with complexity.

The Most Valuable Macro Patterns

**Date spine generation** — generating a table of consecutive dates is a pattern that appears repeatedly in analytics. The dbt-utils package includes a date_spine macro; for custom requirements, a project-level date spine macro is valuable:

{% macro date_spine(start_date, end_date, datepart='day') %}

...complex Jinja generating a date sequence...

{% endmacro %}

**Cross-database compatibility** — SQL syntax for common operations varies by database. A macro that abstracts the database-specific syntax allows models to remain portable:

{% macro datediff(datepart, start_date, end_date) %}

{% if target.type == 'snowflake' %}

DATEDIFF({{ datepart }}, {{ start_date }}, {{ end_date }})

{% elif target.type == 'bigquery' %}

DATE_DIFF({{ end_date }}, {{ start_date }}, {{ datepart }})

{% elif target.type == 'databricks' %}

DATEDIFF({{ end_date }}, {{ start_date }})

{% endif %}

{% endmacro %}

Note: dbt-utils provides a cross-database datediff macro; use the package version rather than writing your own unless you have specific requirements.

**Fiscal calendar calculations** — many organisations use fiscal years that do not align with calendar years. A macro that converts a calendar date to a fiscal period is a common requirement:

{% macro fiscal_year(date_column, fiscal_year_start_month=4) %}

CASE

WHEN EXTRACT(MONTH FROM {{ date_column }}) >= {{ fiscal_year_start_month }}

THEN EXTRACT(YEAR FROM {{ date_column }})

ELSE EXTRACT(YEAR FROM {{ date_column }}) - 1

END

{% endmacro %}

**Pivot / unpivot** — generating a CASE WHEN pivot expression over a dynamic list of values:

{% macro pivot(column, values, alias=True) %}

{% for value in values %}

SUM(CASE WHEN {{ column }} = '{{ value }}' THEN 1 ELSE 0 END)

{% if alias %} AS {{ value | replace(' ', '_') | lower }}{% endif %}

{% if not loop.last %},{% endif %}

{% endfor %}

{% endmacro %}

**Surrogate key generation** — generating consistent surrogate keys from natural keys:

{% macro generate_surrogate_key(field_list) %}

{{ dbt_utils.generate_surrogate_key(field_list) }}

{% endmacro %}

(Wrapping dbt-utils functions in project macros allows future changes to the implementation without updating every model that calls the macro.)

When Not to Use Macros

Macros are powerful but overuse is a common mistake in dbt projects:

**Do not use macros for logic that is model-specific** — if a calculation only appears in one model, keep it in the model. Extracting it to a macro adds indirection without adding reuse value.

**Do not use macros for simple calculations** — a macro that generates "column_name * 100" is not meaningfully simpler than writing the expression directly. Macros earn their complexity overhead when they abstract patterns that are actually complex or frequently repeated.

**Avoid deep macro nesting** — macros that call macros that call macros become difficult to debug. The compiled SQL for a deeply-nested macro chain can be hard to read and hard to test.

**Test macros by testing the models that call them** — macros do not have unit tests in the standard dbt framework. The correctness of a macro is verified through the dbt tests on the models that use it. If a macro generates incorrect SQL, the tests on the consuming models should catch it.

Macros for Custom Operations

Beyond SQL generation, macros can perform custom dbt operations: logging, environment checks, and calling dbt's adapter methods.

run_started_at logging:

{% macro log_run_start() %}

{{ log("dbt run started at: " ~ run_started_at, info=True) }}

{% endmacro %}

Called in on-run-start hooks in dbt_project.yml.

**Environment assertions** — a macro that fails if run in the wrong environment:

{% macro assert_not_production() %}

{% if target.name == 'prod' %}

{{ exceptions.raise_compiler_error("This macro should not be run in production!") }}

{% endif %}

{% endmacro %}

Useful for protecting destructive operations (like truncating tables) from accidental production execution.

The dbt-utils and dbt-expectations Packages

Before writing a macro, check whether dbt-utils or dbt-expectations already provides it. These widely-used packages cover:

**dbt-utils**: date spine, surrogate key, safe divide, pivoting, unpivoting, star (select all columns except...), cross-database compatibility functions, and many testing macros.

**dbt-expectations**: Great Expectations-style test macros — expect_column_values_to_not_be_null, expect_column_values_to_be_between, expect_table_row_count_to_be_between, and many others.

Using established packages over custom macros has significant advantages: they are tested by the dbt community across many database platforms, they are maintained and updated as dbt evolves, and they have documentation. Write custom macros only for requirements that packages do not cover.

Our data architecture practice designs dbt project architectures including macro libraries and package selection for enterprise analytics engineering teams — contact us to discuss your dbt project design.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.