dbt Macros Basics: Reusable SQL Logic with Jinja

A dbt macro is a small template function written in Jinja. It removes duplication inside models, absorbs warehouse-specific differences, and lets your whole team safely reuse the same logic.

Based on the official documentation, this article quickly covers when to use macros, how to write them, dialect handling with adapter.dispatch, and Generic Tests. It also calls out what matters for the Analytics Engineer certification.

Why Macros, and the Compile Flow

A macro is a Jinja function that returns a SQL fragment, called from models or other macros. dbt first resolves Jinja, then generates the final SQL and sends it to the target data warehouse.

Encapsulating duplicated CASE expressions, normalization rules, or DDL differences inside macros improves both maintainability and dialect tolerance. The exam frequently asks whether you can clearly distinguish the compile and execute stages.

• Compile time: Jinja is expanded and macros are resolved
• Execute time: the generated SQL runs against the warehouse (runtime APIs like run_query are only available here)
• Macros fundamentally generate SQL rather than execute it

dbt compile-to-execute flow (including macros and dispatch)

Minimal macro definition and call

-- macros/cleaning.sql
{% macro trim_upper(col) -%}
  upper(trim({{ col }}))
{%- endmacro %}

-- models/fct_orders.sql
select
  {{ trim_upper('customer_name') }} as customer_name_norm
from {{ ref('stg_orders') }};

Macro vs. Model vs. Hook vs. Materialization vs. Operation

These terms overlap, so it helps to sort them by role and execution timing. A macro is a reusable building block used inside other constructs — it is not something you schedule on its own.

The Analytics Engineer exam often asks you to pick which items are compile-time mechanisms and which are units in the execution plan.

• Models are execution units (nodes); macros are template parts
• Hooks and materializations are also implemented as macros, but their purpose and trigger differ
• run-operation is the CLI for manually executing a macro

Running a macro one-off with run-operation

-- macros/say_hello.sql
{% macro say_hello(name) -%}
  {{ log('hello ' ~ name, info=True) }}
{%- endmacro %}

# 実行例（シェル）
# dbt run-operation say_hello --args '{"name": "NicheeLab"}'

Basic Syntax: Arguments, return, and the execute Guard

A macro is defined with arguments and returns a SQL string. dbt provides a return function, and you can branch and loop with Jinja's if/for.

Runtime APIs (run_query, load_result, log, etc.) are not active during compilation, so guard them with if execute. The exam frequently tests the meaning of execute and when run_query is actually usable.

• Explicitly returning a string with return improves readability
• Wrap code that uses runtime APIs in if execute
• Arguments can have defaults (design conservatively for compatibility)

A safe macro using return and execute

-- macros/label_case.sql
{% macro label_case(expr, label='unknown') -%}
  {% set sql %}
    case when {{ expr }} then '{{ label }}' else 'other' end
  {% endset %}
  {{ return(sql) }}
{%- endmacro %}

-- 実行時 API 例（ログ出力）
{% macro log_rowcount(model) -%}
  {% if execute %}
    {% set q %}select count(*) as c from {{ model }}{% endset %}
    {% set t = run_query(q) %}
    {% if t and t.rows and t.rows[0] %}
      {{ log('rowcount=' ~ t.rows[0]['c'], info=True) }}
    {% endif %}
  {% endif %}
  {{ return('') }}
{%- endmacro %}

Packaging and Dialect Handling with adapter.dispatch

Macros shared across teams or as OSS belong in a package. Call them through the package_name.macro_name namespace to avoid collisions.

Handle warehouse differences with adapter.dispatch: provide dialect-specific implementations like snowflake__macro_name plus a default__macro_name fallback. dbt resolves to the right implementation based on the target adapter.

• Call: {{ mypkg.some_macro(...) }}
• Resolution order: <adapter>__name if present, otherwise default__name
• Always ship a default implementation (the exam asks about this as the recommended pattern)

The basic dispatch pattern

-- macros/some_macro.sql（公開ラッパー）
{% macro mypkg.some_macro(arg) -%}
  {% set impl = adapter.dispatch('some_macro', 'mypkg') %}
  {{ impl(arg) }}
{%- endmacro %}

-- macros/some_macro_default.sql（既定実装）
{% macro default__some_macro(arg) -%}
  {{ return('/* default */ ' ~ arg) }}
{%- endmacro %}

-- macros/some_macro_snowflake.sql（Snowflake 実装）
{% macro snowflake__some_macro(arg) -%}
  {{ return('/* snowflake */ ' ~ arg) }}
{%- endmacro %}

-- 呼び出し
select {{ mypkg.some_macro('select 1') }};

Building Generic Tests as Macros

A dbt Generic Test is implemented as a macro that generates a SELECT returning failing rows. Zero rows means pass; one or more rows means fail.

The model name is passed at test time, so receive it as an argument and avoid hard-coding.

• Test macros return failing rows via a SELECT
• Taking model and column_name as arguments is the standard shape
• Call from YAML with the tests: - my_test: format

Example of a conditional not_null test

-- tests/not_null_if_active.sql（Macro として）
{% macro test_not_null_if_active(model, column_name) -%}
  select {{ column_name }}
  from {{ model }}
  where is_active = true
    and {{ column_name }} is null
{%- endmacro %}

# schema.yml（抜粋）
# models:
#   - name: dim_customer
#     columns:
#       - name: email
#         tests:
#           - not_null_if_active: {}

Exam Angles and Pitfalls

The Analytics Engineer exam targets macro execution timing, adapter.dispatch resolution order, and the return shape of Generic Tests. Lock in the points below and you should not lose easy marks.

In practice, do not conflate dbt context functions like ref or source with the role of macros, and switch warehouse-dependent logic with dispatch rather than if statements — that is the key to stable operations.

• Macros expand at compile time; run_query and similar APIs work only at execute time (if execute)
• dispatch resolves in the order <adapter>__name then default__name
• A Generic Test is a SELECT that returns failing rows (zero rows = pass)
• Keep project macros in macros/*.sql; package anything you want to share
• Avoid heavy use of if target.type for dialect branching; prefer dispatch

Check Your Understanding

Frequently Asked Questions