DEV Community: ArisynData

From Discovered Metadata to Trusted Business Knowledge: Building a Validation Pipeline

ArisynData — Wed, 11 Nov 2026 12:00:00 +0000

AI can discover a relationship in your data. That does not mean your business should trust it.

Enterprise data platforms are beginning to extract more knowledge from assets companies already have: SQL history, dashboards, transformation models, metadata, and usage patterns.

That is useful. Years of business logic are often buried in queries and reports rather than maintained in one clean catalogue.

But there is a dangerous shortcut:

Found in SQL
    ↓
Assumed to be correct
    ↓
Used by an AI agent

A frequently used query can contain an old business rule. A join can run successfully and still multiply rows. A column called revenue may represent booked orders rather than recognized revenue.

The engineering challenge is therefore not just knowledge discovery. It is building a pipeline that separates candidate knowledge from validated evidence and approved business definitions.

This article proposes a practical design for that pipeline.

1. Start with candidates, not facts

Imagine a discovery process scans historical SQL and finds this pattern repeatedly:

SELECT
    c.customer_id,
    SUM(o.order_amount) AS revenue
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

It may extract three candidates:

{
  "candidate_metric": {
    "name": "revenue",
    "expression": "SUM(orders.order_amount)"
  },
  "candidate_relationship": {
    "left": "customers.customer_id",
    "right": "orders.customer_id"
  },
  "candidate_dimension": {
    "name": "customer",
    "source": "customers"
  }
}

These are useful discoveries. They are not yet business facts.

The query might be an approved Finance report—or a temporary Sales analysis. It might be copied from an old dashboard. It might omit cancellations, refunds, or recognition rules.

Store the discovery as a candidate with provenance:

{
  "candidate_id": "cand_1042",
  "kind": "metric",
  "name": "revenue",
  "proposed_expression": "SUM(orders.order_amount)",
  "discovered_from": {
    "asset_type": "sql_query",
    "asset_id": "query_8821"
  },
  "first_seen": "2026-10-14",
  "last_seen": "2026-11-08",
  "observed_uses": 137,
  "status": "discovered"
}

The example values are illustrative. In a real system, usage counts and timestamps should come from the actual query and asset logs.

Keep the source evidence attached to the candidate. If a reviewer cannot trace where a proposed rule came from, it is difficult to assess or correct it.

2. Validate relationships with multiple kinds of evidence

Consider a possible relationship between:

orders.customer_id
customers.customer_id

A useful first pass checks whether the proposed key on the customer side is unique and whether order values are covered by the customer key set.

For PostgreSQL, a basic profile could look like this:

-- Is the proposed customer key unique?
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT customer_id) AS distinct_count,
    COUNT(*) - COUNT(DISTINCT customer_id) AS duplicate_count
FROM customers;

-- What share of order customer IDs exist in customers?
SELECT
    COUNT(*) AS order_rows,
    COUNT(c.customer_id) AS matched_rows,
    COUNT(*) - COUNT(c.customer_id) AS unmatched_rows,
    ROUND(
        100.0 * COUNT(c.customer_id) / NULLIF(COUNT(*), 0),
        2
    ) AS match_percentage
FROM orders o
LEFT JOIN customers c
    ON o.customer_id = c.customer_id;

These checks provide evidence, but interpret them carefully.

Uniqueness on customers.customer_id supports a one-to-many relationship from customer to orders.
A high match percentage suggests value inclusion.
Unmatched values may be legitimate because of historical records, data latency, or deleted entities.
Matching values do not prove that the two columns represent the same business entity.

A candidate relationship should therefore carry separate evidence fields:

{
  "relationship_candidate": {
    "left": "orders.customer_id",
    "right": "customers.customer_id",
    "evidence": {
      "right_key_unique": true,
      "left_value_match_rate": 99.2,
      "observed_cardinality": "many_to_one",
      "metadata_foreign_key": false
    },
    "validation_status": "needs_review"
  }
}

Do not collapse these signals into a single confidence score and treat that score as proof. Each signal answers a different question.

A join can be valid and still be wrong for a metric

Suppose an order has two order lines and three payment records. Joining both child tables directly through order_id creates six rows.

2 order lines × 3 payments = 6 joined rows

A query that sums order-line amounts after that join can multiply revenue by three.

Relationship validation must therefore consider cardinality and analytical grain, not only whether key values match.

A candidate join path can be structurally plausible and still be unsafe for a particular metric.

3. Validate metrics separately from joins

A relationship test cannot establish the meaning of a metric.

For example, these are all plausible measures:

Booked Revenue
Invoiced Revenue
Recognized Revenue
Cash Received

They may use different source tables, dates, filters, and aggregation rules.

A metric candidate should be checked against a business contract:

metric_id: recognized_revenue
display_name: Recognized Revenue
owner: Finance
source_expression: SUM(revenue_ledger.recognized_amount)
time_dimension: recognition_date
required_filters:
  - posting_status = 'posted'
status: pending_business_review

This is an illustrative contract, not a vendor-specific schema.

A reviewer should be able to answer:

Is this the approved definition?
Is the source field correct?
Is the time dimension correct?
Are required filters included?
Does the aggregation match the intended grain?
Which business domain is allowed to use it?

A model can suggest an expression. It should not silently decide the organization's accounting policy.

4. Use a validation pipeline with explicit states

A practical lifecycle is:

DISCOVERED
    ↓
VALIDATING
    ↓
VALIDATED
    ↓
APPROVED
    ↓
PUBLISHED

And it needs failure and retirement paths:

VALIDATING ──→ REJECTED
PUBLISHED  ──→ DEPRECATED

The states should mean different things:

State	Meaning	Production use
Discovered	Extracted from an asset or inferred from data	No
Validating	Evidence checks and review are in progress	No
Validated	Technical and business checks have passed	Not automatically
Approved	Authorized owner accepted the definition	Subject to publication policy
Published	Released to the intended consumers	Yes, within scope
Rejected / Deprecated	Invalid or no longer current	No

The important boundary is between validated and approved.

A technically valid relationship is not necessarily an approved business relationship. A metric that passes data tests may still use the wrong definition for Finance.

Approval should be assigned to a real owner or governance process, not inferred from the LLM's confidence.

5. A small policy gate before production use

The following Python example illustrates the final control: only approved and published knowledge is available to the production query planner.

from dataclasses import dataclass
from enum import Enum


class KnowledgeStatus(str, Enum):
    DISCOVERED = "discovered"
    VALIDATING = "validating"
    VALIDATED = "validated"
    APPROVED = "approved"
    PUBLISHED = "published"
    REJECTED = "rejected"
    DEPRECATED = "deprecated"


@dataclass(frozen=True)
class KnowledgeObject:
    object_id: str
    status: KnowledgeStatus
    owner: str | None
    evidence_ref: str | None
    scope: str | None


def can_use_in_production(obj: KnowledgeObject, requested_scope: str) -> bool:
    return (
        obj.status == KnowledgeStatus.PUBLISHED
        and obj.owner is not None
        and obj.evidence_ref is not None
        and obj.scope == requested_scope
    )


candidate = KnowledgeObject(
    object_id="relationship.orders_customer",
    status=KnowledgeStatus.VALIDATED,
    owner="Data Governance",
    evidence_ref="profile_run_2026_1108",
    scope="sales_analytics",
)

assert not can_use_in_production(candidate, "sales_analytics")

The object is validated, but it has not been published. The gate blocks it.

This is intentionally simple. A production implementation would also check user permissions, effective dates, version, data-source identity, approval records, and policy inheritance.

The critical design rule is straightforward:

Do not let the query-generation model promote its own discoveries into production knowledge.

6. Keep governance proportional to risk

Not every discovered object needs the same review process.

A synonym such as "client" → "customer" may be low risk in a particular domain.

A metric used for regulatory reporting or executive financial decisions is not.

A practical risk model can consider:

Financial or regulatory impact
Number of downstream reports and agents
Cross-domain reuse
Relationship cardinality and fanout risk
Whether the source is authoritative
Whether the definition conflicts with another approved object

Low-risk metadata can follow lightweight review. High-impact metrics and relationships should require explicit owner approval and stronger evidence.

This avoids two extremes: publishing every AI inference automatically, or creating a manual approval bottleneck for every minor metadata change.

7. Preserve provenance and changes

When a published definition changes, do not silently overwrite its history.

Keep at least:

Object ID
Version
Previous version
Source evidence
Validation results
Owner
Approver
Approval timestamp
Effective date
Publication scope

For example:

recognized_revenue v2
    status: deprecated
    effective_to: 2026-10-31

recognized_revenue v3
    status: published
    effective_from: 2026-11-01

This allows a team to understand which definition was used for a past answer and why a new answer may differ.

It also makes it possible to invalidate downstream caches or trigger review when a source relationship or metric definition changes.

8. What to measure

A knowledge pipeline should be evaluated on more than how many objects it discovers.

Useful measures include:

Metric	What it tells you
Candidate validation rate	How many discoveries survive evidence checks?
Rejection rate by source	Which sources produce unreliable candidates?
Unapproved production usage	Did any unapproved knowledge reach query execution?
Relationship test coverage	Which published joins have uniqueness and cardinality evidence?
Stale knowledge rate	How much published knowledge is past its review or effective date?
Conflict resolution time	How quickly are competing definitions resolved?

A high discovery count is not necessarily success. If a system generates thousands of candidates that nobody can validate, it may simply create another metadata backlog.

The goal is to increase the amount of useful, explainable, approved knowledge available to AI applications.

9. Where semantic and relationship intelligence fit

This lifecycle requires different capabilities to work together.

A relationship intelligence layer can discover candidate links between tables and fields, and preserve evidence such as key patterns, value overlap, and observed cardinality.

A semantic layer can manage business terms, metrics, dimensions, mappings, ambiguity, and the approved definitions used by query workflows.

In our work on IntaLink and Semora, these responsibilities are separate but complementary:

IntaLink
Discover candidate data relationships
        ↓
Evidence for validation
        ↓
Semora
Business semantics and mappings
        ↓
Governed query context
        ↓
Natural-language query workflow

Semora manages its own data-source connections and permissions. It maps the relevant data source by ID and requests relationship information from IntaLink through APIs.

This is a foundation for connecting physical data relationships with business semantics. It should not be confused with a claim that the products already provide the complete automated validation, approval workflow, or enterprise knowledge registry described in this article.

Those are important areas for product evolution.

Final thought

AI can make enterprise knowledge discovery much cheaper.

But if every discovered pattern becomes a trusted rule, automation will scale old mistakes just as efficiently as it scales useful knowledge.

A reliable pipeline needs three separate responsibilities:

Discovery finds candidates. Validation establishes evidence. Governance grants authority.

The distinction matters because enterprise truth is not whatever the model finds most often.

It is what the organization can explain, verify, approve, and safely use.

Your Business Ontology Won't Fix Missing Database Relationships

ArisynData — Mon, 09 Nov 2026 02:10:23 +0000

An ontology can describe what a customer, contract, or payment means. It cannot, by itself, prove which columns should be joined.

Business ontology is getting a lot of attention in enterprise AI. That is a welcome development. Models need more than table names: they need definitions of customers, contracts, revenue, and the rules behind those terms.

But there is a problem that tends to appear one layer below the ontology.

You can define every business entity correctly and still generate the wrong SQL.

The reason is surprisingly mundane: the database does not always tell you how its tables connect.

Let's work through a realistic example.

The question is simple. The join path isn't.

A finance manager asks:

How much have customers paid against their active contracts this quarter?

The business concepts are reasonably clear:

A customer is the legal entity buying a service.
An active contract is a contract that meets the organization's approved status and date rules.
A payment is cash received and allocated to an invoice.

Now imagine the underlying data lives in four tables:

crm_customers
    customer_id
    customer_name

sales_contracts
    contract_id
    customer_ref
    contract_status

billing_invoices
    invoice_id
    agreement_no
    invoice_amount

cash_allocations
    allocation_id
    invoice_ref
    received_amount
    received_at

The intended business route is:

Customer → Contract → Invoice → Cash Allocation

But the physical columns don't share a consistent naming convention. customer_ref points to customer_id; agreement_no contains contract identifiers; invoice_ref points to invoice_id.

And suppose none of those relationships is declared as a database foreign key.

An ontology might tell the AI that contracts belong to customers and payments settle invoices. It still needs evidence that billing_invoices.agreement_no is the right physical connection to sales_contracts.contract_id.

Without that connection, a text-to-SQL system may omit the invoice table, guess a join, or return no answer. A syntactically valid query does not resolve the uncertainty.

Why schema inspection alone is not enough

A common approach is to inspect database metadata:

-- PostgreSQL: inspect declared foreign keys
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
 AND tc.constraint_schema = kcu.constraint_schema
JOIN information_schema.constraint_column_usage ccu
  ON tc.constraint_name = ccu.constraint_name
 AND tc.constraint_schema = ccu.constraint_schema
WHERE tc.constraint_type = 'FOREIGN KEY';

This is useful when constraints exist. In legacy databases, imported ERP data, and analytical warehouses, they may not.

That does not mean the relationships are absent. It means they are not explicitly represented in the catalog.

Three common complications follow:

Different names, same identifier. agreement_no may hold values from contract_id.
A hidden intermediate table. Customer and payment may connect only through contract and invoice.
Similar-looking columns, different meanings. Two fields named customer_id may refer to different identifier domains.

The right response is not to ask an LLM to make a more confident guess. It is to collect evidence.

Step 1: Find candidate relationships

Start with declared constraints, if available. Then inspect additional signals:

Column types and compatible formats
Column names and metadata descriptions
Distinct-value counts and uniqueness
Null rates
Value inclusion and overlap
Known query patterns and approved documentation

For example, compare contract IDs with invoice agreement numbers:

-- PostgreSQL example; assumes compatible identifier types.
WITH invoice_keys AS (
    SELECT DISTINCT agreement_no AS id
    FROM billing_invoices
    WHERE agreement_no IS NOT NULL
),
contract_keys AS (
    SELECT DISTINCT contract_id AS id
    FROM sales_contracts
    WHERE contract_id IS NOT NULL
)
SELECT
    COUNT(*) AS distinct_invoice_keys,
    COUNT(c.id) AS matching_keys,
    ROUND(
        COUNT(c.id)::numeric / NULLIF(COUNT(*), 0),
        4
    ) AS inclusion_ratio
FROM invoice_keys i
LEFT JOIN contract_keys c ON i.id = c.id;

An inclusion ratio close to 1.0 is a useful signal. It is not proof of a valid business relationship.

A high ratio could result from shared sequential IDs, overlapping test data, or a mapping that was valid only during a migration. Conversely, a legitimate relationship may have a lower ratio because of delayed synchronization or historical records.

Treat the result as a candidate, not a verdict.

Step 2: Test cardinality before trusting the JOIN

A candidate join can match values and still be analytically unsafe.

Check whether the presumed parent key is unique:

SELECT contract_id, COUNT(*) AS occurrences
FROM sales_contracts
GROUP BY contract_id
HAVING COUNT(*) > 1;

Then inspect the child-side multiplicity:

SELECT agreement_no, COUNT(*) AS invoice_count
FROM billing_invoices
WHERE agreement_no IS NOT NULL
GROUP BY agreement_no
ORDER BY invoice_count DESC
LIMIT 20;

One contract having multiple invoices may be perfectly normal. Multiple rows with the same contract_id in what you assumed was a unique contract master may not be.

This matters because finding a join path and proving a metric's aggregation grain are different tasks.

Suppose one invoice has three cash allocations. If you join invoice rows to allocation rows and then sum invoice_amount, you will count the invoice amount three times.

The join is valid. The metric is wrong.

Step 3: Write the query at the correct grain

For the original question, the amount to sum is received_amount, which already lives at allocation grain. The following is an illustrative PostgreSQL query, not a production-ready definition of “active” or “this quarter”:

SELECT
    c.customer_id,
    c.customer_name,
    SUM(a.received_amount) AS cash_received
FROM crm_customers c
JOIN sales_contracts s
  ON s.customer_ref = c.customer_id
JOIN billing_invoices i
  ON i.agreement_no = s.contract_id
JOIN cash_allocations a
  ON a.invoice_ref = i.invoice_id
WHERE s.contract_status = 'ACTIVE'
  AND a.received_at >= :quarter_start
  AND a.received_at < :next_quarter_start
GROUP BY c.customer_id, c.customer_name;

Before shipping this query, the team still needs to confirm several business rules:

Does ACTIVE mean active today or active when the payment was received?
Are refunds and reversals represented as negative allocations?
Can one invoice cover multiple contracts?
Are payment amounts stored in multiple currencies?
Does the business mean cash received, invoiced revenue, or recognized revenue?

Those are semantic questions. They cannot be answered by a join-discovery algorithm alone.

The engineering task is therefore two-sided: validate the physical relationships and validate the business interpretation.

Step 4: Build a relationship evidence record

Instead of handing an LLM a list of guessed column pairs, an engineering system can produce structured candidates:

{
  "left": "sales_contracts.contract_id",
  "right": "billing_invoices.agreement_no",
  "evidence": {
    "declared_foreign_key": false,
    "type_compatible": true,
    "value_inclusion_ratio": 0.98,
    "parent_key_unique": true
  },
  "status": "candidate_requires_validation"
}

The numbers above are illustrative, not measurements from a real deployment.

A useful workflow is:

Schema metadata
      ↓
Candidate column pairs
      ↓
Value and uniqueness checks
      ↓
Candidate relationship graph
      ↓
Business-rule review
      ↓
Approved join paths for relevant use cases

This keeps the system honest about what it knows. A statistical relationship is evidence to investigate, not automatic authorization to execute business-critical queries.

Where relationship intelligence fits

This is the kind of underlying problem we work on with IntaLink, a data-relationship discovery component of the Arisyn platform. Its documented capabilities include identifying primary/foreign-key relationships and discovering candidate relationships using data-level signals such as inclusion and relationship strength.

Semora, the business-semantic and intelligent-query component, handles terminology, metrics, semantic mappings, ambiguity handling, and SQL generation and validation. In the platform's integration model, Semora manages its own data connections and permissions, maps the relevant data-source ID, and requests relationship information through IntaLink APIs.

That separation is intentional:

Business semantics help determine what the question means.
Relationship discovery helps determine how the relevant data may connect.
Validation and governance determine which interpretation and path are safe to use.

This is not a claim that every inferred relationship is already a production-approved join, or that an ontology can be automatically generated from a schema. Those are separate engineering and governance problems.

A practical test for any enterprise AI data system

If you are evaluating an AI analytics system, don't stop at “Can it generate SQL?”

Give it a question whose answer requires an intermediate table that the user never mentioned. Remove the foreign-key declarations from a test schema. Use inconsistent but realistic column names.

Then ask:

Did it retrieve the bridge table, or silently skip it?
What evidence supports each proposed join?
Did it check key uniqueness and fanout risk?
Did it distinguish an inferred relationship from an approved one?
Did it ask for clarification when the business definition remained ambiguous?

A system that refuses to invent a join may be more useful than one that produces impressive SQL immediately.

The real missing layer

Business ontology is important. It gives AI a vocabulary for how an enterprise thinks about customers, contracts, payments, and revenue.

But a vocabulary alone cannot tell you whether agreement_no really links to contract_id, whether a hidden invoice table is required, or whether a one-to-many join will inflate a metric.

For enterprise AI, meaning and connectivity must meet at the physical data layer.

An ontology tells the model what the business is talking about.

Relationship evidence helps establish whether the model can query the right data without guessing.

Discussion question: In your data warehouse, how many important joins exist only in SQL code or engineers' heads—not in declared foreign keys?

Stop Guessing: Building a Clarification Gate for Text-to-SQL Agents

ArisynData — Wed, 28 Oct 2026 10:58:00 +0000

The SQL can be valid, the query can execute, and the answer can still be wrong—because the agent quietly chose what the business meant.

Imagine an enterprise database with three revenue measures:

Business concept	Physical field	Meaning
Booked revenue	`orders.booked_amount`	Value of accepted orders
Invoiced revenue	`invoices.invoice_amount`	Value billed to customers
Recognized revenue	`revenue_ledger.recognized_amount`	Revenue recognized under Finance rules

A user asks:

Show revenue for Germany last quarter.

A conventional Text-to-SQL agent retrieves a few relevant columns, picks one, generates SQL, and returns €12.8 million.

But which revenue did it calculate?

If the organization has no approved default for the user's context, selecting one is not an answer. It's an unapproved business assumption.

This is a different problem from inventing a nonexistent table. The model may use entirely real data and produce a plausible number with the wrong meaning.

The engineering solution is not simply “tell the LLM to be careful.”

Put a clarification gate between semantic resolution and SQL execution.

1. Treat uncertainty as a first-class state

A useful query planner should return one of three states:

RESOLVED     → enough governed evidence to proceed
AMBIGUOUS    → multiple materially different valid interpretations
UNSUPPORTED  → insufficient evidence or unavailable data

This is a proposed reference architecture, not a claim that any particular vendor implements these exact states.

The distinction matters:

RESOLVED: A Finance-approved default says “Revenue” means recognized revenue for this audience.
AMBIGUOUS: Booked, invoiced, and recognized revenue are all plausible; no governing rule selects one.
UNSUPPORTED: The question requires a margin metric that has no definition or available source.

Do not turn every uncertainty into a follow-up question. An approved default can legitimately resolve ambiguity. Conversely, a high embedding score is not the same as business approval.

2. Resolve concepts before selecting columns

Start with a semantic candidate set, not a single retrieved column.

For example:

{
  "term": "revenue",
  "candidates": [
    {
      "id": "metric.booked_revenue",
      "label": "Booked revenue",
      "owner": "Sales",
      "status": "approved",
      "source": "orders.booked_amount"
    },
    {
      "id": "metric.invoiced_revenue",
      "label": "Invoiced revenue",
      "owner": "Billing",
      "status": "approved",
      "source": "invoices.invoice_amount"
    },
    {
      "id": "metric.recognized_revenue",
      "label": "Recognized revenue",
      "owner": "Finance",
      "status": "approved",
      "source": "revenue_ledger.recognized_amount"
    }
  ]
}

The candidates are illustrative. In a real implementation they must come from the organization's governed semantic definitions and mappings.

Now distinguish relevance from authority:

Vector similarity → Which concepts might the user mean?
Governed policy   → Which concept is authorized as the default?
Clarification     → Which unresolved business choice must the user make?

A retriever can propose candidates. It should not invent the authoritative business definition.

3. A small deterministic gate

Here is a minimal, runnable Python implementation of the decision logic:

from dataclasses import dataclass
from enum import Enum


class Decision(str, Enum):
    RESOLVED = "resolved"
    AMBIGUOUS = "ambiguous"
    UNSUPPORTED = "unsupported"


@dataclass(frozen=True)
class Candidate:
    id: str
    label: str
    approved: bool
    available: bool


@dataclass(frozen=True)
class GateResult:
    decision: Decision
    selected: str | None = None
    options: tuple[str, ...] = ()
    reason: str = ""


def clarification_gate(
    candidates: list[Candidate],
    approved_default_id: str | None = None,
) -> GateResult:
    eligible = [
        c for c in candidates
        if c.approved and c.available
    ]

    if not eligible:
        return GateResult(
            Decision.UNSUPPORTED,
            reason="No approved and available metric"
        )

    if approved_default_id:
        matches = [
            c for c in eligible
            if c.id == approved_default_id
        ]
        if len(matches) == 1:
            return GateResult(
                Decision.RESOLVED,
                selected=matches[0].id,
                reason="Approved default"
            )

    if len(eligible) == 1:
        return GateResult(
            Decision.RESOLVED,
            selected=eligible[0].id,
            reason="Only eligible interpretation"
        )

    return GateResult(
        Decision.AMBIGUOUS,
        options=tuple(c.id for c in eligible),
        reason="Multiple approved interpretations; no default"
    )

Notice what this gate does not do: ask an LLM to assign an arbitrary “93% confidence” and execute above a magic threshold.

It uses explicit eligibility and policy. In production, you would also incorporate the user's role, metric applicability, time validity, dimensions, and data permissions.

Test the gate

revenue = [
    Candidate("booked", "Booked revenue", True, True),
    Candidate("invoiced", "Invoiced revenue", True, True),
    Candidate("recognized", "Recognized revenue", True, True),
]

assert clarification_gate(revenue).decision == Decision.AMBIGUOUS

assert clarification_gate(
    revenue, approved_default_id="recognized"
).selected == "recognized"

assert clarification_gate(
    [Candidate("margin", "Gross margin", False, True)]
).decision == Decision.UNSUPPORTED

assert clarification_gate(
    [Candidate("recognized", "Recognized revenue", True, True)]
).decision == Decision.RESOLVED

These tests establish the gate's control flow, not the correctness of the semantic catalog. That requires separate governance and evaluation.

4. Ask a question the business user can actually answer

When ambiguity is material, don't ask:

Which table should I use: orders, invoices, or revenue_ledger?

Ask:

When you say revenue, do you mean:

Booked revenue — accepted order value

Invoiced revenue — amounts billed

Recognized revenue — revenue under Finance's recognition rules

The system should then bind the selected concept ID to the pending request and resume planning.

{
  "pending_question": "Show revenue for Germany last quarter",
  "status": "awaiting_clarification",
  "slot": "metric",
  "options": [
    "metric.booked_revenue",
    "metric.invoiced_revenue",
    "metric.recognized_revenue"
  ]
}

A clarification is not an invitation to generate another free-form interpretation. It is a constrained business decision.

5. Don't confuse semantic ambiguity with missing data

These situations require different behavior:

Situation	Correct behavior
Two plausible revenue definitions	Clarify unless an approved default applies
One approved definition	Proceed, subject to other checks
No supported metric or source	Abstain; explain what's missing
Missing time range where policy requires one	Ask for the time range
Multiple join paths with different business grain	Validate or clarify the business interpretation
A valid relationship path but unknown cardinality	Don't treat it as safe without checking

The last two are especially important. A relationship-discovery engine may identify candidate joins, but a plausible relationship is not proof that the resulting aggregation is correct.

6. Make SQL generation conditional on resolution

An orchestration pattern:

def handle_question(question, context):
    intent = parse_intent(question)
    candidates = semantic_catalog.lookup(intent.metric)

    policy_default = governance.resolve_default(
        intent=intent,
        user=context.user,
        business_context=context.business_context,
    )

    decision = clarification_gate(candidates, policy_default)

    if decision.decision == Decision.AMBIGUOUS:
        return ask_business_clarification(decision.options)

    if decision.decision == Decision.UNSUPPORTED:
        return explain_unsupported(decision.reason)

    plan = build_query_plan(
        intent=intent,
        metric_id=decision.selected,
    )

    validate_relationships_and_grain(plan)
    validate_permissions(plan)

    return execute_validated_sql(plan)

The functions beyond clarification_gate are architecture pseudocode, not a drop-in SDK. The essential control is real: no query execution before the required business choices have been resolved and validated.

And clarification is not always a single question. The metric may be resolved while the geographic dimension remains ambiguous: customer country, billing country, or sales territory. The gate should operate on every material semantic slot.

7. Preserve what was confirmed

After the user chooses “Recognized revenue,” store the decision in the current request:

{
  "question_id": "q_1042",
  "metric": "metric.recognized_revenue",
  "resolution_source": "user_confirmation",
  "user_confirmed": true
}

If you want to reuse that choice later, do it under an explicit scope and governance policy. A one-time user preference should not silently become a company-wide metric definition.

Also preserve the mapping, version, and SQL used for the final answer so that a later audit can distinguish a user-confirmed interpretation from an automatic default.

8. Evaluate the *decision to clarify*

Most Text-to-SQL benchmarks focus on executable SQL or answer correctness. That misses an important failure mode: a model that answers a deliberately ambiguous question without asking.

Build a benchmark with three labeled groups:

A. RESOLVED:
   One governed meaning or an approved default exists

B. AMBIGUOUS:
   Multiple materially different interpretations, no default

C. UNSUPPORTED:
   No valid definition or accessible data

Measure at least:

Metric	What it measures
Clarification recall	Among cases that require clarification, how often did the agent ask?
False clarification rate	How often did the agent interrupt a resolvable question?
Unjustified assumption rate	How often did it execute using a material unapproved choice?
Unsupported-answer rate	How often did it fabricate an answer when evidence was insufficient?
Business-correct answer rate	After resolution, did the result match the approved business interpretation?

A good system does not maximize clarification frequency. It minimizes unapproved assumptions while keeping interactions efficient.

A useful test pair:

Question 1:
"Show revenue for Germany last quarter."
Expected: Clarify, when no default exists.

Question 2:
"Show recognized revenue for Germany last quarter."
Expected: Proceed, when the definition and source are available.

The two prompts differ by one word, but they should trigger different behavior.

9. Where semantic layers and relationship intelligence fit

This is not just a prompt-engineering problem.

A clarification gate needs a reliable catalog of business concepts, their mappings, and the relationship context that can support a query.

In our work on Semora, the documented platform capabilities include semantic definitions, mappings, terminology, ambiguity detection, clarification judgment, and SQL generation/validation. IntaLink contributes discovered table/field relationship information as context for query planning.

The products have distinct responsibilities: Semora manages its own actual data connections and permissions; it associates a data source with IntaLink by source ID and requests relationship information through APIs.

That combination is relevant to the problem, but the three-state gate, policy-based resolution code, and evaluation framework in this article are proposed engineering patterns, not claims that these exact implementations already ship in either product.

The architectural lesson is bigger than any one vendor:

A semantic layer should not only tell an agent what a business term could mean. It should help establish when the meaning is sufficiently resolved to execute.

10. A production checklist

Before enabling autonomous Text-to-SQL execution, I would verify:

Materially different candidate metrics are preserved until resolved.
Approved defaults are explicit and scoped.
Missing evidence leads to abstention, not fabricated certainty.
Clarifications use business language, not raw schema names.
Relationship paths and aggregation grain are validated separately.
User selections are bound to the pending query.
Semantic decisions are logged for audit.
Evaluation includes deliberately ambiguous questions, not only easy ones.

Final thought

A fluent answer can hide an unapproved assumption. That's why the hardest part of enterprise Text-to-SQL is not always generating SQL.

Sometimes it is deciding whether the system has enough business meaning to generate SQL at all.

The smartest data agent is not the one that answers every question. It's the one that knows when to ask one first.

How Semantic Errors Propagate Through Multi-Step AI Analytics

ArisynData — Mon, 26 Oct 2026 02:24:37 +0000

description: "Valid SQL and correct Python can still produce false business conclusions. Learn to test metric definitions, join fanout, grain, and data context in AI agents."
tags: ai, dataengineering, sql, agents

published: false

How Semantic Errors Propagate Through Multi-Step AI Analytics

A successful query is not proof of a correct analysis. And correct Python cannot repair a broken business assumption.

On October 9, 2026, Databricks announced code execution for Genie One in Public Preview. It's a useful signal: enterprise analytics agents are moving beyond returning SQL results toward multi-step calculations, statistical analysis, and scenarios.

That introduces an important engineering question: what happens when step 1 returns plausible but semantically wrong data, and steps 2–5 faithfully build on it?

Consider:

Question → Metric resolution → Table selection → JOIN → SQL
                                                  ↓
                                         Python analysis
                                                  ↓
                                         Chart / Forecast
                                                  ↓
                                         Business conclusion

Every component can run successfully while the final decision is wrong. I call this analytical error propagation—a general architectural risk, not a claim about a specific defect in Genie One.

A minimal example: correct SQL syntax, inflated revenue

Imagine an order with two order lines and three payment records.

order_lines
order_id | line_id | amount
42       | 1       | 100
42       | 2       |  50

payments
order_id | payment_id | paid_amount
42       | 10         |  60
42       | 11         |  50
42       | 12         |  40

The order-line total is 150. Payment total is also 150.

Now suppose an agent generates:

SELECT SUM(ol.amount) AS revenue
FROM order_lines ol
JOIN payments p ON p.order_id = ol.order_id
WHERE ol.order_id = 42;

The join returns six rows:

2 order lines × 3 payments = 6 joined rows

So SUM(ol.amount) returns 450, not 150. The SQL is valid; the JOIN is legal; the database behaves correctly. But the result is inflated 3×.

The immediate fix for this metric is not to add DISTINCT indiscriminately. It is to use the correct fact grain and avoid joining payment rows if the approved revenue metric does not require them:

SELECT SUM(ol.amount) AS order_line_total
FROM order_lines ol
WHERE ol.order_id = 42;

If the analysis actually needs payment data, aggregate each fact to a compatible grain before combining them:

WITH line_totals AS (
  SELECT order_id, SUM(amount) AS line_total
  FROM order_lines
  GROUP BY order_id
),
payment_totals AS (
  SELECT order_id, SUM(paid_amount) AS payment_total
  FROM payments
  GROUP BY order_id
)
SELECT l.order_id, l.line_total, p.payment_total
FROM line_totals l
JOIN payment_totals p ON p.order_id = l.order_id
WHERE l.order_id = 42;

Important: order_line_total is not automatically the company's recognized revenue. That depends on the approved business definition. Correct grain solves the fanout problem; it does not settle accounting semantics.

How the error spreads

Suppose an agent uses the inflated query as the source for a Python notebook:

revenue = run_sql(generated_sql)  # returns inflated figures

by_country = revenue.groupby("country")["revenue"].sum()
growth = by_country.pct_change()
forecast = forecasting_model.fit(revenue)
summary = llm.summarize(by_country, growth, forecast)

The Python may be bug-free. But now the agent can produce:

Incorrect regional revenue totals
Misleading country rankings
Distorted growth rates (especially when fanout differs by period)
Forecasts trained on invalid baselines
Convincing explanations of a decline or recovery that never occurred

Notice the crucial detail: if the multiplication factor changes between periods, even percentage growth becomes wrong. This is not merely a cosmetic scale error.

Four correctness boundaries

Before giving an agent more analytical autonomy, I would put explicit checks at four boundaries.

1. Business meaning

What does revenue mean?

Possible candidates include booked orders, invoiced amounts, recognized revenue, and cash collected. They may be valid metrics, but not interchangeable.

Represent the selected definition explicitly:

{
  "metric_id": "recognized_revenue",
  "owner": "finance",
  "aggregation": "SUM",
  "time_dimension": "recognition_date",
  "status": "approved"
}

This is an illustrative design object, not a claimed vendor API.

Check: If two metrics could reasonably match the question, clarify or apply a governed default. Do not silently select by column-name similarity.

2. Physical mapping

What fields actually implement the selected business metric?

Business term → Governed metric → Physical columns

Check: Verify source fields, types, filter mappings, and date semantics. “Europe” may mean customer location, sales territory, or legal entity.

3. Relationships and grain

A valid key relationship is not enough to guarantee a valid analytical join.

Check: Examine join cardinality, fact grain, bridge tables, fanout risk, and null behavior.

Useful assertions include:

assert result.row_count >= 0
assert plan.metric_grain == "order"
assert plan.join_path_is_approved
assert plan.fanout_risk == "none"

These fields are conceptual; in a real system, implement the checks against metadata and actual query plans rather than trusting LLM-produced flags.

For example, a practical preflight can compare row counts and distinct business keys before and after a proposed join. Any unexpected multiplicative increase should trigger investigation.

4. Analytical execution

Only after the query foundation is checked should the agent run further calculations.

Check: Preserve the resolved metric, dimensions, filters, time window, and dataset identity across every step. A later forecasting tool should not silently switch from recognized revenue to invoice amounts.

Treat the query plan as a contract

Rather than passing only SQL from one agent stage to another, preserve a structured analytical contract:

{
  "question": "Why did revenue decline in Europe last quarter?",
  "metric": "recognized_revenue",
  "metric_owner": "finance",
  "dimension": "sales_region",
  "period": "previous_fiscal_quarter",
  "fact_grain": "order",
  "join_path": [
    "customer -> order",
    "order -> revenue_fact"
  ],
  "validation": {
    "metric_approved": true,
    "grain_checked": true,
    "join_cardinality_checked": true
  }
}

Again, this is a proposed reference design. The true values must be produced by real validation routines, not asserted by the language model.

A useful pipeline looks like:

Natural-language question
        ↓
Resolve business meaning
        ↓
Map concepts to physical data
        ↓
Retrieve candidate relationships
        ↓
Validate cardinality and grain
        ↓
Build and validate query plan
        ↓
Execute SQL
        ↓
Run Python / statistics / forecasts
        ↓
Return conclusion with evidence

This separates finding a plausible path from proving that the path is safe for the metric.

Where Semora and IntaLink fit

This distinction also explains how complementary components can help.

Semora manages its own data-source connections and query context. Its documented capabilities include business semantics, terminology and mappings, metric/dimension definitions, ambiguity handling, and SQL generation/validation.

IntaLink discovers candidate table/field relationships from metadata and data characteristics, and exposes relationship paths through APIs. Semora maps the corresponding data source by data-source ID, requests relationship information from IntaLink, and continues managing the actual data access and query execution independently.

User question
    ↓
Semora: business semantics + physical mappings
    ↓
IntaLink API: relationship candidates / paths
    ↓
Semora: query construction and validation
    ↓
Query result

This is a useful foundation for trustworthy querying, not a claim that either platform already provides Genie-style sandboxed Python execution, automated forecasting, or complete analytical error-propagation detection.

The missing engineering work for a more autonomous analytics agent is to turn relationship evidence into validated analytical plans, with explicit grain and metric checks.

Five tests I would run before production

Test	Deliberate trap	What success looks like
Metric ambiguity	`invoice_amount` and `recognized_amount` both exist	Correct governed metric or clarification
Join fanout	Two order lines and three payments	No multiplication of revenue
Grain mismatch	Customer metric built from line-level facts	Correct aggregation grain
Multi-step consistency	SQL result feeds a forecast	Same metric, filters, and period throughout
Correction and rerun	User corrects a metric choice	Recompute from source; don't just rewrite the summary

A useful sixth test is counterfactual consistency: replace a wrong relationship with the correct one and check whether every downstream chart, statistic, and narrative is regenerated rather than cached from the previous run.

Don't measure only execution success

For production analytics agents, I would track:

SQL execution success
Metric-resolution accuracy
Join/cardinality validation pass rate
Grain-validation coverage
Multi-step context consistency
End-to-end answer correctness

Execution success is a necessary operational metric. It is not a business-correctness metric.

Final thought

As AI agents become capable of running code, forecasting, and explaining trends, it's tempting to focus on the sophistication of the analysis engine.

But the dangerous failure mode may be much simpler: the agent starts with the wrong metric or the wrong join, then performs increasingly sophisticated operations on incorrect data.

Better reasoning cannot repair an incorrect understanding of the data.

The engineering priority is to validate business meaning and relational structure before amplifying their outputs through multi-step analysis.

ersioning Business Semantics for Enterprise AI

ArisynData — Thu, 24 Sep 2026 23:05:00 +0000

Your SQL can be perfectly reproducible while your business meaning is not.

Suppose a user asks:

What was revenue in Q1?

Your data agent resolves Revenue, generates valid SQL, executes it successfully, and returns a number.

Now suppose Finance changed the definition of Revenue in June.

The old definition was:

Revenue v3
=
Recognized Revenue

The new definition is:

Revenue v4
=
Recognized Revenue
- Approved Adjustments

When the same user asks about Q1 in September, which definition should the agent use?

That is not an SQL problem.

It is a semantic versioning problem.

Enterprise data agents need more than a mapping from:

Business Term → Metric

They increasingly need:

Business Term
      ↓
Metric
      ↓
Version
      ↓
Effective Time
      ↓
Approval State
      ↓
Physical Mapping

If business meaning changes over time, the semantic layer needs a lifecycle.

Why a Static Semantic Layer Breaks Down

A basic semantic registry might store:

{
  "id": "revenue",
  "name": "Revenue",
  "field": "finance_revenue.recognized_amount",
  "aggregation": "SUM"
}

That works until the definition changes.

If the object is simply overwritten, you lose important information:

What was the previous definition?
When did the change become effective?
Who approved it?
Which answers used the old definition?
Should historical periods use the old or new logic?

A production semantic object should therefore be treated more like a versioned artifact than a mutable label.

Model Semantic Objects as Immutable Versions

One useful pattern is to separate the stable identity of a business concept from its versions.

Metric Identity
revenue
   │
   ├── v1
   ├── v2
   ├── v3
   └── v4

For example:

{
  "metric_id": "revenue",
  "version": 4,

  "definition": "Recognized revenue after approved adjustments",

  "expression": {
    "type": "formula",
    "value": "recognized_revenue - approved_adjustments"
  },

  "owner": "finance",

  "status": "published",

  "effective_from": "2026-06-01",

  "effective_to": null
}

Do not mutate v3 into v4.

Create a new version.

That preserves historical meaning.

Separate Version Time From Business Time

This is where implementation gets interesting.

There are at least two relevant timelines.

System time

When was the semantic definition created or published?

created_at
published_at
deprecated_at

Effective business time

When is the definition supposed to apply?

effective_from
effective_to

These are not always the same.

Finance might approve a new metric definition on June 10 but make it effective from June 1.

So a semantic object may need:

{
  "published_at": "2026-06-10T09:00:00Z",
  "effective_from": "2026-06-01",
  "effective_to": null
}

This distinction is essential for historical queries.

Resolve Semantics With Time Context

A semantic resolver should not simply do:

metric = registry.get("revenue")

It needs temporal context.

Conceptually:

metric = registry.resolve(
    metric_id="revenue",
    effective_at=query_time
)

For a question like:

What was revenue in January?

the pipeline becomes:

Question
      ↓
Intent Resolution
      ↓
Metric = Revenue
      ↓
Time Context = January
      ↓
Applicable Semantic Version
      ↓
Physical Mapping
      ↓
Query Plan

This is version-aware semantic resolution.

But Historical Queries Have Two Meanings

There is an important complication.

When someone asks:

What was Revenue in January?

they may mean:

Historical interpretation

Calculate Revenue using the definition that was valid in January.

or:

Restated interpretation

Calculate January data using today's Revenue definition.

These can produce different numbers.

So your semantic system may need an explicit policy:

{
  "historical_metric_policy": "as_was"
}

or:

{
  "historical_metric_policy": "restated"
}

In some environments, the right answer may depend on the metric itself.

If the system cannot determine the intended policy safely, clarification may be better than silently guessing.

Comparison Queries Are Harder

Now consider:

Compare Q1 and Q3 Revenue.

Suppose the definition changed in Q2.

If you use each period's historical definition:

Q1 → Revenue v3
Q3 → Revenue v4

the comparison may not be semantically consistent.

If you restate both periods using v4:

Q1 → Revenue v4
Q3 → Revenue v4

the comparison is consistent, but it no longer represents exactly what the organization reported in Q1.

This decision belongs in business governance.

The LLM should not invent the policy.

Version More Than Metrics

Metrics are the obvious case, but other semantic objects can change too.

Business terms

"Active Customer"

may change definition.

Semantic mappings

"Product Code"
→ product_master.material_id

may later become:

"Product Code"
→ product_dim.product_code

Dimensions

Region
Business Unit
Product Category
Customer Segment

can change structure.

Business rules

Valid Order
Eligible Customer
Completed Transaction

can change inclusion logic.

If a change can alter analytical results, it should be traceable.

A Generic Semantic Version Model

You can model semantic objects with a shared envelope:

{
  "object_id": "metric.revenue",
  "object_type": "metric",
  "version": 4,

  "lifecycle": {
    "status": "published",
    "owner": "finance",
    "approved_by": "finance_governance"
  },

  "validity": {
    "effective_from": "2026-06-01",
    "effective_to": null
  },

  "provenance": {
    "created_at": "2026-05-28T08:12:00Z",
    "published_at": "2026-06-10T09:00:00Z",
    "previous_version": 3
  },

  "payload": {
    "definition": "...",
    "expression": "...",
    "physical_mapping": "..."
  }
}

The payload differs by semantic object type.

The lifecycle and provenance model can remain consistent.

Add Lifecycle States

Versioning alone is not governance.

A new definition should not automatically become production truth.

A useful lifecycle might be:

Draft
  ↓
Review
  ↓
Validated
  ↓
Published
  ↓
Deprecated

This prevents a work-in-progress definition from being used by production agents.

For example:

{
  "metric_id": "gross_margin",
  "version": 5,
  "status": "draft"
}

should not automatically replace:

{
  "metric_id": "gross_margin",
  "version": 4,
  "status": "published"
}

in production query resolution.

Controlled Rollout Matters

Sometimes a semantic change needs to be tested before becoming the default.

Conceptually:

Gross Margin v5
      ↓
Test Workspace
      ↓
Selected Users
      ↓
Validation
      ↓
Production

This is similar to feature rollout in software systems.

The semantic definition itself becomes a governed production artifact.

A platform such as Semora already treats business semantic definitions, mappings, metrics and dimensions as governed objects alongside version and controlled-release management. That is the right architectural direction: meaning needs lifecycle management, not just storage.

Define Material vs. Non-Material Changes

Do not create a new analytical version for every edit.

A typo fix:

"recgonized revenue"
→
"recognized revenue"

does not change analytical behavior.

A formula change does.

A practical classification:

NON-MATERIAL
- spelling
- description wording
- examples
- documentation

MATERIAL
- formula
- aggregation
- source field
- semantic mapping
- filter rule
- inclusion/exclusion logic
- hierarchy

Only material changes need to create a new analytical version.

You can still audit non-material edits separately.

Compute a Semantic Diff

When a new version is created, show what changed.

Example:

Revenue v3 → v4

- expression:
-   recognized_revenue

+ expression:
+   recognized_revenue - approved_adjustments

+ effective_from:
+   2026-06-01

For mappings:

Product Code v1 → v2

- product_master.material_id
+ product_dim.product_code

A semantic diff is much easier to review than comparing two large JSON objects manually.

Dependency Analysis Before Publishing

Semantic objects rarely exist alone.

Suppose:

Gross Margin

depends on:

Revenue

If Revenue changes, downstream metrics may be affected.

Represent dependencies:

Revenue
   ↓
Gross Profit
   ↓
Gross Margin

Before publishing Revenue v4:

Change
  ↓
Dependency Graph
  ↓
Impacted Metrics
  ↓
Validation
  ↓
Publish

This is where semantic governance begins to resemble software dependency management.

Keep Semantic Versions in the Query Plan

Once a version is resolved, preserve it.

A semantic query plan should not contain only:

{
  "metric": "revenue"
}

Prefer:

{
  "metric": {
    "id": "revenue",
    "version": 4
  }
}

Then downstream stages know exactly which meaning was selected.

Persist Versions Into Answer Lineage

The final answer should preserve the semantic version that produced it.

{
  "answer_id": "ans_9281",

  "question": "What was revenue in Germany in July?",

  "semantic_evidence": {
    "metric_id": "revenue",
    "metric_version": 4,
    "mapping_version": 2
  },

  "query_id": "q_18273"
}

Six months later, you can reconstruct the answer even if Revenue has moved to v5.

This is reproducible meaning.

Why SQL Versioning Is Not Enough

Imagine storing the generated SQL:

SELECT SUM(recognized_amount - adjustment_amount)
FROM finance_revenue;

That tells you what executed.

But it does not necessarily tell you:

Why this expression represented Revenue
Which business definition authorized it
Who owned that definition
When it became effective

SQL provenance and semantic provenance solve different problems.

Production AI analytics needs both.

Cache Carefully

Semantic versioning also affects caching.

A cache key like:

hash(question)

is unsafe if the semantic definition changes.

A better cache identity may include:

Question
+
Semantic Version
+
Data Source Version / Freshness
+
Policy Context

Conceptually:

cache_key = hash(
    question,
    metric_version,
    mapping_version,
    policy_version
)

Otherwise the system can return an answer generated under outdated semantics.

Version-Aware Retrieval

If semantic retrieval uses embeddings, versioning creates another issue.

Suppose both:

Revenue v3
Revenue v4

exist in the semantic index.

The retriever should not blindly return whichever vector is closest.

Retrieval needs governance filters:

semantic_search(
    term="revenue",
    status="published",
    effective_at=query_time
)

Similarity identifies candidates.

Governance determines which candidate is valid.

Audit Every Semantic Resolution

For production use, log:

{
  "question_id": "qst_182",
  "term": "revenue",
  "resolved_object": "metric.revenue",
  "resolved_version": 4,
  "effective_at": "2026-07-15",
  "resolution_source": "semantic_registry"
}

This makes semantic decisions observable.

If an answer is disputed, you can inspect exactly which definition the system used.

A Reference Resolution Pipeline

Putting the pieces together:

Natural-Language Question
          ↓
Intent Resolution
          ↓
Business Concept
          ↓
Extract Time Context
          ↓
Semantic Registry
          ↓
Filter:
- published
- effective at time
- allowed for user
          ↓
Resolve Version
          ↓
Resolve Physical Mapping
          ↓
Relationship Context
          ↓
Semantic Query Plan
          ↓
SQL
          ↓
Answer
          ↓
Answer Lineage

The LLM does not decide which semantic version is authoritative.

The governed semantic layer does.

What to Test

Semantic versioning needs its own test cases.

Current definition

Question:
What is Revenue this month?

Expected:
Latest published effective version

Historical definition

Question:
What was Revenue in January?

Expected:
Historical version or governed restatement policy

Cross-version comparison

Question:
Compare Q1 and Q3 Revenue.

Expected:
Explicit comparison policy

Draft version

Revenue v5 = draft

Expected:
Production agent does not use it

Deprecated mapping

Product Code v1 = deprecated

Expected:
New queries use the published replacement

These are semantic correctness tests, not SQL syntax tests.

What to Measure

Useful operational metrics include:

% queries using published semantic objects
% answers with semantic version lineage
Semantic resolution failures
Queries affected by semantic changes
Deprecated-version usage
Average approval time for material changes

These help teams operate the semantic layer as production infrastructure.

Final Thoughts

Enterprise AI cannot treat business meaning as a static prompt.

Metrics change.

Mappings change.

Business rules change.

Dimensions change.

And historical questions still need to remain explainable and reproducible.

The architecture therefore needs to move from:

Term → Definition

to:

Term
 ↓
Governed Semantic Object
 ↓
Version
 ↓
Effective Time
 ↓
Approval State
 ↓
Physical Mapping
 ↓
Query

The key engineering principle is:

Version the meaning, not just the code that implements it.

Because a perfectly reproducible SQL query can still produce the wrong business answer if the system cannot reproduce the meaning that was valid when the question was asked.

Building Answer Lineage for Enterprise Data Agents

ArisynData — Tue, 15 Sep 2026 06:57:53 +0000

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

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

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

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"
}

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"
  }
}

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

Example:

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

Later:

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

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"
}

If ambiguity exists, preserve it:

{
  "metric": {
    "status": "ambiguous",
    "candidates": [
      "recognized_revenue",
      "invoice_amount"
    ]
  }
}

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"
  }
}

The lineage should preserve:

User Language
      ↓
Business Concept
      ↓
Physical Mapping

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"
    }
  ]
}

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"
}

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
}

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]]
  }
}

Then link the user-facing answer:

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

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

Reproducibility depends on historical identity, not only current configuration.

Useful provenance fields include:

Source
Version
Owner
Status
Timestamp
Validation State

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

A generated explanation describes.

Evidence artifacts verify.

Design an Evidence API

A simple API might be:

GET /answers/{answer_id}/evidence

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": "..."
}

The UI can then progressively disclose detail.

A business user sees:

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

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

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?

Answer lineage starts becoming an impact-analysis system.

Debugging Becomes Faster

Suppose:

Expected: €16.9M
Returned: €18.6M

Without lineage:

Reproduce
Inspect prompt
Inspect SQL
Guess

With lineage:

Intent        ✓
Metric        ✓
Dimension     ✓
Time Range    ✓
Relationship  ✕

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"
  }
}

A user-facing trust summary can then show:

Semantic Context       ✓
Relationship Context   ✓
Query Validation       ✓
Execution              ✓

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

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

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

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

That is Answer Lineage.

It turns:

"Revenue was €18.6M."

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.

Why RBAC Alone Isn't Enough for Enterprise Data Agents

ArisynData — Thu, 10 Sep 2026 02:21:17 +0000

A user can be blocked from a sensitive column and still receive sensitive information derived from data they are allowed to access.

That changes the authorization problem for enterprise data agents.

Traditional access control asks:

Can this user read this database object?

An AI analytics system also needs to ask:

Is this user allowed to receive what the system can infer from those objects?

Consider a simple example.

A user cannot access:

employee.salary

But the same user can access:

department.total_cost
department.employee_count

A capable data agent can derive:

estimated_average_salary
=
department.total_cost
/
department.employee_count

No forbidden salary column was queried.

The database permission model may have worked perfectly.

The answer may still disclose information the policy intended to protect.

This is why:

Table access ≠ Answer access.

RBAC Still Matters

This is not an argument against role-based access control.

RBAC remains a critical foundation.

A typical model might define:

Role: Sales Manager

ALLOW:
  customer
  sales_order
  product
  regional_revenue

DENY:
  employee.salary
  payroll
  compensation_detail

At the database layer, those controls should continue to be enforced.

The problem is that an AI agent introduces several stages above the database:

Natural Language
      ↓
Intent Resolution
      ↓
Semantic Resolution
      ↓
Context Retrieval
      ↓
Relationship Planning
      ↓
SQL Generation
      ↓
Execution
      ↓
Answer Generation

Authorization therefore has more surfaces than a traditional application issuing predefined SQL.

The Inference Gap

Let's formalize the salary example.

Suppose policy says:

{
  "resource": "employee.salary",
  "action": "read",
  "effect": "deny"
}

But:

{
  "resource": "department.total_cost",
  "action": "read",
  "effect": "allow"
}

and:

{
  "resource": "department.employee_count",
  "action": "read",
  "effect": "allow"
}

The agent creates:

f(total_cost, employee_count)
→ estimated_average_salary

Every input is authorized.

The derived concept may not be.

Call this the inference gap:

Authorized Inputs
      ↓
Reasoning / Aggregation
      ↓
Restricted Information

Traditional object-level authorization may not express that boundary.

Add Semantic Authorization

Users ask questions in business concepts.

So policy should increasingly understand business concepts too.

Instead of governing only:

employee.salary

define a semantic concept:

{
  "concept": "employee_compensation",
  "direct_access": "deny",
  "derived_access": "deny"
}

Now a request such as:

What is the average salary of the engineering team?

can be resolved first:

Intent
→ Employee Compensation

Then evaluated:

Employee Compensation
→ DENY

before SQL generation begins.

This is semantic authorization.

It lets policy operate at the same abstraction level as the user's question.

Authorization Should Start Before SQL Generation

A common architecture is:

Question
   ↓
Retrieve Schema
   ↓
Generate SQL
   ↓
Database Permission Check
   ↓
Execute

The problem is that the model may already have received context it should not use.

A stronger pipeline is:

Question
      ↓
Identity
      ↓
Intent Resolution
      ↓
Semantic Policy
      ↓
Authorized Context
      ↓
Authorized Relationship Graph
      ↓
Query Planning
      ↓
SQL Generation
      ↓
Database Enforcement
      ↓
Answer Policy

The important change is:

Authorization constrains reasoning context before it constrains execution.

Build an Authorized Context Resolver

Imagine the enterprise semantic layer contains:

Revenue
Gross Margin
Customer Risk
Payroll Cost
Employee Compensation
Product Profitability

A generic context retriever might return all concepts semantically related to the question.

That is risky.

Instead:

Candidate Context
      ↓
Identity + Policy
      ↓
Authorized Context
      ↓
LLM

Pseudocode:

def resolve_authorized_context(question, user):
    intent = resolve_intent(question)

    candidates = retrieve_semantic_context(intent)

    allowed = [
        item for item in candidates
        if policy.can_use(user, item)
    ]

    return allowed

The real implementation will need stronger policy semantics, but the architectural boundary matters.

Do not give the model unauthorized context and hope the final SQL check fixes everything.

Relationships Need Authorization Too

Suppose relationship discovery finds:

Employee
   ↓
Department
   ↓
Cost Center
   ↓
Financial Cost

The path is structurally valid.

But a Sales user may not be allowed to traverse it.

So distinguish:

Trusted Relationship

from:

Authorized Relationship

A relationship object could carry policy metadata:

{
  "source": "department",
  "target": "cost_center",
  "status": "trusted",

  "policy": {
    "allowed_roles": [
      "finance",
      "hr"
    ]
  }
}

Then query planning uses a user-specific graph:

def authorized_graph(graph, user):
    return graph.filter(
        lambda edge: policy.can_traverse(user, edge)
    )

This gives us another useful rule:

Valid relationship ≠ Authorized relationship.

Query Planning Should Operate on the Authorized Graph

Assume the full relationship graph contains:

Customer ─ Order ─ Payment
Employee ─ Department ─ Cost Center
Supplier ─ Contract ─ Pricing

For a Sales user:

Customer ─ Order ─ Payment

may be available.

But:

Employee ─ Department ─ Cost Center

may be removed from the planning graph.

The SQL generator never sees that path.

That is safer than generating the query first and rejecting it later.

Direct Access and Derived Access Are Different Policies

Some concepts need two policy dimensions.

Example:

{
  "concept": "customer_credit_risk",

  "direct_access": {
    "roles": ["risk", "finance"]
  },

  "derived_access": {
    "roles": ["risk", "finance"]
  }
}

Why distinguish them?

Because an organization might allow:

Department Cost

but restrict:

Individual Compensation

or permit individual operational metrics while restricting a derived risk score.

The derived concept may have different sensitivity from its inputs.

Answer-Level Policy Is the Final Boundary

Even with pre-query authorization, a final result check is useful.

The pipeline may produce:

SQL Valid               ✓
Database Access         ✓
Relationship Valid      ✓
Execution               ✓
Answer Policy           ✕

The system should not return the result.

Conceptually:

result = execute(sql)

answer_concepts = classify_result_semantics(
    question=question,
    plan=query_plan,
    result=result
)

for concept in answer_concepts:
    if not policy.can_receive(user, concept):
        raise PolicyDenied(concept)

This is not simply SQL validation.

It is answer validation.

Why Result Classification Is Hard

A result rarely arrives labeled:

"This is sensitive compensation information."

The system needs evidence from:

Original Intent
Resolved Semantic Concepts
Selected Metrics
Query Plan
Aggregations
Relationship Path
Output Columns

That means answer governance should not be implemented as a disconnected moderation step.

It should preserve semantic provenance throughout query execution.

Preserve a Semantic Query Plan

Instead of storing only SQL:

SELECT ...

store a structured plan:

{
  "intent": "average employee compensation",

  "concepts": [
    "employee_compensation"
  ],

  "metrics": [
    "department_total_cost",
    "employee_count"
  ],

  "derived_metric": {
    "name": "estimated_average_salary",
    "expression": "department_total_cost / employee_count"
  },

  "relationships": [
    "employee -> department"
  ]
}

Now authorization has something meaningful to evaluate.

This is another reason production Text-to-SQL should not be treated as:

Question → SQL

The intermediate query plan matters.

Add Policy to the Query Plan

A policy-aware plan might look like:

{
  "concept": "employee_compensation",

  "authorization": {
    "semantic_access": false,
    "data_access": true,
    "relationship_access": true,
    "answer_access": false
  },

  "decision": "deny"
}

The system can stop before execution.

For a different user:

{
  "role": "HR Partner",
  "decision": "allow"
}

The same natural-language question can therefore produce different authorized query plans.

Policy-Aware Clarification

Authorization can also affect clarification.

Suppose a user asks:

Show employee cost.

The system resolves two candidates:

Department Operating Cost
Employee Compensation

The user is authorized for the first but not the second.

A naive clarification UI might reveal both options.

That itself may leak sensitive semantic structure.

Instead, candidate generation should be policy-filtered:

Candidate Concepts
      ↓
Policy Filter
      ↓
Allowed Clarification Options

Authorization therefore affects not only execution but also what the system is allowed to discuss.

Explain Denials in Business Terms

A natural-language interface should not return:

SQLSTATE 42501
permission denied

when the real issue is semantic.

A better response might be:

This question would reveal restricted employee compensation information. You can query department-level operating cost, but not derived salary information.

This improves both security and user experience.

The system can explain:

What category is restricted
What level is allowed
What alternative question is permitted

without exposing sensitive details.

Don't Try to Solve Every Possible Inference

There is an important practical limit.

If two harmless numbers can theoretically be combined into sensitive information, trying to enumerate every possible derivation can become impossible.

So focus governance on high-impact semantic concepts.

Examples:

Compensation
Protected Personal Information
Credit Risk
Confidential Pricing
Sensitive Forecasts
Health Information

Then model known derivation patterns and business policies around those concepts.

The goal is not mathematical prevention of all inference.

It is business-risk-aware governance.

A Practical Policy Model

One possible abstraction:

concept: employee_compensation

sensitivity: restricted

direct_access:
  allow:
    - hr
    - executive

derived_access:
  allow:
    - hr
    - executive

related_metrics:
  - salary
  - bonus
  - estimated_average_salary

restricted_derivations:
  - department_cost / employee_count

Another:

relationship:
  source: employee
  target: cost_center

trusted: true

traverse:
  allow:
    - finance
    - hr

This makes policy part of semantic and relationship metadata rather than an afterthought.

Audit the Reasoning Path

When a query is allowed or denied, log why.

Example:

{
  "user": "sales_manager",
  "question": "What is the average salary in engineering?",

  "resolved_intent": "employee_compensation",

  "policy_decision": "deny",

  "reason": "derived_access_not_allowed"
}

For allowed queries, record:

Semantic concepts used
Relationship path
Metrics selected
Policy decisions
Generated SQL

This creates an audit trail that is much more useful than logging SQL alone.

Test Authorization With Adversarial Questions

Enterprise data-agent security testing should include inference cases.

For example:

Direct request

Show individual employee salaries.

Expected:

DENY

Derived request

Divide engineering payroll cost by headcount.

Expected:

DENY

Allowed aggregate

Show total engineering operating cost.

Expected:

ALLOW

Relationship traversal

Join employee records with cost-center financials.

Expected:

ROLE DEPENDENT

These tests reveal whether the system governs meaning or only columns.

What to Measure

Useful authorization metrics could include:

Direct Sensitive Query Block Rate
Derived Sensitive Query Block Rate
False Denial Rate
Unauthorized Relationship Block Rate
Policy Explanation Accuracy

A secure system that denies every complex query is not useful.

The goal is:

Maximum useful access
within authorized semantic boundaries

A Reference Architecture

Putting everything together:

                 Natural Language
                        ↓
                     Identity
                        ↓
                Intent Resolution
                        ↓
                Semantic Policy
                        ↓
               Authorized Context
                        ↓
          Authorized Relationship Graph
                        ↓
                 Query Planning
                        ↓
                Policy-Aware Plan
                        ↓
                 SQL Generation
                        ↓
              Database Enforcement
                        ↓
                    Execution
                        ↓
              Answer Policy Check
                        ↓
             Return / Explain / Deny

RBAC remains underneath this architecture.

The new layers do not replace database security.

They extend governance into the reasoning process.

Final Thoughts

Enterprise data agents make databases easier to use because users no longer need to know schemas or SQL.

That abstraction is powerful.

It also means users can ask for information without knowing which fields, tables, joins, or calculations the agent will use.

So authorization has to follow the same abstraction upward.

From:

Who can access this table?

to:

Who can use this business concept?

and finally:

Who can receive this derived answer?

That is why RBAC alone is not the whole solution for AI-powered analytics.

Keep RBAC.

Keep row- and column-level controls.

But add governance around:

Intent
Semantics
Relationships
Derivations
Answers

Because:

Table access ≠ Answer access.

And:

Valid relationship ≠ Authorized relationship.

A production data agent should not only know how to find an answer.

It should know whether it is allowed to reveal it.

The Semantic Cold Start Problem in Enterprise Data Agents

ArisynData — Mon, 07 Sep 2026 22:00:00 +0000

Connecting an AI agent to a database is easy. Bootstrapping enough enterprise context for it to use that database correctly is the real engineering problem.

A new data agent can inspect a schema almost immediately:

tables
columns
data types
primary keys
sample values

Then a user asks:

What was revenue by product code last quarter?

The schema alone does not tell the agent:

What does "Revenue" mean here?
Which "product code" does the business use?
Which fields are authoritative?
How should the required tables be connected?
Which relationship candidates are trusted?

This gap between data access and business understanding is what I call the semantic cold start problem.

For production data agents, reducing that cold-start cost may matter as much as improving the underlying LLM.

1. Model the Problem as Knowledge Bootstrapping

A useful bootstrap pipeline looks like:

Connect Data
     ↓
Schema Discovery
     ↓
Relationship Discovery
     ↓
Semantic Candidate Generation
     ↓
Existing Knowledge Import
     ↓
Human Confirmation
     ↓
Runtime Clarification
     ↓
Knowledge Persistence
     ↓
Reuse

The important point is that no single step is expected to magically understand the enterprise.

Instead, the system progressively converts different forms of evidence into reusable context.

2. Start With a Machine-Readable Inventory

The first layer is deterministic.

For each data source, collect technical metadata such as:

{
  "table": "finance_revenue",
  "columns": [
    {
      "name": "recognized_amount",
      "type": "decimal"
    },
    {
      "name": "recognition_date",
      "type": "date"
    },
    {
      "name": "order_id",
      "type": "bigint"
    }
  ]
}

This makes the agent schema-aware.

But schema awareness is only Level 1.

It tells the system what exists, not what the business means.

3. Discover Relationship Candidates

The next problem is connectivity.

Enterprise databases often have incomplete foreign-key definitions, so relationship discovery may need to combine several signals:

Declared constraints
Column-name similarity
Compatible data types
Uniqueness
Value overlap
Value inclusion
Existing metadata

For example:

account.customer_id
customer.customer_id

may be a strong relationship candidate even if no FK constraint exists.

A candidate could be represented as:

{
  "source": "account.customer_id",
  "target": "customer.customer_id",
  "evidence": {
    "type_compatible": true,
    "name_similarity": 0.91,
    "inclusion": 0.98,
    "target_uniqueness": 0.99
  },
  "confidence": 0.96,
  "status": "candidate"
}

The important distinction is:

discovered relationship
≠
trusted relationship

Discovery reduces manual work.

Validation turns a candidate into enterprise knowledge.

4. Generate Semantic Candidates, Not Semantic Truth

Now consider the business term:

Product Code

The schema may contain:

product_master.material_id
inventory.item_code
sales_detail.sku_no
product_dim.prod_master_id

An LLM or semantic retrieval system can rank candidates:

{
  "term": "Product Code",
  "candidates": [
    {
      "field": "product_master.material_id",
      "score": 0.88
    },
    {
      "field": "inventory.item_code",
      "score": 0.84
    },
    {
      "field": "sales_detail.sku_no",
      "score": 0.79
    }
  ]
}

This is useful.

But it should not automatically become:

Product Code = material_id

A semantic candidate is evidence.

A business mapping is a governed decision.

That separation prevents model confidence from being mistaken for organizational truth.

5. Import What the Enterprise Already Knows

Cold start should not mean starting from zero.

Useful knowledge may already exist in:

Business glossaries
Metric definitions
Data catalogs
BI models
Dashboard logic
Documentation
Existing SQL

For example, Finance may already define:

metric:
  name: Revenue

definition:
  Recognized revenue for financial reporting

source:
  table: finance_revenue
  field: recognized_amount

time_field:
  recognition_date

If this definition already exists, the agent should reuse it instead of asking users to redefine Revenue.

A good bootstrap system therefore needs both:

Discovery

and:

Import

6. Use Humans for Confirmation, Not Exhaustive Modeling

Some mappings cannot be inferred safely.

Instead of asking a data team to document everything manually, let the system narrow the decision first.

Example:

"Product Code" has 3 likely mappings:

1. product_master.material_id
2. inventory.item_code
3. sales_detail.sku_no

A domain expert selects:

product_master.material_id

Persist the decision:

{
  "knowledge_type": "semantic_mapping",
  "term": "Product Code",
  "target": "product_master.material_id",
  "source": "human_confirmation",
  "status": "trusted",
  "confidence": 1.0
}

This changes the human role from:

Author everything

to:

Review high-value uncertainty

That is much more scalable.

7. Runtime Clarification Is Also a Learning Channel

Some ambiguity only appears when users ask real questions.

Suppose:

Show revenue by product code.

The semantic resolver returns:

{
  "term": "Product Code",
  "status": "ambiguous",
  "candidates": [
    "Master Product Code",
    "SKU Code",
    "Internal Product ID"
  ]
}

The agent asks:

Which product code do you mean?

The user chooses:

Master Product Code

A weak implementation uses the answer only for the current query.

A stronger implementation creates a knowledge event:

{
  "event": "semantic_confirmation",
  "user_phrase": "product code",
  "resolved_concept": "Master Product Code",
  "physical_field": "product_master.material_id",
  "source": "runtime_clarification"
}

Now the interaction improves future queries.

Every Correction Should Make the Next Question Cheaper

This is the core learning property.

Without persistence:

User A asks
   ↓
System clarifies
   ↓
Answer

User B asks same concept
   ↓
System clarifies again

With reusable knowledge:

User A asks
   ↓
System clarifies
   ↓
Knowledge stored
   ↓
User B asks
   ↓
Known mapping
   ↓
Answer

The second query requires less reasoning and less human effort.

That is what enterprise learning should look like.

8. Separate Runtime State From Persistent Knowledge

Not every user choice should become global enterprise truth.

Suppose a user says:

Use invoice amount for this analysis.

That may be a query-specific preference, not a new definition of Revenue.

So distinguish:

Session State
User Preference
Workspace Default
Governed Enterprise Knowledge

A useful persistence policy might look like:

def persist_resolution(resolution):
    if resolution.scope == "query":
        return save_session_state(resolution)

    if resolution.source == "user_selection":
        return propose_workspace_knowledge(resolution)

    if resolution.approved_by_domain_owner:
        return save_governed_knowledge(resolution)

The exact workflow varies, but the principle matters:

Learning requires scope and governance.

Otherwise one user's temporary choice can corrupt the shared semantic layer.

9. Track Provenance

Every knowledge object should answer:

Where did this come from?
Who confirmed it?
When was it created?
How confident are we?
Is it still active?

Example:

{
  "term": "Revenue",
  "target": "finance_revenue.recognized_amount",
  "source": "finance_metric_catalog",
  "owner": "finance",
  "status": "active",
  "confidence": 1.0,
  "version": 4
}

For discovered relationships:

{
  "relationship": "account.customer_id -> customer.customer_id",
  "source": "relationship_discovery",
  "confirmed_by": "data_team",
  "status": "trusted"
}

Without provenance, accumulated knowledge becomes difficult to trust.

10. Build a Context Resolver Above the Raw Knowledge

The query runtime should not dump every known object into the prompt.

Instead:

User Question
      ↓
Intent Extraction
      ↓
Context Resolver
      ↓
Relevant Business Terms
Relevant Metrics
Relevant Fields
Relevant Relationships
      ↓
Compact Query Context
      ↓
SQL Generation

For:

Revenue by product code last quarter

the context resolver might return:

{
  "metric": {
    "name": "Revenue",
    "field": "finance_revenue.recognized_amount"
  },

  "dimension": {
    "name": "Product Code",
    "field": "product_master.material_id"
  },

  "relationship_path": [
    "product_master -> sales_order",
    "sales_order -> finance_revenue"
  ],

  "time_range": "last_quarter"
}

The model reasons over a smaller, better-defined space.

11. Treat Knowledge Acquisition as an Event Stream

A useful architecture is to treat semantic learning as events:

schema_discovered
relationship_candidate_found
relationship_confirmed
metric_imported
semantic_mapping_confirmed
clarification_resolved
mapping_deprecated

For example:

{
  "event_type": "semantic_mapping_confirmed",
  "timestamp": "2026-09-07T10:30:00Z",
  "payload": {
    "term": "Product Code",
    "field": "product_master.material_id"
  }
}

This gives you:

Auditability
Versioning
Rollback
Analytics
Learning metrics

It also avoids hiding important enterprise knowledge inside opaque prompt history.

12. Measure Time to Intelligence

Connection latency is easy to measure:

Database connected in 4 minutes.

But a better enterprise metric is:

Time to Intelligence

Possible operational measurements include:

Time until first trusted answer

% of common business terms resolved

% of critical metrics governed

% of required relationships trusted

Clarification rate

Repeated clarification rate

Manual confirmations per 100 queries

A healthy system should improve over time.

For example:

Week 1
Semantic coverage: 42%
Repeated clarification: 31%

Month 1
Semantic coverage: 68%
Repeated clarification: 17%

Month 3
Semantic coverage: 84%
Repeated clarification: 6%

The exact targets depend on the enterprise.

The direction is what matters.

13. Measure Enterprise Learning Rate

Another useful concept is:

Enterprise Learning Rate

How quickly does real usage produce reusable trusted knowledge?

You could track:

New trusted mappings / week
New trusted relationships / week
Clarifications converted to reusable knowledge
Repeated ambiguity reduction
Human confirmation workload

The goal is not maximum knowledge creation.

It is reducing repeated uncertainty.

A useful signal might be:

Repeated Clarification Rate ↓

If users keep clarifying the same concepts month after month, the system is not learning effectively.

14. A Maturity Model

A data agent can be viewed as progressing through several levels.

Level 0 — Connected

Can access data

Level 1 — Schema-Aware

Understands tables and fields

Level 2 — Relationship-Aware

Understands how relevant data connects

Level 3 — Semantic-Aware

Understands business terms,
metrics, and dimensions

Level 4 — Business-Aware

Uses governed definitions,
trusted mappings, and business context

Level 5 — Learning

Turns corrections,
confirmations, and clarifications
into reusable knowledge

Most enterprise AI demos prove Levels 0 and 1.

Production value increasingly appears in Levels 3 through 5.

15. The Goal Is Not Fully Automatic Semantics

There is a tempting but unrealistic objective:

Connect database
↓
AI automatically understands everything

Some business meaning cannot be inferred from data.

If a company decides that Revenue means:

Recognized Revenue

rather than:

Invoice Amount

that is an organizational decision.

The system should not pretend otherwise.

A more realistic architecture is:

Machine discovers
      ↓
AI proposes
      ↓
Humans confirm high-impact meaning
      ↓
System remembers

The goal is not zero human input.

It is:

Minimum repetitive human work with preserved human authority over business meaning.

16. Keep Enterprise Knowledge Model-Agnostic

The underlying LLM will change.

Enterprise knowledge should survive those changes.

Avoid storing important semantics only inside:

Prompt templates
Few-shot examples
Conversation history
Model-specific instructions

Instead, keep durable knowledge as structured objects:

Business Terms
Metrics
Dimensions
Mappings
Relationships
Rules
Provenance

Then different models can consume the same enterprise context.

Models are replaceable.

Enterprise knowledge compounds.

17. A Practical Bootstrap Architecture
Putting the pieces together:

              Enterprise Data
                     ↓
             Metadata Discovery
                     ↓
          Relationship Discovery
                     ↓
      Existing Knowledge Import
                     ↓
       Semantic Candidate Layer
                     ↓
            Human Confirmation
                     ↓
          Trusted Knowledge Store
                     ↑
                     │
User Question → Runtime Resolver
                     │
                     ↓
              Clarification
                     │
                     ↓
              Knowledge Event
                     │
                     └──────────→ Trusted Knowledge Store

Trusted Context
      ↓
Query Planning
      ↓
SQL Generation
      ↓
Validation
      ↓
Execution

The system becomes better not because the model retrains after every question, but because the enterprise context around the model improves.

Final Thoughts

The semantic cold start problem is easy to underestimate.

A database connection gives an agent access to:

Data

Production analytics requires:

Data
+
Meaning
+
Relationships
+
Authority
+
Learning

So the engineering goal should not simply be:

Connect the database faster.

It should be:

Reduce the amount of work required for the system to become business-aware.

That means discovering what can be discovered, importing what already exists, asking humans only where judgment matters, and turning useful corrections into durable knowledge.

The most important property is simple:

Every correction should make the next question cheaper.

That is how a generic data agent starts becoming an enterprise data agent.

How I Would Benchmark a Text-to-SQL System for Production

ArisynData — Wed, 02 Sep 2026 22:26:00 +0000

A clean question against a clean schema is a demo. A production benchmark should deliberately test ambiguity, missing relationships, competing metrics, and SQL that executes successfully but answers the wrong business question.

Most Text-to-SQL evaluations start with questions like:

What was revenue last quarter?
Show sales by region.
List the top 10 customers.

These tests are useful for checking whether the basic pipeline works.

They are not enough to tell you whether the system is ready for enterprise production.

In production, the difficult cases are rarely caused by SQL syntax alone. They come from business language, incomplete metadata, ambiguous metrics, undocumented relationships, aggregation grain, and assumptions that look reasonable but are wrong.

So if I were benchmarking a Text-to-SQL system, I would build the benchmark around failure paths, not just happy paths.

## Why Execution Accuracy Is Not Enough

A typical evaluation might measure:

Question
   ↓
Generated SQL
   ↓
Expected SQL

or:

Generated SQL
   ↓
Execute
   ↓
Compare Result

Execution-based evaluation is better than exact SQL matching because multiple SQL statements can produce the same correct answer.

But enterprise reliability requires additional questions:

Did the system understand the business term correctly?

Did it choose the authoritative metric?

Did it use the right relationship path?

Did it preserve the correct aggregation grain?

Did it recognize ambiguity?

Did it know when not to query?

A query can execute successfully and still fail every one of these tests.

# Build a Failure-Path Benchmark

I would create at least seven test categories.

A. Ambiguous Intent
B. Business Term Mapping
C. Competing Metrics
D. Missing Relationships
E. Executable-but-Wrong SQL
F. Insufficient Intent
G. Environment Change

Each category tests a different part of the system.

## Test A: Ambiguous Intent

Question:

Show me our best customers.

Possible interpretations:

Revenue
Profit
Growth
Retention
Lifetime Value

The benchmark should not define best in the question.

The correct behavior depends on enterprise context.

If a governed definition exists:

term: best_customer
metric: customer_lifetime_value
status: active

the system can resolve it.

If multiple valid definitions remain, the expected behavior should be:

CLARIFY

not:

GENERATE_SQL

### Example benchmark case

{
  "id": "ambiguity_001",
  "question": "Show me our best customers.",
  "valid_metrics": [
    "revenue",
    "profit",
    "growth",
    "retention"
  ],
  "governed_default": null,
  "expected_action": "clarify"
}

### What to score

Ambiguity detected?
Correct candidates identified?
Clarification triggered?
Clarification options useful?

This gives you a Clarification Accuracy metric rather than only SQL accuracy.

## Test B: Business Term Mapping

Question:

What was revenue by product code this quarter?

Now make sure the schema does not contain a field called:

product_code

Instead:

product_master.material_id
sales_detail.sku_no
inventory.item_code
product_dim.prod_master_id

The benchmark should contain a governed semantic mapping:

business_term:
  name: Product Code

maps_to:
  table: product_master
  field: material_id

### Failure mode

A schema-similarity system may select:

inventory.item_code

because the name looks closest.

But the benchmark expects:

product_master.material_id

because that is the governed enterprise definition.

### What to score

Business term resolved correctly?
Correct physical field selected?
Governed mapping preferred over lexical similarity?

Call this:

# **Semantic Mapping Accuracy**

## Test C: Competing Metrics

Create several plausible financial fields:

sales_order.total_amount
invoice.invoice_amount
finance_revenue.recognized_amount
payment.received_amount

Question:

What was revenue last quarter?

All four fields are relevant to money.

Only one represents the governed Revenue metric.

Benchmark definition:

metric:
  name: Revenue

source:
  table: finance_revenue
  field: recognized_amount

time_field:
  finance_revenue.recognition_date

### What to inspect

Do not only inspect the final number.

Inspect the query plan.

Expected:

Metric
→ Revenue

Field
→ finance_revenue.recognized_amount

Time
→ finance_revenue.recognition_date

### What to score

Metric resolution accuracy
Source selection accuracy
Time-field accuracy

This catches systems that accidentally return the right number from the wrong business definition.

## Test D: Missing Relationships

Production databases often lack perfect foreign keys.

Build:

customer
account
sales_order
finance_revenue

Expected relationship path:

Customer
   ↓
Account
   ↓
Order
   ↓
Revenue

Then deliberately remove some database constraints.

The benchmark should still contain enough data evidence for a relationship system to infer or retrieve valid relationships.

For example:

account.customer_id
sales_order.account_id
finance_revenue.order_id

### Useful relationship evidence

A relationship engine might consider:

Column names
Data types
Uniqueness
Value overlap
Inclusion
Known metadata
Validated relationships

One useful inclusion signal is:

Inclusion(A → B)
=
|distinct(A) ∩ distinct(B)|
---------------------------
|distinct(A)|

where A is a candidate foreign-key-like column and B is a candidate referenced column.

### What to score

Correct tables selected?
Correct relationship path found?
Unsupported direct joins avoided?

Call this:

# **Relationship Path Accuracy**

## Test E: Executable-but-Wrong SQL

This is the test I would care about most.

Suppose the schema allows:

Customer → Order

and:

Customer → Account → Order

Both can generate executable SQL.

But for consolidated customers, only:

Customer → Account → Order

preserves the correct business grain.

Create two candidate queries.

### Query A

SELECT
    c.customer_name,
    SUM(o.amount)
FROM customer c
JOIN sales_order o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

### Query B

SELECT
    c.customer_name,
    SUM(o.amount)
FROM customer c
JOIN account a
    ON c.customer_id = a.customer_id
JOIN sales_order o
    ON a.account_id = o.account_id
GROUP BY c.customer_name;

Both may execute.

Only Query B is business-valid for the benchmark scenario.

### What to score

SQL executable?
Business relationship valid?
Aggregation grain valid?
Result semantically correct?

This creates an important distinction:

Execution Accuracy
≠
Business Accuracy

## Test F: Insufficient Intent

Question:

Show our best-performing products recently.

The benchmark intentionally leaves unresolved:

best-performing
recently

Possible metric candidates:

Revenue
Profit
Units Sold
Growth

Possible time candidates:

7 Days
30 Days
Current Month
Current Quarter

If no governed defaults exist, the expected result is not SQL.

It is:

{
  "action": "clarify",
  "unresolved": [
    "metric",
    "time_range"
  ]
}

### What to score

Did the system detect missing intent?
Did it avoid premature SQL generation?
Did it ask the minimum useful clarification?

This measures:

# **Safe Failure Accuracy**

A production system should not be rewarded for answering every question.

## Test G: Environment Change

Static benchmarks miss one of the largest production costs: maintenance.

Start with a working environment.

Then introduce changes:

Add a table
Rename a field
Add a new metric
Deprecate an old metric
Add a new relationship
Change a semantic mapping

For example:

Before:
Product Code → product_master.material_id

After:
Product Code → product_dim.product_code

Then rerun the benchmark.

Measure:

How many cases break?

How much manual configuration is required?

How quickly does the system recover?

Which knowledge objects need updating?

This produces a metric that benchmark leaderboards rarely show:

# **Semantic Maintenance Cost**

# Score More Than SQL

A useful benchmark report could look like:

Metric	Score
Semantic Mapping Accuracy	94%
Metric Resolution Accuracy	96%
Relationship Path Accuracy	91%
Clarification Accuracy	93%
SQL Execution Accuracy	98%
Business Answer Accuracy	89%
Safe Failure Accuracy	95%
Maintenance Effort	3.2 min/change

The exact metrics depend on the product.

The important point is that:

SQL Accuracy

should be one row, not the entire benchmark.

# Use a Multi-Stage Evaluation Harness

Instead of treating Text-to-SQL as one black box:

Question
→ SQL

capture intermediate artifacts.

For example:

{
  "question": "Revenue by customer last quarter",

  "intent": {
    "metric": "recognized_revenue",
    "dimension": "customer",
    "time": "last_quarter"
  },

  "semantic_objects": [
    "metric.revenue",
    "dimension.customer"
  ],

  "tables": [
    "customer",
    "account",
    "sales_order",
    "finance_revenue"
  ],

  "relationship_path": [
    "customer.customer_id -> account.customer_id",
    "account.account_id -> sales_order.account_id",
    "sales_order.order_id -> finance_revenue.order_id"
  ],

  "generated_sql": "...",

  "action": "execute"
}

Now failures become diagnosable.

If the final answer is wrong, you can determine whether the failure came from:

Intent
Semantic Mapping
Table Selection
Relationship Resolution
Metric Selection
SQL Generation
Execution

That is much more useful than:

FAILED

# Build Adversarial Test Pairs

A strong benchmark should include pairs of questions that look similar but require different interpretations.

Example:

Q1

What was our revenue last quarter?

Expected metric:

recognized_revenue

Q2

What was our invoiced amount last quarter?

Expected metric:

invoice_amount

Another pair:

Q1

Sales by customer region.

Expected dimension:

customer_region

Q2

Sales by billing region.

Expected dimension:

billing_region

These pairs test whether the system is actually resolving semantics or simply reusing the most common mapping.

# Include Negative Tests

Traditional benchmarks mostly contain questions that should be answerable.

Production benchmarks should include questions that should not.

For example:

Question:
"Show profitability by happiness score."

No governed metric:
happiness_score

Expected:
Cannot resolve metric / clarification required

Or:

Question:
"Join employee salary with public customer data."

Expected:
Rejected by policy

The exact safety rules depend on the deployment.

The principle is:

A system should be tested on its ability to refuse invalid query plans, not only generate valid ones.

# Test With Messy Schemas

Do not benchmark only:

customer
orders
products

Use schemas that look more like enterprise reality:

t_cust_m
cust_master_old
acct_rel
f_ord_h
ord_detail_v2
fin_rev_rec
inv_hdr
inv_line

Add:

Deprecated tables
Duplicate concepts
Missing descriptions
Inconsistent naming
Cross-system IDs
Partial foreign keys

If the product claims to work with enterprise data, the benchmark should look like enterprise data.

# Measure Human Setup Cost

Before the first benchmark run, track setup effort.

For example:

Schema descriptions:       4 hours
Metric definitions:        3 hours
Relationship configuration: 6 hours
Example SQL:                5 hours
Prompt tuning:              2 hours

Total:

20 hours

Another system may achieve slightly lower raw accuracy but require:

4 hours

of setup.

That difference matters.

So benchmark:

Accuracy

and:

Cost to achieve that accuracy

together.

# A More Useful Production Score

I would think about production value approximately as:

                  Accuracy × Trust × Coverage
Value  ≈  ─────────────────────────────────────
                Setup + Maintenance Cost

Again, this is not a universal mathematical formula.

It forces the benchmark to consider what enterprises actually pay for.

A system that scores 97% on 50 carefully prepared questions may be less valuable than one scoring 93% across thousands of messy tables with far less manual configuration.

# What I Would Put in a POC

If I were designing a vendor POC, I would include:

10 clear questions
10 ambiguous questions
10 semantic-alias questions
10 competing-metric questions
10 missing-relationship questions
10 multi-path join questions
10 executable-but-wrong traps
10 questions that require clarification
10 unanswerable questions
10 post-schema-change regressions

That gives:

100 cases

with intentionally different failure modes.

Then evaluate both:

Can it answer correctly?

and:

Can it recognize when it should not answer yet?

# Final Thoughts

A production Text-to-SQL benchmark should not primarily test whether an LLM knows SQL.

Modern models are already good at SQL generation under clean conditions.

The harder questions are:

Did it understand the business term?

Did it select the right metric?

Did it find the right data?

Did it use the right relationship?

Did it preserve the right grain?

Did it detect ambiguity?

Did it fail safely?

How much human work was required?

Those are system-level questions.

So when evaluating a Text-to-SQL platform:

Don't benchmark the easiest route from question to SQL.

Deliberately break the assumptions.

Remove the foreign key.

Add three plausible revenue fields.

Use business terminology that does not match the schema.

Create two executable join paths where only one is correct.

Ask a question that should trigger clarification.

Then change the data model and run everything again.

That is where you start learning whether you have a good demo—or a production system.

Building a Trustworthy Semantic Layer for Natural Language Analytics

ArisynData — Mon, 31 Aug 2026 22:10:00 +0000

Natural language analytics looks simple from the outside:

User asks a question.

The system generates SQL.

The database returns an answer.

In real enterprise environments, this breaks quickly.

The reason is not just SQL generation. The reason is semantic ambiguity.

When a user asks:

Show sales by product code for last month.

The system needs to resolve several questions before it can safely generate a query:

What does "sales" mean?
Is it gross sales, net sales, recognized revenue, bookings, or paid order amount?
What does "product code" mean?
Is it internal product code, SKU, marketplace product code, barcode, or ERP material number?
Which date field should represent "last month"?
Which tables contain the right facts and dimensions?
Which join path is trusted?
Is the current user allowed to access the required data?

This is why a semantic layer is becoming a critical part of natural language analytics.

But not all semantic layers are designed for the same job.

Databricks is building semantic capabilities natively inside its lakehouse platform. Semora is building an independent semantic layer for complex, cross-platform enterprise data. From an engineering perspective, the interesting question is not "Which product has a better chat interface?"

The better question is:

What system architecture makes natural language analytics both fluent and accurate?

## The Core Problem: Language Models Should Not Guess the Data Model

A large language model can usually understand that these phrases are related:

product code
product ID
item number
SKU
material number

But in a real company, those phrases may not be interchangeable.

One table may contain:

product_code
product_id
sku_id
barcode
platform_item_id
material_number

If the system maps "product ID" to product_code just because the words sound close, the answer may be fluent but wrong.

On the other hand, if the system asks the user to clarify every similar term, the experience becomes painful.

The engineering challenge is to build a runtime that can decide:

When can we answer directly?
When should we show the assumption?
When should we ask a lightweight clarification?
When must we require explicit confirmation?
How do we save the user's answer so we do not ask again?

This is where Semora can differentiate.

## Databricks: Platform-Native Semantic Context

Databricks has been expanding its semantic layer and natural language analytics stack through Unity Catalog business semantics, metric views, Genie, and Genie Agents.

The Databricks approach is powerful because semantic assets live close to the governed data platform.

A Databricks-style flow can look like this:

User question
  -> Genie Agent scope
  -> Unity Catalog metadata
  -> Metric views
  -> Table and column descriptions
  -> Trusted queries and examples
  -> SQL generation
  -> Query inspection
  -> Governed answer

This works especially well when:

The company already uses Databricks as the main analytical platform.
Tables, permissions, lineage, and metrics are managed in Unity Catalog.
Business domains are curated into focused Genie Agents.
Data teams maintain metric views, examples, and trusted assets.

The key engineering lesson is that Databricks does not treat the language model as the only source of intelligence. It surrounds the model with governed context.

That context reduces the search space.

If a user is asking inside a Sales Genie Agent, "revenue" can be interpreted using sales-domain semantics. If they ask inside a Finance context, the default meaning may be different.

This domain scoping is one of the most important reasons a natural language analytics experience feels smooth.

## Semora: Independent Semantic Runtime

Semora has a different opportunity.

Many enterprises do not have one clean analytical platform. They have multiple warehouses, operational databases, business intelligence tools, spreadsheets, and legacy systems. Field names are inconsistent. Foreign keys are missing. Business terms vary by department.

In this environment, an independent semantic layer is valuable because it can sit above the data platforms instead of being tied to only one.

A Semora-style flow can look like this:

User question
  -> Intent classification
  -> Role and permission scope
  -> Business domain routing
  -> Synonym and semantic retrieval
  -> Business concept mapping
  -> Certified field selection
  -> Relationship discovery
  -> Trusted question template matching
  -> Query generation
  -> Query validation
  -> Answer with lineage
  -> Clarification memory update

The important word here is runtime.

It is not enough to have pages for synonyms, business semantics, data definitions, question templates, and knowledge bases. Those assets must be actively retrieved, ranked, and applied while the user is asking a question.

Otherwise, the product can have many semantic assets and still feel rough.

## Runtime Component 1: Intent Classification

The first step should not be SQL generation.

The first step should be intent classification.

For example, the user may ask:

What questions can you answer for me?

This is not a data query. It is a capability discovery question.

A poor system routes it into the query pipeline, fails to find a metric, and asks for clarification.

A better system recognizes the intent and answers based on the user's role, permissions, business domains, and available question templates.

Useful intent classes include:

DATA_QUERY
CAPABILITY_DISCOVERY
METRIC_EXPLANATION
FIELD_EXPLANATION
DOMAIN_EXPLORATION
TEMPLATE_RECOMMENDATION
PERMISSION_SCOPE_EXPLANATION
PRODUCT_HELP

For CAPABILITY_DISCOVERY, the response should be generated from available semantic assets:

Accessible business domains
Certified metrics
Question templates
Frequently used questions
Available dimensions
Permission boundaries

Example response:

Based on your current access, I can answer questions about sales, products, inventory, and channels. You can ask about sales trends, product rankings, inventory shortages, channel comparisons, and customer repeat purchase behavior.

This small routing layer can dramatically improve first-time user experience.

## Runtime Component 2: Business Domain Routing

Even if Semora keeps one universal question box, the backend should not treat all data as one global space.

The system should route each question into one or more business domains.

For example:

"sales by product code"
  -> Sales domain
  -> Product domain

"inventory risk by SKU"
  -> Inventory domain
  -> Product domain

"recognized revenue by customer"
  -> Finance domain
  -> Customer domain

Domain routing should use:

User role
Permission scope
Question text
Matched metrics
Matched templates
Historical user behavior
Business glossary terms

The user can still see one clean interface. Internally, the system should load a scoped semantic context.

This reduces ambiguity before the language model starts planning the query.

## Runtime Component 3: Multi-Path Semantic Retrieval

A common failure mode is exact or weak lexical matching.

If the physical field is called product_code, and the user says "product number," the system may fail unless that synonym was manually configured.

This does not scale.

Semora should use multi-path retrieval:

User phrase
  -> Exact match
  -> Token match
  -> Synonym match
  -> Embedding similarity
  -> Field description match
  -> Sample value match
  -> Historical clarification match
  -> Question template match
  -> Domain default match

For the phrase "product code," candidates might include:

Business concept: Product Code
Fields:
  - dim_product.product_code
  - order_items.product_code
  - inventory_sku.sku_code
  - marketplace_items.platform_item_id
  - product_barcodes.barcode

The retrieval layer should not immediately decide. It should return candidates with evidence.

Example evidence:

{
  "candidate": "dim_product.product_code",
  "business_concept": "Product Code",
  "evidence": {
    "synonym_match": ["product code", "item code"],
    "domain": "Product",
    "field_description": "Internal product code used across sales and inventory",
    "historical_confirmations": 17,
    "certification_status": "certified"
  },
  "confidence": 0.93
}

This gives the language model structured context instead of asking it to guess from raw schema names.

## Runtime Component 4: Confidence-Based Clarification

Clarification should be a decision, not a default.

A useful policy could be:

confidence >= 0.85
  -> answer directly and show the assumption

0.60 <= confidence < 0.85
  -> answer with a lightweight change option, or ask a short clarification

confidence < 0.60
  -> require clarification

high-risk metric or high-risk join
  -> require confirmation even if confidence is moderate

For a high-confidence case:

I interpreted "product code" as the certified Product Code concept and used the internal product code field.

For an ambiguous case:

Which product identifier do you mean?

Internal product code

SKU number

Marketplace product code

Barcode

ERP material number

This is better than showing raw column names to a business user.

Technical users can still inspect the underlying fields, joins, and SQL.

## Runtime Component 5: Trusted Question Templates

Question templates should not be simple prompt examples.

They should be trusted analytical paths.

For example, a template for:

Top-selling products in a time period

Should include:

template_id: top_selling_products
intent: ranking
metric:
  name: Sales Quantity
  certification: certified
dimensions:
  - Product Code
  - Product Name
time:
  default_field: Paid Time
filters:
  - Paid Orders Only
relationships:
  - order_items -> dim_product by product_code
parameters:
  - time_range
  - channel
  - category
validation:
  - sales_quantity >= 0
  - product_code is not null
owner: Sales Analytics Team
status: certified

When the user asks:

Show last month's product code sales ranking.

The system should match the template first, extract parameters, and reuse the certified query path.

The language model should not rebuild the business logic from scratch.

## Runtime Component 6: Relationship Discovery and Join Safety

Natural language analytics systems often fail silently when joins are wrong.

A query can run successfully and still be incorrect because:

It joined at the wrong grain.
It created duplicate rows.
It used a weak relationship.
It joined through the wrong entity.
It mixed internal identifiers with external identifiers.

For Semora, relationship discovery should be a major differentiator.

A relationship engine should evaluate:

Field name similarity
Data type compatibility
Value overlap
Inclusion ratio
Cardinality
Null rate
Uniqueness
Historical usage
Human approval
Domain relevance

The output should be explainable:

{
  "join_path": [
    "order_items.product_code",
    "dim_product.product_code"
  ],
  "confidence": 0.91,
  "evidence": {
    "value_overlap": "high",
    "right_side_uniqueness": "strong",
    "approved_by": "data_owner",
    "fanout_risk": "low"
  }
}

This is where independent semantic layers can create real value. Platform-native semantic layers work well when relationships are already modeled. Independent semantic layers can help discover and govern relationships where metadata is incomplete.

## Runtime Component 7: Query Validation

SQL generation is not the end of the pipeline.

The system should validate the generated query before presenting the answer.

Validation checks may include:

Does the user have permission to access every field?
Is the selected metric certified?
Is the date field appropriate for the metric?
Are filter values valid?
Is the join path approved?
Is there fanout risk?
Is the aggregation grain correct?
Is the result within a reasonable historical range?
Did the query return no rows because of a value mapping issue?

This validation layer is one of the most important differences between a demo and a production-ready product.

## Runtime Component 8: Clarification Memory

Every clarification should become a reusable asset.

If a user confirms:

By "product code," I mean the internal product code.

The system should save:

user_phrase: product code
standard_concept: Product Code
field_mapping: dim_product.product_code
business_domain:
  - Product
  - Sales
confirmed_by: user_or_owner
scope: user | team | organization
status: draft | reviewed | certified
usage_count: 1

Over time, repeated confirmations should create candidate semantic rules. Data owners can review and promote them.

This makes the system smoother with use.

The goal is not to ask better clarification questions forever. The goal is to ask fewer clarification questions over time because the semantic layer is learning.

## A Practical Architecture

Here is a practical architecture for a natural language analytics runtime:

                         +----------------------+
                         |   User Question      |
                         +----------+-----------+
                                    |
                                    v
                         +----------------------+
                         | Intent Classifier    |
                         +----------+-----------+
                                    |
              +---------------------+---------------------+
              |                                           |
              v                                           v
   +------------------------+                 +------------------------+
   | Non-query Intents      |                 | Data Query Intent      |
   | Help, scope, examples  |                 | Metrics, filters, SQL  |
   +------------------------+                 +-----------+------------+
                                                          |
                                                          v
                                           +---------------------------+
                                           | Domain Router             |
                                           +------------+--------------+
                                                        |
                                                        v
                                           +---------------------------+
                                           | Semantic Retrieval        |
                                           +------------+--------------+
                                                        |
                                                        v
                                           +---------------------------+
                                           | Template Matching         |
                                           +------------+--------------+
                                                        |
                                                        v
                                           +---------------------------+
                                           | Confidence Decision       |
                                           +------------+--------------+
                                                        |
                             +--------------------------+--------------------------+
                             |                                                     |
                             v                                                     v
                 +------------------------+                           +------------------------+
                 | Clarification          |                           | Query Planning         |
                 +-----------+------------+                           +-----------+------------+
                             |                                                    |
                             v                                                    v
                 +------------------------+                           +------------------------+
                 | Semantic Memory        |                           | SQL Generation         |
                 +------------------------+                           +-----------+------------+
                                                                                  |
                                                                                  v
                                                                      +------------------------+
                                                                      | Query Validation       |
                                                                      +-----------+------------+
                                                                                  |
                                                                                  v
                                                                      +------------------------+
                                                                      | Answer + Lineage       |
                                                                      +------------------------+

The language model appears in multiple places:

Intent classification
Query planning
Clarification generation
Parameter extraction
Result explanation
Follow-up question handling

But the model is not operating alone. It is grounded by semantic assets, trusted templates, permissions, relationship evidence, and validation rules.

That is the difference between natural language analytics as a demo and natural language analytics as enterprise infrastructure.

## Where Semora Can Stand Apart

Databricks has a strong platform-native story. If a company has standardized its data and governance inside Databricks, that integration is hard to beat.

Semora should not try to be a smaller version of Databricks.

It should focus on the problems that appear when the enterprise data world is messy:

Business terms vary across teams.
Field names do not match user language.
Relationships are not fully documented.
Metrics conflict across systems.
Users need answers across multiple platforms.
Clarification should become governed semantic memory.
Natural language queries need explainable lineage.

This is a valuable and distinct engineering problem.

The best semantic layer is not the one that lets the model guess more freely. It is the one that gives the model enough context, evidence, and constraints to answer correctly.

## Conclusion

Natural language analytics is not solved by connecting a language model to a database.

It requires a semantic runtime.

That runtime must classify intent, route by business domain, retrieve semantic assets, match trusted templates, evaluate confidence, clarify only when necessary, generate governed queries, validate results, and save human feedback as reusable semantic knowledge.

Databricks approaches this through a platform-native lakehouse stack.

Semora's opportunity is to build an independent semantic intelligence layer for enterprises whose data, terminology, and relationships span many systems.

For developers and data engineers, the lesson is clear:

Do not make the model guess the enterprise data model. Build the semantic system that lets it reason with evidence.

Your AI Data Agent Needs a “Do Not Use” Layer

ArisynData — Tue, 25 Aug 2026 02:08:09 +0000

The missing layer in many enterprise AI systems is not more metadata. It is a machine-readable record of the mistakes the organization already knows not to make.

A surprising amount of enterprise data expertise sounds like this:

Don't use that table after the migration.

Don't use invoice_amount as recognized revenue.

Don't join those two tables directly.

Don't use created_at for financial reporting.

Don't aggregate those rows before deduplicating accounts.

These rules are rarely visible in the database schema. They may not exist in the catalog. Often they live only in the heads of experienced analysts, engineers, and business owners.

That worked when humans were the primary consumers of enterprise data. It becomes a serious problem when AI agents start querying data directly.

The usual response is to give the model more context.

But what if the missing context is not another description of what the data is?

What if the missing layer is a structured description of what the AI must not do?

## The Data Model Contains Less Knowledge Than We Think

Consider:

CREATE TABLE invoice (
    invoice_id      BIGINT,
    customer_id     BIGINT,
    invoice_amount  DECIMAL(18,2),
    invoice_date    DATE,
    status          VARCHAR(20)
);

An AI can identify a customer reference, an amount, and a date. It can probably generate valid SQL for:

What was recognized revenue last quarter?

But the enterprise may already know:

invoice_amount is not recognized revenue.

That fact is not a schema fact.

It is organizational knowledge.

Without it, the agent can be technically competent and still wrong.

## “Do Not Use” Is a Real Data Asset

Data platforms traditionally capture positive knowledge:

What tables exist?
What do columns mean?
What is the metric definition?
How are entities related?

Expert users also carry negative knowledge:

Which source is misleading?
Which join is dangerous?
Which field looks correct but isn't?
Which timestamp should not be used?
Which aggregation creates double counting?

This knowledge is valuable precisely because the wrong choice often looks reasonable.

A production data agent therefore needs:

Positive Knowledge
→ What can I use?

Negative Knowledge
→ What must I avoid?

## A “Do Not Use” Layer

Imagine structured constraints attached to enterprise data objects.

For a field:

object:
  type: column
  name: invoice.invoice_amount

meaning:
  business_term: Invoice Value

usage:
  valid_for:
    - invoice_analysis

  invalid_for:
    - recognized_revenue

reason:
  Invoice value may include amounts that have
  not yet met revenue recognition rules.

owner:
  finance

status:
  active

For a relationship:

object:
  type: relationship

from:
  customer.customer_id

to:
  order.customer_id

constraint:
  invalid_when:
    account_structure: consolidated

required_path:
  - customer
  - account
  - order

reason:
  Direct joins can duplicate orders across child accounts.

For a time field:

object:
  type: time_rule

metric:
  recognized_revenue

required_field:
  finance_revenue.recognition_date

do_not_use:
  - invoice.invoice_date
  - sales_order.created_at

This is not a blacklist.

It is contextual enterprise knowledge.

## Why a Blacklist Is Too Primitive

Marking:

invoice.invoice_amount = forbidden

would be wrong.

The field is perfectly valid for Invoice Value. It is invalid only when interpreted as Recognized Revenue.

So the real rule is:

Object + Business Intent + Context → Valid / Invalid

For example:

field: invoice.invoice_amount

valid_for:
  - invoice_value

invalid_for:
  - recognized_revenue
  - cash_received

The same physical field can be valid or invalid depending on the analytical task.

## Put Constraints Before the LLM

A naive pipeline:

Question
   ↓
Schema Retrieval
   ↓
LLM
   ↓
SQL

A stronger pipeline:

Question
   ↓
Business Intent Resolution
   ↓
Candidate Retrieval
   ↓
“Do Not Use” Constraint Check
   ↓
Trusted Relationship Resolution
   ↓
Allowed Query Context
   ↓
LLM
   ↓
SQL
   ↓
Constraint Validation

The constraint layer appears twice.

First, it prevents known-invalid data from entering the model's decision space.

Second, it validates that generated SQL did not violate a known rule.

## Filter Invalid Candidates Before Generation

Suppose retrieval returns:

candidates = [
    "finance_revenue.recognized_amount",
    "invoice.invoice_amount",
    "sales_order.total_amount",
    "payment.received_amount"
]

For a recognized-revenue question:

def filter_by_constraints(candidates, intent, knowledge):
    allowed = []
    excluded = []

    for candidate in candidates:
        rule = knowledge.evaluate(
            object=candidate,
            intent=intent
        )

        if rule.is_hard_violation:
            excluded.append({
                "object": candidate,
                "reason": rule.reason
            })
            continue

        allowed.append(candidate)

    return allowed, excluded

The model now receives a much cleaner problem.

But keep the exclusion reason:

excluded_context:
  - object: invoice.invoice_amount
    reason: not valid for recognized revenue

That helps explanation and debugging.

## Relationship Constraints May Matter Even More

Wrong joins are dangerous because they can return plausible results.

Imagine:

Customer ───── Order
    │
    └──── Account ───── Order

Both paths may technically exist.

For consolidated accounts, the direct path may duplicate orders.

Represent the rule:

constraint:
  type: relationship_path

  from: customer
  to: order

  when:
    account_structure: consolidated

  forbidden_path:
    - customer
    - order

  required_path:
    - customer
    - account
    - order

Then build a task-specific graph:

Customer ──X── Order
    │
    └──── Account ───── Order

Conceptually:

valid_graph = relationship_graph.apply_constraints(
    business_context=context
)

path = valid_graph.shortest_trusted_path(
    source="customer",
    target="order"
)

This is safer than asking an LLM to infer join topology from names every time.

## Hard Rules and Soft Rules

Not every constraint should block execution.

### Hard Constraint

severity: hard

rule:
  do_not_use: customer_legacy

when:
  query_date >= 2026-01-01

Violation should stop or regenerate the query.

### Soft Guidance

severity: soft

rule:
  prefer: billing_region

fallback:
  registered_region

Violation may lower ranking or trigger review.

This avoids two extremes: an inflexible rule engine and a prompt that treats every rule as optional advice.

## Compile Governance Into Runtime Constraints

Suppose a governed metric says:

metric:
  id: recognized_revenue

source:
  finance_revenue.recognized_amount

time_field:
  finance_revenue.recognition_date

invalid_sources:
  - invoice.invoice_amount
  - sales_order.total_amount

required_filters:
  - status=recognized

Compile it:

policy = {
    "required_source":
        "finance_revenue.recognized_amount",

    "required_time_field":
        "finance_revenue.recognition_date",

    "forbidden_sources": [
        "invoice.invoice_amount",
        "sales_order.total_amount"
    ],

    "required_filters": [
        "status=recognized"
    ]
}

The same policy can shape retrieval, context construction, generation, and validation.

One governed definition drives multiple runtime controls.

## Validate What Must Not Appear

After generation, parse the SQL and check the policy:

def validate_query(ast, policy):
    violations = []

    for source in policy["forbidden_sources"]:
        if ast.references(source):
            violations.append({
                "type": "forbidden_source",
                "object": source
            })

    if not ast.references(policy["required_time_field"]):
        violations.append({
            "type": "wrong_time_field"
        })

    return violations

If violations exist:

if violations:
    regenerate_or_escalate(
        sql=sql,
        violations=violations
    )

The agent is no longer trusted to police itself.

## Where Does Negative Knowledge Come From?

One of the best sources is historical failure.

Example:

Problem:
Revenue dashboard overstated.

Root Cause:
Orders joined directly to customers.

Fix:
Use account-level relationship.

Lesson:
Never use direct Customer → Order join
for consolidated accounts.

Most organizations stop after fixing the dashboard.

A stronger system captures the lesson:

Failure
   ↓
Root Cause
   ↓
Data Rule
   ↓
Structured Constraint
   ↓
Future Prevention

Operational experience becomes AI infrastructure.

## Ask Experts for Mistakes, Not Documentation

If you ask a senior analyst:

Please document these 50 columns.

you may get accurate but generic descriptions.

Instead ask:

What are the five mistakes a new analyst is most likely to make with this data?

You may hear:

Don't use that amount as revenue.
Don't use this table after migration.
Don't join those IDs directly.
Don't use created_at for monthly reporting.
Don't SUM before deduplicating accounts.

Those answers encode experience, not just definitions.

They are often extremely valuable for AI.

## Version and Own the Constraints

A constraint can become outdated.

It should include:

ID
Version
Owner
Status
Effective Date
Reason
Validation State

For example:

constraint:
  id: C-REV-014
  version: 3

  rule:
    invalid_for:
      object: invoice.invoice_amount
      metric: recognized_revenue

  owner:
    finance

  status:
    active

  effective_from:
    2026-01-01

  reason:
    revenue recognition follows finance_revenue

Now the system knows whether the rule is still authoritative.

## Make the Layer Observable

Useful metrics include:

Constraint Coverage
% of critical metrics with explicit negative rules

Prevented Violations
Invalid candidates removed before LLM generation

Generated SQL Violation Rate
% of queries violating known constraints

Known-Mistake Recurrence
How often a known incident repeats after its rule is encoded

The last metric captures the real purpose.

The goal is not to create more documentation.

It is to stop known mistakes from recurring.

## Do Not Turn Everything Into a Rule

Not every piece of human judgment should become deterministic policy.

Some decisions are ambiguous. Some rules have many exceptions. Some knowledge is uncertain.

Encode negative knowledge when it is:

Known
Repeated
High-impact
Business-specific
Stable enough to govern
Hard for the model to infer from schema alone

Leave genuinely ambiguous reasoning to the model.

The goal is not:

Replace AI reasoning with rules.

The goal is:

Stop using AI reasoning for mistakes
the enterprise already knows how to prevent.

## A More Mature Data Agent Runtime

Putting it together:

User Question
      ↓
Business Intent
      ↓
Candidate Retrieval
      ↓
Positive Knowledge
      +
Negative Knowledge
      ↓
Constraint Filtering
      ↓
Trusted Relationship Planning
      ↓
Allowed Context
      ↓
LLM Reasoning / SQL Generation
      ↓
Constraint Validation
      ↓
Execution
      ↓
Answer

The LLM no longer operates over everything that looks relevant.

It operates inside a data space shaped by enterprise knowledge.

## The Bigger Engineering Principle

A wrong enterprise answer is not always a hallucination.

Sometimes it is:

valid computation over an invalid business assumption.

A bad join can execute.

A wrong metric can aggregate.

A deprecated table can return real rows.

A wrong date field can produce a believable trend.

That is why more context alone is not enough.

The system also needs boundaries.

## Final Thoughts

The most valuable enterprise data knowledge may not be another description of what a field means.

It may be:

Don't use this field for that metric.

Or:

Don't take that join path.

Or:

Don't use that timestamp for reporting.

Those warnings represent accumulated organizational experience.

If they remain in people's heads, every new AI agent can repeat old mistakes.

If they become structured, versioned, and enforceable, they become something more useful:c

A “Do Not Use” layer for enterprise AI.

And that may be one of the missing pieces between an AI agent that can query data and one that can use enterprise data reliably.

Beyond SQL Accuracy: Building Evidence Chains for AI Data Agents

ArisynData — Thu, 20 Aug 2026 03:10:07 +0000

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;

The query is valid.

But suppose the enterprise definition is:

Net Revenue
=
Recognized Revenue
-
Refunds
-
Credits

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?

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

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                        ✓

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

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

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"
  }
}

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

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

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?

the system should be able to show:

User Term:
Revenue

Resolved Metric:
Net Revenue

Version:
3.2

Definition:
Recognized Revenue - Refunds - Credits

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

A production evidence record may also include fields:

{
  "source": "orders",
  "fields": [
    "billing_account_id",
    "recognized_amount",
    "settlement_date"
  ]
}

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

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"
}

More advanced systems could include relationship evidence such as:

Database Constraint
Naming Similarity
Value Inclusion
Uniqueness
Business Validation

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"

might resolve to:

2026-04-01 → 2026-06-30

and use:

settlement_date

rather than:

invoice_date

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"
  }
}

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"
  ]
}

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

Potentially:

{
  "status": "success",
  "duration_ms": 842,
  "rows_returned": 8,
  "result_id": "r_4412"
}

This lets the system distinguish:

Bad SQL

from:

Valid SQL + Wrong Business Interpretation

## 12. Validation Should Check the Chain

Instead of asking only:

Did the SQL execute?

validation can inspect several layers.

### Semantic Validation

Was an approved metric definition used?

### Source Validation

Were authorized and current data sources used?

### Relationship Validation

Were joins based on trusted relationships?

### Filter Validation

Were required business filters applied?

### Query Validation

Did the SQL pass syntax and safety checks?

This produces a richer validation object:

{
  "semantic": "passed",
  "sources": "passed",
  "relationships": "passed",
  "filters": "passed",
  "sql": "passed"
}

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

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

With evidence:

Semantic Resolution      PASS
Source Selection         PASS
Relationship Selection   FAIL
Filter Resolution        PASS
SQL Validation           PASS
Execution                PASS

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

teams can measure:

Metric Resolution Accuracy
Source Selection Accuracy
Relationship Selection Accuracy
Filter Resolution Accuracy
Trusted Relationship Usage
SQL Validation Pass Rate

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"
  }
}

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

Then:

Why this answer?

can reveal:

Metric Definition
Source Data
Relationships
Filters
SQL
Validation

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

and:

Inspectable

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

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

Depending on governance requirements, also consider:

Semantic Model Version
Relationship Model Version
Data Snapshot / Query Timestamp
Authorization Context
Agent Version
Model Version

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

For production enterprise AI, a stronger standard is:

Question
→ Governed Interpretation
→ Trusted Data Path
→ Executable Query
→ Traceable Result
→ Evidence-Backed Answer

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.