Turning Incident Knowledge into Automated Tests

Executive Summary

Data engineering teams currently lose an estimated 10 hours per quarter re-investigating recurring data quality incidents. These incidents get documented in postmortems, but that knowledge rarely makes it back into the codebase as a preventive safeguard. This proposal outlines a solution that uses an LLM to automatically translate postmortem documentation into dbt tests, closing the gap between what a team learns from an incident and what it actually enforces in code.

🎯 The ROI

Reduces incident remediation time by 75% and increases test coverage from incidents by 3x.

1. The Problem: Where Knowledge Goes to Die

Here’s what typically happens after an incident today. Notice where the process quietly breaks down.

flowchart TD
    A[Data Quality Incident Occurs] --> B[Team Investigates & Applies Reactive Fix]
    B --> C[Postmortem Written]
    C --> D[Postmortem Archived in Wiki Page]
    D -.->|"KNOWLEDGE GAP"| E[No Test Created / No Enforcement]
    E --> F[Business Logic Lives Only in Human Memory]
    F -.-> A

    style D fill:#ff4d4f,stroke:#a8071a,stroke-width:2px,color:#fff
    style E fill:#ff4d4f,stroke:#a8071a,stroke-width:2px,color:#fff

Reactive fixes are prioritized over durable prevention. The postmortem — which actually contains the business logic needed to stop recurrence — gets filed away instead of being turned into enforced code. That’s an inefficient loop: the same class of incident can resurface months later, and the team re-investigates from scratch because nothing was ever encoded as a guardrail.

2. The Solution: An 8-Step Automated Workflow

The proposed “To-Be” process closes that loop by inserting an LLM-powered translation step between “we understand what went wrong” and “the system now prevents it from happening again.”

flowchart TD
    S1[1. Incident Occurs & Is Resolved] --> S2[2. Postmortem Written Using Structured Template]
    S2 --> S3[3. Postmortem Stored in Confluence / Notion]
    S3 --> S4[4. LLM Tool Ingests Postmortem + Latest dbt manifest.json / catalog.json]
    S4 --> S5[5. PII Scan & Anonymization Pre-Processing]
    S5 --> S6[6. LLM Generates dbt SQL Test + YAML Config]
    S6 --> S7[7. Human-in-the-Loop Review — Senior Engineer Approves / Modifies / Rejects]
    S7 --> S8[8. Test Merged & Runs on Every dbt Execution]

    style S7 fill:#1890ff,stroke:#0050b3,stroke-width:2px,color:#fff
    style S8 fill:#52c41a,stroke:#237804,stroke-width:2px,color:#fff

The two steps worth calling out: Step 5 (PII scanning) exists specifically to satisfy InfoSec before anything touches an external API, and Step 7 (human-in-the-loop review) is non-negotiable — no LLM-generated test is merged without explicit sign-off from a senior engineer.

Scope for v1:

• ✅ In scope: dbt projects with SQL-based tests, postmortems written in English, Confluence/Notion as the source system
• ❌ Out of scope: non-dbt data projects, non-English postmortems, automatic PR creation (v1 generates the test; a human still opens the PR)

3. The Artifacts

quadrantChart
    title Stakeholder Power / Interest Grid
    x-axis Low Interest --> High Interest
    y-axis Low Power --> High Power
    quadrant-1 Manage Closely
    quadrant-2 Keep Satisfied
    quadrant-3 Monitor
    quadrant-4 Keep Informed
    Data Quality Lead: [0.85, 0.85]
    Engineering Manager: [0.55, 0.85]
    InfoSec: [0.45, 0.85]
    Data Engineers: [0.8, 0.55]
    Downstream Consumers: [0.85, 0.2]

-- test: assert_shipped_orders_have_timestamp
SELECT order_id
FROM {{ ref('orders') }}
WHERE status = 'shipped'
  AND shipped_at IS NULL

4. Conclusion & Next Steps

This tool exists to help data and analytics engineers improve data quality by making it easier to implement prevention tests. It bridges the gap between knowledge that’s already been documented and the implementations or improvements that should follow from it. It isn’t meant to replace human judgment — it’s meant to make sure lessons learned from incidents actually turn into prevention, not just documentation.

As data teams mature, the focus shifts from reactive incident response to proactive quality assurance. Tools that operationalize incident knowledge are a step in that direction.

5. BA Reflection

• Managing stakeholder expectations around AI hallucination is just as important as the technical implementation. InfoSec and the Data Quality Lead weren’t worried about whether the LLM could generate a test — they were worried about what happens when it generates a wrong one. Designing the Human-in-the-Loop step wasn’t a technical afterthought; it was the thing that got the project approved.
• A gap analysis is only useful if it separates process, technology, and people. It would have been easy to frame this as “we need better tooling,” but the real blocker was partly organizational: engineering bandwidth is structurally prioritized toward features over test debt. No tool fixes that on its own — the SOP change had to be part of the recommendation.
• The most convincing artifact wasn’t the diagram, it was the SQL. Talking about “closing the knowledge-to-code gap” is abstract. Showing the actual shipped_at IS NULL test generated from a real incident made the value concrete for engineers and tech leads in a way that no process map could.