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:

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:

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:

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.