Skip to main content

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 through dbt 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 compile produce 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.