ETL Error Notification and Escalation Prompt Template

This prompt is designed for the final escalation step in a multi-layered data pipeline recovery strategy. It should be invoked only after automated retry and self-correction prompts have been exhausted. The ideal user is a data platform operator or SRE who needs to convert raw, machine-generated failure context—including error logs, retry history, and SLA impact—into a structured, human-readable incident notification. The core job-to-be-done is deciding whether to wake up an on-call engineer and providing them with a pre-triaged summary that accelerates their response. The required context is rich: you must supply the specific error message, the number of retries attempted, the pipeline run ID, the affected dataset or table, and a clear statement of the SLA risk (e.g., 'data freshness will breach 2-hour SLO in 15 minutes').

Do not use this prompt for transient errors that a self-correction prompt can still resolve, such as a single malformed CSV row or a schema mismatch that has a known mapping. Using it too early creates alert fatigue. Conversely, do not use this prompt for purely informational pipeline events like a successful run with a warning. The prompt's value is in its structured decision output: it must produce a severity classification (e.g., SEV1, SEV2), an impact summary, a suggested owner team, and a boolean page_oncall decision. A concrete implementation will validate this output against a strict schema before sending it to an alerting system. For example, if the page_oncall field is true but the severity is not SEV1, the harness should flag this inconsistency for human review before dispatching a page. This prevents a misconfigured prompt from waking an engineer for a low-priority issue.

Before wiring this prompt into a PagerDuty or Slack integration, run it against a golden dataset of historical pipeline failures where you know the correct escalation decision. Measure precision and recall on the page_oncall decision specifically. A common failure mode is the model defaulting to 'page' for any error that mentions a critical dataset, even if the error is a known, self-resolving blip. To counter this, include a [CONSTRAINTS] block that explicitly instructs the model to consider retry exhaustion and SLA margin as the primary gating factors. The next step after reading this section should be to review the prompt template and adapt the [OUTPUT_SCHEMA] to match your exact incident management payload contract.

This template is designed to be dropped directly into an AI harness that monitors data pipeline execution. It consumes structured error context, retry history, and SLA metadata, then produces a human-readable incident notification and a clear escalation decision. The output is intended for delivery to Slack, PagerDuty, email, or a runbook automation system. Every placeholder is a square-bracket token that your application must populate before sending the prompt to the model.

textCopy
You are an ETL incident notification assistant for a data platform operations team.

Your job is to convert pipeline failure context into a structured, human-readable notification and an escalation decision.

## INPUT

[ERROR_CONTEXT]

## RETRY HISTORY

[RETRY_HISTORY]

## SLA CONTEXT

[SLA_CONTEXT]

## CONSTRAINTS

[CONSTRAINTS]

## OUTPUT SCHEMA

Return a single JSON object with these fields:
- "severity": "critical" | "high" | "medium" | "low"
- "incident_summary": string (one sentence, plain language, max 200 characters)
- "impact_summary": string (what downstream systems, SLAs, or data products are affected, max 300 characters)
- "suggested_owner": string (team or on-call rotation name, or "unclear" if you cannot determine it)
- "escalation_decision": "page_on_call" | "notify_channel" | "log_only" | "human_review_required"
- "escalation_reason": string (why this decision was made, referencing retry budget, SLA breach, or data impact, max 250 characters)
- "next_steps": array of strings (ordered, actionable steps for the responder)
- "confidence": number between 0.0 and 1.0 (your confidence in the severity and escalation decision)

## RULES

1. If the retry budget is exhausted and SLA breach is imminent, severity must be "critical" and escalation_decision must be "page_on_call".
2. If the error is transient and retries remain, prefer "notify_channel" or "log_only".
3. If you cannot determine the owner from the context, set suggested_owner to "unclear" and escalation_decision to "human_review_required".
4. Never invent impact details. If impact is unknown, state "Impact unknown; manual investigation required."
5. If confidence is below 0.7, set escalation_decision to "human_review_required" regardless of other signals.
6. Do not include markdown formatting in the JSON values.

Return only the JSON object. No preamble, no commentary.

To adapt this template, replace each placeholder with live data from your pipeline observability layer. [ERROR_CONTEXT] should include the pipeline name, run ID, error message, stack trace, and the specific component that failed. [RETRY_HISTORY] should list each retry attempt with its timestamp, outcome, and any error variation. [SLA_CONTEXT] should specify the data freshness SLA, the current delay, and the downstream consumers affected. [CONSTRAINTS] is where you inject organization-specific policies—for example, "Never page on-call between 22:00 and 06:00 for medium-severity pipeline failures" or "Escalate to the data-engineering-oncall rotation for any failure in the fact_sales pipeline." The output schema is intentionally flat and machine-readable so that your harness can parse the JSON, route the notification to the correct channel, and trigger a pager if the escalation decision demands it. Before deploying, run this prompt against a golden dataset of historical pipeline incidents where you know the correct severity and escalation outcome, and measure whether the model's decisions match your on-call team's expectations.

The ETL Error Notification and Escalation Prompt Template is designed to sit at the boundary between automated pipeline recovery and human incident response. It consumes structured error context—failure type, retry history, SLA impact, and affected datasets—and produces a notification payload that downstream systems can route to Slack, PagerDuty, or email. The harness must enforce that the prompt only fires after automated retries are exhausted, preventing noisy alerts for transient failures that self-resolve.

Wire the prompt into your pipeline orchestrator (Airflow, Dagster, Prefect) as a final step in the error-handling DAG. Before calling the model, assemble the input context from pipeline metadata: the task ID, the exception message and stack trace, the number of retry attempts consumed, the retry budget limit, the SLA window for the affected dataset, and a list of downstream consumers. Pass these as [ERROR_CONTEXT], [RETRY_HISTORY], [SLA_IMPACT], and [DOWNSTREAM_DEPENDENCIES]. The model should return a structured JSON object with fields for severity (INFO, WARN, CRITICAL), summary, suggested_owner_team, page_on_call (boolean), and notification_channel (slack, pagerduty, email). Validate the output against this schema immediately. If the model returns a page_on_call: true decision, route to PagerDuty only after a human-in-the-loop confirmation step—never auto-page directly from model output. Log every notification decision, including the raw prompt, the model response, and the routing action taken, for post-incident review.

For retry and fallback behavior, implement a circuit breaker: if the model call fails or returns unparseable JSON, retry once with a simplified prompt that omits the downstream dependency detail. If that also fails, fall back to a static notification template that includes the raw error context and a severity: WARN default. This ensures that a model outage does not silence pipeline failures. When integrating with PagerDuty, map the severity field to PagerDuty urgency levels and include the suggested_owner_team as the routing key. For Slack, use the summary as the message text and attach the full error context in a thread. Evaluate the prompt's performance quarterly by sampling notification decisions and comparing them against actual incident outcomes—look for over-escalation (paging for self-resolving errors) and under-escalation (missing critical failures). Adjust the retry budget thresholds and SLA impact weights in the prompt based on these findings.