qa-aman/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.1%
- Duplicate rate: 0% (on primary key)
- Freshness: data timestamp within [N hours] of run time
- Downstream table last_updated: matches run date

Alert on these metrics: [link to monitoring tool]

Step 3: Diagnose a failed job

Use this decision tree when a job fails or an alert fires:

1. Check job status:

[Orchestration UI link] → find the failed run → check error message
Common errors:
  - "Connection refused" → source database is unavailable (see Step 4a)
  - "Schema mismatch" → source schema changed (see Step 4b)
  - "Out of memory" → data volume spike (see Step 4c)
  - "Timeout" → source query is slow (see Step 4d)

2. Check if data arrived:

-- Did the source data land in the raw layer?
SELECT COUNT(*), MAX(extracted_at)
FROM raw.[source_table]
WHERE DATE(extracted_at) = CURRENT_DATE;
-- Expected: > 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 (< 30 min): wait and trigger a manual rerun after source recovers
If prolonged: escalate to source team via [contact method]
Rerun command: [airflow trigger_dag / dbt run / etc.]

4b. Source schema changed

Symptom: column not found / type mismatch error in job logs
Diagnose: compare current source schema against schema spec [link]
Fix:
  1. Identify which column changed
  2. Update schema spec
  3. Update pipeline transformation logic
  4. Backfill affected date range (see backfill section)
Escalate: notify [data platform team] if schema change was unannounced

4c. Out of memory / volume spike

Symptom: job killed due to OOM, or runtime > [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")