signalpilot-labs dbt Debugging Skill
1. Duplicate YML Patches (VERY COMMON)
dbt fails with "Duplicate patch" when the same model appears in multiple YML files. Fix in ONE pass:
- Glob
models/**/*.ymlto find all YML files - Keep the entry with the full contract (descriptions, refs, columns) — usually in a subdirectory YML
- Remove the duplicate from
schema.yml(which typically only has tests)
2. Ref Not Found
If Compilation Error: node not found for ref():
- Check if the name is a raw DuckDB table:
SELECT table_name FROM information_schema.tables WHERE table_name = 'name' - If yes, create an ephemeral stub:
{{ config(materialized='ephemeral') }} select * from main.<name> - If ephemeral causes CTE issues, replace
{{ ref('name') }}withmain.namedirectly
3. Passthrough Model Warning
NEVER create .sql files named after raw tables (e.g. circuits.sql, results.sql).
This DESTROYS source data by replacing it with a materialized model.
Fix: add schema: main to the source definition in YML instead.
4. current_date Fix
If dbt_project_map warns about current_date usage:
- Call
get_date_boundaries— find the column marked "USE THIS" - Replace
current_date/now()with(SELECT MAX(<col>) FROM {{ ref('<table>') }}) - For package models: create
models/<name>.sql, paste full SQL, replace current_date
5. ROW_NUMBER Non-Determinism
If dbt_project_map warns about ROW_NUMBER/RANK:
- Check if ORDER BY columns are unique within each partition
- If not unique, append the primary key to ORDER BY
- Re-run
dbt run --select <model>
6. DuckDB Error Messages
| Error | Fix |
|---|---|
invalid date field format |
STRPTIME(col, '%d/%m/%Y')::DATE |
Table does not exist |
Check actual names with describe_table |
column not found |
Check exact names — case matters in DuckDB |
Cannot mix TIMESTAMP and INTEGER |
Cast both args to same type |
No function matches DOUBLE / VARCHAR |
Add explicit CAST() |
fivetran_utils is undefined |
Run dbt deps (only if packages.yml exists) |
7. Zero-Row Model
Binary search: comment out WHERE clauses and JOINs one at a time to find which condition drops all rows. Most common cause: INNER JOIN where LEFT JOIN is needed.
8. Fan-Out (Too Many Rows)
- Diagnose:
SELECT join_key, COUNT(*) FROM right_table GROUP BY 1 HAVING COUNT(*) > 1 - Fix A: pre-aggregate right table before joining
- Fix B:
SELECT DISTINCT(if valid for the grain) - Fix C:
ROW_NUMBER()dedup pattern