Data Pipeline Checkpoint Recovery Prompt

This prompt is built for data engineers and platform operators who need to restart a failed batch or streaming pipeline from the last successful checkpoint. It takes the checkpoint metadata, the failure error, and the pipeline DAG, then returns a safe restart point and any state-repair instructions. The goal is to minimize data loss and avoid duplicate processing while giving the operator a clear, auditable recovery path.

Use this prompt when a pipeline run has failed with a specific error, the checkpoint system has recorded a last-committed offset or watermark, and you need to decide whether to resume from that point, rewind, or skip a poisoned segment. The prompt works best when you can provide structured context: the checkpoint timestamp or sequence number, the exact error message and stack trace, the pipeline topology (source, transforms, sinks), and any exactly-once or at-least-once delivery guarantees. It is designed for integration into runbook automation, where the output feeds directly into a restart script or an operator's decision dashboard.

Do not use this prompt when the failure is a systemic infrastructure outage (e.g., network partition, storage unavailability) rather than a data or logic error. It is not a replacement for a dead-letter queue or a schema registry. Avoid using it for pipelines that lack checkpointing entirely—if there is no recorded state, the prompt cannot invent a safe restart point. In regulated environments where exactly-once semantics are auditable, always route the prompt's output through a human approval step before executing a restart. The prompt's confidence score and state-repair instructions are advisory, not a substitute for verifying that reprocessing won't violate delivery guarantees.

This template is the core instruction set you'll send to the model when a batch or streaming pipeline fails. It forces the model to reason about checkpoint metadata, the specific failure error, and the pipeline's directed acyclic graph (DAG) before suggesting a restart point. The output is designed to be parsed by an orchestration tool like Airflow or Dagster, so the structure is strict. Use this prompt as a starting point; you will need to populate the placeholders with data from your pipeline's execution context and monitoring systems before every call.

textCopy
You are a data reliability engineer diagnosing a failed data pipeline. Your task is to analyze the provided checkpoint metadata, the failure error, and the pipeline DAG to determine a safe restart point and any required state-repair instructions.

Your primary goal is to minimize data loss and duplicate processing. You must explicitly check if the proposed restart would violate exactly-once processing semantics and issue a clear warning if it does.

## INPUTS
- **Pipeline DAG:** A representation of the task dependencies.
[DAG_DEFINITION]
- **Last Successful Checkpoint:** Metadata about the last completed task before failure.
[CHECKPOINT_METADATA]
- **Failure Error:** The full error message and stack trace from the failed task.
[FAILURE_ERROR]
- **Pipeline Execution Context:** Runtime parameters, environment variables, and run ID.
[EXECUTION_CONTEXT]

## CONSTRAINTS
- Prioritize data integrity over speed.
- If a safe restart point cannot be determined with high confidence, recommend a full backfill from the last verified clean state instead of guessing.
- The output must be a valid JSON object matching the specified schema.
- [ADDITIONAL_CONSTRAINTS]

## OUTPUT_SCHEMA
{
  "diagnosis": {
    "root_cause_category": "string (e.g., DATA_CORRUPTION, DEPENDENCY_FAILURE, RESOURCE_EXHAUSTION, SCHEMA_DRIFT)",
    "failed_task_id": "string",
    "summary": "string"
  },
  "recovery_plan": {
    "recommended_action": "RESTART_FROM_CHECKPOINT | SKIP_TASK | FULL_BACKFILL | MANUAL_INTERVENTION",
    "restart_point_task_id": "string | null",
    "state_repair_instructions": [
      {
        "task_id": "string",
        "action": "string (e.g., DELETE_PARTITION, RESET_OFFSET, CLEAR_TEMP_TABLE)",
        "details": "string"
      }
    ],
    "exactly_once_violation_warning": "string | null"
  },
  "confidence_score": 0.0-1.0,
  "requires_human_approval": true | false
}

To adapt this template, start by replacing the [DAG_DEFINITION] placeholder with a structured representation of your pipeline, such as a JSON list of tasks and their dependencies. The [CHECKPOINT_METADATA] should include the last completed task's ID, its output partition or offset, and a timestamp. For streaming pipelines, this is often a Kafka offset or a Kinesis sequence number. The most critical adaptation is the [ADDITIONAL_CONSTRAINTS] field; use it to inject your organization's specific data retention policies, SLAs, or regulatory rules that might override a purely technical recovery path. After running the prompt, always validate the output against the OUTPUT_SCHEMA before passing it to an orchestrator. If the requires_human_approval flag is true or the confidence_score is below 0.85, route the plan to an on-call engineer for review instead of executing it automatically.

The Data Pipeline Checkpoint Recovery Prompt is designed to sit inside an orchestration layer—such as Airflow, Dagster, or Prefect—that catches a pipeline failure, freezes the DAG, and calls the model before any automated restart. The harness should supply three inputs: the checkpoint metadata (last successful offset, watermark, or sequence number), the full error message and stack trace from the failed task, and a serialized representation of the pipeline DAG showing upstream dependencies and state. The model returns a structured JSON payload containing a restart_point, a list of state_repair_instructions, and a boolean exactly_once_violation_risk flag. The harness must parse this output and decide whether to proceed automatically or escalate.

Before executing any restart, the harness must validate the model's output against a strict schema. The restart_point must be a valid node or offset that exists in the provided DAG; if the model hallucinates a task name or offset that isn't present, the harness should reject the output and retry with a clarified error context. The exactly_once_violation_risk flag acts as a circuit breaker: when true, the harness must route the decision to a human operator via a ticketing system or runbook notification, never auto-restarting. For automated paths, implement a retry budget—if the model's recovery suggestion fails more than three times, escalate permanently. Log every recovery attempt with the model's raw output, the validation result, and the execution outcome to build an audit trail for postmortems.

Model choice matters here. Use a model with strong reasoning capabilities and a large context window, as the DAG representation plus error logs can exceed 4K tokens. If the DAG is large, preprocess it to include only the failed task, its direct upstream dependencies, and the checkpoint state—don't send the entire pipeline graph. For streaming pipelines (Kafka, Kinesis), include consumer group offsets and partition assignments. The harness should also implement idempotency guards: before applying any state-repair instruction, check whether the repair was already applied in a prior partial recovery to avoid double-processing. Finally, never let the model directly execute pipeline commands—its output is a recommendation that must pass through validation, human approval gates for high-risk cases, and your existing deployment controls.