[06] – Jinja Templates and Macros in dbt

This course explores Jinja templates and macros in dbt, enabling you to automate SQL generation, enhance reusability, and optimize query logic. You’ll learn to create dynamic SQL, implement macros, and use control structures to streamline data workflows. By the end, you’ll be equipped to write flexible, efficient, and maintainable dbt models.

Jinja Templates #

Jinja is one of the strengths of dbt. This templating language allows us to do a whole bunch of things that would otherwise be impossible with plain SQL. Thanks to it, we can notably factorize our code and thus apply the DRY (Don’t Repeat Yourself) principle.

dbt allows dynamic SQL generation using Jinja, and it can be applied to any SQL within a dbt project, including models, analyses, tests, and hooks.

It enhances SQL in dbt by enabling advanced functionalities like

• Control structures (if statements, loops)
• Creating reusable macros, similar to functions in programming languages, to simplify and automate SQL code
• Environment variables for deployments
• Dynamic project builds
• Query-based operations

Using Variables in SQL #

In the example below, instead of hardcoding the value for start_date, we use Jinja syntax to dynamically insert the value passed via the --vars flag. If no value is provided, the default value (“2024-01-01”) will be used.

SELECT *
FROM customers
WHERE signup_date >= '{{ var("start_date", "2024-01-01") }}'

This approach makes the query more flexible and reusable by allowing you to pass different values for start_date without modifying the SQL code.

Macro #

Macros in Jinja are reusable code blocks, similar to functions in other programming languages. They are especially useful for avoiding code repetition across multiple models. Macros are defined in .sql files, typically located in the macros directory.

The macros directory is, by default, configured as macro-paths in the dbt_project.yml file. However, you can customize this configuration to use a different directory if needed.

Creating a macro #

Now, let’s create a macro that performs currency conversion dynamically.

Step 1: Create the Macro File

Create a file named macros/convert_currency.sql and add the following code:

{% macro convert_currency(column, rate) %}
    ({{ column }} * {{ rate }})
{% endmacro %}

Step 2: Understanding the Macro

The convert_currency macro converts monetary values by multiplying a given column by an exchange rate. It takes two parameters:

• column – The name of the column containing the amount to be converted.
• rate – The exchange rate to apply.

Wrapping the calculation in parentheses ensures correct arithmetic evaluation in SQL expressions.

Use a macro in the model #

SELECT
    order_id,
    {{ convert_currency('total_amount', 1.1) }} AS total_in_euros
FROM orders;

After dbt compiles the query, it translates it into standard SQL:

SELECT
    order_id,
    (total_amount * 1.1) AS total_in_euros
FROM orders;

This allows us to reuse the convert_currency macro across multiple models without rewriting the logic.

Use macro from package #

Several useful macros are bundled into packages, with dbt-utils being the most widely used.

Once a package is installed in your project, you can leverage any of its macros. To avoid conflicts and ensure proper usage, always prefix the macro with the package name.

Example: Using a Macro from dbt-utils

SELECT *
FROM {{ dbt_utils.date_spine(
    datepart="day",
    start_date="2024-01-01",
    end_date="2024-12-31"
) }}

In this example, the date_spine macro from dbt-utils is used to generate a series of dates between start_date and end_date.

Next #

By integrating Jinja with dbt, we can:

• Write cleaner, reusable SQL
• Avoid repetitive code with macros
• Dynamically generate SQL queries
• Enhance flexibility using variables and control structures