Why Text2SQL Must Be Integrated into AI Agents – An Interviewer's Guide

1. Why Text2SQL Must Be Part of an Agent / Function‑Call Architecture

In interviews, candidates who list Text2SQL are often asked whether their implementation is merely a demo or an engineered component that can run inside an Agent system. The real value of Text2SQL is not just generating a SQL statement, but serving as a core, read‑only tool that is scheduled, constrained, verified, and iterated upon by the Agent.

2. The Real Identity of Text2SQL Inside an Agent

Text2SQL is defined as a tool that only performs queries and does not make decisions. Its interface is a simple function:

{
  "name": "text2sql",
  "description": "Convert natural‑language queries into read‑only SQL and execute",
  "parameters": {
    "type": "object",
    "properties": {
      "question": {"type": "string", "description": "User's query"}
    },
    "required": ["question"]
  }
}

The Agent decides when to invoke this function; Text2SQL never decides to run itself.

3. Standard Agent‑Driven Text2SQL Call Flow

Agent receives the user question.

Agent determines whether the request involves structured data retrieval.

If ambiguity exists, the Agent asks clarification questions.

When all conditions are clear, the Agent calls Text2SQL.

The generated SQL is validated.

The result is checked.

The final natural‑language answer is produced.

Thus Text2SQL is a middle step, not the first step, in the workflow.

4. Why the Full Schema Should Not Be Sent to the LLM

Providing the entire database schema to the LLM leads to token explosion and semantic noise, especially when the schema contains dozens or hundreds of tables. The engineering solution is dynamic schema pruning: only the tables likely relevant to the query are included.

Implementation steps:

Generate embeddings for each table.

Generate an embedding for the user question.

Retrieve the top‑k most similar tables.

Insert only those table definitions into the prompt.

def _get_relevant_schema(self, question: str, top_k: int = 2) -> str:
    question_embedding = self.embedding.embed(question)
    relevant_tables = self._find_similar_tables(question_embedding, top_k)
    return self._format_schema(relevant_tables)

5. Ambiguity Is a System Problem, Not a Model Problem

Two strategies are used:

For definable ambiguous terms, map them to concrete constraints (e.g., "最近" → "最近30个自然日").

For undefined ambiguous terms, trigger a clarification dialogue with the user.

BUSINESS_TERMS = {
    "最近": "最近30个自然日",
    "大涨": "涨跌幅 > 5%",
    "低估值": "PE < 15"
}

AMBIGUOUS_TERMS = {
    "最新": ["最新交易日", "最新报告期"],
    "业绩": ["营收", "净利润", "ROE"]
}

6. SQL Safety Checks Are a P0 Requirement

Before execution, the system enforces strict safety rules:

Disallow destructive statements such as DELETE, DROP, UPDATE, INSERT, ALTER.

Force SELECT queries only.

Enforce a LIMIT clause and cap its value.

Restrict sub‑query depth.

FORBIDDEN_KEYWORDS = {'DELETE', 'DROP', 'UPDATE', 'INSERT', 'ALTER'}

if limit_value > MAX_LIMIT:
    sql = replace_limit(sql, MAX_LIMIT)

7. Result Verification Is Essential

Correct syntax does not guarantee correct semantics. The system performs three layers of validation:

Check whether the returned row count is reasonable.

Validate that numeric values fall within expected ranges.

Ask the LLM to self‑check whether the result matches the original intent.

if result["row_count"] == 0:
    warnings.append("查询结果为空")

Warnings are fed back to the Agent, which may regenerate the SQL or ask the user to adjust conditions.

8. Semantic Caching Reduces Cost and Latency

When users repeatedly ask similar questions, caching the "question → SQL → result" triple avoids redundant embedding computation, LLM calls, and database execution.

if similarity > threshold:
    return cached_result

This improves performance and stability.

9. Logging and Bad‑Case Closed‑Loop Are Must‑Haves

Comprehensive logging captures the entire pipeline:

Original question.

Pre‑processed question.

Generated SQL.

Execution success flag.

Result row count.

User feedback.

class Text2SQLLog:
    question
    processed_question
    generated_sql
    execution_success
    result_count

Periodic analysis of these logs identifies failure patterns, distinguishes schema‑related errors from semantic errors, and enriches the few‑shot example library, enabling a continuously improving system.

10. Interview‑Ready Summary Answer

In production, Text2SQL is a read‑only tool invoked by an Agent after intent detection, ambiguity clarification, and dynamic schema selection. Safety checks, result verification, semantic caching, and detailed logging form a robust, iteratively optimizable pipeline suitable for real‑world deployments.