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

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.

Comments

[pm25coder]

I ran the gate from section 3 exactly as published, with your four asserts as the control, and all four pass: AMBIGUOUS with three eligible, RESOLVED to recognized when the default names it, UNSUPPORTED for the unapproved margin metric, RESOLVED for the single eligible one. The rest of this is about a branch those asserts do not touch.

Mark the default unavailable instead — say an ETL job on revenue_ledger is down. clarification_gate(candidates, approved_default_id="recognized") then returns RESOLVED, selected="booked", reason="Only eligible interpretation". That is right by the code and wrong by the business: the user asked for revenue, Finance's default is recognized revenue, and the answer now aggregates accepted order value. This is the unapproved assumption the gate exists to prevent, arriving through the availability flag rather than through a model picking a column. With two other candidates still eligible the same call asks instead, so the outcome depends on how many metrics happen to be up: one outage produces a question in one catalog and a silent switch in another.

A mistyped default id has the same shape — clarification_gate(candidates, "recognised") is identical to passing no default at all. One cause covers both: the default is resolved against eligible, so once availability has filtered the list there is nothing left to say about it, and GateResult has no field recording that a default was configured and did not apply.

The change I would try: resolve approved_default_id against the catalog first, then ask whether it is available, and return UNSUPPORTED naming the default rather than falling through to the "only eligible interpretation" branch. A default_applied: bool on the result would also make a dead default visible to whoever reads the audit log in section 7.