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

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