# 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

```sql
{% 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

```sql
-- macros/format_currency.sql

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

#### Use the macro in a model

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

This compiles into:

```sql
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:

```sql
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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.dexlabs.io/lakehouse-platform/develop-with-dex/jinja-and-macros.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.