Root Cause Analysis And Postmortems

Why this matters

As a Data Platform Engineer, you will be paged when metrics look wrong, pipelines fail, or dashboards drift. Root Cause Analysis (RCA) and postmortems help you resolve issues faster, prevent repeats, and build trust with stakeholders.

• Diagnose spikes in nulls or duplicates after schema changes.
• Explain why a daily pipeline arrived late and how to prevent it.
• Coordinate fixes across ingestion, transformation, and serving layers.
• Write clear postmortems that lead to durable improvements, not blame.

Concept explained simply

Root Cause Analysis is a structured way to move from a symptom (what you see) to the underlying mechanism (why it happened). A blameless postmortem is a written reflection that captures the timeline, contributing factors, decisions under uncertainty, and concrete actions to reduce recurrence.

Mental model

Think of an incident as a chain:

1. Detect: Alert or user report.
2. Stabilize: Stop the bleeding (mute downstream jobs, roll back, pause backfills).
3. Diagnose: Form and test hypotheses using logs, lineage, diffs, and checks.
4. Fix: Apply the smallest safe change to restore correctness.
5. Learn: Document timeline, decisions, and gaps.
6. Guard: Add tests, alerts, runbooks, and process changes.

Use a simple loop: Observe → Hypothesize → Test → Conclude. If evidence contradicts, discard the hypothesis decisively.

RCA toolkit you can use today

1. Make the timeline: List times for detection, first page, mitigation, fix, and recovery. Add what changed in the last 24–72 hours (code, config, data sources, schedules).
2. Classify the symptom: Missing data, wrong values, late arrival, duplication, drift, or schema break.
3. Generate hypotheses: Use 5 Whys or a fishbone across categories: Data, Code, Config, Infra, Schedule, Upstream provider, Human process.
4. Test fast: Diff inputs/outputs, run subset replays, check lineage, compare good vs bad partitions, inspect sample events.
5. Decide and fix: Choose the minimal safe action (rollback, hotfix, reprocess, reconfigure).
6. Write the postmortem (blameless): Timeline, impact, root cause, contributing factors, what helped/hurt detection, actions with owners and dates.

Title: [YYYY-MM-DD] [Short summary]
Impact: [Who/what was affected, duration, severity]
Timeline: [Detection -> Mitigation -> Recovery]
Root Cause: [Causal chain; smallest change preventing recurrence]
Contributing Factors: [List]
What Went Well: [List]
What Was Painful: [List]
Actions:
  - Corrective: [owner, due]
  - Preventive: [owner, due]
Verification: [How we know it's fixed]
Follow-up Review Date: [date]

Worked examples

Example 1: Metric drop after timezone change

• Symptom: Daily revenue down 28% for 2026-01-08.
• Recent change: Warehouse session timezone changed from UTC to UTC-5.
• Tests:

• Compare raw events count by hour across UTC and UTC-5.
• Recompute the day with explicit UTC window.
• Check Airflow execution time vs partition boundaries.

Findings: Windowing used "current_date()" in warehouse session TZ; late-night events fell into next day. Root cause: Implicit timezone-dependent window in aggregation. Fix: Use event_timestamp with explicit UTC boundaries. Guardrails: Add unit test for boundary hours; lint rule banning session-local dates in prod.

Example 2: Null surge after schema change

• Symptom: user_id nulls from 0.2% to 18% in user_sessions table.
• Recent change: Upstream API added user_id as optional when session from guest.
• Tests:

• Sample rows with null user_id and inspect raw payloads.
• Check ingestion mapping for missing field defaults.
• Trace lineage to transformations casting user_id.

Findings: Ingestion defaulted missing user_id to null; downstream join expected non-null, dropping rows. Root cause: Contract change not captured in schema registry and model assumptions. Fix: Map guest sessions with derived synthetic user_key, adjust joins. Guardrails: Schema evolution alert; contract test enforcing non-breaking changes.

Example 3: Late pipeline due to backfill

• Symptom: Daily pipeline finished 3 hours late.
• Recent change: Backfilled 60 days on same cluster with default priority.
• Tests:

• Check cluster concurrency and queue times.
• Compare resource usage vs SLO windows.
• Simulate with smaller backfill batch size.

Findings: Backfill jobs starved daily run. Root cause: Resource contention from non-prod backfill lacking quotas/priority. Fix: Throttle backfill, set lower priority queue. Guardrails: Backfill playbook, quotas, SLO-aware scheduling, canary window before peak.

Exercises you can do now

These mirror the exercises below. Aim for concise, testable conclusions.

1. Exercise 1: You see a spike in duplicate orders in fact_orders for 2026-01-09. Write a timeline, propose 3 hypotheses, design 3 quick tests, and produce a root cause statement plus two actions.
2. Exercise 2: A dbt model failed with "column not found: total_price" after a PR merge. Diagnose the cause and propose preventive guardrails.

Common mistakes and self-check

• Blame instead of mechanism: Replace "engineer forgot" with the system condition that allowed forgetting to cause impact (missing check, unclear contract).
• Stopping at the trigger: A deploy is not a root cause. Ask why the deploy introduced the fault and why it wasn’t caught.
• Vague actions: "Be careful" is not an action. Prefer tests, alerts, automation, or documented gates.
• Overfitting to this incident: Add guardrails that generalize (schema evolution checks, end-to-end data contracts).
• Skipping verification: Define how you’ll know the fix works (query, metric, canary window).

Practical projects

• Build a runbook: Create a 1-page runbook for "Late daily job" with steps to stabilize, diagnose, and fix. Include sample queries and contact points.
• Synthetic corruption drill: Inject nulls or out-of-range values into a staging table and practice detection and rollback.
• Schema change game day: Simulate an upstream breaking change; practice contract testing and migration steps.
• Alert hygiene: Tune one alert to reduce noise by 30% while preserving detection using precision/recall on historical incidents.

Learning path

1. Foundations: Practice timelines, 5 Whys, and hypothesis testing.
2. Tooling: Learn your lineage tool, query logs, and job orchestration metadata.
3. Writing: Produce 2 blameless postmortems using the template.
4. Automation: Add one contract test, one data quality check, and one SLO alert.
5. Scale: Introduce backfill playbooks, canary runs, and priority queues.

Who this is for and prerequisites

Who

• Data Platform Engineers responsible for pipelines, models, and warehouse reliability.

Prerequisites

• Ability to query data with SQL.
• Basic familiarity with your scheduler (e.g., Airflow), warehouse, and version control.
• Some exposure to data quality checks and alerting.

Mini challenge

A partner feed switched CSV delimiter from comma to semicolon at 02:00 UTC. By 06:00 UTC, downstream joins lost 15% of rows. In 10 minutes, outline: timeline, 3 hypotheses, the fastest test, and one corrective plus one preventive action. Keep it to 8 bullet points.

Next steps

• Pick one recent incident and write a postmortem using the template.
• Add one guardrail (contract test, alert, or automated rollback) this week.
• Take the quick test to check your understanding. Everyone can take it for free; only logged-in users get saved progress.

Quick Test

Ready? Scroll to the Quick Test and aim for 70% or higher. If you don’t pass, revisit the exercises above.