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). See: Jinja documentation.

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

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

SQL model

In Jinja, you can define a local variable as follows:

-- min_rental_duration = 3, defined in the Variables section

SELECT film_id, title, rental_duration
FROM film
WHERE rental_duration >= {{ var('min_rental_duration') }}

Compiled code

SELECT film_id, title, rental_duration
FROM film
WHERE rental_duration >= 3

if statement

SQL model

Check the condition using if statement.

{% set include_r_rated = true %}

SELECT film_id, title, rating
FROM film
WHERE 1=1 
{% if include_r_rated %}
  AND rating = 'R'
{% else %}
  AND rating != 'R'
{% endif %}

Compiled code

SELECT film_id, title, rating
FROM film
WHERE 1=1

  AND rating = 'R'

for loop

SQL model

Iterate through a list of items using for loop.

{% set categories = ['Action', 'Comedy', 'Drama'] %}

SELECT f.film_id, f.title, c.name AS category
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
WHERE c.name IN (
  {% for category in categories %}
    '{{ category }}'{% if not loop.last %},{% endif %}
  {% endfor %}
)

Compiled code

SELECT f.film_id, f.title, c.name AS category
FROM film f
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
WHERE c.name IN ( 
    'Action',
    'Comedy',
    'Drama'
)

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.

{% macro get_actor_films(actor_id) %}

  SELECT f.film_id, f.title
  FROM film f
  JOIN film_actor fa ON f.film_id = fa.film_id
  WHERE fa.actor_id = {{ actor_id }}

{% endmacro %}

SQL model

Calling the macro in a model.

WITH actor_films AS (
  {{ get_actor_films(1) }}
)

SELECT *
FROM actor_films

Compiled code

WITH actor_films AS (

  SELECT f.film_id, f.title
  FROM film f
  JOIN film_actor fa ON f.film_id = fa.film_id
  WHERE fa.actor_id = 1

)

SELECT *
FROM actor_films

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:

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