Turning Incident Knowledge into Automated Tests

Executive Summary

Data engineering teams currently lose an estimated 10 hours per quarter investigating data quality incidents that have happend in the past. These incidents get documented in postmortem analysis, but that knowledge rarely is implemented in the code to prevent future similar data incidents, 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: Knowledge forgotten

Here’s what typically happens after an incident.

Reactive fixes are prioritized over durable prevention. The postmortem which actually contains the business logic needed to prvent future incidents gets archived instead of being turned into enforced code. That’s an inefficient loop: the same class of incident can happen months later, and the team has to investigate again the problem, taking a look at previous documentation, but its not encoded as a guardrail.

2. The Solution: An 8-Step Automated Workflow

The proposed 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.”

The two steps worth calling out: Step 5 (PII scanning) exists specifically to satisfy InfoSec before anything is sended to an external API, and Step 7 (human-in-the-loop review) is needed, no LLM-generated test is merged without explicit validation 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

-- 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.

🔗 Links & Resources

• 💻 Source Code: View this project on GitHub