Jinja templating in SQL

Ascend uses Jinja templating to make SQL Components dynamic and reusable. This guide covers Jinja syntax, macros, and common patterns.

Basic syntax

Expressions

Output values using double curly braces:

SELECT * FROM {{ ref('upstream_component') }}

Statements

Control flow and logic using {% %}:

{% if is_incremental() %}
WHERE updated_at > '{{ get_last_run_timestamp() }}'
{% endif %}

Comments

Add Jinja comments that don't appear in rendered SQL:

{# This is a Jinja comment #}
SELECT * FROM {{ ref('source') }}

Built-in functions

Ascend provides these built-in Jinja functions:

Function	Description
ref('component_name')	Reference another Component
ref('component', flow='flow_name')	Cross-Flow reference
config()	Access component configuration
is_incremental()	Check if running incrementally
with_test(...)	Add inline tests

Component references

-- Same Flow
SELECT * FROM {{ ref('upstream_component') }}

-- Cross-Flow reference
SELECT * FROM {{ ref('shared_table', flow='shared_flow') }}

Incremental processing

{% if is_incremental() %}
SELECT * FROM {{ ref('source') }}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% else %}
SELECT * FROM {{ ref('source') }}
{% endif %}

Variables

Set variables

{% set table_name = 'customers' %}
{% set threshold = 100 %}
{% set columns = ['id', 'name', 'email'] %}

SELECT * FROM {{ ref(table_name) }}
WHERE amount > {{ threshold }}

Dictionary variables

{% set config = {
    'schema': 'analytics',
    'table': 'events',
    'date_column': 'event_date'
} %}

SELECT * FROM {{ config.schema }}.{{ config.table }}
WHERE {{ config.date_column }} > '2024-01-01'

Loops

Basic loop

SELECT
{% for col in ['revenue', 'cost', 'profit'] %}
    SUM({{ col }}) as total_{{ col }}{% if not loop.last %},{% endif %}
{% endfor %}
FROM {{ ref('financial_data') }}

Loop with index

{% for metric in metrics %}
    {{ metric }} as metric_{{ loop.index }}{% if not loop.last %},{% endif %}
{% endfor %}

Loop variables

Variable	Description
loop.index	Current iteration (1-indexed)
loop.index0	Current iteration (0-indexed)
loop.first	True if first iteration
loop.last	True if last iteration
loop.length	Total number of items

Conditionals

If/else

SELECT
    order_id,
    {% if data_plane_type == "snowflake" %}
    TO_TIMESTAMP(order_date) as order_timestamp
    {% elif data_plane_type == "bigquery" %}
    TIMESTAMP(order_date) as order_timestamp
    {% else %}
    order_date as order_timestamp
    {% endif %}
FROM {{ ref('orders') }}

Conditional expressions

SELECT
    {{ 'user_id' if include_user else 'NULL as user_id' }}
FROM {{ ref('events') }}

Macros

Macros are reusable code snippets defined in the macros/ directory.

Define a macro

macros/utils.sql

{% macro safe_divide(numerator, denominator) %}
    CASE
        WHEN {{ denominator }} = 0 THEN NULL
        ELSE {{ numerator }} / {{ denominator }}
    END
{% endmacro %}

{% macro date_trunc_day(column) %}
    DATE_TRUNC('day', {{ column }})
{% endmacro %}

Import and use macros

flows/analytics/components/metrics.sql

{% from 'macros/utils.sql' import safe_divide, date_trunc_day %}

SELECT
    {{ date_trunc_day('created_at') }} as day,
    {{ safe_divide('revenue', 'orders') }} as avg_order_value
FROM {{ ref('orders') }}
GROUP BY 1

Macro with default parameters

macros/aggregations.sql

{% macro sum_if(column, condition, alias=none) %}
    SUM(CASE WHEN {{ condition }} THEN {{ column }} ELSE 0 END)
    {% if alias %} as {{ alias }}{% endif %}
{% endmacro %}

{% from 'macros/aggregations.sql' import sum_if %}

SELECT
    {{ sum_if('amount', "status = 'completed'", 'completed_amount') }},
    {{ sum_if('amount', "status = 'pending'", 'pending_amount') }}
FROM {{ ref('transactions') }}

Common patterns

Multi-source union

Combine multiple sources dynamically:

{% set source_tables = ['orders_2022', 'orders_2023', 'orders_2024'] %}

{% for table in source_tables %}
SELECT
    '{{ table }}' as source_table,
    *
FROM {{ ref(table) }}
{% if not loop.last %}UNION ALL{% endif %}
{% endfor %}

Dynamic column selection

{% set dimensions = ['region', 'product', 'channel'] %}
{% set metrics = ['revenue', 'quantity'] %}

SELECT
    {% for dim in dimensions %}
    {{ dim }},
    {% endfor %}
    {% for metric in metrics %}
    SUM({{ metric }}) as total_{{ metric }}{% if not loop.last %},{% endif %}
    {% endfor %}
FROM {{ ref('sales') }}
GROUP BY
    {% for dim in dimensions %}
    {{ dim }}{% if not loop.last %},{% endif %}
    {% endfor %}

Platform-specific SQL

Handle differences between data platforms:

{% if data_plane_type == "snowflake" %}
SELECT DATEADD('day', -7, CURRENT_DATE()) as week_ago
{% elif data_plane_type == "bigquery" %}
SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) as week_ago
{% elif data_plane_type == "databricks" %}
SELECT DATE_SUB(CURRENT_DATE(), 7) as week_ago
{% endif %}

Conditional joins

SELECT
    o.order_id,
    o.amount
    {% if include_customer_details %}
    , c.name as customer_name
    , c.email as customer_email
    {% endif %}
FROM {{ ref('orders') }} o
{% if include_customer_details %}
LEFT JOIN {{ ref('customers') }} c ON o.customer_id = c.id
{% endif %}

Filters

Jinja filters modify values:

{% set name = 'hello world' %}
{{ name | upper }}          {# HELLO WORLD #}
{{ name | replace(' ', '_') }} {# hello_world #}

{% set items = ['a', 'b', 'c'] %}
{{ items | join(', ') }}    {# a, b, c #}

Common filters:

• upper, lower: Change case
• replace(old, new): String replacement
• join(separator): Join list items
• default(value): Provide default if undefined
• trim: Remove whitespace

Best practices

1. Keep macros focused: Each macro should do one thing well
2. Use descriptive names: Make macro purpose clear from the name
3. Document parameters: Comment what each macro parameter expects
4. Avoid deep nesting: Limit nested loops and conditionals for readability
5. Test incrementally: Build complex templates step by step
6. Use whitespace control: Use {%- and -%} to control output whitespace

Whitespace control

Control whitespace in output with -:

{%- for col in columns -%}
{{ col }}
{%- if not loop.last %}, {% endif -%}
{%- endfor -%}

Next steps

• Learn about SQL Transforms
• Explore Incremental SQL Transforms
• See Simple Applications for template-based Components