Advanced Jinja Techniques

Dispatch Architecture

Adapter Pattern

Custom Macro Architecture

+-----------------------------------------------------------------------------+
|                     CUSTOM MACRO ARCHITECTURE                               |
+-----------------------------------------------------------------------------+
|                                                                             |
|  +---------------------------------------------------------------------+    |
|  |                    MACRO LIBRARY STRUCTURE                           |   |
|  |                                                                     |    |
|  |  macros/                                                            |    |
|  |  +-- general/                                                       |    |
|  |  |   +-- string_utils.sql                                          |     |
|  |  |   +-- date_utils.sql                                            |     |
|  |  |   +-- math_utils.sql                                            |     |
|  |  +-- cross_db/                                                      |    |
|  |  |   +-- safe_cast.sql                                             |     |
|  |  |   +-- date_trunc.sql                                            |     |
|  |  |   +-- concat.sql                                                |     |
|  |  +-- schema_tests/                                                  |    |
|  |  |   +-- test_unique.sql                                           |     |
|  |  |   +-- test_not_null.sql                                         |     |
|  |  +-- generate/                                                      |    |
|  |      +-- create_table.sql                                          |     |
|  |      +-- merge.sql                                                 |     |
|  +---------------------------------------------------------------------+    |
|                              |                                              |
|                              v                                              |
|  +---------------------------------------------------------------------+    |
|  |                    MACRO USAGE PATTERNS                              |   |
|  |                                                                     |    |
|  |  1. Direct call: {{ macro_name(args) }}                            |     |
|  |  2. Return value: {{ return(value) }}                              |     |
|  |  3. Call macro: {% call macro() %}...{% endcall %}                 |     |
|  |  4. Import: {% import 'macros/utils.sql' as utils %}              |      |
|  |  5. From import: {% from 'macros/utils.sql' import safe_cast %}   |      |
|  +---------------------------------------------------------------------+    |
|                                                                             |
+-----------------------------------------------------------------------------+

Detailed Explanation

Advanced Jinja techniques in dbt enable sophisticated data transformation patterns, cross-database compatibility, and reusable code libraries.

What is the Dispatch Pattern?

The dispatch pattern allows macros to have database-specific implementations:

1. Base implementation: Default fallback
2. Adapter-specific: Database-optimized versions
3. Package-level: Shared across projects
4. Project-level: Custom overrides

What are Adapter Functions?

dbt provides adapter functions for database-specific operations:

How to organize Macro Libraries?

Organize macros into reusable libraries:

1. General utilities: String, date, math functions
2. Cross-database: Database-agnostic functions
3. Schema tests: Reusable test definitions
4. Generation macros: Code generation utilities

What are Advanced Patterns?

1. Recursive macros: Process hierarchical data
2. Conditional logic: Dynamic SQL based on conditions
3. Loop constructs: Process collections efficiently
4. Context manipulation: Modify compilation context

Key Takeaway: Advanced Jinja techniques enable cross-database compatibility and reusable code libraries, making dbt projects more maintainable and portable.

Code Examples

Cross-Database Dispatch

-- macros/cross_db/safe_cast.sql
{% macro safe_cast(column, type) %}
    {% set macro = adapter.dispatch('safe_cast', 'dbt_utils')(column, type) %}
    {{ return(macro) }}
{% endmacro %}

{% macro default__safe_cast(column, type) %}
    cast({{ column }} as {{ type }})
{% endmacro %}

{% macro snowflake__safe_cast(column, type) %}
    safe_cast({{ column }} as {{ type }})
{% endmacro %}

{% macro bigquery__safe_cast(column, type) %}
    safe_cast({{ column }} as {{ type }})
{% endmacro %}

{% macro redshift__safe_cast(column, type) %}
    case 
        when {{ column }} ~ '^[0-9]+\.?[0-9]*$' then cast({{ column }} as {{ type }})
        else null
    end
{% endmacro %}

Advanced Macro with Call Block

-- macros/generate_merge.sql
{% macro generate_merge(target, source, unique_key, update_columns, insert_columns) %}
    {% call statement('merge') %}
        merge into {{ target }} as target
        using {{ source }} as source
        on {{ unique_key }}
        
        when matched then update set
            {% for col in update_columns %}
                {{ col }} = source.{{ col }}
                {% if not loop.last %},{% endif %}
            {% endfor %}
        
        when not matched then insert (
            {{ insert_columns | join(', ') }}
        )
        values (
            {% for col in insert_columns %}
                source.{{ col }}
                {% if not loop.last %},{% endif %}
            {% endfor %}
        )
    {% endcall %}
{% endmacro %}

Recursive Macro for Hierarchical Data

-- macros/generate_recursive_cte.sql
{% macro recursive_cte(cte_name, base_query, recursive_query, max_depth=10) %}
    with recursive {{ cte_name }} as (
        {{ base_query }}
        
        union all
        
        select
            {% for col in base_columns %}
                {{ cte_name }}_next.{{ col }}
                {% if not loop.last %},{% endif %}
            {% endfor %}
        from {{ cte_name }}
        inner join (
            {{ recursive_query }}
        ) {{ cte_name }}_next
        on {{ cte_name }}.id = {{ cte_name }}_next.parent_id
        where {{ cte_name }}.depth < {{ max_depth }}
    )
    
    select * from {{ cte_name }}
{% endmacro %}

Dynamic Column Generation

-- macros/generate_pivot.sql
{% macro pivot(source, group_by_columns, pivot_column, value_column, agg='sum') %}
    {% set pivot_values = run_query(
        "select distinct " ~ pivot_column ~ " from " ~ source ~ " order by 1"
    ).columns[0].values() %}
    
    select
        {{ group_by_columns | join(', ') }},
        {% for value in pivot_values %}
            {{ agg }}(case when {{ pivot_column }} = '{{ value }}' then {{ value_column }} end) as {{ value_column }}_{{ value | replace(' ', '_') | lower }}
            {% if not loop.last %},{% endif %}
        {% endfor %}
    from {{ source }}
    group by {{ group_by_columns | join(', ') }}
{% endmacro %}

Custom Test Macro

-- macros/schema_tests/test_freshness.sql
{% test freshness(model, column_name, interval, datepart) %}
    
    with source_data as (
        select
            max({{ column_name }}) as last_record,
            {{ dbt_utils.current_timestamp() }} as current_time
        from {{ model }}
    ),
    
    validation as (
        select
            last_record,
            current_time,
            {{ dbt_utils.datediff(
                "last_record",
                "current_time",
                datepart
            )}} as time_diff
        from source_data
    )
    
    select *
    from validation
    where time_diff > {{ interval }}

{% endtest %}

Advanced Dispatch with Package Override

-- macros/cross_db/date_trunc.sql
{% macro date_trunc(datepart, date) %}
    {% set macro = adapter.dispatch('date_trunc', 'dbt_utils')(datepart, date) %}
    {{ return(macro) }}
{% endmacro %}

{% macro default__date_trunc(datepart, date) %}
    date_trunc({{ datepart }}, {{ date }})
{% endmacro %}

{% macro snowflake__date_trunc(datepart, date) %}
    date_trunc({{ datepart }}, {{ date }})
{% endmacro %}

{% macro bigquery__date_trunc(datepart, date) %}
    date_trunc({{ date }}, {{ datepart }})
{% endmacro %}

{% macro redshift__date_trunc(datepart, date) %}
    date_trunc({{ datepart }}, {{ date }})
{% endmacro %}

{% macro postgres__date_trunc(datepart, date) %}
    date_trunc({{ datepart }}, {{ date }})
{% endmacro %}

Performance Metrics

Best Practices

1. Use dispatch for cross-database compatibility
2. Organize macros into logical directories
3. Document macros with clear descriptions
4. Test macros with different inputs
5. Use return() to return values from macros
6. Leverage call blocks for complex operations
7. Import strategically to avoid circular dependencies
8. Use adapter functions for database-specific operations

See Also

• Data Quality Tests — Custom test macros and validation patterns
• dbt BigQuery — BigQuery-specific adapter optimizations
• dbt Snowflake — Snowflake adapter configuration and features
• dbt Redshift — Redshift distribution and sort key strategies
• dbt Best Practices — Project structure and code conventions