Root Cause Analysis For Load Issues

Who this is for

ETL Developers, data engineers, and analysts who investigate failed or degraded data loads and need a reliable way to find and fix the true cause.

Prerequisites

• Basic SQL (SELECT, WHERE, GROUP BY)
• Familiarity with batch or streaming jobs and logs
• Understanding of schemas, partitions, and incremental/watermark loads

Why this matters

Root Cause Analysis (RCA) prevents repeat incidents and data quality regressions. In real ETL work you will:

• Diagnose failed jobs and pinpoint whether the issue is data, code, config, schedule, or infrastructure.
• Avoid downstream data corruption by quickly separating symptoms from causes.
• Create permanent fixes and guardrails (tests, alerts, contracts) that keep pipelines reliable.

Concept explained simply

RCA is a structured way to ask: What exactly broke, where did it start, and why did it happen? Instead of jumping to fixes, you collect evidence and narrow down.

Mental model

Think of your pipeline as a relay race across stages: source → ingest → land → transform → load. A slip at any stage leaves fingerprints: counts drift, schema shifts, time windows gap, or error codes appear. You follow the fingerprints backward until you find the first deviation. Fix that first deviation, not just the last crash.

A simple, repeatable RCA workflow

1. Stabilize and scope
   • Freeze changes; capture logs, metrics, inputs.
   • Define blast radius: which tables/partitions/time windows are affected.
2. Classify the failure
   • Data issue (null spike, schema drift, duplicates)
   • Code/config issue (bad mapping, wrong watermark)
   • Infra/schedule issue (timeouts, contention, overlap)
3. Narrow with contrasts
   • Compare last good run vs failed run (inputs, parameters, counts).
   • Binary search the pipeline: find first stage where outputs differ.
4. Validate the hypothesis
   • Run a minimal replay on a tiny slice.
   • Check one alternative explanation before concluding.
5. Fix and prevent
   • Patch the immediate issue.
   • Add a guardrail: test, contract, alert, idempotency, retry, or ordering rule.

Worked examples

Example 1: Foreign key failures after a seemingly normal run

Symptoms: Load to fact table fails with missing dimension keys. Yesterday was fine.

Findings: Fact has 1.2M rows today; dim has only 0.9M (historically 1.2M). Upstream dimension job finished late; fact ran first.

Root cause: Scheduling/order issue causing late-arriving dimension data.

Fix now: Rerun fact after dim completes; enforce dependency.

Prevention: DAG dependency, freshness check on dim before fact; alert if dim row count is below baseline.

Example 2: Parser error after source change

Symptoms: CSV ingest fails with "Too many columns".

Findings: Source added a new optional column at end; schema registry/mapper not updated.

Root cause: Uncoordinated schema change (schema drift).

Fix now: Add tolerant parser or update mapping to include new column.

Prevention: Schema contract, versioned mappings, alert on header/field count change.

Example 3: Incremental gap due to watermark misconfig

Symptoms: Daily run completes but missing rows for 02:00-03:00.

Findings: Watermark uses job end time; late events arrive after window closed.

Root cause: Watermark advanced too aggressively.

Fix now: Backfill the gap.

Prevention: Use event-time watermark with lag, or overlap windows and deduplicate by id.

Diagnostic quick references

Exercises (hands-on)

Do these before the test. Keep notes of your reasoning.

Exercise 1: Missing keys in fact table

Log snippet:

2025-08-02 03:12:01 ERROR FK violation on fact_sales.customer_key
Expected keys: 1,200,000; Found in dim_customers: 900,453
Job order: dim_customers 03:15 finish; fact_sales 03:10 start

Task: State the most likely root cause, immediate fix, and a prevention step.

Exercise 2: Schema drift on ingest

Yesterday header: id,name,amount

Today header: id,name,amount,currency

Error: Parser "expected 3 fields, found 4".

Task: Name the root cause, one safe hotfix, and one long-term prevention.

Self-checklist before you conclude RCA

• Did you verify the first point where outputs deviated?
• Did a minimal replay confirm the hypothesis?
• Did you consider at least one alternative cause?
• Is the fix idempotent and safe to re-run?
• Did you add a guardrail (test/alert/contract)?

Common mistakes and how to self-check

• Fixing the last error, not the first deviation. Self-check: Trace upstream to where counts first diverge.
• Ignoring schedule overlap. Self-check: Inspect run times and resource contention.
• Assuming schema stability. Self-check: Diff headers and field types each run.
• Skipping minimal reproduction. Self-check: Re-run a tiny slice to validate cause.
• No prevention step. Self-check: Add at least one alert or test before closing.

Practical projects

• Build a small pipeline with two dependent jobs; intentionally reorder them to trigger a failure; add a pre-check that blocks the dependent job until upstream row count baseline is met.
• Create a schema drift detector that compares current headers to yesterday and writes a warning record with the diff.
• Implement watermark-based incremental load with a 2-hour lag and idempotent upsert; simulate late data and prove no gaps after rerun.

Learning path

• Start: Understand pipeline stages and typical failure modes.
• Practice: Use the workflow and exercises here to diagnose sample incidents.
• Advance: Add automated data quality checks, schema contracts, and idempotent patterns.

Quick Test

The quick test is available to everyone. If you are logged in, your progress is saved automatically.

When ready, open the test below and check your understanding.

Mini challenge

Given a daily pipeline where counts drop by 15% without errors, list three hypotheses and one minimal experiment for each to validate quickly.

Next steps

• Turn today’s exercise outputs into runbooks: step-by-step RCA with commands and checks.
• Add at least one automated guardrail to a real pipeline.
• Take the quick test below and then move to the next subskill.