Root Cause Analysis

When data quality issues emerge, the clock is ticking. Business decisions are being made on bad data, reports are showing incorrect numbers, and stakeholders are losing trust. Root cause analysis using data lineage helps you trace problems upstream to their source—fast.

The Challenge of Data Quality Issues

"We discovered our revenue was underreported by $2.3M. It took us two weeks to find the root cause: a silent truncation in a string-to-number conversion five steps upstream." — Finance Director

Without lineage, troubleshooting data issues is like debugging with no stack trace. You know something's wrong, but you have to manually investigate every transformation, join, and filter until you find the culprit.

Root Cause Analysis Process

Step 1: Identify the Symptom

Clearly define what's wrong:

• Location: Where is the problem visible?
• Nature: What's incorrect? (values, counts, missing data)
• Scope: All records or specific subset?
• Timeline: When did it start?

Issue Detected:
  Table: analytics.monthly_revenue
  Column: total_revenue
  Problem: Values 30% lower than expected
  Timeframe: Started December 19, 2025
  Affected Records: All records since that date

Step 2: Query Upstream Lineage

Get the full lineage path back to sources:

-- Get upstream lineage for the affected asset
WITH RECURSIVE lineage_path AS (
  SELECT
    table_name,
    column_name,
    upstream_table,
    upstream_column,
    transformation_logic,
    1 as depth
  FROM lineage_metadata
  WHERE table_name = 'analytics.monthly_revenue'
    AND column_name = 'total_revenue'

  UNION ALL

  SELECT
    l.table_name,
    l.column_name,
    l.upstream_table,
    l.upstream_column,
    l.transformation_logic,
    lp.depth + 1
  FROM lineage_metadata l
  JOIN lineage_path lp
    ON l.table_name = lp.upstream_table
  WHERE lp.depth < 10
)
SELECT * FROM lineage_path
ORDER BY depth DESC;

Step 3: Binary Search Through Lineage

Start in the middle of the lineage path and work backward:

Lineage Chain (depth 5 to depth 1):
  ✓ analytics.monthly_revenue      [ISSUE HERE]
  ? staging.aggregated_sales        [TEST THIS]
  ? staging.cleaned_transactions
  ? staging.raw_transactions
  ? external.payment_api

Testing staging.aggregated_sales:
  SELECT SUM(revenue) FROM staging.aggregated_sales
  WHERE date >= '2025-12-19';

Result: Values look correct here
Conclusion: Issue is in analytics.monthly_revenue generation

Testing transformation logic:
  Found: Filter condition added on Dec 19
  WHERE transaction_status = 'completed'

Root Cause Found:
  New filter excludes 'pending' transactions
  Pending transactions are ~30% of volume
  This matches the observed discrepancy!

Step 4: Verify the Root Cause

Confirm your hypothesis:

• Check transformation change history
• Review git commits for that date
• Compare data before/after the change
• Validate against known-good baseline

Step 5: Assess Blast Radius

Use forward lineage to find what else is affected:

Downstream Impact:
  ⚠ dashboards.executive_summary (incorrect revenue)
  ⚠ reports.board_presentation (wrong numbers)
  ⚠ ml_models.revenue_forecast (trained on bad data)
  ⚠ finance.quarterly_report (compliance risk!)

Common Data Quality Patterns

Pattern 1: Silent Truncation

Symptom: Unexpectedly short strings or rounded numbers
Cause: Data type mismatch in transformation
Lineage Search: Look for type conversions (VARCHAR(50) → VARCHAR(20))

Pattern 2: Missing Records

Symptom: Row counts lower than expected
Cause: Overly restrictive filter or failed join
Lineage Search: Check WHERE clauses and JOIN conditions

Pattern 3: Duplicate Records

Symptom: Row counts higher than expected
Cause: Missing DISTINCT or bad join
Lineage Search: Look for many-to-many joins

Pattern 4: Stale Data

Symptom: Data hasn't updated recently
Cause: Upstream pipeline failure
Lineage Search: Check job execution timestamps

Pattern 5: Wrong Aggregation

Symptom: Totals don't match expected values
Cause: Incorrect GROUP BY or SUM logic
Lineage Search: Trace aggregation transformations

Automated Root Cause Detection

import wia_lineage

# Detect anomaly
anomaly = {
    'table': 'analytics.monthly_revenue',
    'column': 'total_revenue',
    'expected': 1000000,
    'actual': 700000,
    'date': '2025-12-19'
}

# Run automated root cause analysis
rca = wia_lineage.analyze_root_cause(anomaly)

# Results:
print(rca.summary())
"""
Root Cause Analysis Results:
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Confidence: 87%
Root Cause: Filter condition change
Location: etl/monthly_revenue.sql:45
Changed: 2025-12-19 08:34:12
Changed By: john.doe@company.com
Git Commit: abc123

Recommendation:
  Remove WHERE transaction_status = 'completed'
  OR include 'pending' transactions in revenue
  Estimated fix time: 15 minutes
  Backfill required: Yes (19 days)
"""

Integration with Observability

Data Quality Checks + Lineage

Combine automated data quality monitoring with lineage:

# Data quality test
assert daily_revenue > 0.8 * expected_revenue

# On failure:
if test_failed:
    lineage = get_upstream_lineage('analytics.daily_revenue')
    for node in lineage:
        run_validation(node)
        if node.validation_failed:
            alert(f"Root cause found: {node}")
            break

Alerting with Context

Include lineage in alerts:

ALERT: Revenue data quality issue

Table: analytics.monthly_revenue
Issue: 30% lower than baseline
Started: 2025-12-19

Lineage Path:
  analytics.monthly_revenue
    ← staging.aggregated_sales
      ← staging.cleaned_transactions
        ← staging.raw_transactions ⚠ SUSPECT
          ← external.payment_api

Recommended Action:
  Investigate staging.raw_transactions
  Check for filter/join changes on 2025-12-19

Preventing Future Issues

1. Pre-Deployment Testing: Use lineage to identify test coverage gaps
2. Data Contracts: Define expectations at each transformation
3. Change Review: Require lineage impact analysis for modifications
4. Continuous Validation: Monitor data quality at every lineage node
5. Automated Rollback: Revert changes when quality degrades

Case Study: E-commerce Platform

Best Practices

1. Document Changes: Annotate transformations with change history
2. Test at Each Node: Don't just test the final output
3. Maintain Baselines: Keep known-good data for comparison
4. Automate What You Can: But keep humans in the loop
5. Share Findings: Build organizational knowledge