Introducing Jinja with dbt

Case Study: Building E-Commerce Data Models with dbt

Susan Sun

Freelance Data Scientist

dbt uses three languages

dbt uses three languages:

SQL for transformations
YAML for documenting and testing
Jinja for templating

Example of SQL:
```
SELECT * FROM ...
```

Example of YAML:

- name: customers
  description: A data mart

Example of Jinja:
```
FROM {{ ref('stg_looker__users') }}
```

What is dbt Jinja

What is dbt Jinja?

A Pythonic templating language
Used to enable dynamic SQL generation
Found in dbt models, macros, tests, etc.

Why is it useful?

Better collaboration
Less repetition (DRY), more reusable code

Examples of Jinja in dbt:

Referencing dbt models

SELECT *
FROM {{ ref('stg_looker__users') }}

Creating loops

{% for column in ['col1', 'col2'] %}
  SELECT {{ column }}
  FROM table_name
{% endfor %}

Types of dbt Jinja

Jinja statements are enclosed in {%...%}

 {% set order_statuses = ['Shipped', 'Complete', 'Processing'] %}

Jinja expressions are enclosed in {{...}}

 SELECT * FROM {{ ref('stg_looker__users') }}

Jinja comments are enclosed in {# #}
```
 {# This is a comment #}
```

Jinja statements: set

Types of Jinja statements: set, loop, conditional (if/else), macros, etc.
A set Jinja statement creates a variable and assigns value.
- Template:
```
{% set ... %}
```
- Example:
```
{% set country = 'Australia' %}
```

Jinja and dbt compile

Usage

Inside customers.sql, as written:

{% set country = 'Australia' %}

SELECT ...
FROM ...
WHERE country = '{{ country }}'

Compiled code

dbt compile -s customers.sql output:



SELECT ...
FROM ...
WHERE country = 'Australia'

Jinja and dbt compile

Terminal output for a single model
dbt compile -s customers.sql output:

Running with dbt=1.8.4
...
Compiled node 'customers' is:
WITH customer_base AS (
    SELECT 
...
    FROM "dbt"."main"."stg_looker__users"
    WHERE country = 'Australia'
)
...

Generated /target/compiled directory
dbt compile generated compiled files:

Screenshot of a series of file directories auto-generated by dbt compile subcommand. Top level folder is the looker ecommerce dbt project. Second tier is called `target` and third tier is `compiled`. Underneath `compiled directory are a series of directories that mimics the dbt project set up where the compiled queries for models and tests live.

Let's practice!

Case Study: Building E-Commerce Data Models with dbt