Advanced Jinja Techniques

Dispatch Architecture

Architecture Diagram

┌─────────────────────────────────────────────────────────────────────────────┐
│                     JINJA DISPATCH ARCHITECTURE                             │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                    DISPATCH FLOW                                     │   │
│  │                                                                     │   │
│  │  Macro Call: {{ adapter.dispatch('macro_name', 'package') }}       │   │
│  │                                                                     │   │
│  │  1. Check project-level override                                    │   │
│  │  2. Check package-level implementation                              │   │
│  │  3. Check adapter-specific implementation                           │   │
│  │  4. Use default implementation                                      │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                              │                                              │
│                              ▼                                              │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                    DISPATCH EXAMPLE                                 │   │
│  │                                                                     │   │
│  │  {% macro safe_cast(column, type) %}                                │   │
│  │      {% set macro = adapter.dispatch('safe_cast', 'my_package')     │   │
│  │                           (column, type) %}                        │   │
│  │      {{ return(macro) }}                                            │   │
│  │  {% endmacro %}                                                     │   │
│  │                                                                     │   │
│  │  Implementations:                                                   │   │
│  │  ├── default__safe_cast      (fallback)                            │   │
│  │  ├── snowflake__safe_cast    (Snowflake-specific)                  │   │
│  │  ├── bigquery__safe_cast     (BigQuery-specific)                   │   │
│  │  └── redshift__safe_cast     (Redshift-specific)                   │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

Adapter Pattern

Architecture Diagram

┌─────────────────────────────────────────────────────────────────────────────┐
│                     ADAPTER PATTERN IMPLEMENTATION                          │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                             │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                    ADAPTER HIERARCHY                                 │   │
│  │                                                                     │   │
│  │                    ┌──────────────┐                                 │   │
│  │                    │ BaseAdapter  │                                 │   │
│  │                    │   (dbt-core) │                                 │   │
│  │                    └──────┬───────┘                                 │   │
│  │                           │                                         │   │
│  │           ┌───────────────┼───────────────┐                        │   │
│  │           │               │               │                        │   │
│  │           ▼               ▼               ▼                        │   │
│  │    ┌──────────────┐ ┌──────────────┐ ┌──────────────┐            │   │
│  │    │  Snowflake   │ │  BigQuery    │ │  Redshift    │            │   │
│  │    │  Adapter     │ │  Adapter     │ │  Adapter     │            │   │
│  │    └──────────────┘ └──────────────┘ └──────────────┘            │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                              │                                              │
│                              ▼                                              │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                    MACRO DISPATCH                                   │   │
│  │                                                                     │   │
│  │  ┌─────────────────────────────────────────────────────────────┐   │   │
│  │  │  safe_cast(column, type)                                    │   │   │
│  │  │                                                             │   │   │
│  │  │  Snowflake: safe_cast({{ column }} as {{ type }})          │   │   │
│  │  │  BigQuery:  safe_cast({{ column }} as {{ type }})          │   │   │
│  │  │  Redshift:  cast({{ column }} as {{ type }})               │   │   │
│  │  │  Default:   cast({{ column }} as {{ type }})               │   │   │
│  │  └─────────────────────────────────────────────────────────────┘   │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                                                                             │
└─────────────────────────────────────────────────────────────────────────────┘

Custom Macro Architecture

Architecture Diagram

┌─────────────────────────────────────────────────────────────────────────────┐
│                     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                                                 │   │
│  └─────────────────────────────────────────────────────────────────────┘   │
│                              │                                              │
│                              ▼                                              │
│  ┌─────────────────────────────────────────────────────────────────────┐   │
│  │                    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.

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

Adapter Functions

dbt provides adapter functions for database-specific operations:

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

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

Advanced Patterns

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

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

Dispatch Pattern

Adapter Functions

Macro Libraries

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

Need Expert dbt Help?