Root Cause Analysis For Data Issues

Why this matters

As a BI Analyst, people rely on you when metrics look wrong. Root Cause Analysis (RCA) helps you go from “the number looks off” to a clear, verified answer plus a prevention plan. You’ll use RCA to:

• Explain sudden metric changes in dashboards (spikes, drops, flatlines).
• Uncover join duplication, stale pipelines, and broken business rules.
• Align definitions across teams to stop recurring issues.
• Protect trust in reports before leadership decisions are made.

Concept explained simply

RCA finds the underlying reason a data symptom exists. The symptom is what you see (e.g., revenue doubled). The root cause is the mechanism that produced it (e.g., a join change that doubled rows).

Mental model

Think of a “data factory” with 6S checkpoints:

• Signal: What exactly is wrong? Define the symptom precisely.
• Scope: When did it start, who/what is affected, and where does it not happen?
• Segment: Slice by time, source, country, product, environment.
• Source: Validate at the earliest reliable source (raw logs, source tables).
• SQL: Reproduce, isolate joins/filters/aggregations.
• Safeguard: Fix and prevent (tests, alerts, docs).

Step-by-step RCA workflow

1. Verify the symptom: Recompute the metric from source data. Confirm data freshness (load times, max timestamps, row counts).
2. Scope the impact: Identify first bad time, affected dashboards, and unaffected controls.
3. Segment to isolate: Split by dimension (country, product, channel). Look for where it breaks/works.
4. Trace upstream: Check model layers (staging → marts → dashboards). Compare counts and key metrics at each layer.
5. Inspect changes: Review recent changes to code, definitions, pipeline schedules, and source systems.
6. Prove the cause: Create a minimal, reproducible query demonstrating the break and the fix.
7. Fix and prevent: Implement the correction, add tests and monitors, and document the decision.

Worked examples

Diagnostic techniques and useful queries

-- Replace table/column names for your stack
SELECT MAX(event_time) AS last_event_time, COUNT(*) AS rows_today
FROM fact_events
WHERE event_date = CURRENT_DATE;

-- Compare base vs joined row counts
SELECT COUNT(*) AS order_rows, COUNT(DISTINCT o.order_id) AS distinct_orders
FROM orders o
LEFT JOIN order_items i ON o.order_id = i.order_id;

SELECT dt, SUM(metric) AS m
FROM mart_daily
GROUP BY dt
ORDER BY dt;
-- Look for the first date where m deviates sharply

SELECT country, dt, SUM(metric) AS m
FROM mart_daily
WHERE dt BETWEEN CURRENT_DATE - 14 AND CURRENT_DATE
GROUP BY country, dt
ORDER BY dt;

Exercises

Do these to practice. The same tasks appear in the exercise panel below.

Exercise 1: Diagnose doubled order counts

Scenario: The “Sales Summary” dashboard shows order_count doubled week-over-week.

Available tables: orders(order_id, created_at, customer_id), order_items(order_id, sku, quantity), customers(customer_id, country).

• Write diagnostic SQL to compare order counts by day using: (a) COUNT(*), (b) COUNT(DISTINCT order_id).
• Test the join between orders and order_items. Check if row counts explode.
• State the most likely root cause and a safe fix.

Exercise 2: Investigate a DAU drop

Scenario: DAU dropped by ~20% yesterday on the “Engagement” dashboard.

Available table: events(user_id, event_name, event_time, event_date).

• Check data freshness: max(event_time) for yesterday and today.
• Compare partition counts vs the prior weekday.
• Decide: delay vs. definition change vs. real user behavior.

Checklist: before declaring the root cause

• Reproduced the metric from source data.
• Confirmed data freshness and load status.
• Scoped the first bad time and unaffected controls.
• Segmented to isolate where it fails vs works.
• Compared row counts and distinct keys across model layers.
• Checked recent changes (code, schedule, definitions, vendors).
• Produced a minimal query that demonstrates the break and the fix.

Common mistakes and self-checks

• Stopping at the first plausible cause: Self-check: can you prove it with a minimal query?
• Ignoring freshness: Self-check: show last load time and row counts for the day.
• Confusing symptoms and causes: Self-check: ask “why” until you reach a changeable mechanism.
• Not validating at the source layer: Self-check: compare raw vs mart values.
• Fixing without preventing: Self-check: what test or monitor ensures it won’t recur?

Practical projects

• Build a “Data Health” dashboard: freshness, row counts, key dimension cardinality, and anomaly flags for top metrics.
• Create an RCA playbook: a template with steps, queries, owners, and a prevention checklist.
• Join-safety toolkit: macros or snippets to check multiplicity, COUNT vs COUNT(DISTINCT), and pre-aggregations.

Who this is for

• BI Analysts who own dashboards and stakeholder trust.
• Analytics Engineers and Data Analysts supporting reporting models.

Prerequisites

• Comfort with SQL (joins, aggregations, window functions).
• Basic understanding of your data pipeline layers.
• Knowledge of business metric definitions (e.g., order, DAU, conversion).

Learning path

• Start: Freshness checks → Row count continuity → Segment comparisons.
• Intermediate: Join safety, dimensional consistency, reproducible RCA notes.
• Advanced: Automated monitors, anomaly detection thresholds, SLA/alerting.

Next steps

• Apply the workflow to one live dashboard this week.
• Add at least two monitors (freshness and join multiplicity) to a critical model.
• Document one RCA with cause, fix, and prevention steps.

Mini challenge

A marketing dashboard shows “Leads” stable, but “Qualified Leads” drop 30% starting Monday. No outages reported. In 15 minutes, outline your RCA steps, which segments you’ll test, and one query to validate the definition.

Quick Test

Take the quick test to check your understanding. Anyone can take it for free; progress is saved only for logged-in users.