Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 41 ยท Jinja and Macros

Jinja Basics for dbt

Learn the three Jinja delimiters used in dbt, set variables, use filters and string operations, and see how Jinja renders SQL, all with the real Jinja library running here.

SQL has no variables, no loops and no functions that generate SQL. Jinja is a templating language that adds those. dbt runs every model, test and macro through Jinja before the SQL goes to the warehouse, so you can write {{ ref('orders') }}, loop over a list of columns, or call a macro. This lesson teaches the Jinja you need; the next ones cover control flow and macros.

The Jinja examples in this chapter run in the real Jinja2 library inside Python on this page. dbt uses Jinja2 too, plus extra functions it injects (ref, source, config, var...). We add small stand-ins for those, labelled each time. Jinja only produces text: the SQL is not executed in the Python examples, but in several lessons the rendered SQL runs against the sample database.

Three kinds of delimiters

Syntax Name Purpose
{{ ... }} Expression Evaluate and print the result
{% ... %} Statement Logic: if, for, set, macro, no output
{# ... #} Comment Removed from the output

Everything outside the delimiters is copied as is. That is why a model file is mostly plain SQL with a few Jinja islands.

Python The three delimiters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice the blank line at the top: the comment and the set statement produced no text, but the newline after each is kept. We fix that with whitespace control below.

Variables with set

JINJA
{% set payment_methods = ['card', 'paypal', 'bank'] %}
{% set min_amount = 10 %}
{% set greeting = 'hello' %}

Jinja variables can be strings, numbers, booleans (true/false, lowercase), lists, dictionaries and none. Print them with {{ }}:

Python Variables, lists and dictionaries

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Lists and dictionaries print in Python's notation, because dbt's Jinja runs on Python. You can index lists (methods[0]), read dict keys with brackets or dots, and ask for a length with the length filter.

Expressions and operators

Inside {{ }} you can use:

  • arithmetic: {{ 3 * 2 }}
  • string concatenation: {{ 'stg_' ~ 'orders' }} (the ~ operator; + works on strings too, but ~ converts numbers automatically)
  • comparison and logic: ==, !=, <, >, and, or, not, in
  • ternary: {{ 'yes' if x > 1 else 'no' }}
JINJA
{{ 'amount_' ~ 'usd' }}               {# amount_usd #}
{{ 'a' in ['a', 'b'] }}                {# True #}
{{ 'big' if 100 > 50 else 'small' }}   {# big #}

Filters

A filter transforms a value and is applied with a pipe |. Common ones in dbt projects:

Filter Example Result
upper, lower {{ 'abc' | upper }} ABC
replace {{ 'a-b' | replace('-', '_') }} a_b
join {{ ['a','b'] | join(', ') }} a, b
length {{ ['a','b'] | length }} 2
default {{ x | default('n/a') }} n/a if x is undefined
trim {{ ' x ' | trim }} x
int, float, string {{ '5' | int }} 5
first, last, sort, unique {{ [3,1,2] | sort }} [1, 2, 3]

Filters can be chained: {{ name | trim | lower | replace(' ', '_') }}.

Python Generate a column list with join

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

One short template produced a formatted column list. Imagine the list coming from a variable or a macro: that is how dbt projects avoid repeating long column lists.

Whitespace control

Statements leave blank lines behind. A minus sign on a delimiter removes the whitespace on that side: {%- strips before, -%} strips after.

Python Whitespace control with -

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The tight version joins everything into 'ab': both newlines were consumed. In practice, many dbt projects write {%- ... -%} on every loop and condition to keep compiled SQL tidy. Whitespace in SQL rarely changes meaning, so tidiness matters mostly for reading target/compiled.

Jinja in a real model

Combining the pieces, a model can generate a status flag per payment method without repeating itself. First the template, rendered, then the result executed on the sample database:

Python Render, then run

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Copy the printed SQL into the next block to run it. (The loop syntax is explained properly in Control Flow and Loops.)

SQL The compiled SQL, executed ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Comments in dbt files

Use {# #} for notes that should not reach the warehouse, and -- for SQL comments that should. A subtle trap: dbt still evaluates Jinja inside -- SQL comments, so a commented-out {{ ref('x') }} still creates a dependency, as the models lesson warned. Wrap code you want to disable in {# ... #} instead:

SQL
{# select * from {{ ref('old_model') }} #}
select * from {{ ref('new_model') }}

Debugging templates

  • Print a value with {{ log(my_var, info=True) }} (a dbt function) or compare with {{ my_var }} in a compiled model.
  • Use dbt compile and open target/compiled/... to see exactly what Jinja produced.
  • Undefined variables print as empty text by default, so a typo can silently produce broken SQL. Look at the compiled file when SQL looks odd.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which delimiter prints the value of an expression?

    Double curly braces evaluate an expression and insert it into the output. {% %} is for statements, {# #} for comments.
  2. 2. How do you assign a variable in Jinja?

    The set statement creates a variable inside {% %}.
  3. 3. What does {%- set x = 1 -%} do differently from {% set x = 1 %}?

    The minus signs remove whitespace on that side of the tag, giving tidier output.
  4. 4. A commented-out line contains -- {{ ref('old_model') }}. What happens?

    dbt renders Jinja even inside SQL comments, so use {# #} to truly disable code.

Summary

  • Jinja has expressions {{ }}, statements {% %} and comments {# #}; dbt renders them before sending SQL to the warehouse.
  • Use set for variables, | filters to transform values, and - to control whitespace.
  • Lists and dicts are Python-style, and join is a handy way to build column lists.
  • Use {# #} to disable Jinja code, and read target/compiled to debug.

Next: Control Flow and Loops in Jinja