dbt Macros: Writing Reusable SQL with Jinja Templating

dbt macros are functions written in Jinja templating that generate SQL. They allow you to write reusable logic once and call it across multiple models, rather than copying and pasting the same SQL fragments. Macros are also the foundation of dbt packages — shareable collections of macros and models that the community publishes for common data transformation patterns.

This guide covers when to write macros, how Jinja templating works in dbt, and the most useful patterns for production dbt projects.

When to write a macro

The simplest rule: write a macro when you would otherwise copy and paste the same SQL in three or more models. Common candidates:

- A date spine generator that creates one row per day between two dates

- A union_all across many similar tables (e.g., one table per region or per client)

- A standardised null-handling pattern applied to the same set of columns across models

- A surrogate key generation pattern (though dbt_utils.generate_surrogate_key already provides this)

- A pivot or unpivot transformation applied to multiple similar tables

Do not write macros for logic that appears in only one or two models — inline SQL is clearer and easier to maintain at that scale.

Jinja basics in dbt

dbt uses Jinja2 templating. In a dbt SQL file, Jinja is written within double curly braces (for expressions that output text) or percentage-curly-brace delimiters (for control flow statements like if/for). The dbt docs use the template syntax throughout, but remember that when writing inside template literals you work with the actual Jinja syntax.

**ref() and source()**: The most common Jinja expressions in dbt. ref('model_name') generates the correct table reference for the compiled model, handling the schema prefix and cross-database references. source('source_name', 'table_name') references a source table with the configured schema and database.

**Jinja variables**: var('variable_name') reads a variable defined in dbt_project.yml or passed via the --vars flag. Variables parameterise models without changing code — a date cutoff variable for incremental models, an environment-specific schema prefix.

**Jinja conditionals and loops**: Control flow for dynamic SQL generation. A loop over a list of column names generates multiple CASE WHEN expressions. A conditional that tests the target.schema generates different SQL for development vs production.

Writing a basic macro

A macro is defined in a .sql file inside the macros/ directory of your dbt project. The macro is defined with a Jinja block that takes optional arguments, and the body contains the SQL logic or fragments to output.

A simple example: a macro that generates a safe division expression (avoids division by zero):

The macro file defines the macro name and its arguments. The body is the SQL that will be substituted wherever the macro is called.

In a model, call the macro with its name and arguments using the Jinja expression syntax. The compiled SQL replaces the macro call with the macro's output.

Macro arguments and defaults

Macros accept positional or keyword arguments. Default values can be specified for optional arguments. When you call a macro without providing an optional argument, the default value is used.

This pattern makes macros flexible without requiring callers to provide every argument. Document the arguments and defaults in a comment at the top of the macro file.

Calling macros from macros

Macros can call other macros. This enables composing complex logic from simple building blocks. A union_all macro might call a schema prefix macro to generate the correct table references for each environment. Keep macros focused on a single responsibility; compose complexity through calling rather than creating monolithic macros.

The dbt_utils package

dbt_utils (from dbt-labs) is the most widely used dbt package, providing macros for the most common data transformation patterns:

- **generate_surrogate_key**: Generates a hash-based surrogate key from a list of columns. Standard in most dbt projects.

- **date_spine**: Generates a series of dates between two dates. Used for building calendar spine tables.

- **pivot** / **unpivot**: Dynamic pivot/unpivot without hard-coding column names.

- **star**: Selects all columns from a table except a specified exclusion list. Useful in staging models.

- **union_relations**: Unions multiple tables or models with schema alignment.

- **get_column_values**: Returns a list of distinct values for a column, used for dynamic SQL generation.

Install dbt_utils by adding it to packages.yml in the dbt project root, then running dbt deps. Use it with the package namespace: dbt_utils.generate_surrogate_key(...).

Custom dispatch: overriding macros by adapter

dbt's dispatch mechanism allows writing macros with different implementations for different warehouses (adapters). A macro that uses REGEXP_REPLACE in Snowflake might use REGEXP_SUBSTR in BigQuery for the same logical operation.

The adapter_macro pattern and dispatch allow the same macro call to resolve to the appropriate implementation for the current warehouse. This is how dbt_utils provides cross-adapter support — the package contains multiple implementations of each macro, and dbt dispatches to the correct one based on the configured adapter.

For macros in internal packages that target multiple warehouses, implement the dispatch pattern to avoid hard-coding warehouse-specific SQL.

Materialisation macros

dbt's materialisation types (table, view, incremental, ephemeral) are themselves implemented as macros. Advanced dbt users can create custom materialisation types for specific patterns — for example, a "clone" materialisation that uses Snowflake Zero Copy Clone instead of CREATE TABLE AS SELECT.

Custom materialisations are advanced and rarely necessary. They are mentioned here because understanding that materialisations are macros demystifies dbt's internals and opens the door to customisation when standard materialisation types are insufficient.

Testing macros

Macros are harder to test than models because they are not directly compiled to standalone SQL. The practical approach:

- Write a simple test model that calls the macro with representative inputs

- Run dbt compile on the test model and inspect the compiled SQL for correctness

- Include the macro in models that have dbt tests, so macro correctness is implicitly validated by model test results

For packages intended for broad reuse, dbt's integration testing with a test project that runs dbt build against sample data is the standard testing approach.

For the broader dbt context, see dbt best practices and dbt incremental models. Our data architecture consulting practice builds production dbt projects including custom macro libraries — book a free review.

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