Back to Details

debugging-dbt-errors

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

dbt Troubleshooting

dbt 故障排查

Read the full error. Check upstream first. ALWAYS run 
dbt build
after fixing.
读取完整错误信息。首先检查上游依赖。修复后务必运行
dbt build
。

Critical Rules

核心规则

  1. ALWAYS run 
    dbt build
    after fixing     - compile is NOT enough to verify the fix
  2. If fix fails 3+ times, stop and reassess your entire approach
  3. Verify data after build - build passing doesn't mean output is correct
  1. 修复后务必运行
    dbt build
     - 仅compile不足以验证修复效果
  2. 如果修复失败3次及以上，请停止并重新评估整体解决思路
  3. 构建完成后验证数据 - 构建通过不代表输出结果正确

Workflow

工作流程

1. Get the Full Error

1. 获取完整错误信息

bash
dbt compile --select <model_name>
bash
dbt compile --select <model_name>

or

or

dbt build --select <model_name>

Read the COMPLETE error message. Note the file, line number, and specific error.
dbt build --select <model_name>

读取完整的错误信息。记录涉及的文件、行号和具体错误内容。

2. Inspect Actual Data (For Data Issues)

2. 检查实际数据（针对数据问题）

Before fixing "wrong output" or "incorrect results", query the actual data:
bash
undefined
在修复“输出错误”或“结果不正确”问题前，请先查询实际数据：
bash
undefined

Preview current output

预览当前输出

dbt show --select <model_name> --limit 20
dbt show --select <model_name> --limit 20

Check specific values with inline query

通过内联查询检查特定值

dbt show --inline "select * from {{ ref('model_name') }} where <condition>" --limit 10
dbt show --inline "select * from {{ ref('model_name') }} where <condition>" --limit 10

Compare with expected - look for patterns

与预期结果对比，寻找规律

dbt show --inline "select column, count(*) from {{ ref('model_name') }} group by 1 order by 2 desc" --limit 10

**Understand what's wrong before attempting to fix it.**
dbt show --inline "select column, count(*) from {{ ref('model_name') }} group by 1 order by 2 desc" --limit 10

在尝试修复前，先明确问题所在。

3. Read Compiled SQL

3. 查看编译后的SQL

bash
cat target/compiled/<project>/<path>/<model_name>.sql
See the actual SQL that will run.
bash
cat target/compiled/<project>/<path>/<model_name>.sql
查看即将执行的实际SQL代码。

4. Analyze Error Type

4. 分析错误类型

Error TypeLook For
Compilation ErrorJinja syntax, missing refs, YAML issues
Database ErrorColumn not found, type mismatch, SQL syntax
Dependency ErrorMissing model, circular reference
错误类型检查要点
Compilation ErrorJinja语法、缺失引用、YAML格式问题
Database Error列不存在、类型不匹配、SQL语法错误
Dependency Error模型缺失、循环引用

5. Check Upstream Models

5. 检查上游模型

bash
undefined
bash
undefined

Find what this model references

查找当前模型引用的依赖

grep -E "ref(|source(" models/<path>/<model_name>.sql
grep -E "ref(|source(" models/<path>/<model_name>.sql

Read upstream model to verify columns

查看上游模型以验证列信息

cat models/<path>/<upstream_model>.sql

Many errors come from upstream changes, not the current model.
cat models/<path>/<upstream_model>.sql

许多错误源自上游模型的变更，而非当前模型本身。

6. Apply Fix

6. 实施修复

Common fixes:
ErrorFix
Column not foundCheck upstream model's output columns
Ambiguous columnAdd table alias: 
table.column
Type mismatchAdd explicit 
CAST()
Division by zeroUse 
NULLIF(divisor, 0)
Jinja errorCheck matching 
{{ }}
and 
{% %}
常见修复方案：
错误场景修复方法
列不存在检查上游模型的输出列
列名歧义添加表别名：
table.column
类型不匹配显式添加
CAST()
转换
除零错误使用
NULLIF(divisor, 0)
处理
Jinja错误检查
{{ }}
和
{% %}
是否配对

7. Rebuild (MANDATORY)

7. 重新构建（强制要求）

bash
dbt build --select <model_name>
3-Failure Rule: If build fails 3+ times, STOP. Step back and:
  1. Re-read the original error
  2. Check if your entire approach is wrong
  3. Consider alternative solutions
bash
dbt build --select <model_name>
三次失败规则：如果构建失败3次及以上，请停止。退一步并执行以下操作：
  1. 重新阅读原始错误信息
  2. 检查你的整体解决思路是否错误
  3. 考虑其他替代方案

8. Verify Fix

8. 验证修复效果

bash
undefined
bash
undefined

Preview the data

预览数据

dbt show --select <model_name> --limit 10
dbt show --select <model_name> --limit 10

Run tests

运行测试

dbt test --select <model_name>
undefined
dbt test --select <model_name>
undefined

9. Re-review Logic Against Requirements

9. 根据需求重新检查逻辑

After fixing, re-read the original request and verify:
  • Does the output match what the user asked for?
  • Are the column names exactly as requested?
  • Is the calculation logic correct per the requirements?
  • Did you solve the actual problem, not just make the error go away?
修复完成后，重新阅读原始需求并验证：
  • 输出结果是否符合用户需求？
  • 列名是否完全符合要求？
  • 计算逻辑是否符合需求规定？
  • 你是否解决了实际问题，而非仅仅消除了错误提示？

10. Check Downstream Impact

10. 检查下游影响

bash
undefined
bash
undefined

Find downstream models

查找下游模型

grep -r "ref('<model_name>')" models/ --include="*.sql"
grep -r "ref('<model_name>')" models/ --include="*.sql"

Rebuild downstream

重新构建下游依赖

dbt build --select <model_name>+
undefined
dbt build --select <model_name>+
undefined

Error Categories

错误分类

Compilation Errors

编译错误

  • Check Jinja syntax: matching 
    {{ }}
    and 
    {% %}
  • Verify macro arguments
  • Check YAML indentation
  • 检查Jinja语法：确保
    {{ }}
    和
    {% %}
    配对正确
  • 验证宏参数
  • 检查YAML缩进格式

Database Errors

数据库错误

  • Read compiled SQL in 
    target/compiled/
  • Check column names against upstream
  • Verify data types
  • 查看
    target/compiled/
    目录下的编译后SQL
  • 对照上游模型检查列名
  • 验证数据类型

Test Failures

测试失败

  • Read the test SQL to understand what it checks
  • Compare your model output to expected behavior
  • Check column names, data types, NULL handling
  • 阅读测试SQL以了解其检查逻辑
  • 将模型输出与预期行为对比
  • 检查列名、数据类型、NULL值处理

Anti-Patterns

反模式

  • Making random changes without understanding the error
  • Assuming the current model is wrong before checking upstream
  • Not reading the FULL error message
  • Declaring "fixed" without running build
  • Getting stuck making small tweaks instead of reassessing
  • 在未理解错误原因的情况下随意修改代码
  • 未检查上游模型就假设当前模型存在问题
  • 未读取完整的错误信息
  • 未运行build就宣称问题已修复
  • 陷入小调整的循环而不重新评估整体思路