dbtonic Jinja | dbt Labs

# dbtonic Jinja

### [Jinja Docs](https://jinja.palletsprojects.com/en/2.11.x/templates/) / [docs.getdbt.com](https://docs.getdbt.com/docs/writing-code-in-dbt/jinja-context/adapter)

???
We've added to the Jinja library!
---

# refs

`{{ ref(dim_customers) }}`

**`analytics.dim_customers`**

Use it always! This is how dbt:
* Manages environments
* Infers all model dependencies (DAG)

???
[Instructor notes]
* 'ref' is a macro built into dbt
* it complies to the target_database.target_schema.node_name
---

<h1>dbtonic Jinja | Focus</h1>

<li> Explain the role of macros in a dbt project </li>

<li> Configure and implement variables in a dbt project </li>

<li> Differentiate between Jinja and SQL Comments </li>

---

# Macros
* Macros are written in `.sql` files in the `macros` directory

```sql
-- macros/cents_to_dollars.sql

{% macro cents_to_dollars(column_name, scale=2) %}
    ({{ column_name }} / 100)::numeric(16, {{ scale }})
{% endmacro %}

```

* Then you can call them in models. Take care with quoting!

```sql
-- models/staging/stg_stripe__payment.sql

select
  {{ cents_to_dollars('amount') }} as amount_usd,
  ...
```

???
Demo Time:
* copy the macro script and paste it into a file in the macros folder
* point out that macros are sql files, they just live in the macros folder instead of models
* call the macro in the stg_stripe__payment model using the amount column

---

# Should I use a macro for that?
Macros introduce tradeoffs:
* Pros of macros:
    * Reduce repeated code
    * Allow you to do things you might not otherwise be able to do in SQL
* Cons of macros:
    * Make it harder for someone else to read your code

---
# Which things are macros, really?

A whole bunch:
* `ref()`
* `source()`
* `config()`
* schema tests
* materializations
* custom schema/database logic
* ...

More on that later!

---

class: subtitle, center, middle
# Even more tips
## _for the discerning Jinja writer_

---

## project-level vars

Encode it once, use it everywhere

```yml
# dbt_project.yml
vars:
  the_distant_future: '2099-12-31'    # default value
```

```sql
select
  id,
  valid_from,
  coalesce(valid_to, '{{ var('the_distant_future') }}'::timestamp) as valid_to
from {{ ref('stg_subscriptions') }}

```
]

???
[instructor note]
* project-level variables are set in the dbt_project.yml file
* they can be called anywhere in your project where jinja compliles
* `var` is a macro that complies to the variable definition from the dbt_project.yml file
--

.dense-text[
```sql
select
  id,
  valid_from,
  coalesce(valid_to, '2099-12-31'::timestamp) as valid_to
from dbt_bhipple.stg_subscriptions
```
]

---

# Hot tip

You can override global variables on a specifc run with a `--vars` flag:
.dense-text[
```sql
select
  id,
  valid_from,
  coalesce(valid_to, '{{ var('the_distant_future') }}'::timestamp) as valid_to
from {{ ref('stg_subscriptions') }}
```

```bash
dbt run --vars '{"the_distant_future": "2199-12-31"}'
```
]
???
[instructor note]
* add the --vars flag to your command with this syntax giving your variable a different value to override the default value
--
.dense-text[
```sql
select
  id,
  valid_from,
  coalesce(valid_to, '2199-12-31'::timestamp) as valid_to
from dbt_bhipple.stg_subscriptions
```
]
---

# Logging

```jinja
{% macro audit_helper_column_comparison(model_name) %}
  {%- set columns_to_compare=adapter.get_columns_in_relation(ref(model_name))  -%}

{% for column in columns_to_compare %}
      {{ log('Comparing column "' ~ column.name ~'"', info=True) }}

...

{% endfor %}

{% endmacro %}

```
???
[instructor note]
* The log macro is a way to add dynamic documentation to the logs file
* the macro agruments allow for a string message, other variables/args, and info=true or info=false
* If False, write to the log file. If True, write to both the log file and stdout 
* the default for info is false

```sql
{{ audit_helper_column_comparison('stg_tpch_customers') }}
```
--
```
Comparing column "CUSTOMER_ID"
Comparing column "NATION_ID"
Comparing column "NAME"
```

.caption[
  The code above is a simplified example. Full code example can be found [here](https://github.com/dbt-labs/dbt-audit-helper#advanced-usage)
]
???
* The output here is the log message with all of the columns the audit helper macro is comparing
---

# Logging

Especially useful when you're working on:
- Complicated macros
- Macros that call other macros
- Macros that run introspective queries against the database¹
- Macros that are run as operations¹
- Custom materializations¹

¹Topics for later (or another time)!
---

# Comments

```sql
-- this is a SQL comment
/* as is this */
```
```jinja
{# whereas this is a Jinja comment #}
```

- Only SQL comments show up in your compiled code
    - Jinja comments for dbt things
    - SQL comments for modeling/business logic
- Be careful about having the two in close proximity!

---

# Whitespace control

Add dashes to remove whitespace from compiled output:
- `{{ this }}` → `{{- this -}}`
- `{% set this="hi" %}` → `{%- set this="hi" -%}`
- `{# this #}` → `{#- this -#}`

```jinja
yeah
{# this will be an empty line in the compiled code #}
good.
{#- but this will not! -#}
ok.
```
--
```
yeah

good.ok.
```

---

# You love to see it:

* `set`-ing at the top of files (feels pythonic)
* Formatting code for Jinja-readability (rather than compiled SQL)
* Using whitespace like `{{ this }}` not `{{this}}`
* Using argument names for complex macros
* Macro filenames that match macro names
* Leveraging package macros where possible

???
You hate to see it
* Models that are entirely macros: https://github.com/Datavault-UK/snowflakeDemo/blob/master/models/load/stage/v_stg_inventory.sql
---

## Checkpoint

- **Share Out:** After learning about macros, what are you excited to implement in your own dbt project?

## Questions

---

## Hands-on (10 min)

- Create a macro that generates a surrogate key by concatenating two columns and md5 hashing them
  - Create a .sql file in the macros folder
  - Add the macro jinja to the file
  - Name the macro and define variables
  - Write the code for the conversion
- Implement the macro in a model

---
class: subtitle
# Knowledge check
You should be able to:
* Use SQL + Jinja to pivot a table
* Have a sense for when to use the Jinja docs vs. when to use the dbt docs
* Know where to get started to write your own macro

---

# Resources

* [Using Jinja in dbt](https://docs.getdbt.com/guides/advanced/using-jinja)

* [dbt Jinja Functions](https://docs.getdbt.com/reference/dbt-jinja-functions)

* [Jinja & Macros](https://docs.getdbt.com/docs/build/jinja-macros)