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.

ReOrc 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 ReOrc SQL models.

Set variable

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

SQL model

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

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

Compiled code

select * from "jaffle_shop"."raw_orders"
where order_date >= '2018-02-17'

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

if statement

SQL model

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 %}

Compiled code

select *
from "jaffle_shop"."main"."raw_orders"
where 1 = 1  -- Always true, allows appending conditions dynamically

  and status = 'shipped'

for loop

SQL model

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') }}

Compiled code

select
        sum(case when status = 'shipped' then 1 else 0 end) as shipped_count, 
        sum(case when status = 'returned' then 1 else 0 end) as returned_count, 
        sum(case when status = 'cancelled' then 1 else 0 end) as cancelled_count
        
from "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.

Macro definition

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 %}

SQL model

Calling the macro in a model.

select
    {{ count_orders_by_status('shipped') }} as shipped_orders,
    {{ count_orders_by_status('returned') }} as returned_orders,
    {{ count_orders_by_status('cancelled') }} as cancelled_orders
from {{ source('jaffle_shop', 'raw_orders') }}

Compiled code

As you can see, afte compilation the macro call is resolved with its definition:

select
   sum(case when status = 'shipped' then 1 else 0 end) as shipped_orders,
   sum(case when status = 'returned' then 1 else 0 end) as returned_orders,
   sum(case when status = 'cancelled' then 1 else 0 end) as cancelled_orders
from "jaffle_shop"."raw_orders"

Built-in Jinja functions

As ReOrc 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') }}