Building an Agentic Workflow for Automated Data Quality Debugging

The Problem: Manual Data Quality Debugging

Data quality failures are inevitable in modern data warehouses. A uniqueness test fails at 3 AM. A null constraint is violated in production. A relationship test breaks downstream pipelines. The traditional response? A data engineer wakes up, starts investigating, and begins a manual process that can take hours.

Tools like dbt have made data testing straightforward. You define tests in YAML, run them, and get pass/fail results. But when a test fails, the real work begins:

1. Understanding the failure — Which table? Which column? What constraint was violated?
2. Finding the data — Query the warehouse to see the actual failing records.
3. Tracing lineage — Where did the bad data come from? Which upstream table?
4. Multi-level investigation — Often the root cause isn't in the immediate upstream table, but 2–3 hops back.
5. Root cause analysis — Logic bug? Source data issue? Join explosion?
6. Solution design — Deduplication? Null handling? Join refinement?
7. Implementation — Modify SQL, test locally, verify the fix.
8. Documentation — Record findings so knowledge isn't lost.

This process is time-consuming (several hours per investigation), context-heavy (requires understanding data models, business logic, and SQL), error-prone (easy to miss upstream causes), and knowledge-siloed (investigation insights live in someone's head or scattered across Slack threads).

The Agentic Solution

We built a multi-tool agentic workflow, grounded in the ReACT (Reasoning + Acting) pattern, that handles dbt test failure investigation end to end. Given a failing test name, the agent autonomously investigates the failure, traces causation through the data lineage, designs a context-appropriate fix, implements it, and generates a comprehensive investigation report.

The key insight is that debugging data quality failures follows a reasoning loop, not a fixed script. Each finding informs what to investigate next. The depth of investigation varies. The solution depends on what the agent discovers along the way. This is a natural fit for an agentic approach.

The ReACT Pattern at Work

The workflow follows the ReACT framework (Yao et al., 2022): the agent iterates through cycles of Thought, Action, and Observation until it reaches a resolution.

Each iteration looks like this:

The agent doesn't execute a predetermined script. It reasons about findings, decides what to do next, and terminates when it has enough evidence—whether that takes 3 iterations or 12.

Architecture and Tools

The system comprises three custom Python tools and an AI agent orchestrator:

Tool Details

The AI agent doesn't need specialized orchestration code. It operates within an AI coding assistant (like Cursor with Claude) guided by a detailed skill prompt—a structured document that teaches the agent the investigation methodology, tool usage patterns, solution design heuristics, and reporting format.

The Investigation Flow

When given a failing test name, here is the typical autonomous workflow. Note that the flow is not rigid—the agent may skip steps, repeat steps, or investigate different branches depending on what it finds.

Worked Example

To make this concrete, here's a simplified walkthrough of a real investigation.

Iteration 1: Understand the test

Iteration 2: Find the duplicates

Iteration 3: Analyze the SQL

Iteration 4: Trace upstream

Iteration 5: Design and implement fix

qualify
    row_number() over (
        partition by
            item_id,
            source_type,
            event_type,
            event_timestamp
        order by
            priority_rank,  -- Business logic: prefer higher priority
            event_id        -- Deterministic tie-breaker
    ) = 1

The agent chose QUALIFY ROW_NUMBER() over DISTINCT or GROUP BY because:

• The partition columns match the surrogate key components exactly.
• The ordering respects business logic (priority ranking).
• The tie-breaker column ensures deterministic results.
• QUALIFY is optimized for columnar warehouses and avoids a subquery.

What Makes It Truly Agentic

This workflow goes beyond simple automation. Here are the characteristics that make it genuinely agentic:

1. Autonomous Goal Pursuit

The agent receives a high-level goal ("fix this test") and autonomously determines the investigation strategy. It doesn't follow a fixed script—it decides when to query upstream, how deep to trace, and when it has enough evidence to design a solution.

2. Dynamic Decision Making

Every investigation is different. The agent adapts its approach based on the test type:

• Unique test failures → investigate duplicates, trace surrogate key components
• Not-null failures → investigate nulls, check upstream propagation and COALESCE gaps
• Relationship failures → investigate orphaned records, check referential integrity

3. Variable Investigation Depth

This is the critical agentic feature. Sometimes the root cause is in the immediate model. Sometimes it's 3–4 tables upstream. The agent doesn't stop at a fixed depth—it continues the ReACT loop until it finds the actual root cause.

4. Context-Aware Solution Design

The agent doesn't apply template fixes. It synthesizes everything it has learned to design a solution that:

• Respects business logic (e.g., priority fields for deduplication ordering)
• Considers performance implications (e.g., QUALIFY vs subquery in columnar warehouses)
• Ensures deterministic behavior (tie-breaker columns)
• Preserves existing code patterns and style

5. Tool Orchestration

Each tool invocation is informed by the previous one. The agent chains tools dynamically:

Manifest  → "Uniqueness test on item_sk"
   ↓
Warehouse → "2 duplicates found, here are the IDs"
   ↓
File Read → "Model has UNION ALL with GROUP BYs"
   ↓
Lineage  → "Column comes from web_events CTE"
   ↓
Warehouse → "Source has multiple records per ID"
   ↓
Reasoning → "Need dedup based on SK components"
   ↓
Implement → "QUALIFY ROW_NUMBER() applied"

Extension: Automated Merge Requests

The base workflow ends with a human review checkpoint: the agent implements the fix and creates documentation, then waits for approval. A natural extension is automating the merge request creation.

With this extension, the merge request becomes a self-contained work package: code changes, investigation rationale, data evidence, and verification queries all in one reviewable artifact.

Results and Impact

Qualitative Improvements

• Reduced cognitive load: Engineers focus on architecture and system design rather than reactive debugging.
• Better root cause identification: The agent doesn't take shortcuts, get tired, or stop at surface-level symptoms.
• Faster onboarding: New engineers read past investigation reports to understand the data model and common failure patterns.
• Audit trail: Every investigation is documented with the queries run, data found, reasoning applied, and solution chosen.

Limitations and Honest Assessment

This workflow is not a silver bullet. Being clear about limitations is important for setting expectations.

What it handles well

• Standard dbt test types (unique, not_null, relationship)
• Root causes within 3–4 hops of the failing model
• SQL-level fixes (deduplication, null handling, join refinement)
• Failures with clear data evidence

What it struggles with

• Business logic ambiguity: When the "correct" behavior requires domain knowledge the agent doesn't have.
• Cross-system issues: Root causes in source systems (API changes, ingestion bugs) that aren't visible in the warehouse.
• Complex custom tests: Tests with custom SQL logic that the manifest parser can't decompose.
• Performance regression: The agent focuses on correctness, not query performance. A fix that passes the test might introduce expensive patterns.

Cost considerations

• Each investigation involves multiple LLM calls (10–30 depending on depth).
• Warehouse queries during investigation consume compute credits. Smart sampling and partition filters are essential guardrails.
• Agent occasionally proposes overly aggressive fixes (e.g., refactoring an entire model when a targeted fix suffices). The skill prompt includes explicit guidance to implement minimal, targeted changes.

Challenges Overcome in Development

Lessons Learned

Tool quality is the foundation

The agent is only as good as its tools. We invested heavily in the manifest parser, lineage tracer, and query executor before building the agent logic. Tools that return well-structured JSON/tables enable much better agent reasoning than free-text outputs.

Prompt engineering scales with complexity

The skill prompt is several hundred lines because the agent needs detailed guidance on when to stop investigating, how to structure reports, what makes a good solution, and how to handle edge cases. This isn't a limitation—it's how you encode domain expertise into an agentic system.

Documentation is a first-class output

The HTML investigation reports aren't afterthoughts. They're how humans understand agent decisions, how knowledge gets preserved, and how auditing happens. Treating documentation as a core deliverable (not a nice-to-have) changed how we designed the entire workflow.

Sandbox-first development

Early in development, the agent occasionally proposed aggressive fixes. Having it work in a dev environment first—and requiring verification queries before declaring success—was essential for building confidence.

The right human checkpoint

Full autonomy isn't the goal. The agent automates investigation, analysis, and implementation. Humans retain judgment over production changes. This division of labor plays to each party's strengths.

Future Directions

Self-Correction Loop

The agent implements a fix, runs the test in a sandbox, and if it still fails, analyzes why and tries an alternative approach. This moves from single-shot fixes to iterative problem-solving.

Learning from History

When a new failure occurs, the agent searches past investigation reports for similar patterns: "This looks like investigation #47: a duplicate UNION ALL issue." It applies proven solution patterns rather than investigating from scratch.

Proactive Detection

Instead of reacting to failures, the agent monitors test runs for flakiness trends—tests that intermittently fail or show degrading data quality—and investigates before a full failure occurs.

Multi-Agent Collaboration

Specialized sub-agents handle different aspects of investigation: a SQL analysis agent reviews query patterns, a data profiling agent analyzes distributions, a testing agent generates comprehensive test cases. A coordinator agent delegates and synthesizes their findings.

Conclusion

Building an agentic workflow for dbt test debugging transformed data quality incidents from multi-hour firefighting into autonomous, well-documented resolutions. The workflow exhibits key agentic characteristics:

• Autonomy: Pursues high-level goals without step-by-step instructions.
• Reasoning: Follows the ReACT pattern—analyzing data, tracing causation, designing solutions.
• Adaptation: Adjusts investigation depth and approach based on findings.
• Tool orchestration: Chains tools dynamically where each informs the next.
• Knowledge creation: Generates comprehensive, reusable documentation.

The future isn't about replacing data engineers. It's about elevating them from reactive debugging to proactive system design. Agentic workflows make that shift possible today.