# Dbt Fixer > Fix dbt models via iterative compile-fix loop. Scoped to dbt projects assigned to the current phase. Use when the stabilization skill invokes dbt fixing. - Skill: `snowflake-labs/dbt-fixer` (Agent Skill) - Install (CLI): `npx skillmds@latest add snowflake-labs/dbt-fixer` - Raw SKILL.md: https://api.skillmd.com/api/skills/snowflake-labs/dbt-fixer/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - License: Proprietary. See License-Skills for complete terms - Author: snowflake-labs (https://skillmd.com/u/snowflake-labs) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/snowflake-labs/dbt-fixer --- # dbt Fixer Fix dbt projects generated by SnowConvert. Scope includes: model SQL files (staging, intermediate, mart), macros, and project configuration (dbt_project.yml, sources.yml). The fixer first resolves bootstrap issues (placeholder config, broken macros) to make the project compilable, then uses an iterative compile-fix-recompile loop to resolve model-level errors until all dbt nodes pass. > **Autonomous mode:** When spawned as a team agent, skip all interactive stopping points, make reasonable defaults for user-facing decisions, and document assumptions in the completion report. When done, send a `send_message` to `main` summarizing your results, and respond to any `shutdown_request` with `send_message` using `type: "shutdown_response"` and `approve: true`. ## Main Mode (interactive / standalone use) ### Input > **Concurrent execution note:** You may be spawned concurrently with other agents fixing different dbt projects in the same ETL unit. Each agent operates on its own project — do not access or modify files belonging to other dbt projects. Writing test/fix artifacts for another agent's project (even to be helpful) breaks that project's own validator lookup and causes a false missing-artifact failure at phase completion. This sub-skill expects: - `session_status.json` — from track_status.py init (contains `dbt_projects` array with project names and paths, and `source_file_path`) - The dbt project folder path (from `dbt_projects` in session status) - The source definition file path (from `source_file_path` in session status) — for understanding original ETL intent - The test report at `/stabilization/tests/dbt//test_report.md` — **required** (contains baseline failures and source-to-dbt component mapping). If this file does not exist, tests have not been generated yet — STOP and return to the parent skill to run Step 9 (dbt-test-gen) first ### Prerequisites - **MUST be called AFTER dbt-test-gen** — tests must exist before fixing begins. The parent skill (stabilization/SKILL.md) enforces this via the phase workflow. If the project status is `pending` instead of `dbt-tested`, STOP and return to the parent skill to run test generation first. - `dbt-snowflake` installed and available on PATH - `dbt compile` needs no warehouse, but it is only a syntactic pre-filter — it cannot tell a correct fix from a wrong one (see Step 2.3) - **A warehouse is required to actually verify a fix.** The convergence gate is the source-derived semantic tests, which must execute. Without a warehouse, nodes can only be recorded with status `unverified` (plus a reason), never as fixed. ### Phase 1: Analyze and Plan 1. Read `ROADMAP.md` for current phase. Process only assigned dbt projects; ignore others. 2. Read `session_status.json` for `dbt_projects` array 3. For each assigned dbt project: read `dbt_project.yml`, `models/sources.yml`, list `.sql` model files. The orchestrator has already registered nodes via `track_status.py init-dbt`. 4. Ensure backup exists at `/stabilization/original/`; create if missing 5. **Bootstrap fixes (before compile loop):** Read the test report's `bootstrap_status` section. If blockers exist: a. Fix `dbt_project.yml` — replace placeholder values (YOUR_PROJECT_NAME → derive from folder name or source definition; YOUR_PROFILE_NAME → use a sensible default based on the Snowflake connection) b. Fix macro syntax — resolve `!!!RESOLVE EWI!!!` markers in `macros/*.sql`, fix unclosed blocks c. Fix `sources.yml` — if source definitions reference non-existent schemas or tables, update to match the test environment d. After each fix, attempt `dbt parse` to validate 6. Run `dbt compile --project-dir --profiles-dir ~/.dbt`; parse errors to identify failing nodes 7. Build fixing plan: bootstrap fixes first (already applied above) → model compilation errors → EWI markers → complex transformations 8. Present summary (total nodes, passing/failing, error types, recommended order) 9. ⚠️ **STOPPING POINT** (interactive mode): confirm with user. Autonomous agents: skip and proceed. ### Phase 2: Fix (Iterative Compile-Fix Loop) After user confirms, work through failing nodes. For each node: ### Step 2.1: Gather Context 1. Read the failing node's `.sql` file 2. Read the original pre-fix version from `/stabilization/original//` — to identify original `ref()`/`source()` targets and logic to preserve 3. Read its model dependencies; verify they expose the columns needed 4. Read `dbt_project.yml` (variable definitions), `sources.yml` (source schemas), and the current error message 4b. Read all macro files under `macros/` — check for syntax errors and EWI markers 5. Read the source dataflow component (from `source_file_path` if available) — especially useful for EWI markers 6. Read the test report for baseline failures on this node — assertions describe expected behavior from source ETL 7. Check `tracking/fix-log.md` for prior successful fixes matching the same error type or EWI code ### Step 2.2: Analyze and Fix Analyze the error and apply a fix directly to the model file. Follow these rules: **Snowflake SQL Rules:** - Use Snowflake SQL dialect exclusively - `ISNULL(a, b)` → `NVL(a, b)` or `COALESCE(a, b)` - `GETDATE()` → `CURRENT_TIMESTAMP()` - `CONVERT(type, expr)` → `expr::type` or `TRY_CAST(expr AS type)` - `SELECT TOP N` → `SELECT ... LIMIT N` - Remove `WITH (NOLOCK)` hints (Snowflake has no lock hints) - When referencing Snowflake reserved keywords as column names or aliases, always use double quotes: `"GROUP"`, `"ORDER"`, `"TABLE"`, `"TIME"`, `"LOGIN"`, `"DATE"`, `"COMMENT"`, `"COLUMN"` **Semantic Preservation Rules:** - Preserve original semantics; make minimal changes to fix the error - Only change column names if there is a typo or mistake; preserve all other column names exactly - Preserve all code not related to the error, including casing and formatting - Avoid mocking data with constant values like `0`, `NULL`, etc. — only as a last resort - Commenting out code entirely is NOT a valid fix — always provide a working replacement - Preserve non-EWI comments in the code unchanged (EWI comment blocks are replaced — see EWI Handling Rules below) - **Preserve `ref()` and `source()` targets from the original file.** Read the original from `/stabilization/original/` before rewriting a model body. The rewritten code MUST use the same targets — do NOT guess; multiple staging models with similar names may have different column sets. **EWI Handling Rules:** - `!!!RESOLVE EWI!!!` markers as bare text produce invalid SQL — resolve them by replacing the **entire EWI block** (the marker line AND all associated commented-out XML/C#/binary code below it) with working SQL. Do NOT preserve the commented-out component definition — it is dead code that inflates the file and confuses downstream tooling. The original is always available in `/stabilization/original/` for reference. - Check `{PLATFORM_DIR}/ewi/` for platform-specific guides first, then [reference/ewi/](../reference/ewi/). See `{PLATFORM_DIR}/element-types.md` for unfamiliar element types. - If no reference exists, apply SQL Server → Snowflake knowledge to reason about the fix **dbt-Specific Rules:** - Remove trailing semicolons (`;`) after SQL expressions — dbt does not want them - Never use `source()` or `ref()` to reference a CTE defined within the same model. CTEs are local to the query and should be referenced by name directly (e.g., `FROM my_cte`, not `FROM {{ source('raw', 'my_cte') }}`) - Only use `source()` for external tables defined in `sources.yml` and `ref()` for other dbt models - Be extremely careful with dbt variable names in `var()` references — the variable name must exactly match what is defined in `dbt_project.yml`. Do not introduce typos or approximate names - Preserve all dbt references to other nodes or sources exactly as they are - Respect and follow conventions already present in the existing converted code **Location-override layer (SnowConvert `sc_override_*` vars):** - Applies only when `dbt_project.yml` declares `sc_override_*` vars — the SnowConvert signal that the source ETL defined session/mapping location overrides. - The `sc_override_*` vars, the `generate_*_name` macros, the var-driven `sources.yml`, and the mart `config(database/schema/alias=…)` headers are the override layer SnowConvert emits on purpose. Don't edit their defaults or remove them to resolve a database- or schema-not-found error on such a model. ### Step 2.3: Verify the Fix **Compilation is a pre-filter, not the verdict.** `dbt compile` only renders Jinja and checks that the project parses — it does not execute the SQL and does not check what the model returns. A fix can compile cleanly and still produce wrong data (a lookup turned into an inner join that silently drops no-match rows, de-duplication that keeps the wrong row, a blank test that changes which rows survive). Those defects are invisible to compilation and are exactly what the source-derived tests exist to catch. So verify in two stages: **Stage 1 — compile (cheap, no warehouse).** Fast syntactic check; if it fails, fix and retry without spending a warehouse round-trip. ```bash dbt compile --project-dir --profiles-dir ~/.dbt ``` **Stage 2 — source-derived semantic tests (the actual gate).** When a warehouse is configured, build the node and run the tests `dbt-test-gen` generated from the source definition. **This is what decides whether the node is fixed.** ```bash # Drop the node's target objects first - see the stale-state warning below dbt build --select --project-dir --profiles-dir ~/.dbt dbt test --select --project-dir --profiles-dir ~/.dbt ``` ⚠️ **Drop and recreate the node's target objects before each attempt.** If a build FAILS, the previous attempt's relation is still in place, and the tests will then pass against stale rows — reporting a fix that did not happen. Always drop (or `--full-refresh`) so a failed build cannot inherit an earlier pass. ⚠️ **An assertion that ERRORS is not an assertion that FAILS.** A test whose SQL is itself invalid (for example a scalar subquery that returns multiple rows once a fix produces duplicates) errors rather than returning a clean fail. Treat an erroring test as a defective test, not as a failed fix: simplify or regenerate that test (up to 2 regenerations) and re-run before counting the attempt against the node. Otherwise the node burns its attempt budget on a test bug. **If no warehouse is available**, Stage 2 cannot run. Converge on compilation, but record the node as status `unverified` with reason `no semantic grade (no warehouse available)` in your learnings artifact — never as fixed. (`unverified` is a valid dbt node status the orchestrator can record via `track_status.py update-dbt-node`.) A compile-only pass is a partial result and must never be reported as a validated fix. ### Step 2.4: Handle Results - **Node compiles AND its source-derived tests pass** → record as fixed in your learnings artifact and move to the next node. This is the only outcome that counts as fixed when a warehouse is available. - **Node compiles but a source-derived test FAILS** → the fix is wrong, not done. Read the failing assertion, which states the expected behaviour traced from the source definition, and revise the fix (counts toward the 5-attempt budget). Do **not** weaken, narrow or delete the assertion to make it pass — the assertion is the specification. - **A source-derived test ERRORS (rather than fails)** → treat it as a defective test per Step 2.3; regenerate it and re-run without consuming an attempt. - **Node still fails to compile with a new error** → read the new error, retry the fix (up to 5 attempts per node) - **Node still fails after 5 attempts** → revert the file to its original content from `/stabilization/original/`, record as failed in your learnings artifact with the error summary and the failing assertions - **No warehouse available** → record status `unverified` with a reason (see Step 2.3), not as fixed After all nodes in a project are processed, record the project-level outcome (all-fixed or N-failed) in your learnings artifact, and state explicitly whether the outcomes were semantically verified or compile-only. > **Do NOT call `track_status.py` directly.** The orchestrator reads your learnings artifact and updates `session_status.json` after all dbt agents complete. ### Step 2.5: Log Each Fix After each fix attempt, append a record to your per-project learning file at `/stabilization/phases/phase_{N}/dbt_learnings_.md`. The orchestrator merges these into `tracking/fix-log.md` after all dbt agents complete. > **Do NOT write directly to `tracking/fix-log.md`** — always use the per-project learning file, regardless of whether you are the only agent or one of many. This ensures consistent behavior and avoids file conflicts. ```markdown ### Fix Record - **Unit**: - **dbt Project**: - **Node**: - **File**: - **Error**: - **Fix Applied**: - **Outcome**: SUCCESS | FAILED | REVERTED - **Attempt**: - **Timestamp**: --- ``` ### Step 2.6: Handle Multiple dbt Projects A single ETL unit may contain multiple dbt project subfolders. Process only the projects assigned to the current phase (as listed in `ROADMAP.md`). Track status per project. ### Phase 3: Report After all dbt projects in the current phase are processed: 1. Read `session_status.json` to check per-node and project-level dbt status for projects in this phase. 2. Present the summary to the user: ``` dbt fixing complete for : Projects processed: N All nodes passing: M projects Partially fixed: K projects Failed: J projects Per-project details: - : X/Y nodes passing (Z fixed by AI) Fix log: /stabilization/tracking/fix_log.md Session: /stabilization/tracking/session_status.json ``` 3. Return to the parent skill (stabilization/SKILL.md) for next steps. The parent skill will run the pre-generated dbt tests to validate fixes. If tests fail, the parent skill will loop back here with the test failure messages. --- ## Task Mode (spawned by orchestrator) ### Input (provided by orchestrator at spawn time) - Package name and path (`PACKAGE`) - Phase number (`N`) - dbt project path (`DBT_PROJECT_PATH`) - Source definition file path: READ-ONLY — for understanding original ETL intent - Test report path: `PACKAGE/stabilization/tests/dbt//test_report.md` - `ROADMAP.md` path — for identifying current phase and assigned dbt projects - `session_status.json` path — for reading/writing project and node status - Skill directory: `SKILL_DIR` ### On-Demand References (read when needed) - Test report: for baseline failures and source-to-dbt mapping before fixing - Source definition file: when resolving EWI markers - EWI guides: from `PLATFORM_DIR/ewi/` when encountering markers - Fix log: `PACKAGE/stabilization/tracking/fix_log.md` for prior patterns ### Output - Fixed dbt model files in `DBT_PROJECT_PATH/models/` - Fixed macro files in `DBT_PROJECT_PATH/macros/` (if bootstrap fixes were needed) - Fixed `dbt_project.yml` (if placeholder values were replaced) - Fixed `sources.yml` (if source definitions were corrected) - Per-project learning file: `PACKAGE/stabilization/phases/phase_N/dbt_learnings_.md` (orchestrator merges into `fix_log.md` at phase end) ### needs-user Enforcement needs-user is a LAST RESORT status, valid ONLY after exhausting fix attempts with documented evidence. Before setting needs-user, you MUST: 1. Attempt to fix the failing models/tests 2. Document what was tried and why it failed 3. Specify what user action is needed (e.g., "configure file stage for FixedWidth source") The --reason flag is mandatory and must include this evidence. ### Workflow Follow the Main Mode workflow in full. Apply these behavioral overrides: - Skip all interactive stopping points — proceed autonomously with reasonable defaults - Document assumptions in the completion report - Process only the dbt project(s) assigned to the current phase in ROADMAP.md - Do NOT write directly to `tracking/fix-log.md` — use per-project learning file instead. The orchestrator merges these after all dbt agents complete. Before summarizing, re-read the final state of every file you edited (`dbt_project.yml`, `sources.yml`, `profiles.yml`) — confirm placeholder keys were actually renamed (not left alongside a new one), and that `profile:` matches this repository's established per-project naming convention (`{project_name}`, matching sibling projects). Do not summarize from memory of what you intended to change — the hard `models:`/`name:` mismatch check now runs automatically at phase completion, but naming-convention drift does not. ### Context Management If you feel context pressure after completing a node, **stop and report partial completion** — list which nodes were fixed and which were not. Prefer stopping early over running to exhaustion. ### Team Protocol When your work is complete: 1. Send a `send_message` to `main` summarizing: models fixed, compilation status, test results, any nodes still failing 2. If you receive a `shutdown_request`, use `send_message` with `type: "shutdown_response"` and `approve: true`