Jinja templating

Jinja is a fast, expressive, powerful templating engine, commonly used to generate dynamic content. It allows you to embed programming logic into any text-based format (HTML, XML, CSV, or LaTex).

In modern data engineering, Jinja has gained popularity by enabling the use of variables, logic, and reusability in SQL transformation, largely thanks to the integration in open-source tools like dbt.

Recurve is integrated with dbt to support Jinja directly in SQL models and allows you to define reusable assets, such as variables, macros, and tests.

Jinja syntax

Jinja's syntax is very similar to Python and is declared inside delimiter tags, which include:

  • Expression {{ ... }}: Expressions are used to output string, reference variables, and call macros.

  • Statement {% ... %}: Statements are used for control flow, such as for loops and if statements, and to define macros.

  • Comments {# ... #}: These are comments in Jinja and won't be compiled.

The following are some examples of using Jinja in Recurve SQL models.

Set variable

With Jinja, you can define a local variable and use directly in the query as follows:

{% set min_date="2018-02-17" %}

select * from {{ source('jaffle_shop', 'raw_orders') }}
where order_date >= '{{ min_date }}'

Recurve supports creating global variables that you can use in multiple models and pipelines. See: Variables.

if statement

Check the condition using if statement.

{% set filter_shipped = True %}

select *
from {{ source('jaffle_shop', 'raw_orders') }}
where 1 = 1  -- Always true, allows appending conditions dynamically
{% if filter_shipped %}
  and status = 'shipped'
{% endif %}

for loop

Iterate through a list of items using for loop.

-- define the list of statuses
{% set statuses = ['shipped', 'returned', 'cancelled'] %}

-- use a for loop to create a query with counts for each status
select
    {% for status in statuses %}
        sum(case when status = '{{ status }}' then 1 else 0 end) as {{ status }}_count
        {% if not loop.last %}, {% endif %}
    {% endfor %}
from {{ source('jaffle-shop', 'raw_orders') }}

Define macro

Jinja macros are similar to functions in programming languages. You can define your logic and transformation into a macro and use it across different models. See: Macros.

Definition of a macro that calculates the sum based on the given status:

{% macro count_orders_by_status(status) %}
    sum(case when status = '{{ status }}' then 1 else 0 end)
{% endmacro %}

Built-in Jinja functions

As Recurve integrates with the open-source dbt framework, the built-in, dbt-specific Jinja functions are also available for use. For the full list of functions and definitions, refer to dbt Jinja functions.

Some commonly used functions are:

  1. ref()

    • Purpose: References another model within the same project or across projects. This function ensures that dependencies are managed correctly.

    • Usage: {{ ref('model_name') }}

  2. source()

    • Purpose: Creates a dependency between a model and a source table, enabling better tracking of data lineage.

    • Usage: {{ source('source_name', 'table_name') }}

  3. var()

    • Purpose: Accesses variables defined in the project Library, allowing for parameterization of models and macros. See: Variables.

    • Usage: {{ var('variable_name') }}

Last updated