Compile vs Database Errors
Concepts covered: Compilation
2 moments a model can fail
Section 0 fixed the order: dbt renders, then the warehouse executes. Each step can fail, and the 2 failures print different headings, point to different files and call for different fixes. A Compilation Error happens inside dbt while it turns Jinja into SQL, and the warehouse received nothing. A Database Error happens inside the warehouse after it received SQL it could not run; rendering succeeded, and the compiled file exists for you to read.
- Compilation Error
- Raised by dbt while rendering Jinja or resolving refs
- The warehouse received nothing
- Causes include a ref() to a model that doesn't exist and a missing endfor or endif. A name that was never set does it too
- Fix the template, and dbt compile confirms the fix without building
- Database Error
- Raised by the warehouse while running the SQL
- dbt forwards the warehouse's own message, line number included
- Causes: a misspelled column, a type mismatch, a comma the loop left behind
- Find the cited line in target/run, read it in target/compiled, then fix the template that produced it
Each error in the terminal
Type {{ ref('stg_merge_request') }} with the final s missing, and dbt reports a Compilation Error in model mr_by_state. The message names the file and says the model depends on a node that was not found. Because dbt resolves every ref() when it parses the project, this error arrives before any node runs, and the whole command stops. A {% for %} with no {% endfor %} fails the same way: Jinja cannot finish the template, so there's no SQL to send.
Type stat for state inside the CASE and the template renders without complaint, because Jinja has no idea what a column is. The warehouse rejects the query, and dbt reports a Database Error in model mr_by_state followed by the warehouse's message: a column that does not exist, and the line it appears on. That line counts the statement in target/run, wrapper included, so find it there and then read the same text in the compiled file. Other models keep building; this one fails and its children are skipped, as you saw with failing tests.
The case that confuses people sits between the 2. Remove the if not loop.last guard from section 2's loop and every pass emits a comma, so the compiled file reads as closed_mrs, on 1 line and from on the next. Jinja was content: a comma is text. The warehouse reports a syntax error near from. The heading says Database Error but the cause is in the template, so read the compiled file to see the stray comma, then fix the loop that printed it.
| The heading reads | What failed | Where to look |
|---|---|---|
| Compilation Error in model ... | Jinja rendering or dependency resolution, inside dbt | the template; the compiled file for that model is missing or stale |
| Database Error in model ... | the warehouse rejected the SQL it received | target/run at the cited line, then the same text in target/compiled |
| Database Error after editing a loop or an if | rendering succeeded and produced SQL the warehouse cannot run | the compiled file to see the shape, then the template to fix it |
A debugging order
- Read the heading before the message: it tells you which program failed
- Compilation Error: fix the template, then run dbt compile until it finishes clean
- Database Error: find the cited line in target/run, then read that SQL in target/compiled
- Run that compiled SQL on its own in a SQL client to confirm the diagnosis
- Fix the template, compile, and compare the new compiled file with the old one
The 2 kinds also differ in blast radius. A compilation error raised during parsing stops the entire command before any model builds, because dbt can't trust a DAG it couldn't resolve. A database error fails 1 node: dbt marks its descendants as skipped and continues with every node that does not depend on it. If a run summary shows 1 error and 6 skips next to 40 successes, the error came from the warehouse, so a compiled file exists for you to open.
About this lesson
This section is part of the Jinja and Macros: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.