Coalesce Job Failure Investigation

Diagnose why a Coalesce pipeline run failed, identify the root cause, inspect the failing SQL, and guide the user toward a fix.

Workflow

Step 1: Find failed runs
         ↓
Step 2: Investigate the failure (root cause + downstream impact)
         ↓
Step 3: Inspect the failing node's SQL
         ↓
Step 4: Recommend a fix
         ↓
Step 5 (optional): Apply the fix if write access is available
         ↓
Step 6 (optional): Verify the fix by re-running the job

Step 1: Find Failed Runs

Goal: Identify which run(s) failed.

If the user provides a run ID: Skip to Step 2.

If the user asks about "the latest failure" or doesn't specify a run:

Call mcp__coalesce__list_failed_runs:

• environment_id — pass if the user specifies an environment, otherwise omit
• limit — default 20; use 1-5 if only looking for the most recent

Response shape:

{
  "runs": [
    {
      "run_id": 7,
      "run_status": "failed",
      "environment_id": "22",
      "job_name": "...",
      "start_time": "2026-03-26T15:51:21.468Z",
      "end_time": "2026-03-26T15:51:35.369Z",
      "run_type": "refresh"
    }
  ],
  "next_cursor": null,
  "count": 1
}

Present the failed run(s) to the user with run ID, timestamp, and environment. If multiple failures exist, ask which one to investigate.

⚠️ STOP if multiple failures: Ask user which run to investigate before proceeding.

Step 2: Investigate the Failure

Goal: Get root cause, failing nodes, error messages, and downstream impact in one call.

Call mcp__coalesce__investigate_failure with the run_id.

This is the best single tool for failure diagnosis. It returns:

• Run metadata — run_id, status, environment, timestamps, run_type
• Failed nodes — node name, node ID, exact error message, the SQL that failed
• Downstream blocked nodes — nodes that were skipped because their upstream failed

Response shape:

{
  "run": {
    "run_id": "7",
    "run_status": "failed",
    "environment_id": "22",
    "run_type": "refresh",
    "start_time": "...",
    "end_time": "..."
  },
  "summary": {
    "total_nodes": 3,
    "failed": 1,
    "succeeded": 1,
    "downstream_blocked": 1
  },
  "failed_nodes": [
    {
      "node_id": "abc123",
      "node_name": "STG_SENSOR_READINGS",
      "error": "Numeric value '3W' is not recognized",
      "sql": "INSERT INTO ..."
    }
  ],
  "downstream_blocked_nodes": [
    {
      "node_id": "def456",
      "node_name": "DIM_SENSOR_READINGS",
      "status": "skipped"
    }
  ]
}

Present to the user:

1. Root cause — which node failed and why (interpret the error message)
2. Impact — which downstream nodes were blocked
3. The failing SQL snippet if available

Ask for the workspace ID now — before proceeding to Step 3. The workspace ID is not included in run metadata and cannot be looked up programmatically. Tell the user:

"To inspect and fix the node I'll need your workspace ID. You can find it in the Coalesce URL when you're in the workspace: .../workspaces/{ID}/build/..."

Store the workspace ID for use in Steps 3 and 5.

Step 3: Inspect the Failing Node

Goal: Get the full SQL and column transforms for the failing node so you can pinpoint the exact issue.

Deciding which tool to call:

• You already have environment_id from the investigate_failure response in Step 2 — use it directly
• Call mcp__coalesce__get_workspace_node with the workspace_id collected in Step 2 and the node_id from the failed node
• Do NOT call list_environment_nodes to try to find the node — you already have the node_id from investigate_failure

What to look for in the response:

• config.columns[].transform — the SQL transform expression per column (e.g., CAST(... AS FLOAT))
• overrideSQL — if true, look at the full custom SQL override
• materializationType — table, view, transient, etc.
• config.selectDistinct, config.whereClause, config.groupByAll — query modifiers

Common error patterns and fixes:

Present the problematic SQL/transform and recommend a specific fix.

Step 4: Recommend a Fix

Goal: Give the user a concrete, actionable fix.

Structure the recommendation as:

1. What to change — the exact transform or SQL line
2. Before — current code
3. After — corrected code
4. Why — brief explanation of the fix

If the node uses column-level transforms (not overrideSQL), call out the specific column name and its transform expression.

Ask the user if they want you to apply the fix.

⚠️ STOP: Wait for user to confirm before attempting any writes.

Step 5 (Optional): Apply the Fix

Goal: Update the workspace node with the corrected SQL.

Prerequisites:

• The patch_node_field tool must be available (not hidden by COALESCE_READONLY_MODE=true)
• You should already have workspace_id from Step 2 — if somehow missing, the URL is .../workspaces/{ID}/build/...

Workflow:

1. Identify the exact field_path to update based on your Step 3 inspection.

Paths use the raw API node shape (not the slimmed display):

• Column transform: "metadata.columns[N].sources[0].transform" — find N by counting the column's position (0-based) in the get_workspace_node output
• WHERE clause: "config.whereClause"
• Pre/post SQL: "config.preSQL" / "config.postSQL"
• JOIN condition: "metadata.storageMapping[N].join"

1. Present a clear before/after diff to the user:

Field:  columns[2].transforms[0]
Before: CAST(raw_value AS FLOAT)
After:  TRY_CAST(raw_value AS FLOAT)

1. ⚠️ STOP — wait for explicit user confirmation ("yes", "apply it", "go ahead") before writing.

1. Once confirmed, call mcp__coalesce__patch_node_field with:

• workspace_id
• node_id
• field_path — the dot/bracket path identified in step 1
• new_value — the corrected SQL string

1. Report the result to the user. On success, the tool returns old_value and new_value confirming the change. Remind the user they may need to re-run the job to verify the fix.

If `patch_node_field` is not available (read-only mode):

Tell the user to make the change manually in the Coalesce UI:

1. Open the node in the workspace
2. Find the column/transform to change
3. Apply the fix
4. Deploy and re-run

Step 6 (Optional): Verify the Fix

Goal: Re-run the job to confirm the fix resolves the failure.

Prerequisites:

• Step 5 must be complete (fix applied)
• retry_run tool must be available (not hidden by COALESCE_READONLY_MODE=true)

⚠️ STOP: Ask the user if they want to re-run before triggering anything.

Workflow:

1. Call mcp__coalesce__retry_run with the original failed `run_id` from Step 1/2.

• This re-runs only the previously failed nodes — not a full environment refresh
• Do NOT use start_run here unless the user explicitly wants a full refresh

1. Note the new_run_id from the response.

1. Poll mcp__coalesce__get_run_status with new_run_id until runStatus is "complete" or "failed".

• Wait a few seconds between polls — don't hammer the API
• Tell the user what status you're seeing as you poll

1. Once complete, call mcp__coalesce__get_run_results with new_run_id to confirm results:

• All nodes passed (failed: 0) → fix is confirmed, tell the user
• Still failing → report the new error; it may be a different issue or the fix was incomplete

If the retry itself fails to start:

• Check that the original run_id is a valid failed run (not canceled or still running)
• If retry_run is unavailable, suggest start_run with the same environment_id and job_id as a fallback

Tools Reference

mcp__coalesce__list_failed_runs

When: First step — find which runs failed. Params: environment_id (optional), limit (optional, default 20), starting_from (optional)

mcp__coalesce__list_job_runs

When: Need broader filtering (by status, environment) or want to see all runs. Params: environment_id, run_status (running|completed|failed|canceled), limit, starting_from

mcp__coalesce__investigate_failure

When: You have a run_id and want the full diagnosis in one call. Best starting point for failure investigation. Params: run_id (required)

mcp__coalesce__get_run_results

When: You want pre-processed results (failed + blocked nodes) without run metadata. Use investigate_failure instead unless you specifically need just results. Params: run_id (required)

mcp__coalesce__get_job_details

When: You want raw run metadata + extracted errors together. Less concise than investigate_failure. Params: run_id (required)

mcp__coalesce__get_run

When: You need the run object only (no node results). Params: run_id (required)

mcp__coalesce__get_run_status

When: Checking if a run is still in progress. Params: run_id (required)

mcp__coalesce__get_environment_node

When: Inspect the deployed (production) version of a node. Params: environment_id (required), node_id (required)

mcp__coalesce__get_workspace_node

When: Inspect the development version of a node, or before updating it. Params: workspace_id (required), node_id (required)

mcp__coalesce__set_node (write — may be disabled)

When: Applying a full node replacement. Prefer patch_node_field for targeted single-field fixes. Params: workspace_id (required), node_id (required), node_config_json (required — full node object as JSON string)

mcp__coalesce__patch_node_field (write — may be disabled)

When: Applying a targeted SQL fix to a single field. Preferred over set_node for failure remediation — handles fetch-modify-replace internally. Params: workspace_id (required), node_id (required), field_path (required — e.g. "columns[0].transforms[0]"), new_value (required) Returns: {success, node_name, field_path, old_value, new_value}

mcp__coalesce__retry_run (write — may be disabled)

When: After applying a fix — re-runs only the previously failed nodes from a prior run. The primary tool for Step 6 verification. Params: run_id (required — the original failed run ID) Returns: {success, original_run_id, new_run_id} — use new_run_id to poll status

mcp__coalesce__start_run (write — may be disabled)

When: Triggering a fresh full-environment refresh (not a retry). Use this if the user wants to run the whole job from scratch rather than retry only failed nodes. Params: environment_id (required), job_id (optional), parallelism (optional) Returns: {success, run_id} — use run_id to poll status

mcp__coalesce__cancel_run (write — may be disabled)

When: Aborting a run that is taking too long, was triggered by mistake, or needs to be stopped before completion. Params: run_id (required), environment_id (optional) Returns: {success, run_id}

Stopping Points

• Step 1: If multiple failed runs, ask which to investigate
• Step 3: After presenting the failing SQL, ask if user wants to see the full node
• Step 4: After recommending a fix, wait for user approval before writing
• Step 5: Show before/after diff and require explicit user confirmation before calling patch_node_field
• Step 6: Ask the user if they want to re-run before calling retry_run — do not trigger a run automatically

Troubleshooting