Jinja and Macros

dex supports Jinja, a powerful templating engine that allows you to add logic, reuse patterns, and dynamically generate SQL or Python code. Combined with macros, you can standardize transformation logic across your models and increase the flexibility and maintainability of your data pipelines.

Jinja brings programming constructs—like variables, loops, conditionals, and functions—into your transformation files. This allows your SQL to behave like code, while still compiling into clean, readable queries that run directly on your cloud warehouse.

What is Jinja?

Jinja is a lightweight templating language that runs before your SQL or Python is executed. It lets you embed logic inside your models using double curly braces {{ ... }} for expressions and `{% ... %}

` for control structures.

Common use cases include:

• Dynamically generating column lists

• Controlling behavior with project/environment variables

• Adding conditional logic or flags

• Looping over fields, tables, or inputs

Example: Basic Jinja

{% set threshold = 100 %}

select *
from orders
where total_amount > {{ threshold }}

In this example, threshold is a Jinja variable, and it will be replaced before the SQL is compiled and sent to your warehouse.

What is a Macro?

A macro is a reusable block of Jinja code, defined once and called many times. Macros are similar to functions in other programming languages and allow you to abstract complex or repetitive logic.

Macros live in .sql files under the macros/ folder in your dex project.

Example: Defining and Using a Macro

Define a macro

-- macros/format_currency.sql

{% macro format_currency(column) %}
  round({{ column }}, 2)
{% endmacro %}

Use the macro in a model

select
  order_id,
  {{ format_currency('order_total') }} as formatted_total
from orders

This compiles into:

select
  order_id,
  round(order_total, 2) as formatted_total
from orders

Jinja Blocks Overview

Syntax	Purpose
{{ ... }}	Print a value or expression
{%​ set var = ... %}	Define a variable
{%​ if condition %} ... {%​ endif %}	Conditional logic
{%​ for item in list %} ... {%​ endfor %}	Loop over a sequence
{%​ macro name() %} ... {%​ endmacro %}	Define a reusable macro
{{ ref('model') }}	Reference another model (dependency)
{{ source('src', 'tbl') }}	Reference a source or seed
{{ config(...) }}	Configure model materialization settings
{{ var('var_name') }}	Access a project or environment variable

Jinja with Environment Variables

Jinja works seamlessly with project and environment variables defined in dex. For example:

select *
from {{ var('default_schema') }}.users

In this case, default_schema would be a variable set in the dex UI or dbt_project.yml.

Debugging Jinja and Macros

You can inspect the compiled SQL of any model in the dex interface by clicking Compile Code in the code editor. This shows exactly what dex will run in your warehouse after all Jinja and macros have been rendered.

If a macro is misused or a variable is missing, dex will show clear errors during compilation.

Best Practices

• Use Jinja for logic that varies by environment or use case

• Keep macro names descriptive and concise

• Store all macros under a dedicated macros/ folder

• Avoid overusing Jinja where pure SQL would be simpler

• Document macros with comments and parameter descriptions