Intro to Jinja | dbt Labs

# `{{ Jinja }}`

`{% set date = '2024-09-12' %}`

---

# Jinja is a templating engine

And it's _pythonic_! So it works in ways you'd expect if you've ever
written python, and even if you haven't.

.left-column[
Other templating engines:
* Nunjucks (JavaScript, Jinja-inspired)
* Liquid (Ruby)<sup>1</sup>
]

[_Read the Jinja docs_](https://jinja.palletsprojects.com/en/3.1.x/templates/)

---

<h1>Intro to Jinja | Focus</h1>

<li> Develop a programmer's intuition for how Jinja works </li>

<li> Explain how dbt leverages Jinja for writing powerful SQL </li>

<li> Leverage Jinja for writing DRY SQL </li>

---

# Let's look at some (non-SQL) examples!
--

.center[
<img src="/ui/img/dbt-learn/lessons/intro-to-jinja/bad-jinja-email.png" style="width: 75%;">
]

---
class: subtitle

# Let's see jinja in action now

* We will see some code, try to predict the output.
* Raise your hand or shout out your answer and explanation.
* This thinking is new for some people - let's help each other get acquainted with jinja!

???

Note: This lesson presents the opportunity to make the learners the expert:
1. Call on students to answer and **explain** this will give students more air time.
2. Try to avoid repeating what students say. If something was unclear, asking them to repeat or another student to bring clarity.

If this goes well, we can think about other ways to make other decks more interactive.

---

---

# Setting local variables

```jinja
{% set me = 3 %}
{% set you = 'foo' %}
```
--

```jinja
{{ me }}
{{ you }}
```
--
```
3
foo
```
---

# Dictionaries and Lists

.left-column[
### Lists
A **list** is an ordered collection of elements
- delimited by [  ]
- accessed using numbers
- e.g. my_list[0] 
]

.right-column[
### Dictionaries
A **dictionary** is an unordered collection of elements stored as key-value pairs
- delimited by { }
- accessed using 'keys'
- e.g. my_dictionary['key'] or my_dictionary.key
]

---
# Lists

```jinja
{% set grocery_list = ['chocolate', 'ice cream'] %}

{% do grocery_list.append('cookies') %}
```
--
```jinja
{{ grocery_list[0] }}
```
--

```
chocolate
```

```jinja
{{ grocery_list | length }}
```
--

```
3
```

---
# Dictionaries

```jinja
{% set person = {
  'identifier': 'Serena Williams',
  'country': 'United States',
  'grand slams won': 23
} %}
```
--
```jinja
{{ person.identifier }}
{{ person['country'] }}
```
--

```
Serena Williams
United States
```

---

# And so on

```jinja
{% set us = [
{
    'identifier': 'me',
    'number': 3,
    'details': 'bar'
  },
  {
    'identifier': 'you',
    'number': 100,
    'details': 'foo'
}
] %}
```
--
```jinja
{{ us[1]['details'] }}
```
--
```
foo
```
---
# And so on

```jinja
{% set us = [
{
    'identifier': 'me',
    'number': 3,
    'details': 'bar'
  },
  {
    'identifier': 'you',
    'number': 100,
    'details': 'foo'
}
] %}
```

--
```jinja
{{ us[0].number }}
```
--
```
3
```

---
# Conditions

```jinja
{% set temperature = 80 %}

On a day like this, I especially like

{% if temperature > 70 %}

a refreshing mango sorbet.

{% else %}

a decadent chocolate ice cream.

{% endif %}
```

```
On a day like this, I especially like a refreshing mango sorbet.
```

---

# Loops

```jinja
{% set flavors = ['chocolate', 'vanilla', 'strawberry'] %}

{% for flavor in flavors %}

Today I want {{ flavor }} ice cream!

{% endfor %}
```

```
Today I want chocolate ice cream!
Today I want vanilla ice cream!
Today I want strawberry ice cream!
```

---

# Macros

```jinja
{% macro hoyquiero(flavor, dessert = 'ice cream') %}

Today I want {{ flavor }} {{ dessert }}!

{% endmacro %}
```
--
```jinja
{{ hoyquiero(flavor = 'chocolate') }}
```
--
```
Today I want chocolate ice cream!
```
--
```jinja
{{ hoyquiero('mango', 'sorbet') }}
```
--
```
Today I want mango sorbet!
```
---

class:title, center, middle

# How does this fit in with SQL?

---

# How does this fit in with SQL?

We use Jinja to generate SQL!

* SQL still transforms the data - it's a highly performant and _very_ declarative language
* Jinja helps us write better SQL

### Let's see it in action!
---

## Jinja challenge
.denser-text[
I have a payment table like this:

`select * from {{ ref('stg_stripe__payment') }}`

| payment_id | order_id | payment_method | amount | created_at |
|------------|----------|----------------|--------|------------|
| 1 | 1 | credit_card | 10 | 2018-01-01 |
| ... | ... | ... | ... | ... |
| 29 | 25 | credit_card | 22 | 2018-01-28 |
| 28 | 25 | bank_transfer | 20 | 2018-01-28 |
| 30 | 25 | coupon | 16 | 2018-01-28 |

]

--
.denser-text[

I want a table like this:

| order_id | bank_transfer_amount | coupon_amount | credit_card_amount | gift_card_amount |
|----------|----------------------|---------------|--------------------|------------------|
| 1 | 0 | 0 | 10 | 0 |
| ... | ... | ... | ... | ... |
| 25 | 20 | 16 | 22 | 0 |

]
--
.denser-text[
How do I do this in SQL?
]

???

```sql
with payments as (
select * from {{ ref('stg_stripe__payment') }}
),

aggregated as (

select
order_id,
sum(case when payment_method = 'bank_transfer' then amount else 0 end) as bank_transfer_amount,
sum(case when payment_method = 'coupon' then amount else 0 end) as coupon_amount,
sum(case when payment_method = 'credit_card' then amount else 0 end) as credit_card_amount,
sum(case when payment_method = 'gift_card' then amount else 0 end) as gift_card_amount

from payments

group by 1

)

select * from aggregated
```

```sql
with payments as (
select * from {{ ref('stg_stripe__payment') }}
),

aggregated as (

select
order_id
{% for payment_method in ['bank_transfer', 'coupon', 'credit_card', 'gift_card'] %}
, sum(case when payment_method = '{{ payment_method }}' then amount else 0 end) as {{ payment_method }}_amount
{% endfor %}

from payments

group by 1

)

select * from aggregated
```

---
class: subtitle

# Working session

**Challenge:** Use SQL + Jinja to "pivot" the payments data.

1. write this in pure SQL
2. use _some_ Jinja + SQL
3. Refactor!

]

???

---
class: subtitle, middle, center
# Let's Jinja!

---

# Resources

* [Jinja Docs](https://jinja.palletsprojects.com/en/3.1.x/templates/)