# Debugging errors

## General process of debugging​

Learning how to debug is a skill, and one that will make you great at your role!

1. Read the error message — when writing the code behind dbt, we try our best to make error messages as useful as we can. The error message dbt produces will normally contain the type of error (more on these error types below), and the file where the error occurred.
2. Inspect the file that was known to cause the issue, and see if there's an immediate fix.
3. Isolate the problem — for example, by running one model a time, or by undoing the code that broke things.
4. Get comfortable with compiled files and the logs.
• The target/compiled directory contains select statements that you can run in any query editor.
• The target/run directory contains the SQL dbt executes to build your models.
• The logs/dbt.log file contains all the queries that dbt runs, and additional logging. Recent errors will be at the bottom of the file.
• dbt Cloud users: Use the above, or the Details tab in the command output.
• dbt CLI users: Note that your code editor may be hiding these files from the tree view (Atom help, VSCode help).
5. If you are really stuck, try asking for help. Before doing so, take the time to write your question well so that others can diagnose the problem quickly.

## Types of errors​

Below, we've listed some of common errors. It's useful to understand what dbt is doing behind the scenes when you execute a command like dbt run.

StepDescriptionError type
InitializeCheck that this a dbt project, and that dbt can connect to the warehouseRuntime Error
ParsingCheck that the Jinja snippets in .sql files valid, and that .yml files valid.Compilation Error
Graph validationCompile the dependencies into a graph. Check that it's acyclic.Dependency Error
SQL executionRun the modelsDatabase Error

Let's dive into some of these errors and how to debug 👇. Note: not all errors are covered here!

## Runtime Errors​

Note: If you're using the dbt Cloud IDE to work on your project, you're unlikely to encounter these errors.

### Not a dbt project​

Running with dbt=0.17.1Encountered an error:Runtime Error  fatal: Not a dbt project (or any of the parent directories). Missing dbt_project.yml file
Debugging
• Use pwd to check that you're in the right directory. If not, cd your way there!
• Check that you have a file named dbt_project.yml in the root directory of your project. You can use ls to list files in the directory, or also open the directory in a code editor and see files in the "tree view".

### Could not find profile​

Running with dbt=0.17.1Encountered an error:Runtime Error  Could not run dbt  Could not find profile named 'jaffle_shops'
Debugging
• Check the profile: key in your dbt_project.yml. For example, this project uses the jaffle_shops (note plural) profile:
dbt_project.yml
profile: jaffle_shops # note the plural
• Check the profiles you have in your profiles.yml file. For example, this profile is named jaffle_shop (note singular).
profiles.yml
jaffle_shop: # this does not match the profile: key  target: dev  outputs:    dev:      type: postgres      schema: dbt_alice      ... # other connection details
• Update these so that they match.
• If you can't find your profiles.yml file, run dbt debug --config-dir for help:

### Invalid dbt_project.yml file​

Encountered an error while reading the project:  ERROR: Runtime Error  at path []: Additional properties are not allowed ('hello' was unexpected)Error encountered in /Users/alice/jaffle-shop-dbt/dbt_project.ymlEncountered an error:Runtime Error  Could not run dbt
Debugging
• Open your dbt_project.yml file.
• Find the offending key (e.g. hello, as per "'hello' was unexpected")
dbt_project.yml
name: jaffle_shophello: world # this is not allowed
• Use the reference section for dbt_project.yml files to correct this issue.
• If you're using a key that is valid according to the documentation, check that you're using the latest version of dbt with dbt --version.

## Compilation Errors​

Note: if you're using the dbt Cloud IDE to work on your dbt project, this error often shows as a red bar in your command prompt as you work on your dbt project. For dbt CLI users, these won't get picked up until you run dbt run or dbt compile.

$dbt run -s customersRunning with dbt=0.17.1Encountered an error:Compilation Error in model customers (models/customers.sql) Model 'model.jaffle_shop.customers' (models/customers.sql) depends on a node named 'stg_customer' which was not found Debugging • Open the models/customers.sql file. • cmd + f (or equivalent) for stg_customer. There must be a file named stg_customer.sql for this to work. • Replace this reference with a reference to another model (i.e. the filename for another model), in this case stg_customers. OR rename your model to stg_customer ### Invalid Jinja​ $ dbt runRunning with dbt=0.17.1Compilation Error in macro (macros/cents_to_dollars.sql)  Reached EOF without finding a close tag for macro (searched from line 1)
Debugging

Here, we rely on the Jinja library to pass back an error, and then just pass it on to you.

This particular example is for a forgotten {% endmacro %} tag, but you can also get errors like this for:

• Forgetting a closing }
• Closing a for loop before closing an if statement

To fix this:

• Navigate to the offending file (e.g. macros/cents_to_dollars.sql) as listed in the error message
• Use the error message to find your mistake

To prevent this:

### Invalid YAML​

dbt wasn't able to turn your YAML into a valid dictionary.

$dbt runRunning with dbt=0.17.1Encountered an error:Compilation Error Error reading jaffle_shop: schema.yml - Runtime Error Syntax error near line 5 ------------------------------ 2 | 3 | models: 4 | - name: customers 5 | columns: 6 | - name: customer_id 7 | tests: 8 | - unique Raw Error: ------------------------------ mapping values are not allowed in this context in "<unicode string>", line 5, column 12 Debugging Usually, it's to do with indentation — here's the offending YAML that caused this error: version: 2models: - name: customers columns: # this is indented too far! - name: customer_id tests: - unique - not_null To fix this: • Open the offending file (e.g. schema.yml) • Check the line in the error message (e.g. line 5) • Find the mistake and fix it To prevent this: • (dbt CLI users) Turn on indentation guides in your code editor to help you inspect your files • Use a YAML validator (example) to debug any issues ### Incorrect YAML spec​ Slightly different error — the YAML structure is right (i.e. the YAML parser can turn this into a python dictionary), but there's a key that dbt doesn't recognize. $ dbt runRunning with dbt=0.17.1Encountered an error:Compilation Error  Invalid models config given in models/schema.yml @ models: {'name': 'customers', 'hello': 'world', 'columns': [{'name': 'customer_id', 'tests': ['unique', 'not_null']}], 'original_file_path': 'models/schema.yml', 'yaml_key': 'models', 'package_name': 'jaffle_shop'} - at path []: Additional properties are not allowed ('hello' was unexpected)
Debugging
• Open the file (e.g. models/schema.yml) as per the error message
• Search for the offending key (e.g. hello, as per "'hello' was unexpected")
• Fix it. Use the model properties docs to find valid keys
• If you are using a valid key, check that you're using the latest version of dbt with dbt --version

$dbt runRunning with dbt=0.17.1-rcEncountered an error:Found a cycle: model.jaffle_shop.customers --> model.jaffle_shop.stg_customers --> model.jaffle_shop.customers Your dbt DAG is not acyclic, and needs to be fixed! ## Database Errors​ The thorniest errors of all! These errors come from your data warehouse, and dbt passes the message on. You may need to use your warehouse docs (i.e. the Snowflake docs, or BigQuery docs) to debug these. $ dbt run...Completed with 1 error and 0 warnings:Database Error in model customers (models/customers.sql)  001003 (42000): SQL compilation error:  syntax error line 14 at position 4 unexpected 'from'.  compiled SQL at target/run/jaffle_shop/models/customers.sql

90% of the time, there's a mistake in the SQL of your model. To fix this:

1. Open the offending file:
• dbt Cloud: Open the model (in this case models/customers.sql as per the error message)
• dbt CLI: Open the model as above. Also open the compiled SQL (in this case target/run/jaffle_shop/models/customers.sql as per the error message) — it can be useful to show these side-by-side in your code editor.
2. Try to re-execute the SQL to isolate the error:
• dbt Cloud: Use the Run SQL button from the model file
• dbt CLI: Copy and paste the compiled query into a query runner (e.g. the Snowflake UI, or a desktop app like DataGrip / TablePlus) and execute it
3. Fix the mistake.
4. Rerun the failed model.

In some cases, these errors might occur as a result of queries that dbt runs "behind-the-scenes". These include:

• Introspective queries to list objects in your database
• Queries to create schemas
• pre-hookss, post-hooks, on-run-end hooks and on-run-start hooks
• For incremental models, and snapshots: merge, update and insert statements

In these cases, you should check out the logs — this contains all the queries dbt has run.

• dbt Cloud: Use the Details in the command output to see logs, or check the logs/dbt.log file
• dbt CLI: Open the logs/dbt.log file.
##### Isolating errors in the logs

If you're hitting a strange Database Error, it can be a good idea to clean out your logs by opening the file, and deleting the contents. Then, re-execute dbt run for just the problematic model. The logs will just have the output you're looking for.

## Common pitfalls​

### run sql vs. dbt run​

(dbt Cloud IDE users only)

There's two interfaces that look similar:

• The run sql button executes whatever SQL statement is in the active tab. It is the equivalent of grabbing the compiled select statement from the target/compiled directory and running it in a query editor to see the results.
• The dbt run command builds relations in your database

Using the run sql is useful when developing models and you want to visually inspect the results of a query. However, you'll need to make sure you have executed dbt run for any upstream models — otherwise dbt will try to select from tables and views that haven't been built.

### Forgetting to save files before running​

We’ve all been there. dbt uses the last-saved version of a file when you execute a command. In most code editors, and in the dbt Cloud IDE, a dot next to a filename indicates that a file has unsaved changes. Make sure you hit cmd + s (or equivalent) before running any dbt commands — over time it becomes muscle memory.

### Editing compiled files​

(More likely for dbt CLI users)

If you just opened a SQL file in the target/ directory to help debug an issue, it's not uncommon to accidentally edit that file! To avoid this, try changing your code editor settings to grey out any files in the target/ directory — the visual cue will help avoid the issue.