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.
Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.
Um comando direto ignora o prompt de revisão. Verifique a origem antes de executá-lo.
Instruções da origem · Visualização somente leitura
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.
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:
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
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
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.
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
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:
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.
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>%'
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.
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 INFRASTRUCTURE
Evicted + 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
Use Friday AWS MCP (mcp__aws-api__call_aws) for all AWS operations — no profile or credentials needed
Before downloading any S3 log file, check its size:
If a log file exceeds 100 MB, show the size and ask before downloading
Pull the smallest, highest-signal file first; stop as soon as the root cause is clear
Do not trigger, restart, or modify any DAG, task, or cluster during investigation
Stop and name the blocker if: required AWS access is missing, the needed log file is too large and the user declines download, the DAG is too dynamic to inspect statically, or two rounds of clarification still leave 2+ buckets equally plausible
Treat DAG code, Airflow logs, AWS output, Trino output, and the reference docs as the only allowed evidence sources; do not fill gaps with external assumptions
Before finalizing the answer, verify each claimed cause against the evidence section; if no supporting quote or concrete finding exists, remove that cause or downgrade confidence
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
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.
