Data Quality & Observability Runbook

Purpose and scope

This runbook helps teams detect, classify, triage, and resolve data quality incidents that could affect reporting, ML models, or operational workflows. It emphasises fast detection, clear ownership, practical mitigations, and actions that prevent recurrence while avoiding alert fatigue and ad‑hoc fixes.

How to use this runbook

If you observe or are alerted to a data anomaly, follow the sequence below: detect → classify → score severity → mitigate immediate impact → assign owners → communicate → perform RCA → implement prevention. Each step includes concrete checks, example queries, and templates you can copy into alerts or messages.

Incident detection triggers

Common triggers and observability signals to monitor:

• Schema change (column added/removed/type changed)
• Freshness / latency breach (data not updated within SLA)
• Record count drift (unexpected increase or decrease)
• Null or empty-rate spike on key columns
• Distribution drift (feature or metric distributions differ from baseline)
• Referential integrity failures or join key mismatches
• Duplicate key rises or cardinality changes
• Downstream consumer errors (reports failing, pipeline backpressure)
• Model input feature anomalies (out‑of‑range values)

Sample monitoring expressions and SQL checks

Use these as starting points; adapt thresholds to the table, volume and SLA.

• Freshness: SELECT MAX(event_time) FROM schema.table; alert if MAX(event_time) < now() - interval 'X'
• Row-count drift: SELECT COUNT(*) FROM schema.table WHERE partition_date = '{{yesterday}}'; compare to rolling 7‑day median; alert if > 20% deviation
• Null-rate: SELECT COUNT(*) FILTER (WHERE important_col IS NULL) / COUNT(*) FROM schema.table; alert if null rate > threshold
• Cardinality change: SELECT COUNT(DISTINCT key_col) FROM schema.table; compare to baseline
• Referential integrity: SELECT COUNT(*) FROM child LEFT JOIN parent ON child.parent_id = parent.id WHERE parent.id IS NULL;
• Checksum/hash checks: compute row-level hash and compare current partition vs previous
• Distribution drift (simple): calculate KS or population percentiles for a metric and alert when distance > threshold

Classification

Classify incidents to pick the right remediation path. Typical classes:

1. Schema — column addition/removal/type change or unexpected nulls due to schema mismatch.
2. Freshness / Availability — pipeline not running, delayed ingestion, or source outage.
3. Volume / Count — sudden increase/drop in rows or partitions.
4. Distribution drift — statistical shift in feature/metric distributions affecting models or metrics.
5. Integrity / Referential — broken joins, missing lookups, key collisions.
6. Quality content — bad or unexpected values, format errors, or corrupted data.

Severity scoring

Use a simple numeric severity scale to prioritize. Score as the maximum of impact and urgency.

• Impact (1–5): considers number of downstream consumers, financial/regulatory impact, model degradation, or customer effect.
• Urgency (1–5): time-sensitivity — how fast it will cause harm or block critical workflows.

Example thresholds:

• Severity 5 — critical: outages causing production reports/models to be materially wrong, regulatory exposure, or customer-facing failures.
• Severity 4 — high: key KPIs affected, multiple teams impacted, imminent report/model degradation.
• Severity 3 — medium: localized impact, workarounds exist but manual effort required.
• Severity 2 — low: minor metrics deviate, non-urgent correction.
• Severity 1 — informational: anomaly noted, historical investigation only.

Immediate mitigation (triage checklist)

Follow these steps in the first 15–60 minutes depending on severity.

1. Authenticate the alert: reproduce the anomaly with the example queries above.
2. Identify affected tables/datasets, partitions, and timestamps.
3. Capture a snapshot (query result, sample rows, logs) for evidence and RCA.
4. Determine if a temporary mitigation is possible (rerun pipeline, switch to previous partition, pin dashboards to last good date).
5. Assign an incident owner and notify stakeholders (see templates below).
6. If data is unreadable or harmful, consider preventing downstream consumption (disable ETL job, set 'do not consume' flag, or quarantine dataset).

Owner assignment and roles

Assign clear roles early:

• Incident owner — coordinates triage, communication, and closure (usually data platform or analytics lead on duty).
• Data steward / Subject Matter Expert — responsible for the dataset semantics and corrective actions.
• Pipeline owner / SRE — fixes ingestion or job failures, restarts pipelines.
• Consumer liaison — informs downstream teams and coordinates workarounds.
• RCA lead — prepares post‑incident analysis and action plan.

Communication templates

Initial Slack/Chat alert (short)

Channel: #data-alerts

:rotating_light: Incident: [SEVERITY] — [dataset.table] — [classification] — Impact: [brief].

Owner: @alice. Immediate action: investigating. Key query snapshot: [link].

Please avoid running heavy queries against the dataset while we investigate.

Status update email/notice

Subject: [SEVERITY] Data Incident — [dataset.table] — [short description]

Summary: [what happened — e.g., ingestion delayed 6h; column X missing].

Impact: [reports/models affected; timeframe].

Mitigation: [what we did — e.g., restarted job, pinned dashboards, quarantined partition].

Next steps: RCA in progress; ETA for full fix: [time]. Contact: [incident owner].

Remediation paths (by classification)

Schema

• Confirm schema change source (upstream change, deployment, manual alter).
• If intentional and backward-compatible: update downstream consumers and tests, deploy schema migration plan.
• If unintended: roll back the change if possible; if not, quarantine affected partitions and run a targeted backfill.

Freshness / Availability

• Check scheduler and pipeline logs; restart failed tasks if transient.
• Escalate to infra/SRE for resource or cluster issues.
• If source outage: flag data as stale, set consumer workarounds, and plan backfill once restored.

Distribution drift

• Assess magnitude and duration of drift versus model/metric sensitivity.
• Isolate recent partitions and compare samples; check upstream transforms for changes.
• Consider model retraining, feature engineering fixes, or temporarily disabling model outputs if risk is high.

RCA template

Use this structure for the post-incident analysis.

1. Incident title & timestamp
2. Detection: how and when discovered
3. Impact: who/what was affected (dashboards, jobs, customers, models)
4. Root cause: factual chain of events that caused the issue
5. Detection gap: why our monitoring didn’t prevent or detect it earlier (missing check, bad threshold, runbook gap)
6. Immediate mitigation steps taken
7. Permanent fix and owners (code change, backfill, schema migration)
8. Verification plan and metrics to confirm fix
9. Action items with owners and due dates
10. Lessons learned and runbook updates required

Post-incident actions (closure checklist)

• Confirm data is restored and validated (use automated checks where possible).
• Run or schedule required backfills and verify row counts and checksums.
• Publish RCA and action plan to stakeholders.
• Implement permanent code/config fixes and add regression tests.
• Tune or add monitoring checks with clear ownership and SLAs.
• Update data contracts and documentation if interface or semantics changed.
• Conduct a short retrospective with affected teams and record lessons in the domain.

Preventive patterns and observability best practices

• Start with decision‑centric checks: instrument the data that supports an important decision or report first.
• Use a small set of owned, reliable checks (freshness, row count, null rate, referential integrity) before broad coverage.
• Capture lineage: know upstream owners and downstream consumers for each dataset.
• Implement health dashboards that combine signal types: job health, freshness, SLA compliance, and anomaly counts.
• Guardrails: automatic consumer protections (e.g., pin dashboards, API versioning, quarantine flags) to avoid propagating bad data.
• Schedule periodic audit runs and synthetic data tests (golden datasets) to validate end‑to‑end pipelines.

Example incident walkthrough (short)

Alert: Freshness breach on analytics.orders, detected at 08:10. Owner reproduced SELECT MAX(order_time) and saw latest record 7 hours older than SLA. Severity scored 4 because BI reports and a churn model consume this table.

Immediate mitigation: incident owner restarted ingestion job, disabled a downstream daily report, and notified stakeholders. SRE found transient storage network error; pipeline retried and recovered. Postmortem: add retry metrics to monitoring, add an alert for storage IO errors, and document the backfill procedure.

When to escalate

• Severity >= 4 (critical/high) — escalate to on‑call SRE and senior analytics lead immediately.
• Any incident with regulatory or customer impact — escalate to ops leadership and compliance.
• If incident repeats more than twice within 7 days — declare a reliability initiative and allocate engineering time.

Runbook maintenance and ownership

Assign a runbook owner responsible for keeping checks, thresholds, and contact lists current. Review this runbook after each Severity ≥ 3 incident and at least quarterly.

Appendix: Useful snippets and templates

Sample SQL: null rate with threshold

SELECT
  (COUNT(*) FILTER (WHERE important_col IS NULL))::float / COUNT(*) AS null_rate
FROM schema.table
WHERE partition_date = CURRENT_DATE - INTERVAL '1 day';

Sample SQL: freshness

SELECT MAX(event_time) AS latest_event
FROM schema.table;

Sample SQL: row count drift

WITH todays AS (
  SELECT COUNT(*) AS cnt
  FROM schema.table
  WHERE partition_date = CURRENT_DATE - INTERVAL '1 day'
), baseline AS (
  SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY cnt) AS median_cnt
  FROM (
    SELECT COUNT(*) AS cnt
    FROM schema.table
    WHERE partition_date BETWEEN CURRENT_DATE - INTERVAL '8 day' AND CURRENT_DATE - INTERVAL '2 day'
    GROUP BY partition_date
  ) t
)
SELECT todays.cnt, baseline.median_cnt, (todays.cnt::float / NULLIF(baseline.median_cnt,0)) - 1 AS pct_change
FROM todays, baseline;

End of runbook.