Skip to main content

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:

FunctionDescription
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​

VariableDescription
loop.indexCurrent iteration (1-indexed)
loop.index0Current iteration (0-indexed)
loop.firstTrue if first iteration
loop.lastTrue if last iteration
loop.lengthTotal 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​

Last updated on