Dbt Troubleshoot

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

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

Common Mistakes

Reference Guides