# Root Cause Analysis Without the War Room

## 1\. The Problem

A pipeline fails at 2 AM. The on-call engineer opens the job logs, sees a generic Spark exception, and starts the ritual: check the source table, check the schema, check the upstream job that feeds it, check whether someone touched the ADF trigger, check Slack for any "quick change" nobody documented. Forty-five minutes later, the cause turns out to be a column rename three hops upstream, in a table this pipeline doesn't even directly reference but depends on transitively.

None of that forty-five minutes was wasted effort exactly — it was necessary detective work. But it was *manual* detective work, repeated by a different engineer, slightly differently, every time a pipeline breaks. The knowledge of "how we traced the last five incidents" rarely survives past the postmortem doc, if a postmortem doc even gets written.

Three things make root cause analysis (RCA) structurally hard at scale:

*   **Lineage complexity.** A single Gold-layer table might sit six or seven transformations downstream of a dozen source systems. Tracing "what changed upstream" by hand means walking a graph humans were never built to hold in working memory.
    
*   **Unstructured logs.** Spark stack traces, ADF activity logs, and Databricks job-run history are text-heavy, inconsistent in format, and scattered across systems. Correlating "this job failed" with "that upstream job also had an unusual run at the same time" requires cross-referencing timestamps across sources that don't share a common schema.
    
*   **Institutional memory gaps.** The senior engineer who instinctively knows "when Table X looks weird, check the CRM sync job first" is a single point of failure. When they're on leave, in a different time zone, or have left the team, that pattern-matching disappears with them.
    

The war room — pulling in five engineers to stare at dashboards and logs together — is not a process. It's an admission that no single person, and no tooling, can trace the failure alone.

## 2\. The AI Opportunity

RCA is a search-and-correlate problem before it's anything else: given a failure, search the lineage graph for what it depends on, search the logs and metadata for what changed in a relevant time window, and correlate the two. That search-and-correlate step is exactly what AI is well-suited to accelerate — not to replace the engineer's judgment on what the failure *means*, but to compress the time spent gathering the evidence the judgment is applied to.

Two capabilities matter here, and they do different jobs:

**Lineage-graph traversal** answers "what could have caused this" by mechanically walking upstream dependencies from the failed asset — tables, jobs, notebooks — and surfacing what else touches the same objects. This is deterministic graph work. No LLM required, no hallucination risk. It's the evidentiary backbone.

**LLM-assisted synthesis** answers "given this evidence, what's the most likely story" by taking the structured output of the lineage walk plus a bundle of correlated log excerpts and schema-change events, and producing a ranked set of hypotheses in plain language — the kind of narrative a senior engineer would sketch on a whiteboard, but generated in seconds and grounded in the specific evidence retrieved, not general knowledge.

The important architectural point: the LLM never gets to invent the lineage or the log content. It only synthesizes *from* structured evidence that a deterministic system already retrieved. This is the difference between an RCA assistant and an RCA hallucination generator.

*Karta* — the doer — is the engineer who used to manually walk every log and every lineage edge. *Sakshi* — the witness — is the engineer who now reviews a pre-assembled evidence bundle and a ranked hypothesis list, and applies judgment to accept, reject, or redirect. The investigation still requires a human. What changes is where the forty-five minutes goes: from gathering evidence to evaluating it.

## 3\. Implementation Sketch

A practical shape on Azure + Databricks + Unity Catalog: three stages — evidence retrieval, evidence bundling, and LLM-assisted synthesis — kept strictly separate so the LLM only ever reasons over facts you've already retrieved.

**Stage 1 — Lineage retrieval.** Unity Catalog's system tables expose table- and column-level lineage. Walk upstream from the failed asset within a bounded time window around the failure:

```python
from pyspark.sql import functions as F

failed_table = "prod.sales.gold_daily_revenue"
failure_ts = "2026-07-28T02:14:00Z"
lookback_hours = 24

lineage_df = spark.table("system.access.table_lineage")

upstream = (
    lineage_df
    .filter(F.col("target_table_full_name") == failed_table)
    .filter(F.col("event_time").between(
        F.to_timestamp(F.lit(failure_ts)) - F.expr(f"INTERVAL {lookback_hours} HOURS"),
        F.to_timestamp(F.lit(failure_ts))
    ))
    .select("source_table_full_name", "source_type", "event_time")
    .distinct()
)
```

**Stage 2 — Correlated event bundling.** For each upstream table surfaced, pull job-run history and any schema-change events in the same window — the goal is a compact, structured "evidence bundle," not raw logs:

```python
job_runs = spark.table("system.lakeflow.job_run_timeline")

correlated_runs = (
    job_runs
    .join(upstream, job_runs.table_full_name == upstream.source_table_full_name, "inner")
    .filter(job_runs.period_start_time.between(
        F.to_timestamp(F.lit(failure_ts)) - F.expr(f"INTERVAL {lookback_hours} HOURS"),
        F.to_timestamp(F.lit(failure_ts))
    ))
    .select("table_full_name", "run_id", "result_state",
            "period_start_time", "period_end_time")
)

evidence_bundle = {
    "failed_asset": failed_table,
    "failure_time": failure_ts,
    "upstream_assets": [row.source_table_full_name for row in upstream.collect()],
    "correlated_job_runs": [row.asDict() for row in correlated_runs.collect()],
    "error_excerpt": get_error_excerpt(failed_table, failure_ts, max_chars=800),
}
```

`get_error_excerpt` is a custom helper, not a native PySpark or system-table function — you write it to pull from wherever your job-run error output actually lands (Databricks job-run API, `system.lakeflow.job_run_timeline` error fields, or your logging sink), truncate it, and hand back a plain string.

Note the `error_excerpt` cap and the absence of raw row-level data anywhere in the bundle — only table names, job metadata, and a bounded error message make it into what eventually reaches the LLM.

**Stage 3 — LLM-assisted synthesis.** Pass the structured bundle — never raw data, never full logs — to the model with a prompt constrained to reason only over what's supplied:

```python
system_prompt = """You are assisting a data engineer with root cause analysis.
You will be given a structured evidence bundle: a failed table, upstream
dependencies, correlated job-run outcomes, and an error excerpt.

Rules:
- Base every hypothesis strictly on the evidence provided. Do not assume
  facts not present in the bundle.
- Produce a ranked list of up to 3 hypotheses with a confidence label
  (high / medium / low) and the specific evidence supporting each.
- For each hypothesis, state one concrete next diagnostic step the
  engineer should take to confirm or rule it out.
- If the evidence is insufficient to form a hypothesis, say so explicitly
  rather than speculating."""

response = call_llm(system_prompt=system_prompt, user_content=json.dumps(evidence_bundle))
```

The output is a starting hypothesis list with pointers back to evidence — not a verdict. It gets logged alongside the incident and reviewed by the engineer before any action is taken.

## 4\. Limitations & Risks

**Hallucination in the synthesis step.** Even constrained to a structured bundle, an LLM can still overstate confidence or invent plausible-sounding connections between unrelated events. This is *maya* again — the same appearance-mistaken-for-substance risk this series keeps returning to, because it keeps showing up wherever an LLM sits between evidence and a decision. The constraint isn't optional: every hypothesis needs a "confirm or rule out" step attached, and no hypothesis should move to remediation without that confirmation.

**Lineage completeness gaps.** Unity Catalog lineage only captures what runs through Unity Catalog–governed compute. Jobs that read or write outside that boundary — legacy ADF activities hitting non-UC storage, external ETL tools — create blind spots in the graph. A confident-looking lineage walk that's silently incomplete is worse than an honest "lineage unavailable beyond this point."

**Correlation is not causation.** Two upstream jobs running near the failure time doesn't mean either caused it. The LLM synthesis step can make coincidental timing look like a causal narrative if the prompt and evidence bundle aren't explicit about what "correlated" means. Confidence labels help, but they don't eliminate the risk — engineer review is the actual safeguard, not a formality.

**Data exposure through error excerpts.** Error messages and stack traces can contain fragments of actual data values — a failed row, a bad key, a customer identifier embedded in an exception message. Sending that verbatim to an external LLM API is a PII exposure path that's easy to miss because it doesn't look like "sending data" the way a table export does.

**Cost and latency at incident volume.** A single evidence bundle plus LLM call is cheap. Hundreds of failures a week, each triggering lineage walks and LLM synthesis, is not free — and if RCA assist becomes another noisy layer that fires on every transient job retry, it adds cost without adding signal.

## 5\. How to Overcome

**Redact before synthesis, not after.** Run error excerpts through a redaction pass — pattern-matching known PII formats, masking literal values in exception messages — before they enter the evidence bundle. Treat this as a mandatory pipeline stage, not a best-effort filter.

**Scope lineage claims honestly.** Have the retrieval stage explicitly flag when it hits the edge of Unity Catalog's visibility, and pass that flag into the evidence bundle so the LLM prompt — and the engineer — knows the lineage picture is partial, not complete.

**Require evidence citations in every hypothesis.** Enforce, at the prompt level, that each hypothesis names the specific evidence field it draws from. A hypothesis with no traceable evidence pointer gets discarded before it reaches the engineer, not treated as a lower-confidence option.

**Gate on severity, not on every failure.** Trigger the full RCA-assist pipeline only for failures above a severity threshold — SLA-critical tables, repeated failures on the same asset, failures affecting downstream reporting. Transient retries and known-flaky jobs don't need lineage walks and LLM calls.

**Close the loop with a feedback table.** Log whether each engineer confirmed, rejected, or modified the top hypothesis. Review this monthly. If confirmed-hypothesis rate drops, that's a signal to revisit the prompt, the evidence bundle composition, or the lineage retrieval scope — not to quietly stop trusting the tool.

## 6\. The Takeaway

**For engineers:** Next incident you trace manually, write down the actual evidence trail you followed — which tables you checked, in what order, what made you rule things in or out. That trail is the specification for what an RCA-assist bundle needs to contain. You cannot automate a search you haven't first done deliberately by hand.

**For leads:** Ask your team how postmortem knowledge currently gets reused — is it a doc nobody rereads, or a live system that makes the next incident faster to trace? RCA-assist tooling is only worth building if the evidence-gathering step is genuinely the bottleneck, not the judgment step. Measure time-to-root-cause before and after, not just adoption.

* * *

> *AI doesn't eliminate engineering judgment — it demands better judgment, faster.*

* * *

*Next: Article 5 — Metadata Intelligence: Making Your Catalog Work for You. Turning Unity Catalog from a static inventory into an actively maintained knowledge base with LLM-assisted description generation and PII classification.*

* * *

*Karthik Darbha is a Senior Data Engineering & AI Leader with 23 years of professional experience, including 20+ years building enterprise data platforms across Healthcare, Pharma, Retail, Insurance, and Financial Services. He writes about data engineering, program management, and the intersection of technology and philosophy at* [*tech4nirvana.com*](https://tech4nirvana.com/)*.*