DEV Community

Arisyn
Arisyn

Posted on

Beyond SQL Accuracy: Building Evidence Chains for AI Data Agents

#ai #data #llm #sql

AI data agents are getting good at producing executable SQL.

That is useful, but executable SQL is not the same thing as a correct business answer.

A query can compile, run successfully, return real rows, and still answer the wrong question because the agent selected the wrong metric definition, source table, relationship, grain, time field, or filter.

For production systems, this creates a different engineering requirement:

An AI data agent should not only generate an answer. It should preserve the evidence that produced it.

This article explores how to treat that evidence as a first-class artifact rather than an explanation generated after the fact.

## 1. Why SQL Accuracy Is an Incomplete Target

Consider this question:

What was net revenue by region last quarter?

The agent generates:

SELECT
    c.region,
    SUM(i.invoice_amount) AS net_revenue
FROM invoices i
JOIN customers c
    ON i.customer_id = c.customer_id
WHERE i.invoice_date >= '2026-04-01'
  AND i.invoice_date < '2026-07-01'
GROUP BY c.region;
Enter fullscreen mode Exit fullscreen mode

The query is valid.

But suppose the enterprise definition is:

Net Revenue
=
Recognized Revenue
-
Refunds
-
Credits
Enter fullscreen mode Exit fullscreen mode

and regional attribution is based on the billing account rather than the customer master.

The SQL engine cannot detect this error.

From the database's perspective, the query is correct.

From the business's perspective, it is wrong.

That gives us two different validation layers:

SQL Validity
=
Can this query execute?

Business Validity
=
Does this query represent the intended business question?
Enter fullscreen mode Exit fullscreen mode

Production AI analytics needs both.

## 2. The Failure Surface Is Larger Than SQL Generation

A useful way to model an AI data query is:

Question
   ↓
Semantic Resolution
   ↓
Source Selection
   ↓
Relationship Selection
   ↓
Filter / Time Resolution
   ↓
SQL Generation
   ↓
Execution
   ↓
Answer
Enter fullscreen mode Exit fullscreen mode

An error can occur at any stage.

For example:

Semantic Resolution
Revenue → Invoice Amount       ❌

Source Selection
invoices                       ✓

Relationship Selection
invoice.customer_id → customer ✓

Time Resolution
invoice_date                   ❌

SQL Generation
Valid SQL                      ✓

Execution
Success                        ✓
Enter fullscreen mode Exit fullscreen mode

If we evaluate only the final SQL syntax or execution status, the system appears healthy.

The real failure happened much earlier.

This is why AI data agents need evidence chains, not just query logs.

## 3. What Is an Evidence Chain?

An evidence chain records the artifacts and decisions that connect a user question to the final answer.

Conceptually:

User Question
      │
      ▼
Business Definition
      │
      ▼
Source Data
      │
      ▼
Relationships
      │
      ▼
Filters / Time Rules
      │
      ▼
Generated SQL
      │
      ▼
Execution Result
      │
      ▼
Final Answer
Enter fullscreen mode Exit fullscreen mode

The goal is not to create another verbose chain-of-thought log.

The goal is to preserve verifiable system artifacts.

That distinction matters.

Internal model reasoning is probabilistic and may not be suitable as an audit artifact.

A metric identifier, relationship identifier, SQL query, data-source identifier, and execution result are inspectable.

## 4. Evidence Should Be Structured

Instead of storing only:

question
sql
answer
Enter fullscreen mode Exit fullscreen mode

store a structured evidence object.

For example:

{
  "question": "What was net revenue by region last quarter?",

  "semantic_resolution": {
    "metric_id": "net_revenue",
    "metric_version": "3.2",
    "dimensions": ["billing_region"]
  },

  "sources": [
    "orders",
    "refunds",
    "billing_accounts"
  ],

  "relationships": [
    {
      "relationship_id": "rel_orders_billing_account",
      "from": "orders.billing_account_id",
      "to": "billing_accounts.account_id",
      "status": "trusted"
    }
  ],

  "time": {
    "field": "settlement_date",
    "period": "last_quarter"
  },

  "sql": {
    "query_id": "q_8271"
  },

  "execution": {
    "result_id": "r_4412",
    "status": "success"
  },

  "validation": {
    "status": "passed"
  }
}
Enter fullscreen mode Exit fullscreen mode

Now the answer has provenance.

## 5. Capture Evidence During Execution, Not Afterward

A common anti-pattern is:

1. Agent generates answer
2. User asks "Why?"
3. LLM generates an explanation
Enter fullscreen mode Exit fullscreen mode

That produces a narrative, not necessarily provenance.

The model may describe what it believes happened.

A stronger implementation captures evidence as each stage executes.

For example:

evidence = Evidence(question=question)

metric = resolve_metric(question)
evidence.metric_id = metric.id
evidence.metric_version = metric.version

dimension = resolve_dimension(question)
evidence.dimensions.append(dimension.id)

path = relationship_service.get_trusted_path(
    metric=metric,
    dimensions=[dimension]
)
evidence.relationship_ids.extend(path.relationship_ids)

sql = generate_sql(
    question=question,
    metric=metric,
    dimensions=[dimension],
    relationship_path=path
)
evidence.sql = sql

result = execute(sql)
evidence.execution_status = result.status
evidence.result_id = result.id
Enter fullscreen mode Exit fullscreen mode

The evidence object is built from actual system events.

That makes it much more useful for debugging and audit.

6. Semantic Evidence

The first layer is semantic resolution.

If the user asks:

What was revenue last quarter?
Enter fullscreen mode Exit fullscreen mode

the system should be able to show:

User Term:
Revenue

Resolved Metric:
Net Revenue

Version:
3.2

Definition:
Recognized Revenue - Refunds - Credits
Enter fullscreen mode Exit fullscreen mode

This matters because business terms are often ambiguous.

The SQL may be technically perfect while using the wrong definition.

A metric version is particularly important.

If Revenue v3.1 and v3.2 differ, the evidence chain should preserve which version generated the answer.

## 7. Source Evidence

The next question is:

Which data actually contributed to the answer?

For example:

orders
refunds
billing_accounts
Enter fullscreen mode Exit fullscreen mode

A production evidence record may also include fields:

{
  "source": "orders",
  "fields": [
    "billing_account_id",
    "recognized_amount",
    "settlement_date"
  ]
}
Enter fullscreen mode Exit fullscreen mode

This makes it possible to inspect whether the agent used an authoritative source rather than merely a semantically similar table.

## 8. Relationship Evidence

Multi-table queries require another layer of provenance.

Suppose the query joins:

orders
   ↓
billing_accounts
   ↓
region
Enter fullscreen mode Exit fullscreen mode

The evidence should preserve why that path was selected.

At minimum:

{
  "relationship_id": "rel_orders_billing_account",
  "from": "orders.billing_account_id",
  "to": "billing_accounts.account_id",
  "status": "trusted"
}
Enter fullscreen mode Exit fullscreen mode

More advanced systems could include relationship evidence such as:

Database Constraint
Naming Similarity
Value Inclusion
Uniqueness
Business Validation
Enter fullscreen mode Exit fullscreen mode

The important point is that the agent is not silently inventing a join.

The relationship is inspectable.

## 9. Filter and Time Evidence

Filters are easy to overlook because they often appear as simple SQL predicates.

But they can completely change the business answer.

For example:

"last quarter"
Enter fullscreen mode Exit fullscreen mode

might resolve to:

2026-04-01 → 2026-06-30
Enter fullscreen mode Exit fullscreen mode

and use:

settlement_date
Enter fullscreen mode Exit fullscreen mode

rather than:

invoice_date
Enter fullscreen mode Exit fullscreen mode

The evidence record should preserve both.

For example:

{
  "time_resolution": {
    "business_period": "last_quarter",
    "start": "2026-04-01",
    "end": "2026-06-30",
    "field": "settlement_date"
  }
}
Enter fullscreen mode Exit fullscreen mode

This makes temporal interpretation auditable.

## 10. Query Evidence

The SQL itself remains critical evidence.

But do not treat SQL as the entire explanation.

SQL is the executable consequence of upstream decisions.

A useful query record might include:

{
  "query_id": "q_8271",
  "sql_hash": "sha256:...",
  "generated_at": "2026-08-20T09:15:00Z",
  "metric_version": "3.2",
  "relationship_ids": [
    "rel_orders_billing_account"
  ]
}
Enter fullscreen mode Exit fullscreen mode

This makes the query reproducible and connects it to the semantic and relationship state used during generation.

## 11. Execution Evidence

Execution success is useful evidence, just not sufficient evidence.

Capture:

Execution Status
Duration
Rows Returned
Data Source
Query Timestamp
Result Identifier
Enter fullscreen mode Exit fullscreen mode

Potentially:

{
  "status": "success",
  "duration_ms": 842,
  "rows_returned": 8,
  "result_id": "r_4412"
}
Enter fullscreen mode Exit fullscreen mode

This lets the system distinguish:

Bad SQL
Enter fullscreen mode Exit fullscreen mode

from:

Valid SQL + Wrong Business Interpretation
Enter fullscreen mode Exit fullscreen mode

## 12. Validation Should Check the Chain

Instead of asking only:

Did the SQL execute?
Enter fullscreen mode Exit fullscreen mode

validation can inspect several layers.

### Semantic Validation

Was an approved metric definition used?
Enter fullscreen mode Exit fullscreen mode

### Source Validation

Were authorized and current data sources used?
Enter fullscreen mode Exit fullscreen mode

### Relationship Validation

Were joins based on trusted relationships?
Enter fullscreen mode Exit fullscreen mode

### Filter Validation

Were required business filters applied?
Enter fullscreen mode Exit fullscreen mode

### Query Validation

Did the SQL pass syntax and safety checks?
Enter fullscreen mode Exit fullscreen mode

This produces a richer validation object:

{
  "semantic": "passed",
  "sources": "passed",
  "relationships": "passed",
  "filters": "passed",
  "sql": "passed"
}
Enter fullscreen mode Exit fullscreen mode

## 13. Why LLM-as-a-Judge Cannot Be the Only Validator

A second model can review generated SQL.

That is useful.

But consider:

Agent:
Revenue = invoice_amount

Judge:
The SQL correctly sums invoice_amount.
Enter fullscreen mode Exit fullscreen mode

Both models agree.

Both can still be wrong relative to the enterprise definition.

Language models are good at evaluating logical consistency inside the context they receive.

They cannot recover enterprise truth that is missing from that context.

So:

You cannot verify a data answer with language alone.

Verification needs grounded evidence from semantic definitions, metadata, relationships, executed SQL, and query results.

## 14. Evidence Chains Improve Debugging

Suppose an answer is reported as incorrect.

Without structured evidence, the debugging workflow may be:

Reproduce Prompt
↓
Inspect Agent Logs
↓
Inspect SQL
↓
Guess What Went Wrong
Enter fullscreen mode Exit fullscreen mode

With evidence:

Semantic Resolution      PASS
Source Selection         PASS
Relationship Selection   FAIL
Filter Resolution        PASS
SQL Validation           PASS
Execution                PASS
Enter fullscreen mode Exit fullscreen mode

The engineering team immediately knows where to investigate.

This is especially valuable because many production NL2SQL failures happen before SQL generation.

## 15. Evidence Chains Improve Evaluation

Evidence also makes offline and online evaluation more granular.

Instead of only measuring:

Exact SQL Match
Execution Accuracy
Final Answer Accuracy
Enter fullscreen mode Exit fullscreen mode

teams can measure:

Metric Resolution Accuracy
Source Selection Accuracy
Relationship Selection Accuracy
Filter Resolution Accuracy
Trusted Relationship Usage
SQL Validation Pass Rate
Enter fullscreen mode Exit fullscreen mode

This turns a monolithic accuracy score into a diagnostic system.

If final-answer accuracy drops, the team can determine which layer caused the regression.

## 16. Evidence as an API Object

Evidence should not exist only in the UI.

It can be part of the agent API.

For example:

{
  "answer": {
    "metric": "Net Revenue",
    "value": 27300000,
    "currency": "USD"
  },

  "evidence": {
    "metric": {
      "id": "net_revenue",
      "version": "3.2"
    },

    "sources": [
      "orders",
      "refunds",
      "billing_accounts"
    ],

    "relationships": [
      "rel_orders_billing_account"
    ],

    "query_id": "q_8271",

    "validation": "passed"
  }
}
Enter fullscreen mode Exit fullscreen mode

Now downstream systems can choose how much evidence to expose.

## 17. Progressive Disclosure in the UI

Most business users do not want to read SQL after every question.

So the default UI can remain simple:

Net Revenue
$27.3M

✓ Evidence available
Enter fullscreen mode Exit fullscreen mode

Then:

Why this answer?
Enter fullscreen mode Exit fullscreen mode

can reveal:

Metric Definition
Source Data
Relationships
Filters
SQL
Validation
Enter fullscreen mode Exit fullscreen mode

Different users can inspect different levels.

An executive may stop at the answer.

An analyst may inspect the metric and filters.

A data engineer may inspect joins and SQL.

## 18. Evidence Is Stronger Than Explanation

There is an important difference between:

Explainable
Enter fullscreen mode Exit fullscreen mode

and:

Inspectable
Enter fullscreen mode Exit fullscreen mode

An LLM can generate a convincing explanation.

An inspectable system exposes the artifacts that actually produced the answer.

For enterprise data, inspectability is often the stronger trust mechanism.

The user does not have to believe the explanation.

They can inspect the evidence.

## 19. A Practical Evidence Pipeline

A simplified architecture could look like:

User Question
      │
      ▼
Semantic Resolver
      │
      ├── metric_id
      └── dimension_ids
      │
      ▼
Relationship Resolver
      │
      └── relationship_ids
      │
      ▼
Context Builder
      │
      ▼
SQL Generator
      │
      └── query_id
      │
      ▼
SQL Validator
      │
      ▼
Query Executor
      │
      └── result_id
      │
      ▼
Answer Generator
      │
      ▼
Evidence Object
Enter fullscreen mode Exit fullscreen mode

Every stage contributes structured evidence.

The evidence object becomes the trace that connects the original question to the final answer.

## 20. What Should Be Stored?

At minimum:

Question ID
User Question
Resolved Metric + Version
Resolved Dimensions
Source Tables / Fields
Relationship IDs
Filters
Time Interpretation
Generated SQL / Query ID
Execution Result ID
Validation Status
Timestamp
Enter fullscreen mode Exit fullscreen mode

Depending on governance requirements, also consider:

Semantic Model Version
Relationship Model Version
Data Snapshot / Query Timestamp
Authorization Context
Agent Version
Model Version
Enter fullscreen mode Exit fullscreen mode

This makes answers reproducible even as the system evolves.

## 21. A Better Definition of Production Readiness

For a prototype, this may be enough:

Question → SQL → Answer
Enter fullscreen mode Exit fullscreen mode

For production enterprise AI, a stronger standard is:

Question
→ Governed Interpretation
→ Trusted Data Path
→ Executable Query
→ Traceable Result
→ Evidence-Backed Answer
Enter fullscreen mode Exit fullscreen mode

The difference is not cosmetic.

It changes how the system can be trusted, evaluated, debugged, audited, and improved.

## Final Thoughts

AI data agents are rapidly improving at generating SQL.

But SQL generation is only one stage in a much larger correctness problem.

A production system should be able to answer two questions:

What is the answer?

and:

What evidence produced that answer?

That evidence should include the business definition, source data, relationships, filters, SQL, execution result, and validation state.

Not because every user wants to inspect every detail.

But because enterprise trust should be based on something stronger than model confidence.

A trustworthy data agent does not just return a number. It preserves the chain of evidence that makes the number verifiable.

Top comments (0)