Orchestration Fixer

Orchestration Fixer

Fix orchestration SQL files generated by SnowConvert. These files contain Snowflake task graphs (CREATE TASK) and stored procedures (CREATE PROCEDURE) that replicate ETL control flow.

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)

Rules

• Read the reference file for an EWI/FDM code BEFORE attempting a fix
• Use ---- Start / ---- Start block / ---- End block tags to locate elements, NEVER line numbers. Non-container elements (---- Start) have no closing tag — they end at the next tag. Container elements (---- Start block) have a matching ---- End block.
• Do NOT stop after every element — flow autonomously, pause only when genuinely blocked or needing user input
• Do NOT remove --** SSC-FDM-* / --** SSC-EWI-* comment markers — they are documentation
• DO remove !!!RESOLVE EWI!!! marker lines when applying a fix — they break compilation
• Keep the fix log updated after every fix attempt, including failures
• Record element statuses in your task artifact — the orchestrator updates session_status.json
• Valid output statuses: test-passed, test-failed, skipped, needs-user, auto-fixed-needs-review, failed. Do NOT invent new statuses. Elements with no-fix-needed stay unchanged.

Input

This reference expects:

• session_status.json — from track_status.py init (current status of each element; may include test_environment config if set via set-test-env)
• ROADMAP.md — phase plan with element assignments (authored by stabilization)
• The orchestration .sql file path (from scan results)
• Test files at <UNIT>/stabilization/tests/orchestration/<task_procedure_name>/<element_name>.sql (generated by orchestration-test-gen)
• <UNIT>/stabilization/tests/orchestration/test_report.md with baseline results

Workflow

Copy this checklist and track your progress:

Orchestration Fixer Progress:
- [ ] Step 1: Analyze and plan (read session + ROADMAP, scope to current phase, build fixing plan, get user confirmation)
- [ ] Step 2: Fix using test files (skip completed, skip dbt elements, fix each element via test-file workbench, apply proven fixes)
- [ ] Step 3: Report (present summary, return to parent skill)

Pre-Fix Context

Before starting fixes, review the Enriched Baseline Report (at {PHASES_DIR}/baseline_batch_{B}.md or in your prompt):

• Elements with baseline PASS → skip (status: no-fix-needed)
• Elements with baseline FAIL → these are your work items
• Clone annotations → process archetypes before their clones within this task
• Failure details → use these to guide your initial fix analysis

This baseline was generated by the test-gen agent. Test files are already on disk. Do NOT regenerate test files — use them as your TDD workbench.

Step 1: Analyze and Plan

1. Read session_status.json to understand the package structure, which elements are pending, and the test environment config
2. Read ROADMAP.md for the current phase number and its goals
3. Read session_status.json and filter elements where phase == <PHASE_NUM>. Only fix elements in this phase's scope. Ignore elements assigned to other phases.
4. Read test_report.md to understand the baseline test results (which assertions failed and why)
5. Build a fixing plan — prioritized list of statements (only those containing in-scope elements):
   • Simple single-element statements first (easier to validate)
   • Multi-element container statements after
   • Statements with only EXECUTE DBT PROJECT elements last (mark as skipped/dbt-dependency)
6. Present a summary to the user showing:
   • Current phase number and scope
   • Total statements and elements in this phase
   • Issues by code (e.g., "12 x SSC-FDM-0007, 2 x SSC-EWI-SSIS0002")
   • Baseline test failures (from test_report.md)
   • Recommended order
7. ⚠️ STOPPING POINT (interactive mode): Ask user to confirm or adjust the plan. If running as an autonomous agent, skip — proceed with the generated plan and document assumptions in the completion report.

Context efficiency: session_status.json is your input for building a fix plan. Read it once in Step 1 — don't re-read it per element in Step 2. Record statuses in your task artifact, not via track_status.py.

Batch fixing strategy: Group elements by EWI code in Step 1. Process all elements sharing the same code consecutively — this lets you build familiarity with the pattern and cross-reference prior fixes in {UNIT}/stabilization/tracking/fix_log.md. Read the orchestration SQL once at the start of Step 2 and build a mental index by ---- Start tags.

Step 2: Fix Using Test Files as Workbench

Skip-completed check: Before processing each element, check its status in session_status.json. Skip elements with status test-passed, auto-fixed-needs-review, or no-fix-needed. This enables idempotent re-entry if a phase is retried after partial completion.

Clone Element Handling

If the element is marked as a clone in the ROADMAP task section:

1. Read the archetype's fix from the learnings artifact (learnings_batch_{B}.md) — the archetype was processed first in this same batch
2. Understand what differs between archetype and clone (documented in ROADMAP: scope swap, table name change, file path substitution, etc.)
3. Adapt the archetype's fix with reasoning about the differences — NOT a mechanical copy
4. Update the ACT section in the clone's test file with the adapted fix
5. Execute: ARRANGE:SEED → ACT → ASSERT (batched UNION ALL format)
6. If assertions PASS:
   • Record as clone fix in task artifact and learnings
   • Status: test-passed
   • Note in learnings: clone-applicable: YES
7. If assertions FAIL:
   • ESCALATE: this element is not a true clone
   • Treat it as a regular element — full TDD reasoning from scratch
   • Note in learnings: "Clone escalation: fingerprint insufficient for {element_name}"
   • Continue with normal fix workflow (analyze, fix, test, iterate max 3 cycles)

Exception — disabled elements: disabled-in-source elements still require code cleanup — replace the commented-out XML/C# body with a clean one-liner (step 1 below). Elements with dbt-dependency or external-dependency skip reasons are fully skipped.

Ensure a backup exists before editing. The parent skill creates <UNIT>/stabilization/original/ during setup; create it if missing.

After user confirms, mark all EXECUTE DBT PROJECT elements as skipped with reason dbt-dependency in your task artifact, then work through statements autonomously.

Read the Statement: Locate CREATE TASK/CREATE PROCEDURE by name (not line number — lines shift after edits).

Fix Each Element (identified by ---- Start tags):

1. Disabled in source — if disabled-in-source in status or element body is commented-out XML with !!!RESOLVE EWI!!!. Replace the entire element body with:

   ---- Start '<element_name>'
   -- Disabled in source package. Original element removed.
   

   Update status skipped --reason disabled-in-source. Continue to next element.

   Check downstream dependents before moving on. Search the orchestration SQL for every AFTER clause that lists this element's Snowflake task name <T>. Snowflake never executes a task whose required predecessor is permanently suspended, so each of those tasks (and everything AFTER them) would otherwise silently stop running once deployed, with no error anywhere. Fan-out is the common case: several downstream tasks can reference the same disabled <T>. Snowflake AFTER can list multiple predecessors (AFTER T1, T2); when rewriting a clause, replace only <T> and preserve every other name already in that list.

   Detect a real data dependency (required recipe — do not invent a check):

   1. From the source definition of disabled <T>, collect its write targets. Informatica: the mapping's TARGETINSTANCE / TARGETLOADORDER names and any CONNECTOR TOINSTANCE that lands on a target (see platforms/informatica/mapping-guide.md). Other platforms: the equivalent target/output list in the source definition.
   2. A downstream task depends on <T> iff its converted body references any of those concrete target names (table, view, or stream identifiers).
   3. Known limitation: names built via variables / dynamic CTAS, plus secondary effects such as control-table row counts or session variables <T> would have set, are not covered by step 2. If those are the only signals, treat as a data dependency (needs-user) rather than rewiring.
   • If the downstream task does not depend on <T>'s output — for every downstream task whose AFTER list contains <T>, replace <T> in that list with the set of <T>'s enabled predecessors (walk further back through any chain of disabled predecessors). If that walk finds no enabled ancestor, mark the downstream element needs-user rather than dropping the AFTER clause (dropping it would turn a scheduled dependent into a root task the customer never authored). Add a one-line comment explaining the rewire and, if applicable, cite the source $<task>.PrevTaskStatus workflow variable's own description (it documents PowerCenter's built-in skip-and-continue semantics for disabled predecessors — check the source XML for a WORKFLOWVARIABLE with DESCRIPTION containing "not disabled"). This is a required fix, not optional.
   • If the downstream task does depend on <T>'s output — do not rewire silently. Mark the downstream element needs-user, with a reason describing the data gap (the disabled step's output is missing and the downstream logic needs it). This is a real functional question for the customer, not something to resolve unilaterally.

2. EXECUTE DBT PROJECT — mark skipped --reason dbt-dependency. Continue.

3. Identify issues — look for !!!RESOLVE EWI!!! markers and --** SSC-* comments

4. Check prior fixes (cross-learning) — search {UNIT}/stabilization/tracking/fix_log.md for entries matching **EWI/FDM**: <ISSUE_CODE> (all entries are successful fixes — the file only records patterns that worked). If prior fixes exist for the same issue code:

   • Read their Fix pattern and After fields to understand what approaches have worked before
   • Assess whether the same approach applies to THIS element — the same EWI/FDM code can cover very different scenarios (e.g., SSC-EWI-SSIS0004 covers ScriptTask, ForEachLoop, and other control flow elements)
   • If the element is structurally identical to a prior fix (same element type, same pattern), you can apply the same approach — but still verify with the test file
   • If the element differs, use the prior fix as context to inform your reasoning, then proceed to step 5

   Prior fixes are learning material, not templates to blindly apply. Always analyze the concrete element.

5. Read the reference file (when needed) — for each issue code, see reference/ewi/.md (e.g., reference/ewi/SSC-FDM-0007.md). Follow the decision tree and fix patterns documented there. For unfamiliar element types, see {PLATFORM_DIR}/element-types.md. Skip if the element is structurally identical to a prior fix you already analyzed and you're confident the same approach applies.

6. Determine the fix:

   • If the reference file provides a clear pattern → apply it
   • If the fix needs information from the user (e.g., "does this staging table exist?") → ask the user, wait for response, then apply
   • If the reference file says the marker is informational (most FDMs) → note it and move on
   • If no reference file exists → use your knowledge of SQL Server → Snowflake patterns to reason about the fix

7. Test the fix using the test file — the test file is your workbench:

   a. Find the test file: <UNIT>/stabilization/tests/orchestration/<task_procedure_name>/<element_name>.sql

   b. Update ACT: Replace the ACT section content (between -- ACT and -- ASSERT markers) with your proposed fix wrapped in CREATE OR REPLACE PROCEDURE ... test_act_<element_name>() + CALL. Use test environment DB references. Do NOT include ---- Start/End block markers. See ../orchestration-test-gen/stored-procedure-wrapping.md.

   c. Execute section-by-section via snowflake_sql_execute:

   • First cycle: ARRANGE:SETUP → ARRANGE:SEED → ACT → ASSERT
   • Subsequent cycles: ARRANGE:SEED → ACT → ASSERT (objects persist; re-run SETUP only if you modified it)
   • For grouped files: ACT blocks in element order, then ASSERT blocks per-element. See mcp-test-execution.md.
   • After editing ACT, re-read the test file before executing.

   d. Pass → fix proven; apply it (step 8). The test file is now the canonical source of truth.

   e. Fail → iterate (max 3 cycles). Mark test-failed if still failing after 3.

   If no test file exists (FDM-only informational markers), skip the test loop and apply directly.

8. Apply the proven fix to the orchestration SQL (Main Mode only — Task Mode agents skip this step and write Replacement SQL to their task artifact instead; see Task Mode workflow below) — replace the entire element body in the orchestration .sql file with the validated fix from the test file. This is a full replacement, not a patch.

   Element boundary rules (determines what gets replaced):

   • Non-container (---- Start '<name>'): replace everything from the ---- Start tag up to (but not including) the next ---- Start, ---- Start block, or ---- End block tag. Non-container elements have NO closing tag.
   • Container (---- Start block '<name>'): replace the content between ---- Start block and the matching ---- End block tags. Preserve both boundary tags.

   Replacement rules:

   • The replacement content is the stored procedure body from the test file, adapted only for database references (test environment → production)
   • The first line after the ---- Start tag MUST be a brief SQL comment explaining the fix, e.g. -- FIX: Replaced C# ScriptTask with DATEDIFF equivalent (SSC-EWI-SSIS0004). One line, include the EWI/FDM code.
   • The ---- Start tag line MUST be preserved as the first line of the replacement
   • Remove ALL original content within the element boundaries — this includes !!!RESOLVE EWI!!! markers, commented-out XML/C# source code, commented-out binary data, and any other dead code from the SnowConvert output
   • The replacement content MUST include the relevant --** SSC-FDM-* / --** SSC-EWI-* one-line comment markers that document the issue — these are carried into the fix, not stripped out
   • Do NOT change code outside the element boundaries
   • The fix logic must match exactly what was validated in the test file

9. Report status in batch artifact — the Status field in your batch_{B}.md element section is the source of truth. Use one of: test-passed, test-failed, skipped, needs-user, auto-fixed-needs-review, failed. Include a Reason field for non-passing statuses.

   Do NOT call track_status.py directly. The orchestrator reads your task artifact and updates session_status.json after all fix agents complete.

10. Append to fix log (Main Mode only — Task Mode agents write to learnings_batch_{B}.md instead; the orchestrator merges learnings into fix_log.md after all agents complete) — write a record to {UNIT}/stabilization/tracking/fix_log.md:

## Fix Record
- **Unit**: <unit_name>
- **File**: <orchestration_file_path>
- **Statement**: <statement_name>
- **Element**: <element_name>
- **Issue Code**: <SSC-CODE>
- **Issue Description**: <description from marker>
- **Fix Applied**: <what you changed>
- **Test Result**: N of M assertions passed
- **Outcome**: SUCCESS | FAILED | NEEDS-USER
- **Timestamp**: <ISO timestamp>

---

Post-Fix Check: After all fixable elements in the statement are addressed, check for remaining !!!RESOLVE EWI!!! markers in the statement. If any remain, go back and address them — these break compilation.

Step 3: Report

After all statements in this phase are processed:

1. Read session_status.json to verify all phase elements have terminal statuses.

2. Present the summary to the user:

   Phase <N> fixing complete for <unit_name>:
     Fixed: N elements
     Skipped: M elements (dbt-dependency: D, disabled: B)
     Needs user: K elements
     Failed: J elements
   
   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.

Troubleshooting

Test file not found for element

• Verify test-gen ran for this phase: check <UNIT>/stabilization/tests/orchestration/<task_procedure_name>/ for .sql files
• If missing, return to orchestrator — test-gen must run before fixing

ARRANGE:SETUP fails (permission denied, object not found)

• Verify Snowflake connection: SELECT CURRENT_DATABASE(), CURRENT_SCHEMA()
• Verify write privileges: CREATE TABLE, CREATE FUNCTION, CREATE PROCEDURE
• If schema doesn't exist, check if mock DDLs reference a schema the user can't create

ACT stored procedure fails with syntax error

• Compare the ACT body in the test file with the original orchestration SQL element
• Check for unresolved !!!RESOLVE EWI!!! markers — these break compilation
• Verify database references were rewritten to test environment (<DATABASE>.<SCHEMA>)

Assertions pass but fix doesn't apply cleanly to orchestration SQL

• Verify element boundaries: non-container elements end at the next ---- Start tag, container elements end at ---- End block
• Re-read the orchestration SQL to check if line numbers shifted from a prior fix in this phase
• Locate elements by name (from ---- Start tags), never by line number

Same element fails repeatedly across retries

• Check {UNIT}/stabilization/tracking/fix_log.md for prior attempts — review what was tried
• After 3 failed cycles, mark as test-failed and move on
• Log a decision via parent skill's add-decision command for orchestrator review

Task Mode (spawned by orchestrator)

Mode boundary: Steps 1-10 above are Main Mode only. Task Mode agents follow the workflow below. If any instruction above conflicts with Task Mode instructions, Task Mode takes precedence.

Input (provided by orchestrator at spawn time)

• Package name and path (PACKAGE)
• Phase number (N) and batch ID ({B}, format: B{P}.{M}, e.g., B1.1)
• Task schema (TASK_SCHEMA) — pre-assigned, use for ALL created objects
• Database (DATABASE)
• Elements to process (ELEMENT_LIST) with archetype/clone annotations
• Test strategy (STRATEGY): isolated or grouped:<name>
• File paths:
  • Orchestration SQL: READ-ONLY — do NOT modify this file
  • Source definition file: READ-ONLY — read only if provided context is insufficient
  • Orchestration guide: platform's source structure reference
  • Platform directory: PLATFORM_DIR/ (element-types.md, ewi/)
  • Phases directory: PHASES_DIR/
  • Skill directory: SKILL_DIR
• ROADMAP batch context section (provided in prompt)
• Enriched baseline report (at PHASES_DIR/baseline_batch_{B}.md or provided in prompt)
• Fix log path: PACKAGE/stabilization/tracking/fix_log.md (READ-ONLY — consult if exists; absent in Phase 1)
• Execute ALL SQL via snowflake_sql_execute MCP tool — no Python connections

On-Demand References (read when needed)

• EWI guides: from PLATFORM_DIR/ewi/ when encountering markers
• stored-procedure-wrapping.md: when wrapping element logic
• mcp-test-execution.md: when executing test files

Output

• Test files (ACT updates only): PACKAGE/stabilization/tests/orchestration/<task_procedure_name>/
• Batch artifact: PHASES_DIR/batch_{B}.md
• Learnings artifact: PHASES_DIR/learnings_batch_{B}.md
• Do NOT append to artifacts/tracking/fix_log.md — write fix patterns to learnings_batch_{B}.md only. The orchestrator merges learnings after all agents complete.

Write artifacts incrementally — one element section appended at a time. The orchestrator handles partial artifacts: completed elements are already on disk and will not be re-processed on retry.

needs-user Enforcement

needs-user is a LAST RESORT status, valid ONLY after exhausting 3 full TDD fix cycles with different approaches. Before setting needs-user, you MUST:

1. Attempt 3 genuine fix cycles (each with a different approach)
2. Document what was tried in each cycle
3. Explain why each failed
4. Specify what user decision is needed

The --reason flag is mandatory and must include this evidence.

Valid Skip Reasons

Only these skip reasons are accepted by track_status.py:

• disabled-in-source — element is disabled in the source definition
• dbt-dependency — element is an EXECUTE DBT PROJECT invocation
• external-dependency — element has unfixable external dependencies

ScriptTasks with no Snowflake equivalent (filesystem I/O, COM objects) are NOT skipped. They are test-failed with a reason. The TDD loop must be attempted.

Workflow

Process archetypes before clones within your element list.

For each element:

1. Read baseline report — determine pass/fail status for this element from the Enriched Baseline Report.

2. If element passed baseline — record status no-fix-needed, write artifact section, proceed to next element.

3. If element is an archetype — apply full TDD fix loop: a. Analyze the EWI issue and the failing assertion(s) in the test file. Use the issue summary, source excerpt, and applicable EWI guide section from the enriched baseline. b. Check fix_log.md for a reusable pattern before attempting from scratch. c. Consult the EWI guide content for the relevant code. d. Use the source definition excerpt to understand the intended logic. e. Apply a fix in the test file workbench (in TASK_SCHEMA). f. Execute SEED → ACT → ASSERT as a batch (following the Test Execution Protocol in mcp-test-execution.md). g. If assertions still fail, revise and re-test. Maximum 3 iterations. h. If still failing after 3 iterations, set status auto-fixed-needs-review with the best-guess fix and document the assumption. i. Record the proven fix: Write the Replacement SQL to your task artifact (see format below). The Replacement SQL must use production database/schema references from the orchestration SQL file — NOT TASK_SCHEMA. Do NOT edit the orchestration SQL file.

4. If element is a clone — apply accelerated fix: a. Find its archetype in the learnings artifact for this batch (PHASES_DIR/learnings_batch_{B}.md) or in fix_log.md. b. Read the archetype's fix pattern and adapt it to this clone's specific context, documenting your reasoning. c. Apply the adapted fix in the test file workbench and run SEED → ACT → ASSERT. d. If the adapted fix passes — record status test-passed. e. If it fails — escalate to full TDD (follow the archetype workflow above, max 3 iterations). f. Record the proven fix: Same as step 3.i above — write Replacement SQL to task artifact. Do NOT edit the orchestration SQL file.

5. Write artifacts incrementally — append this element's section to PHASES_DIR/batch_{B}.md and PHASES_DIR/learnings_batch_{B}.md immediately after completing it. For the first element processed, write the file header first:

   • batch_{B}.md header: # Batch {B} — Phase {N}\n\n## Elements
   • learnings_batch_{B}.md header: # Learnings — Batch {B}, Phase {N}

   For subsequent elements, append the next ### section directly.

6. Proceed to next element.

Batch artifact format: PHASES_DIR/batch_{B}.md

# Batch {B} — Phase {N}

## Elements

### element_name

- **Status**: {status}
- **Reason**: {reason if applicable}
- **Test file**: `{test_file_path}`
- **Replacement tag**: `---- Start block: {exact_tag_from_orch_sql}`
- **Replacement SQL**:
```sql
{production_ready_sql}

**Critical rules for Replacement SQL:**
- Include ONLY for elements with status `test-passed`, `auto-fixed-needs-review`, `skipped:disabled-in-source`, or `no-fix-needed` (if the element still has EWI markers that must be cleared)
- Must use the **production** database/schema references as they appear in the orchestration SQL file (not the test environment's `TASK_SCHEMA`)
- Must be the complete body that replaces everything between the `---- Start block:` and `---- End block:` tags
- The `Replacement tag` must exactly match the `---- Start block:` line from the orch SQL
- **Every replacement MUST start with a brief SQL comment explaining the fix**, e.g. `-- FIX: Replaced C# ScriptTask with DATEDIFF equivalent (SSC-EWI-SSIS0004)`. Keep it to one line.

#### Learnings artifact format: `PHASES_DIR/learnings_batch_{B}.md`

```markdown
# Learnings — Batch {B}, Phase {N}

## element_name: {one-line summary}

- **EWI/FDM**: {code} — {title}
- **Root cause**: {description}
- **Fix pattern**: {transformation applied}
- **Before** (abbreviated):
```sql
{original snippet}

• After (abbreviated):

{fixed snippet}

• Reusable: yes | no — {why}
• Clone-applicable: yes | no — {why}

Only include entries for elements where a fix was applied (`test-passed` or `auto-fixed-needs-review`).
If no fixes were applied, write: `No fixes applied in this task.`

### Autonomous Behavior

- Do NOT ask the user any questions — make reasonable defaults and proceed autonomously
- For `needs-user` situations: attempt a best-guess fix, set status to `auto-fixed-needs-review`, document the assumption in the learnings artifact
- Prefer EWI reference guide patterns over improvised fixes — the guides exist for exactly this purpose
- Consult `fix_log.md` for reusable patterns before attempting fixes from scratch
- If an element is completely blocked (infrastructure failure, not a test failure), set status to `failed` with a descriptive reason

### Context Management

After completing each element, if you feel context pressure (large accumulated outputs, many fix-test iterations), **stop after the current element's artifacts are written** and report partial completion. The orchestrator will spawn a continuation agent.

**Prefer stopping early with artifacts on disk over running to exhaustion.** When stopping early, send a completion message listing: elements completed (with statuses), elements NOT processed, reason: `"partial-completion: context pressure after N elements"`.

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.

### Team Protocol

When your work is complete, your agent will automatically return results to the orchestrator.
If you receive a `shutdown_request`, use `send_message` with `type: "shutdown_response"` and `approve: true`.