Etl Runbook

Overview

Based on Data Pipelines Pocket Reference (Densmore) and Fundamentals of Data Engineering (Reis & Housley). An ETL runbook is the operational contract for a data job: how to monitor it, diagnose when it breaks, and recover data integrity. Densmore's standard: data pipelines fail in subtle ways - not always with an error, sometimes with silent data loss or incorrect rows. A good runbook covers job failures AND data quality failures.

The test: can an on-call engineer who didn't build this pipeline detect a problem, isolate the cause, and restore data integrity at 3am?

Workflow

Step 1: Write the job overview

Job: [job name]
Pipeline: [parent pipeline name]
Owner: [team]
Purpose: [what this job does and what breaks downstream if it doesn't run]
Schedule: [cron expression or trigger condition]
SLA: [data must be available by X time or downstream is impacted]
Typical runtime: [expected duration, e.g. 15-25 minutes]
Max runtime before alert: [e.g. 45 minutes]
Orchestrator: [Airflow / Prefect / dbt Cloud / etc.]
Job link: [direct link to job in orchestration UI]
Dashboard: [link to data quality/freshness monitoring]

Step 2: Define what "healthy" looks like

Data quality thresholds, not just job status:

Healthy signals:
- Job status: SUCCESS
- Row count: [expected range, e.g. 50,000–200,000 rows]
- Null rate on critical fields:  0 rows, extracted_at within last [N] hours

3. Check data quality:

-- Are there unexpected nulls?
SELECT COUNT(*) as nulls
FROM [target_table]
WHERE [critical_field] IS NULL AND DATE(created_at) = CURRENT_DATE;
-- Expected: 0

-- Are there duplicates?
SELECT [primary_key], COUNT(*) as cnt
FROM [target_table]
WHERE DATE(created_at) = CURRENT_DATE
GROUP BY [primary_key] HAVING cnt > 1;
-- Expected: 0 rows

Step 4: Resolve common failures

4a. Source database unavailable

Check: ping source system or check their status page
If transient ( [max runtime threshold]
Diagnose:
  SELECT COUNT(*) FROM [source_table] WHERE DATE(created_at) = CURRENT_DATE;
  Compare to normal range in Step 2.
Fix:
  - If volume is legitimately large: rerun with increased memory allocation
    [specific command or config change]
  - If volume is abnormal (data duplication upstream): do NOT load. Escalate.

4d. Source query timeout

Diagnose: check source query runtime vs. normal
Fix:
  1. Check if source DB has an active incident
  2. If query plan degraded: add EXPLAIN to identify missing index
  3. Short-term: increase job timeout in [config file]
  4. Long-term: file ticket to optimize source query or add index

Step 5: Backfill procedure

When data needs to be reloaded for a historical date range:

When to backfill: source was late, schema changed mid-run, data quality failure detected

Backfill command:
[exact command with date range parameters]

Idempotency: [yes/no]
  If yes: safe to rerun, uses MERGE/UPSERT on [primary key]
  If no: must truncate target partition first:
    DELETE FROM [table] WHERE DATE(partition_date) BETWEEN [start] AND [end];

Backfill duration estimate: [N rows/minute, use to estimate runtime]

After backfill, verify:
  SELECT COUNT(*), MIN(created_at), MAX(created_at)
  FROM [target_table]
  WHERE DATE(created_at) BETWEEN [start] AND [end];

Step 6: Escalation path

| Situation | Contact | Channel | |-----------|---------|---------| | Source system down > 30 min | [Source team on-call] | [PagerDuty / Slack] | | Unannounced schema change | [Source team lead] | [channel] | | Data quality failure affecting downstream reports | [Data platform lead] | [channel] | | Cannot determine root cause within 30 min | [Senior DE on-call] | [PagerDuty] |

Anti-Patterns

1. Job success ≠ data success Bad: "If the job shows SUCCESS, we're done." Good: Check row counts, null rates, and freshness even on successful runs. Silent data quality failures are more dangerous than job failures because they go undetected longer.

2. No backfill procedure Bad: "Rerun the job." Good: Specify whether reruns are idempotent, what to truncate if not, and the exact command with date range parameters.

3. Diagnose without data context Bad: "Check the logs." Good: Provide specific SQL queries to confirm whether data arrived, check for nulls, and detect duplicates.

4. Escalate immediately Bad: On-call escalates after every failure without attempting diagnosis. Good: Runbook gives the on-call engineer 30 minutes of self-service steps before escalation is appropriate.

Quality Checklist

• [ ] Job overview includes schedule, SLA, expected runtime, and direct links
• [ ] "Healthy" state defined with row count ranges, null rates, and freshness thresholds
• [ ] Diagnosis flow covers job failure AND data quality failure (they are different)
• [ ] Top 4 failure modes documented with exact diagnostic steps and SQL queries
• [ ] Backfill procedure specifies idempotency, truncation if needed, and verification query
• [ ] Escalation path names specific people or teams and contact channels
• [ ] All commands and queries are copy-pasteable (no "run the relevant command")

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: qa-aman
• Source: qa-aman/claude-skills
• License: MIT

Install and usage instructions live in the source repository linked above.