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

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
Runtime/Database Error	SQL execution failure	references/runtime-errors.md
Test Failure	Tests return failing rows	references/test-failures.md
Wrong Data	Model builds but data is incorrect	Step 3 below

Step 3: Isolate the Problem

# 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:

# 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.

# 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:

altimate-dbt parents --model <name>

Read the parent models. Build them individually. Query the parent data — don't assume it's correct:

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:

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:

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	Need the full CLI reference
references/compilation-errors.md	Jinja, YAML, or parse errors
references/runtime-errors.md	Database execution errors
references/test-failures.md	Understanding and fixing test failures