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.
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"]
|
Please contact us with any errors or suggestions.
