2022-12-30

Jinja and macros

Introduction

dbt allows you to write flexible SQL code using Jinja and macros. This article describes the notation and usage of Jinja and macros.

Jinja

Jinja is a kind of template engine that can be used to write not only SQL, but also HTML, CSS, and many other text files.

Jinja provides the following three notations.

{% ... %}: control syntax
Controls the flow of processing, such as for or if.
{{ ... }}: Expression
Outputs the result of an expression as a string.
{# ... #}: comment
You can write comments to make the program easier for people to read. They will not be output in the final SQL.

if syntax

Jinja uses the if syntax as follows.

{% if kenny.sick %}
    Kenny is sick.
{% elif kenny.dead %}
    You killed Kenny!  You bastard!!!
{% else %}
    Kenny looks okay --- so far
{% endif %}

If the if syntax is written on a single line, it looks like this.

{{ env if env is defined else 'dev' }}

for syntax

In Jinja, the for syntax is described as follows.

{% for col in columuns %}
  {{ col }} ,
{% endfor %}

{% for key, val in dict %}
  {{ key }}, {{ val }}
{% endfor %}

Special variables can be used in the for syntax.

Variable	Description
loop.index	The current iteration of the loop. (1 indexed)
loop.index0	The current iteration of the loop. (0 indexed)
loop.revindex	The number of iterations from the end of the loop (1 indexed)
loop.revindex0	The number of iterations from the end of the loop (0 indexed)
loop.first	True if first iteration.
loop.last	True if last iteration.
loop.length	The number of items in the sequence.
loop.cycle	A helper function to cycle between a list of sequences. See the explanation below.
loop.depth	Indicates how deep in a recursive loop the rendering currently is. Starts at level 1
loop.depth0	Indicates how deep in a recursive loop the rendering currently is. Starts at level 0
loop.previtem	The item from the previous iteration of the loop. Undefined during the first iteration.
loop.nextitem	The item from the following iteration of the loop. Undefined during the last iteration.
loop.changed(*val)	True if previously called with a different value (or not called at all).

dbt's own Jinja functions

dbt defines its own Jinja functions. In addition to the commonly used config(), ref(), and source(), the following functions are available.

dbt Jinja functions
adapter	execute	run_query
as_bool	flags	run_started_at
as_native	fromjson	schema
as_number	fromyaml	schemas
as_text	graph	selected_resources
builtins	invocation_id	set
config	log	source
cross-database macros	model	statement blocks
dbt_project.yml Context	modules	target
dbt_version	on-run-end Context	this
debug	print	tojson
dispatch	profiles.yml Context	toyaml
doc	project_name	var
env_var	ref	zip
exceptions	return

Jinja notation example

Suppose you have the following SQL.

models/order_payment_method_amounts.sql

select
  order_id,
  sum(case when payment_method = 'bank_transfer' then amount end) as bank_transfer_amount,
  sum(case when payment_method = 'credit_card' then amount end) as credit_card_amount,
  sum(case when payment_method = 'gift_card' then amount end) as gift_card_amount,
sum(amount) as total_amount
from {{ ref('raw_payments') }}
group by 1

The above SQL can be written using Jinja's for loop and variables as follows.

models/order_payment_method_amounts.sql

{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}

select
  order_id,
  {% for payment_method in payment_methods %}
  sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount,
  {% endfor %}
  sum(amount) as total_amount
from {{ ref('raw_payments') }}
group by 1

Use loop.last to avoid putting a comma at the end of a for loop.

models/order_payment_method_amounts.sql

{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}

select
  order_id,
  {% for payment_method in payment_methods %}
  sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount
  {% if not loop.last %},{% endif %}
  {% endfor %}
from {{ ref('raw_payments') }}
group by 1

Macros

Macros are functions that create reusable code. Place SQL files in the directory specified by macro-paths in dbt_project.yml (macros by default) and write macros in them.

Jinja and macros

Introduction

Jinja

if syntax

for syntax

dbt's own Jinja functions

Jinja notation example

Macros

References

DAG in dbt

dbt test

Ryusei Kakujo