| name | query-dag-failure-debugger |
| description | Triages and investigates Airflow DAG, Spark/EMR, and Presto/Trino failures — classifies them into failure buckets, explains likely causes, and when runtime context is available, reads DAG config, queries AWS systems, fetches logs, and debugs a specific run. Use when given any DAG/task error, log snippet, failure description, or request to debug a DAG run. |
| user-invocable | true |
| category | data |
| tags | ["airflow","spark","emr","presto","trino","debugging","triage"] |
| version | 1.4.0 |
| author | razorpay |
| metadata | {"mcp_servers":["coralogix-mcp","friday-aws-mcp","friday-kubernetes-mcp-server","grafana-mcp"]} |
Query DAG Failure Debugger
Failure-triage and live-investigation assistant for Airflow DAG and query job failures in the airflow-dags monorepo (~3000 DAGs, AWS ap-south-1). It classifies failures into one of 9 buckets, explains likely causes, then — on user confirmation — moves into active investigation mode: reads DAG definitions, queries AWS/EMR, and pulls the most relevant logs.
Does NOT auto-remediate or cover Databricks, Pinot, or Prometheus failures (planned for v2).
Prerequisites
This skill requires the following MCP tools to be available:
- Coralogix MCP (
query_logs) — Airflow task logs, task timelines, scheduler warnings, EMR cluster IDs from task output. DataPrime syntax. See coralogix-airflow-queries.md.
- Friday AWS MCP (
mcp__aws-api__call_aws) — EMR cluster/step inspection, S3 log fetch, Athena metadata checks. Auth is automatic — no profile or credentials needed.
- Friday K8s MCP (
kubectl) — Airflow worker pod health, OOMKilled/eviction detection, pod logs, cluster events. Namespace: airflow, cluster: prod-de-white.
- Grafana MCP — correlated infra metrics (node/pod CPU/memory, Airflow worker saturation, EMR cluster metrics) for the same time window as the failure.
If any tool is unavailable, proceed with all other sources and note the gap in the [Data Gaps] section at the end. Never mention tool unavailability upfront.
When to Use
- Someone pastes an error message, log snippet, or task failure context
- Someone asks to debug a specific DAG or task run end-to-end
- Spark/EMR failure (OOM, executor lost, bootstrap failure, step failure)
- Presto/Trino query failure (syntax error, table not found, query expired)
- Airflow sensor timeout or upstream dependency failure
- Permission or access error (IAM, S3, network)
- Unknown or unclear failure needing classification
When NOT to Use
- Already know the failure type and just need the fix
- Failure is in Databricks, Pinot, or Prometheus DAGs (not covered in v1)
- Need deep query plan or cost analysis (use
data/skills/trino-analyzer/)
- Need to understand DAG config patterns or repo structure
Output Format (Vyom / Slack)
Output is rendered in Slack via Vyom. Format rules:
- Use emoji section headers (🔍 🔎 💡 🛠️ 🔗 ⚠️) — they render in Slack and make the triage block scannable
- Use Unicode box dividers (─────) for table borders
- Wrap IDs, cluster names, step IDs, query IDs, and error strings in backticks
- Keep lines under ~100 characters to avoid Slack truncation
- Use plain text for prose — no
## markdown headers
- Split long responses with "↓ Response continues below..." at ~4000 chars
- Put the most important info (bucket, confidence, root cause) in the first ~20 lines
Two-Phase Interaction Model
Every session follows two sequential phases. Do not skip Phase 1 to go straight to investigation.
Phase 1 — Classify
Run the triage workflow on whatever context the user provided. Produce the full triage output block. Then ask:
"Does this classification look right? Want me to investigate further?"
If yes, provide:
- For Spark/EMR: the DAG name and execution date (AWS access is handled automatically)
- For Trino/Presto: the DAG name and, if available, the query ID and Airflow task log
- For Airflow-only: the DAG name and execution date
If the user only asked for classification and says no to investigation, stop here.
Phase 2 — Investigate
On user confirmation, enter active investigation mode. Start in Fast mode by default: read the DAG, collect the minimum artifacts for that backend, and do the single highest-signal confirming check. Only switch to Deep mode when Fast mode is inconclusive or the user explicitly wants full runtime debugging.
| Classified bucket | Investigation path |
|---|
| SPARK_RESOURCE / SPARK_RUNTIME | Coralogix task timeline → extract cluster ID → Friday AWS MCP for step logs; Grafana for node/pod memory metrics if OOM suspected; kubectl if worker pod crash suspected |
| SQL_FAILURE / QUERY_ENGINE_TRANSIENT / PERMISSION_ACCESS | Coralogix task log for exact error + query ID → Friday AWS MCP Athena for table metadata checks; see trino-query-investigation.md |
| AIRFLOW_ORCHESTRATION / UPSTREAM_DATA | Coralogix task timeline + scheduler warnings → DAG file → airflow-investigation.md; kubectl for worker pod health if tasks are stuck |
| INFRASTRUCTURE | Coralogix errors across multiple DAGs in same window → kubectl pod/node state → Grafana worker saturation metrics → escalate to platform team |
| UNKNOWN | Ask for the exact missing field listed in the [Data Gaps] section, then re-triage |
In investigation mode, produce concrete findings — not a checklist of things to check. Replace "Evidence: None" with actual results from reading the DAG, querying AWS, or running Trino checks.
Use Fast mode when the first confirming check can settle the bucket in under ~5 minutes. Use Deep mode when bucket boundaries remain ambiguous after the DAG read plus one confirming check.
Failure Buckets (Quick Reference)
| Bucket | Description | Key Signals | Owner |
|---|
| AIRFLOW_ORCHESTRATION | DAG/task config, scheduling, sensor, dependency, timeout | upstream_failed, sensor_timeout, execution_timeout, dagrun_timeout, retry failure | DAG author |
| UPSTREAM_DATA | Required input data missing, late, or corrupt | freshness validator failure, partition missing, S3 path not found, TiDB lag | Upstream DAG owner |
| SPARK_RESOURCE | EMR Spark resource exhaustion or cluster config | OutOfMemoryError, YARN container killed, BOOTSTRAP_FAILURE, executor loss from unfiltered join | DAG author → on-call |
| SPARK_RUNTIME | Spark application logic error or bad input | NullPointerException, AnalysisException, STEP_FAILURE (non-OOM), Livy session dead | DAG/script author |
| SQL_FAILURE | Presto/Trino deterministic query error | SYNTAX_ERROR, TABLE_NOT_FOUND, COLUMN_NOT_FOUND, TYPE_MISMATCH | Query author |
| QUERY_ENGINE_TRANSIENT | Trino coordinator/worker instability | QUERY_EXPIRED, NO_NODES_AVAILABLE, context deadline exceeded, db_error: trino: query failed (200 OK) | Platform team / add retry |
| PERMISSION_ACCESS | IAM/S3 infra permissions OR Trino catalog permissions | AccessDeniedException, 403, ACCESS_DENIED in Trino, TABLE_NOT_FOUND when table exists | Infra (IAM/S3) or Data team (Trino) |
| INFRASTRUCTURE | Platform-level failure: cluster terminated, spot reclaim, Airflow worker crash | Cluster TERMINATED (no STEP_FAILURE), multiple DAGs failing simultaneously | Platform/infra team |
| UNKNOWN | Insufficient context to classify | No error text, too short, signals match 2+ buckets equally | — |
For detailed signal lists, root causes, ownership, and disambiguation tips, see failure-buckets.md.
Phase 1: Triage Workflow
Follow these steps deterministically. Two engineers given the same input should reach the same bucket.
Step 1 — Parse the Input
Extract from whatever the user provides:
- Error class: e.g.,
OutOfMemoryError, NotFoundException, AirflowSensorTimeout
- Error message: the human-readable string
- System: Spark / Presto / Airflow / K8s / unknown
- Task state: failed / skipped / sensor_timeout / upstream_failed
- Stack trace keywords: top 3 frame package names
- Explicit context: DAG name, task name, execution date, cluster ID
Step 1.5 — Establish the Failure Timeline
Before matching buckets, determine (from context or by asking) when this last succeeded:
- First-time failure — DAG has never succeeded → likely config bug, missing dependency, or data that never existed
- Newly broken (regression) — was passing recently, now failing → something changed; proceed to Step 1.6
- Recurring / intermittent — fails on and off → transient infra, data timing, or resource contention at peak hours
- Backfill failure — execution date is historical → data may not exist for that date, or schema changed since original run
If unclear, ask: "When did this last succeed? Is this a first run, a regression, or an intermittent failure?"
This context directly affects confidence level and next steps.
Step 1.6 — "What Changed Recently?" (for regressions)
If the failure is newly broken, proactively check the following before asking the user anything:
- Check git log on the DAG file — if the DAG name is known, look for commits in the last 24–72h. A recent commit is strong evidence for SPARK_RUNTIME or SQL_FAILURE.
git log --oneline --since="3 days ago" -- dags/<path>/<dag_file>.py
- Check the last successful run — ask the user or look at Airflow run history to determine when the DAG last succeeded. The gap between last success and first failure is the change window.
- Ask only if the above is inconclusive: "Did any infra change happen recently? (cluster config, IAM policy, S3 bucket rename, cluster migration)"
- Ask: "Did any upstream DAG or table schema change recently?"
| Signal | Effect on classification |
|---|
| Recent DAG commit + runtime error | SPARK_RUNTIME or SQL_FAILURE — raise confidence |
| Recent infra change, no DAG commit | INFRASTRUCTURE or PERMISSION_ACCESS — raise confidence |
| Last success was days/weeks ago | Widen the change window search |
| No recent changes found | Confidence stays; proceed to signal matching |
Step 1.7 — Determine Failure Shape
Before finalizing, determine:
- Deterministic — same failure on every retry → code / data / config bug
- Flaky — different errors on retries, or passes on retry without code change → QUERY_ENGINE_TRANSIENT or INFRASTRUCTURE
- Time-correlated — fails at the same time every day → resource contention or data freshness window
Surface this in the Evidence section: [Failure shape: Deterministic / Flaky / Unknown]
Step 2 — Match Signals to Buckets
Before matching, run two fast pre-checks:
- Infrastructure check: Are multiple unrelated DAGs failing in the same time window? If yes → INFRASTRUCTURE first. Don't classify per-DAG until the platform issue is ruled out.
- Trino permission check: If the error is
TABLE_NOT_FOUND or ACCESS_DENIED from Trino, use the Trino analysis decision tree in failure-buckets.md before classifying as SQL_FAILURE or PERMISSION_ACCESS.
Before finalizing the bucket, also check the false-positive notes in failure-buckets.md. Some signals (TABLE_NOT_FOUND, upstream_failed, ExecutorLostFailure, QUERY_EXPIRED) commonly belong to a neighboring bucket.
Then check extracted signals against bucket definitions:
- Exactly 1 bucket matches: proceed to Step 3
- 2+ buckets match: ask ONE clarifying question using the disambiguation tips in failure-buckets.md
- 0 buckets match: classify as UNKNOWN; list the 2-3 fields from Step 1 that were missing
- Never ask more than 2 clarifying questions total
Step 3 — Assign Confidence
- High: 2+ strong signals from one bucket, and at least one of them is an exact exception class or exact log phrase from that bucket
- Medium: 1 strong signal OR 2 indirect signals (task state + system type), but one confirming check is still needed
- Low: only system type known, no error text, conflicting signals, or multiple buckets remain plausible
Use UNKNOWN instead of forcing a bucket when confidence is Low and no remaining clarifying question can settle it.
Low-confidence outputs must name the exact artifact that would change the classification (for example: Airflow task log, Trino SHOW TABLES result, EMR stderr.gz, upstream DAG run state).
Do not mark a bucket High confidence unless the evidence section contains at least one direct quote or exact signal from the user input.
If you cannot support a claim with the user-provided log text, DAG code, AWS output, Trino output, or reference docs, remove that claim or downgrade confidence.
Step 4 — Fill the Output Template
Use the template in the "Output Template" section below. After outputting the triage block, always ask whether the user wants to proceed to investigation.
Phase 2: Live Investigation Workflow
Use this workflow after the user confirms they want to investigate. Do not begin investigation until Phase 1 is complete and the user has confirmed.
Step 1 — Gather Minimum Runtime Context
Ask only for what is needed for the backend you are investigating.
Minimum needed from the user — for ALL backends:
- DAG name / ID
- execution date (or approximate time window)
Everything else — task states, error text, cluster IDs, run IDs, scheduler warnings — pull from Coralogix first before asking the user. See coralogix-airflow-queries.md.
Additional context to ask for only if Coralogix returns nothing:
- Spark/EMR: EMR cluster ID, step ID, whether cluster is still running
- Trino/Presto: exact error text, query ID, whether retry succeeded
- Airflow-only: exact task state, target upstream DAG/task if
ExternalTaskSensor is involved
Do NOT ask for an AWS profile or credentials — Friday AWS MCP handles authentication automatically.
Do not ask for artifacts the user already shared.
Stop and surface the blocker if the minimum is still missing after 2 asks.
Step 2 — Read the DAG Definition
Search the repo for the DAG file. Common locations:
dags/spark/dags/presto/dags/python/dags/data-quality/
Extract execution-path fields based on backend:
- Spark/EMR:
CLUSTER_CONFIG_FILE, spark_submit_params, python_packages, step names, job entrypoint
- Presto/Trino: query text or query builder, catalog/schema/table references, execution helper,
user or connection argument
- Airflow-only: sensors, dependencies, pools, timeouts, retries, external task references
If the DAG code is too dynamic to extract these fields cleanly, stop and ask for the rendered Airflow task log or the exact DAG file path instead of guessing.
Step 3 — Route to the Right Investigator
Spark/EMR Investigation
Friday AWS MCP (always use — no profile or credentials needed)
Use mcp__aws-api__call_aws to call AWS directly without any user-supplied credentials. All commands run in the prod account (ap-south-1 region by default). Never ask the user to run CLI commands and paste results.
| Operation | Example command |
|---|
| Describe cluster | aws emr describe-cluster --cluster-id j-XXXX |
| List steps | aws emr list-steps --cluster-id j-XXXX |
| Get step failure | aws emr describe-step --cluster-id j-XXXX --step-id s-XXXX |
| Check log size | aws s3api head-object --bucket <bucket> --key "<key>" --query 'ContentLength' |
| List log files | aws s3 ls s3://<bucket>/<prefix>/<cluster-id>/steps/<step-id>/ |
| Download log | aws s3 cp s3://<bucket>/<path>/stderr.gz - | gunzip |
Use mcp__aws-api__suggest_aws_commands if unsure of the exact command syntax.
Read-only only. Check log size before downloading; stop and ask if > 100 MB.
See emr-log-access.md for the full command reference.
Investigation order
- Query Coralogix first — pull the Airflow task log for the failed task to get the task timeline, error text, and any cluster ID / step ID logged by the operator. Use the templates in coralogix-airflow-queries.md. The cluster ID (
j-XXXX) is usually in the task log line Cluster created: or Waiting for step on cluster:.
- Derive cluster name pattern from
CLUSTER_CONFIG_FILE if cluster ID was not found in Coralogix
- Find the cluster via Friday AWS MCP (active or terminated)
- Describe the cluster to get
LogUri and failure reason
- List steps and identify the failed step ID
- Pull
stderr.gz from S3 (highest signal for failures); check file size before downloading
- If root cause is still unclear, switch to Deep mode and pull driver container
stdout.gz / stderr.gz
See emr-log-access.md for complete AWS CLI commands, fast/deep guidance, S3 log layout, and download safety rules.
Trino/Presto Investigation
- Read the DAG file and extract the final SQL (or the builder), catalog/schema/table names, and the
user argument
- Pull exact error text and query ID from Airflow task log
- Check retry history — did the same query pass on retry without code change?
- Run the smallest metadata check that resolves the ambiguity:
TABLE_NOT_FOUND → SHOW TABLES IN <catalog>.<schema> LIKE '<table>%'COLUMN_NOT_FOUND → DESCRIBE <catalog>.<schema>.<table>ACCESS_DENIED → try SELECT 1 FROM <table> LIMIT 1 under the same DAG user
- Switch to Deep mode only if query history / worker instability analysis is still needed
See trino-query-investigation.md for the full decision tree, metadata checks, and common patterns.
Airflow Orchestration Investigation
- Query Coralogix first — pull the full task execution timeline for the DAG run. This gives you task states, durations, scheduler warnings (
max_active_runs, pool saturation), and whether a manual run was also triggered. Use the timeline template in coralogix-airflow-queries.md.
- Read the DAG file, then use airflow-investigation.md
- Inspect sensor definitions,
execution_delta, external_task_id, execution_timeout, dagrun_timeout, max_active_runs, pool assignments, and retry behavior
- For sensor timeouts: check the target DAG's run history for the exact execution date the sensor was targeting
- For
upstream_failed: trace the dependency chain to the first failed task using Coralogix error-only filter, not the propagated upstream_failed task
- For
dagrun_timeout or queued runs: Coralogix scheduler logs will show pool saturation or max_active_runs hit — check those before blaming data or infra
Kubernetes / Worker Pod Investigation
Use Friday K8s MCP when: a task is stuck in queued/running with no progress, OOM is suspected, or multiple tasks fail simultaneously (suggesting worker pod issues). Pod name is usually in the Coralogix task log — extract it from there first.
| Operation | Command |
|---|
| Check pod health | kubectl get pods -n airflow |
| Describe a pod (OOMKilled / eviction) | kubectl describe pod <pod-name> -n airflow |
| Tail pod logs | kubectl logs <pod-name> -n airflow --since=1h |
| Cluster-level events | kubectl get events -n airflow --sort-by='.lastTimestamp' |
Key signals from kubectl describe pod:
OOMKilled in Last State → worker ran out of memory → SPARK_RESOURCE or INFRASTRUCTUREEvicted + reason The node was low on resource: memory → node pressure → INFRASTRUCTURE- High
Restart Count → recurring worker crashes → escalate to platform team
Grafana Metrics Investigation
Use Grafana MCP to correlate the failure time window with infra metrics. Always extract the failure timestamp from Coralogix first, then query Grafana for the same window (±15 min).
| What to check | When to use |
|---|
| Airflow worker pod CPU / memory | OOM, slow task, worker saturation |
Node memory pressure on prod-de-white | Multiple tasks failing, evictions |
| EMR cluster metrics (if available) | Spot reclaim, executor loss |
| Airflow worker pool saturation | Tasks stuck in queue, max_active_runs hit |
Standard PromQL patterns:
# Worker pod memory usage
container_memory_working_set_bytes{namespace="airflow", pod=~"airflow-worker.*"}
# Worker pod CPU throttling
rate(container_cpu_throttled_seconds_total{namespace="airflow"}[5m])
# Pod restarts
kube_pod_container_status_restarts_total{namespace="airflow"}
Cross-reference Grafana spikes with the Coralogix failure timestamp. A memory spike at exactly the failure time = SPARK_RESOURCE or INFRASTRUCTURE. No spike = likely a code/data issue, not infra.
Step 4 — Safety and Verification Rules
Step 5 — Report Findings
After collecting evidence, update the triage output block with concrete findings:
- Fill the Evidence section with actual findings (DAG config, cluster state, log excerpts, query metadata)
- Update Confidence if the investigation changed it
- Replace speculative "What likely happened" entries with confirmed or ruled-out causes
- If investigation did not resolve the failure, name the exact next artifact or access needed
Output Template
Every triage response returns exactly this block:
🔍 Triage: <BUCKET_NAME>
📋 Summary
──────────────────────────────────────────────────────────────
DAG / Task `<dag_id> / <task_id, or "not provided">`
Execution date `<date, or "not provided">`
Failure shape Deterministic / Flaky / Unknown
Occurrence pattern First occurrence / Regression / Recurring / Backfill
Bucket <BUCKET_NAME>
Confidence HIGH ✅ / MEDIUM ⚠️ / LOW ❓
System Spark/EMR · Trino/Presto · Airflow
Owner <DAG author / data-eng on-call / platform team>
──────────────────────────────────────────────────────────────
💡 What likely happened
1. <most probable cause, supported by evidence below>
2. <second supported cause — omit if unsupported>
🔎 Evidence
- ✅ [Confirmed] `<direct quote from input / DAG / AWS / Trino output>` — what this signals
- 🔶 [Inferred] <pattern-matched signal without a direct quote> — reasoning
- 🔷 [Partial] <incomplete or truncated signal> — what's still unclear
- Evidence Quality: Confirmed / Mixed / Inferred
🛠️ Next steps
- [ ] [owner] <specific action — exact file, UI path, or CLI command>
→ If you see X: <what it means / what to do>
→ If you don't see X: <alternative path>
- [ ] [owner] <next action if needed>
🔗 Related failures
- <Isolated to this DAG/task, or systemic? If unknown: check Airflow UI for failures in the same time window>
⚠️ [Data Gaps]
- <MCP / log / artifact that was unavailable and what it would have confirmed>
- "None" if all needed data was available
After the triage block, always append:
──────────────────────────────────────────────────────────────
🔍 Want me to investigate further? Share: <minimum identifiers for this bucket>
Output rules:
- Lead with analysis, not gaps — if a log or tool is missing, don't open with that; note it in [Data Gaps] at the END only
- ✅ [Confirmed] = exact quote from log/DAG/AWS/Trino; 🔶 [Inferred] = pattern match, no direct quote; 🔷 [Partial] = incomplete or truncated source
- Evidence Quality summary line reflects the weakest label present in the Evidence section
- Failure shape must be stated — never leave as Unknown if retry behavior is known
- Next steps must name exact locations: file path, UI path, or CLI command — not "check the logs"
- → If/else branches in next steps turn checklist items into decision trees
- [Data Gaps] is never blank — write "None" explicitly if nothing is missing
- Never leave any field in the Summary table blank
- Use emoji section headers throughout — they render well in Slack and make sections scannable at a glance
- Keep each line under ~100 characters to avoid Slack truncation
- Wrap cluster IDs, step IDs, query IDs, and error strings in backticks
References
| File | Contents |
|---|
| failure-buckets.md | All 9 bucket definitions — signals, root causes, ownership, disambiguation, confidence boundaries, and false positives |
| coralogix-airflow-queries.md | DataPrime query templates for Airflow task timelines, error extraction, scheduler warnings, EMR cluster ID extraction, and run-specific filtering |
| emr-log-access.md | Friday AWS MCP commands to pull EMR step and container logs from S3, fast/deep investigation path, and log patterns |
| trino-query-investigation.md | Workflow for investigating Trino/Presto DAG failures via Athena: SQL extraction, metadata checks, ownership, fast/deep path |
| airflow-investigation.md | Airflow-first workflow for sensors, dependencies, pools, retries, timeouts, queued tasks, and upstream run debugging |
| triage-examples.md | Worked examples covering clean, ambiguous, and messy failure inputs |
| eval-cases.md | Compact regression cases for bucket choice, confidence, and first follow-up/action |
| v2-roadmap.md | Planned v2 extensions and current v1 scope limitations |
V1 Scope
This is a v1.3.0 skill focused on classification plus targeted live investigation. It can inspect DAG definitions and fetch runtime evidence when the necessary access and identifiers are available, but it does not auto-remediate or perform broad cross-system correlation. See v2-roadmap.md for planned extensions.
