数据库测试故障排除
- 作者仓库星标 620
- 作者仓库 altimate-code
- 领域
- 数据
- 作者声明的智能体
- 未找到明确声明;不据此推断已兼容或已测试
- 静态检查
- 88 / 100 · 启发式扫描,不代表运行安全
- 运行环境证据
- 未执行;当前没有隔离运行测试证据
- 作者 / 版本 / 许可证
- @AltimateAI · 未声明许可证
- 上下文用量评级
- 低消耗
- 接入复杂程度
- 需简单配置
- 是否需要外部接口密钥
- 不需要
- 兼容的系统
- 未声明(默认跨平台)
- 运行环境要求
- Python
- 检测到的文件与系统行为
-
- 只读
- 允许写入 / 修改
- 命令执行
- 网络行为
- 仅限本地
- 安装命令数
- 无(仅作为资料)
需要注意: 未声明工具权限范围;实际权限由运行环境决定。
如果`doctor`失败,请先修复环境。常见问题: 未找到 Python → 重新安装或设置 `--python-path` dbt-core 未安装 → `pip install dbt-core` 否 `dbt_project.yml` → 目录错误 缺少软件包 → 如果 `packages.yml` 存在但 `dbt_packages/` 不存在,请运行 `dbt deps`
错误类型·症状·跳转到编译错误·Jinja/YAML解析失败·references/compilation-errors.md运行时/数据库错误·SQL执行失败·references/runtime-errors.md测试失败·测试返回失败行·references/test-failures.md错误数据·模型构建但数据不正确·下面的步骤3
在访问数据库之前,离线分析已编译的 SQL: 快速修复工具 — 在错误类型明确时使用这些工具:`altimate_core_fix` 最适合编译错误(名称错误、对象丢失)。 `sql_fix` 最适合运行时错误(数据库告诉你出了什么问题)。当第一次修复无法解决问题时,使用`altimate_core_correct`进行迭代多轮修正。常见发现:
当模型构建但产生错误结果时,错误几乎总是存在于数据假设中,而不是 SQL 语法中。您必须探索实际数据才能找到它。常见的错误数据根本原因: 连接扇出:如果行数高于预期,则连接键不唯一 — 使用`SELECT key, count(*) ... GROUP BY 1 HAVING count(*) > 1` 检查 INNER JOIN 中缺少行:如果行数低于预期,请切换到 LEFT JOIN 并检查 NULL 连接键
大多数错误都是从上游模型级联而来:读取父模型。单独构建它们。查询父数据——不要假设它是正确的:
应用修复后:始终使用`--downstream`进行构建以捕获级联影响。然后使用数据查询验证修复 - 不要只信任构建:
# dbt Troubleshooting
## Requirements
**Agent:** any (read-only diagnosis), builder (if applying fixes)
**Tools used:** bash (runs `altimate-dbt` commands), read, glob, edit, altimate_core_semantics, altimate_core_column_lineage, altimate_core_correct, altimate_core_fix, sql_fix
## When to Use This Skill
**Use when:**
- A dbt model fails to compile or build
- Tests are failing
- Model produces wrong or unexpected data
- Builds are slow or timing out
- User shares an error message from dbt
**Do NOT use for:**
- Creating new models → use `dbt-develop`
- Adding tests → use `dbt-test`
- Analyzing change impact → use `dbt-analyze`
## Iron Rules
1. **Never modify a test to make it pass without understanding why it's failing.**
2. **Fix ALL errors, not just the reported one.** After fixing the specific issue, run a full `dbt build`. If other models fail — even ones not mentioned in the error report — fix them too. Your job is to leave the project in a fully working state. Never dismiss errors as "pre-existing" or "out of scope".
## Diagnostic Workflow
### Step 1: Health Check
```bash
altimate-dbt doctor
altimate-dbt info
```
If `doctor` fails, fix the environment first. Common issues:
- Python not found → reinstall or set `--python-path`
- dbt-core not installed → `pip install dbt-core`
- No `dbt_project.yml` → wrong directory
- Missing packages → if `packages.yml` exists but `dbt_packages/` doesn't, run `dbt deps`
### Step 2: Classify the Error
| Error Type | Symptom | Jump To |
|-----------|---------|---------|
| Compilation Error | Jinja/YAML parse failure | [references/compilation-errors.md](references/compilation-errors.md) |
| Runtime/Database Error | SQL execution failure | [references/runtime-errors.md](references/runtime-errors.md) |
| Test Failure | Tests return failing rows | [references/test-failures.md](references/test-failures.md) |
| Wrong Data | Model builds but data is incorrect | Step 3 below |
### Step 3: Isolate the Problem
```bash
# Compile only — catches Jinja errors without hitting the database
altimate-dbt compile --model <name>
# If compile succeeds, try building
altimate-dbt build --model <name>
# Probe the data directly
altimate-dbt execute --query "SELECT count(*) FROM {{ ref('<name>') }}" --limit 1
altimate-dbt execute --query "SELECT * FROM {{ ref('<name>') }}" --limit 5
```
### Step 3b: Offline SQL Analysis
Before hitting the database, analyze the compiled SQL offline:
```bash
# Check for semantic issues (wrong joins, cartesian products, NULL comparisons)
altimate_core_semantics --sql <compiled_sql>
# Trace column lineage to find where wrong data originates
altimate_core_column_lineage --sql <compiled_sql>
# Auto-suggest fixes for SQL errors
altimate_core_correct --sql <compiled_sql>
```
**Quick-fix tools** — use these when the error type is clear:
```
# Schema-based fix: fuzzy-matches table/column names against schema to fix typos and wrong references
altimate_core_fix(sql: <compiled_sql>, schema_context: <schema_object>)
# Error-message fix: given a failing query + database error, analyzes root cause and proposes corrections
sql_fix(sql: <compiled_sql>, error_message: <error_message>, dialect: <dialect>)
```
`altimate_core_fix` is best for compilation errors (wrong names, missing objects). `sql_fix` is best for runtime errors (the database told you what's wrong). Use `altimate_core_correct` for iterative multi-round correction when the first fix doesn't resolve the issue.
Common findings:
- **Wrong join type**: `INNER JOIN` dropping rows that should appear → switch to `LEFT JOIN`
- **Fan-out**: One-to-many join inflating row counts → add deduplication or aggregate
- **Column mismatch**: Output columns don't match schema.yml definition → reorder SELECT
- **NULL comparison**: Using `= NULL` instead of `IS NULL` → silent data loss
### Step 3c: Wrong Data Diagnosis — Deep Data Exploration
When a model builds but produces wrong results, the bug is almost always in the data assumptions, not the SQL syntax. **You must explore the actual data to find it.**
```bash
# 1. Check the output for unexpected NULLs
altimate-dbt execute --query "SELECT count(*) as total, count(<col>) as non_null, count(*) - count(<col>) as nulls FROM {{ ref('<name>') }}" --limit 1
# 2. Check value ranges — are metrics within expected bounds?
altimate-dbt execute --query "SELECT min(<metric>), max(<metric>), avg(<metric>) FROM {{ ref('<name>') }}" --limit 1
# 3. Check distinct values for key columns — do they look right?
altimate-dbt execute --query "SELECT <col>, count(*) FROM {{ ref('<name>') }} GROUP BY 1 ORDER BY 2 DESC" --limit 20
# 4. Compare row counts between model output and parent tables
altimate-dbt execute --query "SELECT count(*) FROM {{ ref('<parent>') }}" --limit 1
```
**Common wrong-data root causes:**
- **Fan-out from joins**: If row count is higher than expected, a join key isn't unique — check with `SELECT key, count(*) ... GROUP BY 1 HAVING count(*) > 1`
- **Missing rows from INNER JOIN**: If row count is lower than expected, switch to LEFT JOIN and check for NULL join keys
- **Date spine issues**: If using `current_date` or `dbt_utils.date_spine`, output changes daily — check min/max dates
### Step 4: Check Upstream
Most errors cascade from upstream models:
```bash
altimate-dbt parents --model <name>
```
Read the parent models. Build them individually. **Query the parent data** — don't assume it's correct:
```bash
altimate-dbt execute --query "SELECT count(*), count(DISTINCT <pk>) FROM {{ ref('<parent>') }}" --limit 1
altimate-dbt execute --query "SELECT * FROM {{ ref('<parent>') }}" --limit 5
```
### Step 5: Fix and Verify
After applying a fix:
```bash
altimate-dbt build --model <name> --downstream
```
Always build with `--downstream` to catch cascading impacts.
**Then verify the fix with data queries** — don't just trust the build:
```bash
altimate-dbt execute --query "SELECT count(*) FROM {{ ref('<name>') }}" --limit 1
altimate-dbt execute --query "SELECT * FROM {{ ref('<name>') }}" --limit 10
# Check the specific metric/column that was wrong:
altimate-dbt execute --query "SELECT min(<col>), max(<col>), count(*) - count(<col>) as nulls FROM {{ ref('<name>') }}" --limit 1
```
## Rationalizations to Resist
| You're Thinking... | Reality |
|--------------------|---------|
| "Just make the test pass" | The test is telling you something. Investigate first. |
| "Let me delete this test" | Ask WHY it exists before removing it. |
| "It works on my machine" | Check the adapter, Python version, and profile config. |
| "I'll fix it later" | Later never comes. Fix it now. |
## Common Mistakes
| Mistake | Fix |
|---------|-----|
| Changing tests before understanding failures | Read the error. Query the data. Understand the root cause. |
| Fixing symptoms instead of root cause | Trace the problem upstream. The bug is often 2 models back. |
| Not checking upstream models | Run `altimate-dbt parents` and build parents individually |
| Ignoring warnings | Warnings often become errors. Fix them proactively. |
| Not running offline SQL analysis | Use `altimate_core_semantics` before building to catch join issues |
| Column names/order don't match schema | Use `altimate_core_column_lineage` to verify output columns match schema.yml |
| Not querying the actual data when debugging wrong results | Always run data exploration queries — check NULLs, value ranges, distinct values |
| Trusting build success as proof of correctness | Build only checks syntax and constraints — wrong values pass silently |
## Reference Guides
| Guide | Use When |
|-------|----------|
| [references/altimate-dbt-commands.md](references/altimate-dbt-commands.md) | Need the full CLI reference |
| [references/compilation-errors.md](references/compilation-errors.md) | Jinja, YAML, or parse errors |
| [references/runtime-errors.md](references/runtime-errors.md) | Database execution errors |
| [references/test-failures.md](references/test-failures.md) | Understanding and fixing test failures |
按说明文件中的顺序排列。
要求
何时使用此技能
铁律
第 1 步:健康检查
第 2 步:对错误进行分类
文件/命令 -> altimate-dbt · dbt-develop · dbt-test · dbt-analyze · dbt build · doctor · --python-path · pip install dbt-core
内容校验值 -> 6de5b17ad3ff
当前原文的自动摘译,尚未人工审校。
要求
代理:任何(只读诊断)、构建器(如果应用修复) 使用的工具:bash(运行
altimate-dbt命令)、read、glob、edit、altimate_core_semantics、altimate_core_column_lineage、altimate_core_ Correct、altimate_core_fix、sql_fix何时使用此技能
在以下情况下使用: dbt 模型无法编译或构建 测试失败 模型产生错误或意外的数据 构建缓慢或超时
铁律
在不了解测试失败原因的情况下,切勿修改测试以使其通过。修复所有错误,而不仅仅是报告的错误。修复特定问题后,运行完整的
dbt build。如果其他模型失败——即使是错误报告中没有提到的模型——也要修复它们。您的工作是让项目处于完全工作状态。切勿将错误视为“预先存在”或“超出范围”而忽略。第 1 步:健康检查
如果
doctor失败,请先修复环境。常见问题: 未找到 Python → 重新安装或设置--python-pathdbt-core 未安装 →pip install dbt-core没有dbt_project.yml→ 目录错误 缺少软件包 → 如果packages.yml存在但dbt_packages/不存在,则运行dbt deps