1. Complex usage of jinja with dbt
Welcome back! Let's dive into some more complex use cases of dbt Jinja.
2. Jinja statement: loop
Previously, we learned Jinja expressions like `set`, now let's discuss loops. Jinja loops starts with a `for` and ends with a `endfor`, one word.
Here is a loop that iterates through a list of order statuses and generates a `SUM..CASE..WHEN` statement for each status in the list.
3. Jinja statement: loop
Let's apply this to our Looker e-commerce project.
It's common for orders to have multiple order statuses, so we end up writing repeated case when statements for each order status.
Without Jinja, we will have to write three iterations of the `SUM..CASE WHEN..` statements.
4. Jinja statement: loop
With Jinja, we can use a dbt `set` statement to loop through a Jinja variable called `order_statuses` with the three unique status values.
Then we use a Jinja loop to iterate through. Note that not only is the status value parameterized, we also parameterized the column name.
5. Jinja statement: loop
Finally, don't forget to use `dbt compile` for a sanity check.
6. Jinja statement: macros
One last statement worth going over is the Jinja macro.
A macro is a reusable Jinja function stored in the macros folder. It's helpful to combine multiple SQL functions into one. For example, `ROUND()` and `COALESCE()` are often used together for data cleaning.
The `COALESCE()` function is used to handle missing data by replacing NULL values with the first non-null value. Here, if the nickname is missing, return the first name.
7. Jinja statement: macros
A macro always starts with `macro` and ends with `endmacro`, one word.
Here, we are creating a macro called `coalesce_and_round`, which takes in two input parameters, `column_name` and `decimal_places`. If `decimal_places` is not specified, it defaults to value 2.
This function takes in the input column and coalesces NULL values to zero in the column, and then it rounds the output to the decimal place specified.
8. Jinja statement: macros
If we have been repeatedly using `coalesce` and round to calculate sales and cost, we can now replace that with the macro we wrote.
First, we save the macro we wrote inside the macros directory, and then, we replace the repeated SQL logic with the macro call, using two curly brackets.
9. Managing Jinja whitespace
Lastly, let's talk aesthetics. Jinja preserves spaces and newlines by default, which causes some unwanted whitespaces between what is written and what is compiled. Here is the written SQL file.
10. Managing Jinja whitespace
And here is what it gets compiled as. A little too much whitespace, no?
11. Managing Jinja whitespace
Use the minus sign inside the Jinja curly-brace delimiters helps to strip leading or trailing unwanted whitespace.
Here is an example of its application. In general, this takes trial and error and should be done iteratively with `dbt compile`.
12. Let's practice!
We've covered a lot of ground for dbt Jinja. Let's put this to practice!