What is Jinja

Jinja is a templating engine used in dbt to dynamically generate SQL code based on predefined templates. It allows users to embed logical constructs, conditional statements, and variables directly into their SQL code. With Jinja, users can create flexible and reusable dbt models by incorporating dynamic elements like date calculations or conditional logic. Jinja tags, enclosed in curly braces, provide a powerful way to parameterize and customize SQL queries, facilitating the creation of dynamic and data-driven transformations in dbt.

“curlies” used in jinja:

Expressions {{ ... }} - used to output a string, to refer variables and call macros.
Statements {% ... %} - used for "for" loops and if statements, or to define macros.
Comments {# ... #}: These are Jinja comments

Example of jinja code –

{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}
select
order_id,
    {% for payment_method in payment_methods %}
sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount,
    {% endfor %}
    sum(amount) as total_amount
from app_data.paymentsgroup by 1

For the above query, this is how the query will compile to –

select
order_id,
sum(case when payment_method = 'bank_transfer' then amount end) as bank_transfer_amount,
sum(case when payment_method = 'credit_card' then amount end) as credit_card_amount,
sum(case when payment_method = 'gift_card' then amount end) as gift_card_amount,
    sum(amount) as total_amount
from app_data.paymentsgroup by 1

Note – Initially jinja code needs to be compiled to a valid query. Therefore, one should first hit the compile button (in cloud)

What Are Macros In dbt?

In dbt, macros are reusable pieces of SQL code that allow users to encapsulate and parameterize complex logic. These powerful constructs help maintain clean, modular, and DRY (Don’t Repeat Yourself) code in dbt projects. Macros can accept parameters, making them flexible and adaptable to different use cases. They provide a way to standardize and share SQL logic across multiple models, ensuring consistency and efficiency in data transformations. Dbt macros enhance collaboration and code maintainability by enabling the creation of custom, shareable functions that can be utilized throughout the entire analytics workflow.

Here’s a simple example of a macro that calculates the average of a specified column in a table:

{% macro calculate_average(column_name) %}
    SELECT AVG({{ column_name }}) as avg_{{ column_name }}
{% endmacro %}

In this example, the macro is named calculate_average, and it takes one argument, column_name. Inside the macro, a SQL query is defined that calculates the average of the specified column.

Now, you can use this macro in your dbt models:

WITH my_data AS (
    SELECT *
    FROM raw_data
)

SELECT
    *,
{{ my_macros.calculate_average('sales_amount') }} as average_sales
FROM my_data

In this model, the calculate_average macro is called with the argument ‘sales_amount’. When dbt runs, it will replace the macro call with the SQL code from the macro, effectively calculating the average of the ‘sales_amount’ column in the resulting SQL query.

We at Helical have more than 10 years of experience in providing solutions and services in the domain of data and have served more than 85+ clients. We are also DBT partners, hence in case if you are looking for certain assistance, consulting, services please do reach out on nikhilesh@Helicaltech.com