dbt: Debugging dbt Models with –debug

---

🛠️ Debugging dbt Models with --debug

When working with dbt models, you’re not writing raw SQL — you’re writing templated SQL using Jinja, which includes:

• {{ ref('...') }}
• {{ source('...') }}
• Custom macros
• Conditional logic with if/else

Before dbt runs your models, it must compile them into plain SQL that your data warehouse (e.g., Snowflake, BigQuery, Redshift, etc.) can understand.

✅ The --debug flag helps you:

1. See the actual SQL being run.
2. Get precise error messages from the data warehouse.
3. Understand failures related to logic, joins, schema references, etc.

---

📦 Example

Let’s say you have a model: models/orders_summary.sql

select
  customer_id,
  count(*) as order_count
from {{ ref('orders') }}
group by customer_id

Now you run the model:

dbt run --select orders_summary --debug

---

🧾 Output (simplified example):

16:23:45  Running with dbt=1.7.0
16:23:46  Found 1 model, 0 tests, 0 snapshots

16:23:46  Concurrency: 1 threads (target='dev')
16:23:46  1 of 1 START model analytics.orders_summary ............. [RUN]
16:23:46  SQL:
  select
    customer_id,
    count(*) as order_count
  from analytics.raw_orders
  group by customer_id

16:23:47  Finished running 1 model in 1.23s.

16:23:47  Completed successfully

✅ Notice how {{ ref('orders') }} is replaced with the actual table name (analytics.raw_orders).

If there was a problem (e.g. a typo, or the column doesn’t exist), the debug output would include the error from the database:

Database Error: column "customer_id" does not exist at line 2

---

🔍 When is --debug especially useful?

Scenario	How --debug helps
❌ Model fails	Shows exact SQL that failed + warehouse error
❓ Unexpected data	Lets you check the logic that was actually compiled
🔄 Macros misbehaving	Reveals how macro output looks in final SQL
🧪 Testing Jinja	Helps debug if conditions, loops, and dynamic refs
📚 CI/CD issues	Useful for debugging failed pipelines (especially in logs)

---

✅ Bonus: See compiled SQL in files

You can also inspect compiled SQL in the target/compiled/ directory:

cat target/compiled/your_project/models/orders_summary.sql

This shows exactly what will be sent to your data warehouse — without even running it.

---

🧼 Summary

• --debug prints the fully rendered SQL sent to your warehouse.
• It helps you catch:
  • 🧱 Broken refs
  • 💬 Jinja logic errors
  • 📉 Data warehouse SQL errors
• Essential for faster debugging, especially in complex projects using macros and conditionals.