#066: Start Using Jinja in dbt Macros (3 Examples)

A dbt macro is a reusable block of SQL and Jinja, the templating language dbt compiles before anything hits the database, and it works like a function in other languages. Three Jinja features cover most of what a custom macro needs. set declares a variable once at the top, including a multi-line block that holds an entire query. run_query executes a statement against the warehouse and optionally hands back the result. And an if condition changes what compiles based on a value, such as whether a model name exists. Below I work through all three on one macro, an audit log that writes a row to Snowflake every time dbt runs.

The macro we're working on

If you're using dbt, at some point you'll create a macro. Macros let you use variables and conditions and make a project more dynamic, similar to a function in other programming languages. They're one of the most valuable parts of the tool.

The deeper you get into macros, the more you run into Jinja. Jinja is powerful but has a learning curve. Knowing a few key functions covers a wide variety of scenarios, and that's what this working session is about.

An audit log for every run

The example is an audit_log macro. Every time dbt runs, it executes some SQL and logs a row in a dbt_audit table in Snowflake, so there's a record of when runs start, when they end, and which models ran in between.

There are other ways to get run history. Building it yourself is a good way to see how the pieces of Jinja fit together, and the same patterns transfer to whatever else you need.

The macro is called from hooks in dbt_project.yml, which is why it needs to handle both whole-run events and individual models:

on-run-start: "{{ audit_log('run start') }}"
on-run-end: "{{ audit_log('run end') }}"

models:
  my_project:
    +post-hook: "{{ audit_log('model', this.name) }}"

The starting version works, with the database, schema and table names typed straight into the insert statement. It works, but we can do better.

Example 1: Using set variables

set lets you declare a variable inside a macro and reuse it. The syntax is a curly brace, a percent sign, set, the name, an equals sign and the value.

{% set audit_schema = 'utilities' %}
{% set audit_table = 'dbt_audit' %}

Best practice is to put these at the top, whether in a model or a macro. To use one, reference it with double curly braces, the same way you reference the macro's own arguments.

insert into {{ audit_schema }}.{{ audit_table }} ...

What to pull out

Look at the query for values that might differ between people or environments, or that you just want cleaned up. In the audit log that's the schema, utilities, and the table, dbt_audit.

In your real scenario you might always hardcode these. I still think it's good practice to set them at the top, partly so they're easy to change later and partly for readability.

Making the database dynamic with target

The last part of the fully qualified name is the database. Instead of hardcoding it, pull it from the target, which is the connection profile dbt is running against.

{% set audit_database = target.database %}

This is where it gets useful. If dev, CI and prod each have their own database, the audit rows land in the right one automatically, depending on who runs it. You don't mix a developer's audit history with production's.

A quick check that nothing broke:

dbt run --select stg_payment_app__transactions

A new row shows up in the Snowflake table. Everything works as expected.

Example 2: Running queries with run_query

The insert statement runs fine as plain SQL in the macro. But a lot of the time you want queries wrapped a certain way, and dbt offers that through run_query.

run_query is a built-in Jinja macro that wraps a statement block, where a statement is a SQL query that hits the database and returns results. There's complexity underneath, but using it is simple, and it returns a table object with the result of the query.

Set the query, then run it

First, store the query in a variable. Because it spans multiple lines, use a set block closed with endset rather than a one-line set.

{% set audit_prep_query %}
    create table if not exists {{ audit_database }}.{{ audit_schema }}.{{ audit_table }} (
        audit_activity varchar,
        object_name varchar,
        logged_at timestamp
    )
{% endset %}

{% set audit_query %}
    insert into {{ audit_database }}.{{ audit_schema }}.{{ audit_table }}
    values ('{{ audit_activity }}', '{{ object_name }}', current_timestamp())
{% endset %}

Then execute each one with do, which calls a function without rendering its output into the compiled SQL.

{% do run_query(audit_prep_query) %}
{% do run_query(audit_query) %}

Technically you could paste the whole query into run_query directly. Setting it first is cleaner and more structured: values at the top, queries defined in the middle, execution at the bottom.

When you want the result back

In the audit log we don't need anything back, so the return value is none and that's fine. When you do, combine set and run_query.

{% set results = run_query(my_query) %}
{% if execute %}
    {% set first_column = results.columns[0].values() %}
{% endif %}

That's the more involved use: run a query, capture the result set, then do something with it. Same function, one more step.

Example 3: Using if conditions

The audit rows now show the model name, except on the run start and run end rows, where the name is blank. The on-run-start and on-run-end context sits outside any individual model, since it's about the entire run.

An if condition fixes that by changing how the macro compiles based on whether a name exists.

The shape of an if block

Like most Jinja, it starts with a curly brace and a percent sign. if and a condition, the output when it's true, else and the output when it isn't, then endif. You can add more conditions, but two is enough here.

{% set object_name %}
    {% if name %}
        {{ name }}
    {% else %}
        n/a
    {% endif %}
{% endset %}

Leaving the condition as just name is enough. If it has a value, it's treated as true, so you don't need to spell out a comparison. And because it's already inside a Jinja block, name doesn't need its own curly braces.

If the name is empty, the row gets n/a instead, meaning not applicable because it isn't a model. The activity column still says run start or run end.

Checking it in Snowflake

Run it again and the table shows what we'd expect: n/a for run start, a model name for the model, n/a for run end. All of it from one macro.

Putting the pieces together

Here's the full macro with all three examples in place.

{% macro audit_log(audit_activity, name=none) %}

    {% set audit_database = target.database %}
    {% set audit_schema = 'utilities' %}
    {% set audit_table = 'dbt_audit' %}

    {% set object_name %}
        {% if name %}{{ name }}{% else %}n/a{% endif %}
    {% endset %}

    {% set audit_prep_query %}
        create table if not exists {{ audit_database }}.{{ audit_schema }}.{{ audit_table }} (
            audit_activity varchar,
            object_name varchar,
            logged_at timestamp
        )
    {% endset %}

    {% set audit_query %}
        insert into {{ audit_database }}.{{ audit_schema }}.{{ audit_table }}
        values ('{{ audit_activity }}', '{{ object_name | trim }}', current_timestamp())
    {% endset %}

    {% do run_query(audit_prep_query) %}
    {% do run_query(audit_query) %}

{% endmacro %}

This is getting pretty legit. It uses the target, set variables, macro arguments, an if condition and run_query together, and it could use a while loop if it needed one.

As you build macros in a real environment with more going on, lean on these pieces. Organize the macro so it's clean for other people to use and maintain in the long run.

Related reading

• dbt Environments vs Targets | What's the Difference?
• A simple 4-step process for creating dbt models
• How to Run Queries from the Terminal (dbt Show Command)
• 5 Tips for a Successful dbt Project

Final takeaway

Most of the macros I write for client projects come down to these three moves: set a value, run a query, branch on a condition. Learn them on something small like an audit log, and the bigger macros stop looking like magic.