Macro Design Patterns and Best Practices
The goal of a macro is not to make SQL look clever. The goal is to make repeated logic clear, reliable, and easy to change.
1. Keep one responsibility
Prefer focused macros:
{{ standardize_status('order_status') }}
{{ safe_cast('amount', 'number(18, 2)') }}
{{ date_bucket('order_date', 'month') }}
Avoid a single macro that decides a model's source, joins five tables, applies business rules, configures materialization, and writes audit records. Large macros are difficult to test and reason about.
2. Accept expressions, not only column names
A parameter called expression communicates that callers may pass a qualified column or a small SQL expression:
{% macro null_to_unknown(expression) %}
COALESCE({{ expression }}, 'UNKNOWN')
{% endmacro %}
Example calls:
{{ null_to_unknown('customer_status') }}
{{ null_to_unknown('customer.status') }}
Only pass trusted, developer-controlled SQL expressions. Macro parameters are template substitution, not automatically escaped database values.
3. Use defaults carefully
Defaults make utility macros easier to call:
{% macro limit_rows(row_limit=100) %}
LIMIT {{ row_limit }}
{% endmacro %}
Defaults should be safe and documented. Do not use a default that silently changes production behavior without making that choice visible in the model or project configuration.
4. Separate business rules from warehouse operations
A status mapping is a transformation macro. A permission grant is an operational macro. Keep them separate:
- Transformation macros return SQL expressions.
- Operational macros use explicit commands such as
run_query()and run throughdbt run-operation. - Models orchestrate transformations and materialize relations.
This separation makes permissions, testing, and deployment behavior easier to understand.
5. Prefer package macros when the problem is generic
Before writing a utility, check whether a maintained package already solves the problem. For example, dbt_utils commonly provides cross-database helpers.
Write a project macro when the rule is specific to your organization, such as a proprietary status mapping or company naming convention. Use an internal package when multiple dbt projects need the same private logic.
6. Document the contract
A good macro name and parameter contract answer:
- What does the macro accept?
- What SQL does it return?
- How are nulls handled?
- What happens for an unknown value?
- Which adapter assumptions does it make?
For a business-critical macro, add a short README section or a doc block in the project documentation and keep a singular test beside the implementation.
7. Review checklist
Before merging a macro, ask:
- Is the name specific and meaningful?
- Is the macro small enough to explain in one paragraph?
- Are parameters named for their real purpose?
- Are null, empty, whitespace, and unexpected inputs handled?
- Does
dbt compileproduce valid SQL on the supported adapter? - Is there a focused test for the important business cases?
- Would a package or existing project macro be a better choice?
- Does the macro avoid destructive or surprising side effects?
Final exercise
Refactor the status macro so its mapping is easy to extend. Add ON_HOLD and PAUSED as PENDING, write a test case for each, compile stg_customers, and explain the change in a pull request description.
Module recap
You have now learned a complete macro workflow:
Understand the business rule
|
v
Write a focused macro
|
v
Call it from a model or run it directly
|
v
Compile and inspect the generated SQL
|
v
Test representative inputs
|
v
Run and validate in Snowflake
The strongest dbt macros are boring in the best way: predictable inputs, readable generated SQL, clear tests, and one source of truth for a rule.