The dbt Compile Command
As you write more advanced dbt code, your .sql files will become heavily populated with Jinja logic. You will have {{ ref() }} statements, {% if %} statements, and massive {% for %} loops that dynamically generate columns.
Snowflake and BigQuery do not understand Jinja. If you send {{ ref('stg_customers') }} to Snowflake, it will crash immediately.
So how does dbt work? Before dbt sends any code to the database, it performs a Compilation step.
Topics Covered
- What is
dbt compile? - The
target/compiled/directory - Debugging complex Jinja macros
1. What is dbt compile?
When you run dbt run, dbt actually performs two steps behind the scenes:
- Compile: It reads your
.sqlfile, evaluates all the Jinja variables, replaces the{{ ref() }}statements with the actual physical database paths, and generates a pure, standard SQL query. - Execute: It takes that pure SQL query and sends it over the network to Snowflake via a JDBC connection.
Sometimes, you do not want to execute the code. You just want to see the pure SQL that dbt generated so you can debug it.
To do this, you use the compile command:
dbt compile --select fct_orders
This command will evaluate the Jinja, generate the SQL, and stop. It will not touch the database or consume any compute credits.
2. The target/compiled/ Directory
Where does dbt put the compiled SQL?
Every time you run dbt compile, dbt run, or dbt build, dbt creates a folder in your project root called target/.
Inside the target/ directory, there is a compiled/ folder. This folder mimics the exact structure of your models/ folder.
Example:
If you wrote this code in models/marts/fct_orders.sql:
SELECT * FROM {{ ref('stg_orders') }}
After running dbt compile, you can open target/compiled/beems_retail/models/marts/fct_orders.sql. Inside that file, you will find pure SQL:
SELECT * FROM BEEMS_PROD.STAGING.stg_orders
3. Debugging Complex Jinja
Let's assume Priya wrote a complex macro to dynamically generate a pivot table for the 28 states of India.
SELECT
customer_id,
{% for state in ['MH', 'KA', 'DL', 'TN'] %}
SUM(CASE WHEN state_code = '{{ state }}' THEN amount ELSE 0 END) as {{ state }}_revenue
{% if not loop.last %},{% endif %}
{% endfor %}
FROM {{ ref('stg_payments') }}
GROUP BY 1
If Priya runs this and gets a "Syntax Error" from Snowflake, debugging the Jinja loop is extremely difficult. Did she miss a comma? Did she spell a variable wrong?
Instead of guessing, Priya runs:
dbt compile --select fct_state_pivot
She opens the target/compiled/.../fct_state_pivot.sql file and instantly sees the exact SQL dbt generated:
SELECT
customer_id,
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
,
SUM(CASE WHEN state_code = 'TN' THEN amount ELSE 0 END) as TN_revenue
FROM BEEMS_PROD.STAGING.stg_payments
GROUP BY 1
She can now copy-paste this pure SQL directly into the Snowflake UI and debug it perfectly!
Summary
- Data warehouses do not understand Jinja.
dbt compiletranslates your Jinja/SQL mixture into pure, executable SQL.- It does not execute the query against the database.
- The compiled SQL is always saved in the
target/compiled/directory. dbt compileis your best friend when debugging complex loops and macros!