Advanced Jinja Techniques

Dispatch Architecture

Adapter Pattern

Custom Macro Architecture

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:

Base implementation: Default fallback
Adapter-specific: Database-optimized versions
Package-level: Shared across projects
Project-level: Custom overrides

What are Adapter Functions?

dbt provides adapter functions for database-specific operations:

Function	Purpose
`adapter.dispatch()`	Route to correct implementation
`adapter.resolve()`	Resolve model references
`adapter.get_relation()`	Get database objects
`adapter.create_schema()`	Create database schemas
`adapter.drop_relation()`	Drop database objects

How to organize Macro Libraries?

Organize macros into reusable libraries:

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

What are Advanced Patterns?

Recursive macros: Process hierarchical data
Conditional logic: Dynamic SQL based on conditions
Loop constructs: Process collections efficiently
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

Pattern	Compilation Time	Use Case
Direct call	O(1)	Simple macros
Dispatch	O(n)	Cross-database
Recursive	O(depth)	Hierarchical data
Dynamic columns	O(rows)	Pivot operations
Import	O(1)	Code reuse

Best Practices

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

Advanced Jinja Techniques

Advanced Jinja Techniques

Dispatch Architecture

Adapter Pattern

Custom Macro Architecture

Detailed Explanation

What is the Dispatch Pattern?

What are Adapter Functions?

How to organize Macro Libraries?

What are Advanced Patterns?

Code Examples

Cross-Database Dispatch

Advanced Macro with Call Block

Recursive Macro for Hierarchical Data

Dynamic Column Generation

Custom Test Macro

Advanced Dispatch with Package Override

Performance Metrics

Best Practices

See Also

Need Expert dbt Help?