1. Introducing Jinja with dbt
Welcome back! In this chapter, we will focus on dbt Jinja.
2. dbt uses three languages
Did you know that when we use dbt, we are actually using three different languages?
We use: SQL for transformations, YAML for documentation, and Jinja for templating.
In fact, we have already been using Jinja without realizing it!
3. What is dbt Jinja
Jinja is a Pythonic templating language. It's used in dbt for creating dynamic SQL generation and can be found in dbt models, macros, tests, and more. It is extremely useful for creating reusable code to avoid repetition and help make SQL a more dynamic language.
4. Types of dbt Jinja
There are three common types of Jinja.
Jinja statements are enclosed in curly brackets and a percentage followed by another percentage sign and curly brackets. Here is an example of a Jinja statement.
Jinja expressions are enclosed by double curly brackets. They are used to evaluate existing values and insert them into SQL. This dbt reference is an example of a Jinja expression, used to dynamically call and insert an upstream table. We will cover this in the next video.
Lastly, Jinja has comments, enclosed by curly brackets and hash symbols.
5. Jinja statements: set
Jinja statements can take many forms: `set`, `loop`, conditional logic, macros, and so on.
Let's focus on a `set` statement for now.
A `set` statement creates a variable and assigns value to it. It starts with curl brackets and a percentage sign and uses the keyword `set` to indicate that we are creating variables.
Here, we create a variable called country and assign the value Australia.
6. Jinja and dbt compile
But why even set a Jinja variable country?
It's helpful for legibility. For example, inside the customers mart, if we want to repeatedly filter the SQL logic to only Australia, it makes more sense to create a Jinja variable at the top of the file called `country`, and pass that into the filters later.
Note that technically we have two use cases of Jinja here. The set is a Jinja statement creating the variable. The `WHERE` filter is a Jinja expression, calling the variable country and passing it in as a string value.
To check if Jinja is generating the right SQL, we use `dbt compile`. `dbt compile` processes the Jinja templating and converts them into raw SQL. It's very useful for sanity checking.
7. Jinja and dbt compile
Running `dbt compile` actually simultaneously outputs the SQL in two ways.
If we specify a single model for compile, it will output directly into the terminal. Here is an example.
At the same time, `dbt compile` also creates a temporary folder called `target` in the dbt home directory that houses all the compiled files. Here is what it looks like in an IDE exercise.
8. Let's practice!
Let's apply your new Jinja knowledge to some exercises!