Blog
Dbt & Airflow

Ultimate dbt Jinja Cheat Sheet

By
Mayra Peña

Dbt jinja cheat sheet
Key Summary

A practical cheat sheet for using Jinja in dbt — covering syntax, loops, macros, filters, and tips to make SQL models more dynamic, organized, and efficient.

Jinja templating in dbt offers flexibility and expressiveness that can significantly improve SQL code organization and reusability. There is a learning curve, but this cheat sheet is designed to be a quick reference for data practitioners, helping to streamline the development process and reduce common pitfalls.

Whether you're troubleshooting a tricky macro or just brushing up on syntax, bookmark this page. Trust us, it will come in handy and help you unlock the full potential of Jinja in your dbt projects.

If you find this cheat sheet useful, be sure to check out our Ultimate dbt Jinja Functions Cheat Sheet. It covers the specialized Jinja functions created by dbt, designed to enhance versatility and expedite workflows.

dbt Jinja: Basic syntax

This is the foundational syntax of Jinja, from how to comment to the difference between statements and expressions.

Basic Syntax
Comments 
{#  Example Comment  #}
Statements 
{% … %} e.g.: for, if
Expressions 
{{ … }} e.g.: ref(), source(),

dbt Jinja: Variable assignment 

Define and assign variables in different data types such as strings, lists, and dictionaries.

Variable Assignment
Strings 
                {% set my_string = "example" %}
Lists 
{% set my_list = ["apple", "lemon"] %}
Dictionaries 
{% set my_dict = {"fruit_1": "apple",
                   "fruit_2": "lemon"} %}

‍

dbt Jinja: White space control 

Jinja allows fine-grained control over white spaces in compiled output. Understand how to strategically strip or maintain spaces.

White Space Control
Strip Before 
          {%- ... %}
Strip After 
          {% ... -%}
String Before and After 
          {%- ... -%}

       

dbt Jinja: Control flow

In dbt, conditional structures guide the flow of transformations. Grasp how to integrate these structures seamlessly.

Control Flow
If/elif/else/endif 
{%- if target.name == 'dev' -%}
{{ some code }}
{%- elif target.name == 'prod' -%}
{{ some other code }}
{%- else -%}
{{ some other code }}
{%- endif -%}

dbt Jinja: Looping

Discover how to iterate over lists and dictionaries. Understand  simple loop syntax or accessing loop properties.

Looping
Loop Syntax 
{%- for item in my_iterable -%}
  --Do something with item
  {{ item }}
{%- endfor -%}
loop.last
This boolean is False unless the current iteration is the last iteration. 
          {% for item in list %}
  {% if loop.last %}   
    --This is the last item
    {{ item }}
  {% endif %}
{% endfor %}
loop.first
A boolean that is True if the current iteration is the first iteration, otherwise False. 
{% for item in list %}
  {% if loop.first %}
    --first item
    {{ item }}
  {% endif %}
{% endfor %}
loop.index
An integer representing the current iteration of the loop (1-indexed). So, the first iteration would have loop.index of 1, the second would be 2, and so on. 
{% for item in list %}
   --This is item number
   {{ loop.index }}
{% endfor %}
Looping a List 
{% set rating_categories = ["quality_rating",
                            "design_rating",
                            "usability_rating"] %}
SELECT product_id,
 {%- for col_name in rating_categories -%}
   AVG({{ col_name }}) as {{ column_name }}_average
   {%- if not loop.last  -%} 
     , 
   {%- endif -%}
 {%- endfor -%}
 FROM product_reviews
 GROUP BY 1
Compiled code 
SELECT product_id,
   AVG(quality_rating) as quality_rating_average,
   AVG(design_rating) as design_rating_average,
   AVG(usability_rating) as usability_rating_average
FROM product_reviews
GROUP BY 1
Looping a Dictionary 
{% set delivery_type_dict = {"a": "digital_download",
                             "b": "physical_copy"} %}
SELECT order_id,
{%- for type, column_name in delivery_type_dict.items() -%}
COUNT(CASE 
      WHEN delivery_method = '{{ type }}' THEN order_id 
      END) as {{ column_name }}_count
      {%- if not loop.last  -%}
       , 
      {%- endif -%}
      {%- endfor -%}
FROM order_deliveries
GROUP BY 1
SELECT order_id,
COUNT(CASE 
      WHEN delivery_method = 'a' THEN order_id 
      END) as digital_download_count,
COUNT(CASE 
      WHEN delivery_method = 'b' THEN order_id 
      END) as physical_copy_count
FROM order_deliveries
GROUP BY 1

dbt Jinja: Operators 

These logical and comparison operators come in handy, especially when defining tests or setting up configurations in dbt.

Logic Operators
and 
{% if condition1 and condition2 %}
or 
{% if condition1 or condition2 %}
not 
{{  not condition1 }}

‍

Comparison Operators
Equal To 
{% if 1 == 2 %}
Not Equal To 
{% if 1 != 2 %}
Greater Than 
{% if 1 > 2 %}
Less Than 
{% if 1 < 2 %}
Greater Than or Equal to 
{% if 1 >= 2 %}
Less Than or Equal To 
{% if 1 <= 2 %}

dbt Jinja: Variable tests

Within dbt, you may need to validate if a variable is defined or a if a value is odd or even. These Jinja Variable tests allow you to validate with ease.

Jinja Variable Tests
Is Defined 
                  {% if my_variable is defined %}
-- Handle conditions when variable exists
{% endif %}
Is None 

{% if my_variable is none %}
-- Handle absence of my_variable
{% endif %}
Is Even 

{% if my_variable is even %}
-- Handle when my_variable is even
{% endif %}
Is Odd 

{% if my_variable is odd %}
-- Handle when my_variable is odd
{% endif %}
Is a String 

{% if my_variable is string %}
-- Handle when my_variable is a string
{% endif %}
Is a Number 

{% if my_variable is number %}
-- Handle when my_variable is a number
{% endif %}

dbt Jinja: Creating macros & tests

Macros are the backbone of advanced dbt workflows. Review how to craft these reusable code snippets and also how to enforce data quality with tests.

Creating Macros & Tests
Define a Macro
Write your macros in your project's macros directory. 
{% macro ms_to_sec(col_name, precision=3) %}   
  ( {{ col_name }} / 1000 )::numeric(16, {{ precision }})   
{% endmacro %}
Use a Macro from a Model
In a model: 
SELECT order_id,       
  {{ ms_to_sec(col_name=time_ms, precision=3) }} as time_sec
FROM order_timings;
Compiled code: 
SELECT order_id,
(time_ms/ 1000 )::numeric(16, 3) AS time_sec
FROM order_timings;
Run a Macro from the Terminal
Define in your macros directory. Ex)macros/create_schema_macro.sql: 
{% macro create_schema(schema_name) %}
    CREATE SCHEMA IF NOT EXISTS {{ schema_name }};
{% endmacro %}
In Termial:

dbt run-operation create_schema --args '{"schema_name": "my_new_schema"}'
Define a Generic Test
Generic Tests used to be defined in the macros directory. It is now recommended to write your Generic Tests in the tests/generic directory. 

{% test over_10000(model, column_name) %}
  SELECT {{column_name}} 
  FROM {{ model }}   
  WHERE {{column_name}} > 10000     
{% endtest %}
Use a Generic test
In models/schema.yml add the generic test to the model and column you wish to test. 
version: 2

models:
  - name: my_model
    columns:
      - name: column_to_test
        tests:
          - over_10000
          - not_null
Define a Singular Test
Write your dbt Singular tests in the tests directory and give it a descriptive name. Ex) test/test_suspicious_refunds.sql 
    
SELECT order_id, 
SUM(CASE
    WHEN amount < 0 THEN amount 
    ELSE 0 
    END) as total_refunded_amount,       
COUNT(CASE 
     WHEN amount < 0 THEN 1 
     END) as number_of_refunds  
FROM {{ ref('my_model') }}  
GROUP BY 1   
HAVING number_of_refunds > 5

dbt Jinja: Filters (aka Methods)

Fine-tune your dbt data models with these transformation and formatting utilities.

String Manipulation
Lower 
{{ "DATACOVES" | lower }} => "datacoves"
Upper 
{{ "datacoves" | upper }} => "DATACOVES"
Default 
{{ variable_name | default("Default Value") }}    
If value exists => "Sample Value"
If value does not exist => "Default Value"
Trim 
{{ "Datacoves   " | trim }} => "Datacoves"
Replace 
{{ "Datacoves" | replace("v", "d") }} => "Datacodes"
Length 
{{ "Datacoves" | length }} => 9
Capitalize 
{{ "datacoves" | capitalize }} => "Datacoves"
Title 
{{ "datacoves managed platform" | capitalize }}
  => "Datacoves managed platform”
Repeat a String 
{{ print('-' * 20) }}
Substring 
{{ "Datacoves"[0:4] }} => "Data"
Split 
{{ "Data coves".split(' ') }} => ["Data", "coves"]

‍

Number Manipulation
Int 
{{ "20" | int }} => 20
Float 
{{ 20 | float }} => 20.0
Rounding to Nearest Whole Number 
{{ 20.1434 | round }} => 21
Rounding to a Specified Decimal Place 
{{ 20.1434 | round(2) }} => 20.14
Rounding Down (Floor Method) 
{{ 20.5 | round(method='floor') }} => 20
Rounding Up (Ceil Method) 
{{ 20.5 | round(method='ceil') }} => 21

Please contact us with any errors or suggestions.

FAQ

How do you control whitespace in dbt Jinja?

Jinja preserves whitespace by default, which can produce messy compiled SQL. To strip whitespace use a dash  * before a code block use {%- ... %}  * after a code block use {% ... -%}  * both sides, use {%- ... -%}It is  recommended to prioritize readable code over clean compiled output. Do not spend significant time on whitespace control unless it is causing actual SQL errors.

How do you debug Jinja in dbt?

The fastest way to debug Jinja is to run dbt compile or dbt run-operation and inspect the output in target/compiled folder. This shows you the raw SQL your Jinja compiled to, so you can catch logic errors before they hit the warehouse. You can also use {{ print("some message") }} to print values to the terminal during a run. For advanced debugging in dbt check out our document https://docs.datacoves.com/docs/how-tos/dbt/advenced-dbt-debug/ Note, advanced debugging it is not available in dbt Cloud.

How do you pass variables into a dbt model using Jinja?

You can define variables in dbt_project.yml and access them in models with {{ var("my_variable") }}. You can also pass variables at runtime via the command line: dbt run --vars '{"start_date": "2024-01-01"}'. Variables passed via --vars take the highest precedence. It is a good practice to provide a default value using {{ var("my_variable", "default_value") }} to avoid failures when the variable is not set.

How do you run a macro directly from the command line in dbt?

Use dbt run-operation followed by the macro name and any arguments: dbt run-operation my_macro --args '{"schema_name": "my_schema"}'. This is useful for operational tasks like creating schemas, dropping tables, or running admin logic that should not be tied to a model run. Any macro in your macros/ directory is available to run-operation.

What are the three types of Jinja delimiters in dbt?

There are three delimiters, each with a distinct purpose: {{ ... }} are Expressions, used to output values, call functions like ref() and source(), or render variables. {% ... %} are Statements, used for control flow: for loops, if blocks, set assignments, and macro definitions. {# ... #} are Comments, stripped at compile time and never appear in the generated SQL. Getting these three right is the foundation for everything else in dbt Jinja.

What is Jinja in dbt and why does it matter?

Jinja is a Python-based templating language that dbt uses to make SQL dynamic. It lets you write loops, conditionals, variables, and reusable macros directly inside your SQL files. Without Jinja, you are writing static SQL that breaks the moment your logic needs to change. With it, your models become flexible, maintainable, and DRY. If you are using {{ ref() }} or {{ source() }}, you are already using Jinja.

What is loop.last and when should you use it in dbt Jinja?

loop.last is a boolean available inside a Jinja for loop that evaluates to True on the final iteration. It is most commonly used to avoid trailing commas in dynamically generated SQL column lists. Without it, your compiled SQL will include a trailing comma after the last column, which may cause a syntax error for example if not using Snowflake. Pattern: {% if not loop.last %},{% endif %} placed after each dynamic column reference.

What is the difference between a dbt macro and a Jinja variable?

A Jinja variable ({% set my_var = "value" %}) stores a value you reference within a single model. A dbt macro ({% macro my_macro(arg) %}) is a reusable block of logic you can call across multiple models and files, similar to a function in Python. Use variables for model-scoped values. Use macros when the same logic appears in more than one place.

What is the difference between a generic test and a singular test in dbt?

A generic test is a reusable Jinja macro defined in the tests/generic/ directory. It accepts arguments like model and column_name, making it applicable across multiple models via schema.yml. A singular test is a standalone SQL file in the tests/ directory that tests one specific condition on one specific model. Use generic tests for patterns you want to enforce across the project. Use singular tests for one-off validations that are too specific to generalize.

When should you avoid using Jinja in a dbt model?

Jinja adds power but also complexity. Avoid it when plain SQL is readable and sufficient. Overusing Jinja, such as turning every repeated line into a macro, makes models harder to onboard new engineers to, harder to review in pull requests, and harder to debug. The dbt community calls well-written dbt code "dbtonic" and recommends favoring readability over cleverness. If your model is mostly Jinja with little SQL, consider whether the logic belongs in a Python model or a more structured macro instead.

Last updated on
August 5, 2024

Get our free ebook dbt Cloud vs dbt Core

Comparing dbt Core and dbt Cloud? Download our eBook for insights on feature, pricing and total cost. Find the best fit for your business!

Get the PDF
Get free ebook dbt cloud
Author photo profile
Mayra Peña
Author

Data Engineering | Technical Customer Success.

Solving enterprise data challenges quickly with dbt & Airflow.

Table of Contents

Example H2
Example H2

Get our free ebook dbt Cloud vs dbt Core

Success Message
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Free ebook dbt cloud

Continue reading

Blog thumbnailDbt & Airflow

dbt Core vs dbt Cloud: Key Differences and How to Choose


Blog thumbnailDbt & Airflow

What Is dbt? A Guide for Enterprise Data Leaders


Blog thumbnailTooling

Lean Data Stack with dlt, DuckDB, DuckLake, and dbt