Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 41 ยท Jinja and Macros

Macro Patterns: Cross-Database Macros, run_query and Overrides

Advanced dbt macro patterns: write cross-database macros with adapter.dispatch, generate pivots from data with run_query, override built-in macros, and keep macros testable.

You know how to write a macro and what the dbt context offers. This lesson combines them into the patterns that experienced teams use: macros that work on more than one database, macros that look at your data before generating SQL, and overrides of dbt's own behavior. Each pattern comes with a runnable stand-in, because the real adapter and run_query need a warehouse.

Pattern 1: portable SQL with adapter.dispatch

SQL dialects differ. Getting the date seven days ago is dateadd('day', -7, current_date) in Snowflake, current_date - interval '7 days' in Postgres, date_sub(current_date, interval 7 day) in BigQuery. If your macro hard-codes one, it breaks on the others. dbt's answer is dispatch: you write one macro with several implementations, and dbt picks the one that matches the adapter.

SQL
-- macros/days_ago.sql
{% macro days_ago(n) -%}
    {{ return(adapter.dispatch('days_ago', 'shop_analytics')(n)) }}
{%- endmacro %}

{% macro default__days_ago(n) -%}
    current_date - interval '{{ n }} days'
{%- endmacro %}

{% macro snowflake__days_ago(n) -%}
    dateadd('day', -{{ n }}, current_date)
{%- endmacro %}

{% macro bigquery__days_ago(n) -%}
    date_sub(current_date, interval {{ n }} day)
{%- endmacro %}

The naming convention is <adapter>__<macro_name> (two underscores), with default__ as the fallback. adapter.dispatch('days_ago', 'shop_analytics') looks for the implementation in this order: the current adapter (snowflake__days_ago), its parent adapters (Postgres-based adapters inherit from postgres), then default__days_ago. The second argument names the package whose macros are searched, which also lets a project override a package's implementations through the dispatch config in dbt_project.yml:

YAML
dispatch:
  - macro_namespace: dbt_utils
    search_order: ["shop_analytics", "dbt_utils"]

Here is the mechanism in a stand-in: pure Python and real Jinja pick the macro by adapter name. This is not dbt's code, but the lookup rule is the same.

Python Stand-in: adapter.dispatch

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

DuckDB has no specific implementation, so it falls back to default__. If a function is the same everywhere, you only need the default. If it only works on two adapters, write those two and raise a compiler error in default__.

You will rarely write dispatch macros for yourself if you only target one warehouse, but you will constantly use them: packages such as dbt_utils (see the packages lesson in the next part of the course) are full of them.

Pattern 2: generate SQL from data with run_query

Sometimes the list of columns depends on what is in the data: one column per payment method, per country, per product category. Write the list by hand and it goes stale; read it from the warehouse and it never does.

SQL
-- macros/get_distinct_values.sql
{% macro get_distinct_values(relation, column) -%}
    {%- if execute -%}
        {%- set results = run_query('select distinct ' ~ column ~ ' from ' ~ relation ~ ' order by 1') -%}
        {{ return(results.columns[0].values()) }}
    {%- else -%}
        {{ return([]) }}
    {%- endif -%}
{%- endmacro %}
SQL
-- models/marts/orders_by_status.sql
{% set statuses = get_distinct_values(ref('stg_orders'), 'status') %}

select
    customer_id
    {%- for s in statuses %},
    sum(case when status = '{{ s }}' then 1 else 0 end) as {{ s }}_orders
    {%- endfor %}
from {{ ref('stg_orders') }}
group by customer_id

Look at the execute guard (parse-time returns an empty list). A subtle point: a model that uses ref(...) as an argument to a macro still registers the dependency, so the stg_orders build is guaranteed to finish before this model compiles its SQL... when run through dbt run, since the query happens at compile time of this model.

The next stand-in plays both roles: step one runs the "distinct values" query on the sample database, step two renders the template with the results. Run the discovery query first:

SQL Step 1: the run_query lookup ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Four statuses come back: cancelled, delivered, pending, shipped. Now render with those values and see what the macro would generate:

Python Step 2: render the pivot from the discovered values

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Costs and risks of this pattern:

  • Compilation needs a warehouse connection and runs a query for each call.
  • The columns of the model depend on the data, so a new status silently adds a column and may break downstream models that expect a fixed shape. For marts, a contract or an explicit list is often safer. Dynamic columns suit exploration and wide reporting tables.
  • Order matters: always order by in the lookup so the output is stable between runs.

Pattern 3: override built-in macros

dbt's behavior for naming things is itself implemented as macros that you may override by defining a macro with the same name in your project:

  • generate_schema_name(custom_schema_name, node): which schema a model goes to.
  • generate_alias_name(custom_alias_name, node): object names.
  • generate_database_name(custom_database_name, node): database or catalog.

A common override uses the custom schema name exactly in production but prefixes it in development (compare the stand-in in Model Configs):

SQL
-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}
        {{ default_schema }}
    {%- elif target.name == 'prod' -%}
        {{ custom_schema_name | trim }}
    {%- else -%}
        {{ default_schema }}_{{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

The project's macro wins over dbt's built-in one. Keep overrides small and documented, since they change behavior for every model.

Pattern 4: macros that do work, not just print

Some macros run statements, for example to clean up, grant permissions or clone a schema. They use run_query or {% call statement(...) %} and are invoked with dbt run-operation:

SQL
{% macro grant_select(schema_name, role) %}
    {% set sql %}
        grant usage on schema {{ schema_name }} to {{ role }};
        grant select on all tables in schema {{ schema_name }} to {{ role }};
    {% endset %}
    {% do run_query(sql) %}
    {{ log('Granted select on ' ~ schema_name ~ ' to ' ~ role, info=True) }}
{% endmacro %}
Terminal
dbt run-operation grant_select --args '{schema_name: analytics, role: reporter}'

Note the block set ({% set sql %}...{% endset %}), which captures text into a variable: a tidy way to write multi-line SQL inside a macro.

Python Block set captures text

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Keeping macros healthy

  • Test the output. Call the macro in a scratch model or dbt compile --inline and read the result. For important macros, write a small model that uses them on seed data and add data tests.
  • Name arguments clearly and document them in YAML so they appear in the docs.
  • Prefer packages for common needs. Before writing a date spine or surrogate-key macro, check dbt_utils and dbt_date; battle-tested code is cheaper than your own.
  • Version overrides in one folder, for instance macros/overrides/, so the next developer knows why behavior differs from the documentation.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the naming convention for an adapter-specific implementation of a dispatched macro 'days_ago' on Snowflake?

    The adapter name, two underscores, then the macro name. default__days_ago is the fallback.
  2. 2. Which problem does the run_query pattern solve?

    run_query lets a macro look at the data first, then generate matching SQL.
  3. 3. How do you replace dbt's built-in generate_schema_name?

    A macro in your project overrides the built-in one of the same name.
  4. 4. What is a risk of generating columns dynamically from data?

    Data-driven columns can change the output shape between runs, which can break downstream models that expect fixed columns.

Summary

  • adapter.dispatch lets one macro name map to adapter-specific implementations (snowflake__x, default__x).
  • run_query (guarded by execute) lets macros read data first, then generate SQL; mind the compile-time cost and unstable column sets.
  • You can override built-ins such as generate_schema_name by defining a macro with the same name in your project.
  • Macros can also perform actions through dbt run-operation; test and document them, and use packages instead of rewriting common helpers.

Next: Generic Tests: unique and not_null