dbtonic Jinja | dbt Labs

class: title, center, middle

# 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(stg_stripe__payment) }}`

.grey[`dbt_bhipple.stg_stripe__payment`]

**`analytics.stg_stripe__payment`**

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

---

<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,
  ...
```
---
# 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

.dense-text[

```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') }}

```
]

--

.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"}'
```
]
--
.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 %}

```

```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)
]
---

# 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
---

class: subtitle

## Checkpoint

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

--

## Questions

---

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)

---

class: subtitle

# Zoom Out

<li> Polish your dbt project</li>

<li> Intro to Jinja</li>

<li> dbtonic Jinja</li>

--
<li class="orange"> Packages and projects</li>

<li class="grey">dbt Materializations</li>

<li class="grey">Incremental Models</li>

<li class="grey">Techniques of the trade</li>

<li class="grey">Closing ceremony</li>

<a href="/dbt-learn/lessons/packages">	
    <img	
        style="width: 4%; position: absolute; bottom: 10%; right: 5%;"	
        src="/ui/img/dbt-standalone-new.png"></img>	
</a>

---