What Are dbt Macros? Reusable SQL Logic with Jinja Templating

dbt macros are reusable blocks of SQL logic written with Jinja templating that can be called from any model, test, or other macro in a dbt project. They work like functions: you define the logic once, parameterize it, and call it wherever you need it. When dbt compiles a model that calls a macro, the macro is rendered into SQL before execution — the database sees only standard SQL, not the macro syntax.

Why Macros Exist

SQL is not a programming language designed for reuse. If the same transformation logic appears in ten models — the same date truncation pattern, the same currency conversion calculation, the same deduplication logic — any change to that logic requires updating all ten models individually. Miss one, and your models diverge silently.

Macros solve this by extracting repeated logic into a single definition. Change the macro, and every model that calls it reflects the change immediately on the next dbt run.

Basic Macro Syntax

Macros are defined in .sql files in the macros/ directory of a dbt project. The syntax uses Jinja's macro keyword:

A simple macro that generates a date truncation expression:

{% macro fiscal_year_start(date_column) %}

date_trunc('year', {{ date_column }}) + interval '3 months'

{% endmacro %}

Called in a model:

SELECT

{{ fiscal_year_start('order_date') }} as fiscal_year_start_date,

SUM(revenue) as revenue

FROM orders

GROUP BY 1

When dbt compiles this, the macro is rendered: date_trunc('year', order_date) + interval '3 months' replaces the macro call in the output SQL.

Macros for Cross-Database Compatibility

One of the most common uses of macros in production dbt projects is abstracting SQL functions that differ across databases. dbt itself ships with a library of these: dbt_utils and the built-in adapter_macro pattern handle functions that are named differently in Snowflake, BigQuery, and Redshift.

For example, generating a surrogate key requires CONCAT() in some databases, the || operator in others, and has different null-handling behavior across platforms. The dbt_utils.generate_surrogate_key() macro handles this cross-database normalization, so the same dbt model works in Snowflake and BigQuery without modification.

Custom macros for cross-database compatibility follow the same pattern: define the SQL once with adapter-specific conditionals using target.type, and all models call the macro rather than writing adapter-specific SQL inline.

Macros with Conditional Logic

Macros support Jinja control flow — if/else, for loops, variable assignment. This enables dynamic SQL generation:

A macro generating a CASE statement for a list of status values:

{% macro status_label(column, statuses) %}

CASE {{ column }}

{% for status in statuses %}

WHEN '{{ status.code }}' THEN '{{ status.label }}'

{% endfor %}

ELSE 'unknown'

END

{% endmacro %}

Called with a list of status mappings, the macro generates the full CASE expression. Adding a new status requires updating the macro call argument, not modifying the CASE statement in every model.

dbt Packages and Shared Macros

dbt packages are collections of models, macros, and tests published to the dbt Hub or a Git repository. Installing a package makes its macros available to your project.

**dbt_utils** is the most widely used dbt package. It provides macros for: generating surrogate keys, date spine generation (producing a row for every date in a range), pivot table generation, and cross-database SQL function compatibility. Most production dbt projects install dbt_utils.

**dbt_expectations** extends dbt's native test library with additional test macros (expect_column_values_to_be_between, expect_table_row_count_to_be_between, expect_column_to_exist) inspired by the Great Expectations library.

**dbt_date** provides macros for fiscal year calculations, date formatting, and calendar arithmetic.

Installing a package adds it to packages.yml. Running dbt deps installs the package and makes its macros available with the package namespace: {{ dbt_utils.generate_surrogate_key([...]) }}.

Macros vs. CTEs vs. Models

Knowing when to use a macro vs. a CTE vs. a separate model is a common dbt design question.

Use a macro when: the same SQL expression pattern is used in multiple models and the logic itself does not produce rows (it produces a scalar expression or SQL fragment); when you need to abstract database-specific syntax; when dynamic SQL generation based on parameters is needed.

Use a CTE when: the logic produces an intermediate result set used within a single model. CTEs are for within-model reuse; macros are for across-model reuse.

Use a separate model when: the intermediate result is needed by multiple downstream models and is expensive to recompute. Materializing the intermediate as a table means it is computed once and read by all downstream consumers, rather than re-executed as a CTE in each calling model.

Macro Testing

Macros themselves are not directly testable via dbt test, but models that use them are. The standard approach: create a test model that exercises the macro with known inputs and assert the output matches expected values, then run dbt test against that model.

Our data architecture services includes dbt project design, macro library development, and transformation architecture for Snowflake, BigQuery, and Redshift. Contact us to discuss your dbt implementation.

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