dbt-fixer

name	dbt-fixer
parent_skill	etl-stabilization
description	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.
license	Proprietary. See License-Skills for complete terms

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.

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 <UNIT>/stabilization/tests/dbt/<project_name>/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 <UNIT>/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 <DBT_PROJECT_PATH> --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 <UNIT>/stabilization/original/<project_name>/ — 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 <UNIT>/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 <UNIT>/stabilization/original/ for reference.
• Check {PLATFORM_DIR}/ewi/ for platform-specific guides first, then 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.

dbt compile --project-dir <DBT_PROJECT_PATH> --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.

# Drop the node's target objects first - see the stale-state warning below
dbt build --select <node_name> --project-dir <DBT_PROJECT_PATH> --profiles-dir ~/.dbt
dbt test --select <node_name> --project-dir <DBT_PROJECT_PATH> --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 <UNIT>/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 <UNIT>/stabilization/phases/phase_{N}/dbt_learnings_<project_name>.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.

### Fix Record
- **Unit**: <unit_name>
- **dbt Project**: <dbt_project_name>
- **Node**: <node_id>
- **File**: <model_file_path>
- **Error**: <error message>
- **Fix Applied**: <what you changed>
- **Outcome**: SUCCESS | FAILED | REVERTED
- **Attempt**: <N of 5>
- **Timestamp**: <ISO 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 <unit_name>:
     Projects processed: N
     All nodes passing: M projects
     Partially fixed: K projects
     Failed: J projects
   
   Per-project details:
     - <project_name>: X/Y nodes passing (Z fixed by AI)
   
   Fix log: <UNIT>/stabilization/tracking/fix_log.md
   Session: <UNIT>/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/<project_name>/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_<project_name>.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.

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