DEV Community

ArisynData
ArisynData

Posted on

Building Answer Lineage for Enterprise Data Agents

#ai #data #llm #sql

If a data agent returns €18.6M, production observability should tell you exactly how that answer was constructed.

Most Text-to-SQL pipelines log prompts, generated SQL, latency, tokens, and execution status. Those signals matter, but when a business user says, “This number is wrong,” they are not enough.

The failure may have happened before SQL generation:

"Revenue"      → wrong metric
"Germany"      → wrong dimension
Customer data  → wrong relationship path
"Last quarter" → wrong time interpretation
Enter fullscreen mode Exit fullscreen mode

The SQL can be valid while encoding the wrong business meaning.

For enterprise data agents, observability should cover the entire path from question to answer. That is Answer Lineage.

Data Lineage vs. Answer Lineage

Traditional data lineage answers:

Where did this dataset or column come from?

ERP → ETL → Warehouse → Semantic Model → Dashboard
Enter fullscreen mode Exit fullscreen mode

Answer lineage asks:

How did this natural-language question become this business answer?

Question
   ↓
Resolved Intent
   ↓
Semantic Objects
   ↓
Selected Data
   ↓
Relationship Path
   ↓
Query Plan
   ↓
SQL
   ↓
Execution Result
   ↓
Answer
Enter fullscreen mode Exit fullscreen mode

Data lineage follows data. Answer lineage follows a decision request.

Start With an AnswerLineage Object

Create the lineage object when the request begins, not after the answer is generated.

{
  "lineage_id": "al_78291",
  "question": "What was revenue in Germany last quarter?",
  "status": "running",
  "created_at": "2026-09-11T10:24:18Z"
}
Enter fullscreen mode Exit fullscreen mode

Enrich it as the pipeline executes.

A completed object might contain:

{
  "lineage_id": "al_78291",

  "intent": {
    "metric": "revenue",
    "dimension": "country",
    "time_range": "last_quarter"
  },

  "semantic_resolution": {
    "metric": {
      "name": "Recognized Revenue",
      "version": "v4",
      "owner": "Finance"
    },
    "dimension": {
      "name": "Customer Country"
    }
  },

  "filters": {
    "country": "Germany",
    "time_range": "2026-Q2"
  },

  "fields": [
    "finance_revenue.recognized_amount",
    "customer.country_code"
  ],

  "relationship_path": [
    "customer -> account",
    "account -> sales_order",
    "sales_order -> finance_revenue"
  ],

  "query_id": "q_91821",

  "answer": {
    "value": 18600000,
    "currency": "EUR"
  }
}
Enter fullscreen mode Exit fullscreen mode

Now €18.6M is not an isolated output. It has provenance.

Capture Lineage as Events

An append-only event stream works well for multi-stage agents:

question_received
intent_resolved
semantic_object_selected
relationship_path_selected
query_plan_created
sql_generated
query_executed
answer_generated
Enter fullscreen mode Exit fullscreen mode

Example:

{
  "lineage_id": "al_78291",
  "event": "semantic_object_selected",
  "payload": {
    "type": "metric",
    "name": "Recognized Revenue",
    "version": "v4"
  }
}
Enter fullscreen mode Exit fullscreen mode

Later:

{
  "lineage_id": "al_78291",
  "event": "relationship_path_selected",
  "payload": {
    "path": [
      "customer -> account",
      "account -> sales_order",
      "sales_order -> finance_revenue"
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

This provides auditability, ordering, replay, debugging, and version history.

Capture Intent Evidence

The first important artifact is not SQL. It is the system's interpretation of the question.

{
  "metric": "revenue",
  "dimension": "country",
  "filter_value": "Germany",
  "time_expression": "last quarter"
}
Enter fullscreen mode Exit fullscreen mode

If ambiguity exists, preserve it:

{
  "metric": {
    "status": "ambiguous",
    "candidates": [
      "recognized_revenue",
      "invoice_amount"
    ]
  }
}
Enter fullscreen mode Exit fullscreen mode

Otherwise engineers may blame SQL for what was actually an intent-resolution failure.

Capture Semantic Evidence

Record how business language mapped to governed concepts:

{
  "input_term": "revenue",
  "resolved_metric": {
    "id": "metric.recognized_revenue",
    "name": "Recognized Revenue",
    "version": "v4",
    "status": "certified",
    "owner": "Finance"
  }
}
Enter fullscreen mode Exit fullscreen mode

The lineage should preserve:

User Language
      ↓
Business Concept
      ↓
Physical Mapping
Enter fullscreen mode Exit fullscreen mode

not merely the final column name.

Capture Relationship Evidence

For multi-table questions, store the path selected by the planner:

{
  "path_id": "rp_4821",
  "nodes": [
    "customer",
    "account",
    "sales_order",
    "finance_revenue"
  ],
  "edges": [
    {
      "from": "customer.customer_id",
      "to": "account.customer_id",
      "status": "trusted"
    },
    {
      "from": "account.account_id",
      "to": "sales_order.account_id",
      "status": "trusted"
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

Why separate this from SQL?

Because the relationship decision is a reasoning artifact. SQL is only its execution representation.

If the answer is inflated by fanout, this is often the first artifact to inspect.

Preserve a Semantic Query Plan

Before generating SQL, create a structured plan:

{
  "metric": {
    "field": "finance_revenue.recognized_amount",
    "aggregation": "SUM"
  },

  "dimension": {
    "field": "customer.country_code"
  },

  "filters": [
    {
      "field": "customer.country_code",
      "operator": "=",
      "value": "DE"
    }
  ],

  "time_filter": {
    "field": "finance_revenue.recognition_date",
    "start": "2026-04-01",
    "end": "2026-06-30"
  },

  "relationship_path_id": "rp_4821"
}
Enter fullscreen mode Exit fullscreen mode

This separates the business query plan from the SQL dialect and makes semantic, relationship, grain, and policy validation easier.

Store SQL as Evidence, Not the Whole Explanation

Generated SQL belongs in lineage:

{
  "query_id": "q_91821",
  "dialect": "postgresql",
  "sql_hash": "sha256:...",
  "execution_status": "success",
  "row_count": 1
}
Enter fullscreen mode Exit fullscreen mode

But SQL tells you what executed, not why those business choices were made.

You still need semantic and relationship evidence.

Link Results to Answers

Execution output should remain traceable:

{
  "query_id": "q_91821",
  "result": {
    "columns": ["recognized_revenue"],
    "rows": [[18600000]]
  }
}
Enter fullscreen mode Exit fullscreen mode

Then link the user-facing answer:

{
  "answer": "Revenue in Germany last quarter was €18.6M.",
  "evidence": {
    "query_id": "q_91821",
    "lineage_id": "al_78291"
  }
}
Enter fullscreen mode Exit fullscreen mode

The statement is now connected to the actual execution artifact.

Make Historical Evidence Reproducible

If Revenue v4 becomes Revenue v5 tomorrow, yesterday's answer should still point to v4.

Persist versions for:

Metric definitions
Semantic mappings
Relationship definitions
Policies
Enter fullscreen mode Exit fullscreen mode

Reproducibility depends on historical identity, not only current configuration.

Useful provenance fields include:

Source
Version
Owner
Status
Timestamp
Validation State
Enter fullscreen mode Exit fullscreen mode

This turns lineage from a debugging trace into governance evidence.

Evidence Is Not Chain-of-Thought

An enterprise evidence layer should expose verifiable artifacts, not hidden model reasoning.

Useful evidence includes:

Resolved Metric
Metric Version
Selected Fields
Relationship Path
Filters
Time Range
SQL
Query Result
Enter fullscreen mode Exit fullscreen mode

A generated explanation describes.

Evidence artifacts verify.

Design an Evidence API

A simple API might be:

GET /answers/{answer_id}/evidence
Enter fullscreen mode Exit fullscreen mode

with a response such as:

{
  "answer_id": "ans_1192",
  "lineage_id": "al_78291",

  "summary": {
    "metric": "Recognized Revenue",
    "dimension": "Customer Country",
    "filter": "Germany",
    "time_range": "2026 Q2"
  },

  "semantic_evidence": "...",
  "relationship_evidence": "...",
  "query_evidence": "...",
  "governance_evidence": "..."
}
Enter fullscreen mode Exit fullscreen mode

The UI can then progressively disclose detail.

A business user sees:

€18.6M
Metric: Recognized Revenue
Period: Q2 2026
Country: Germany
[View Evidence]
Enter fullscreen mode Exit fullscreen mode

A technical view can expose fields, relationship paths, SQL, query ID, and execution metadata.

Build an Evidence Graph

For complex systems, lineage can be modeled as a graph:

Question
   │
   ├── Intent
   ├── Metric ──→ Metric Version
   ├── Dimension
   ├── Relationship Path
   │        ├── Edge 1
   │        ├── Edge 2
   │        └── Edge 3
   ├── Query Plan
   ├── SQL
   ├── Result
   └── Answer
Enter fullscreen mode Exit fullscreen mode

This enables questions such as:

Which answers used Revenue v4?
Which answers used this relationship edge?
Which answers depended on this field?
Which answers were generated before a metric was deprecated?
Enter fullscreen mode Exit fullscreen mode

Answer lineage starts becoming an impact-analysis system.

Debugging Becomes Faster

Suppose:

Expected: €16.9M
Returned: €18.6M
Enter fullscreen mode Exit fullscreen mode

Without lineage:

Reproduce
Inspect prompt
Inspect SQL
Guess
Enter fullscreen mode Exit fullscreen mode

With lineage:

Intent        ✓
Metric        ✓
Dimension     ✓
Time Range    ✓
Relationship  ✕
Enter fullscreen mode Exit fullscreen mode

Now you know where to investigate.

That makes answer lineage an observability capability, not merely a compliance feature.

Add Validation Status

Each stage can carry validation state:

{
  "semantic_resolution": {
    "status": "validated"
  },
  "relationship_path": {
    "status": "trusted"
  },
  "query_plan": {
    "status": "validated"
  },
  "execution": {
    "status": "success"
  }
}
Enter fullscreen mode Exit fullscreen mode

A user-facing trust summary can then show:

Semantic Context       ✓
Relationship Context   ✓
Query Validation       ✓
Execution              ✓
Enter fullscreen mode Exit fullscreen mode

This is more meaningful than an arbitrary Confidence: 94%.

The Evidence Test

A simple production test for any data agent:

Ask:

What was revenue by region last quarter?

Let it answer.

Then ask:

Prove it.

The system should be able to retrieve:

Business Definition
Metric
Fields
Relationship Path
Filters
Time Range
SQL
Result
Enter fullscreen mode Exit fullscreen mode

If it cannot, the answer may still be correct—but it is difficult to independently verify, audit, or debug.

Reference Architecture

                  User Question
                        ↓
                 Intent Resolver
                        │
                        ├── evidence event
                        ↓
                Semantic Resolver
                        │
                        ├── evidence event
                        ↓
               Relationship Resolver
                        │
                        ├── evidence event
                        ↓
                  Query Planner
                        │
                        ├── evidence event
                        ↓
                  SQL Generator
                        │
                        ├── query artifact
                        ↓
                    Executor
                        │
                        ├── result artifact
                        ↓
                Answer Generator
                        │
                        └── answer artifact

All artifacts
      ↓
Answer Lineage Store
      ↓
Evidence API / Audit / Debugging / UI
Enter fullscreen mode Exit fullscreen mode

The architecture is model- and database-agnostic.

The requirement is simply to preserve the decisions that matter.

What to Measure

Once lineage exists, useful operational metrics become possible:

% of answers with complete lineage
% using certified metrics
% using trusted relationship paths
% with validated query plans
Average time to diagnose incorrect answers
Answers impacted by deprecated semantic objects
Enter fullscreen mode Exit fullscreen mode

These describe production reliability more meaningfully than token count alone.

Final Thoughts

A production data agent should not produce an answer and then forget how it got there.

The answer should remain linked to:

Intent
Semantics
Data
Relationships
Query Plan
SQL
Result
Governance
Enter fullscreen mode Exit fullscreen mode

That is Answer Lineage.

It turns:

"Revenue was €18.6M."
Enter fullscreen mode Exit fullscreen mode

from an isolated generated statement into a verifiable analytical artifact.

Don't just log the answer. Preserve the evidence chain that produced it.

Production AI analytics needs more than observability of models and SQL.

It needs observability of business meaning.

Top comments (1)

Collapse
 
jo-do profile image
Jo Do •

Starting the lineage object before interpretation is the important move. I would also store the rejected candidates and the evidence that eliminated them, especially for ambiguous metrics and relationship paths. The final path explains what happened; the alternatives explain why it was reasonable and make later policy or semantic-model changes replayable.