Skip to main content

Debugging Macros with dbt compile

Writing Jinja macros is incredibly powerful, but it introduces a massive new problem: Debugging.

When you write a complex {% for %} loop that dynamically generates 50 columns, and you run dbt run, Snowflake might immediately reject your query and throw a Syntax Error: Unexpected Token ','.

Looking at your .sql file won't help you, because the syntax error isn't in your .sql file. The syntax error is in the generated SQL that dbt created after it evaluated the Jinja!

To solve this, you must compile before you run.

Topics Covered​

  • The Danger of Jinja Loops
  • Using dbt compile
  • Inspecting the target/compiled folder

1. The Danger of Jinja Loops​

Let's assume our Data Engineer, Priya, writes a loop to dynamically pivot revenue for different Indian states.

-- models/marts/fct_state_revenue.sql

SELECT
order_date,
{% for state in ['MH', 'KA', 'DL'] %}
SUM(CASE WHEN state_code = '{{ state }}' THEN amount ELSE 0 END) as {{ state }}_revenue,
{% endfor %}
FROM {{ ref('stg_orders') }}
GROUP BY 1

Priya types dbt run --select fct_state_revenue. Snowflake instantly crashes. Why?

2. Using dbt compile​

Instead of staring blindly at the Jinja code trying to guess the issue, Priya runs:

dbt compile --select fct_state_revenue

The dbt compile command does everything dbt run does, but it stops exactly one second before sending the code to Snowflake. It evaluates the Jinja, replaces the {{ ref() }}, and saves the pure, raw SQL to your hard drive.

3. Inspecting the target/compiled Folder​

Every time you compile, dbt writes the output to the hidden target/compiled/ directory in your project root.

Priya opens target/compiled/beems_retail/models/marts/fct_state_revenue.sql and sees exactly what dbt generated:

SELECT
order_date,

SUM(CASE WHEN state_code = 'MH' THEN amount ELSE 0 END) as MH_revenue,

SUM(CASE WHEN state_code = 'KA' THEN amount ELSE 0 END) as KA_revenue,

SUM(CASE WHEN state_code = 'DL' THEN amount ELSE 0 END) as DL_revenue,

FROM BEEMS_PROD.STAGING.stg_orders
GROUP BY 1

The Bug is Obvious! Look at the third line inside the SELECT statement: DL_revenue,. There is a trailing comma right before the FROM clause! Snowflake crashed because you cannot have a comma immediately preceding a FROM statement.

4. Fixing the Jinja​

Now that Priya can actually see the bug, she knows exactly how to fix her Jinja macro. She needs to use the loop.last variable to ensure the comma is only added if it is not the final iteration of the loop.

-- models/marts/fct_state_revenue.sql

SELECT
order_date,
{% for state in ['MH', 'KA', 'DL'] %}
SUM(CASE WHEN state_code = '{{ state }}' THEN amount ELSE 0 END) as {{ state }}_revenue

-- THE FIX: Only add a comma if it's not the last state!
{% if not loop.last %} , {% endif %}

{% endfor %}
FROM {{ ref('stg_orders') }}
GROUP BY 1

She runs dbt compile again, checks the target/ folder, verifies the trailing comma is gone, and then runs dbt run.

Summary​

  • Jinja errors are almost impossible to debug by looking at the .sql source code.
  • Always run dbt compile when writing complex macros or loops.
  • Open the target/compiled/ directory to see the exact, raw SQL that your Jinja generated.
  • Debug the pure SQL, find the syntax error, and then adjust your Jinja to fix it.