Models
Jinja templating

Jinja templating

Jinja enhances SQL by transforming it into a more dynamic and programmable environment. With Jinja, you can perform various operations in SQL that are usually not possible. Here's what you can do:

  • Integrate control structures like if-else and loops.
  • Utilize environment variables for deployment configurations.
  • Generate one SQL query from the result of another.
  • Create reusable SQL snippets through macros, similar to functions in programming languages.

If you've used the {{ ref() }} function, you're already utilizing Jinja!

Jinja in SQL: A Quick Example

Here's a simple example using Jinja to define payment methods in a SQL query.

source_code
compiled_query

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

The above query will be compiled to standard SQL, generating columns for each specified payment method.

Identifying Jinja Syntax

Jinja syntax uses special delimiters:

  • Expressions {{ ... }}: Used for string outputs, variable referencing, and macro calling.
  • Statements {% ... %}: Used for control structures like loops and conditional statements.
  • Comments {# ... #}: Text within these delimiters won't be compiled.
HooksLinting with SQLFluff