Stop Making Text-to-SQL Agents Rediscover Your Database Every Time

An AI agent shouldn't have to rediscover how your business data works every time someone asks a question.

Imagine an employee asking an enterprise AI assistant:

"Which customers generated the highest revenue last quarter?"

The agent needs to understand the question, identify relevant tables, find the correct customer relationships, select the revenue definition, generate SQL, and execute it.

Now imagine another employee asks:

"Show revenue by customer region."

The agent may need to rediscover much of the same information.

Which table contains customer information? Which field represents revenue? How are customers connected to orders? Which date should be used?

This happens because many Text-to-SQL architectures treat each question as a relatively independent schema retrieval and SQL generation task.

That approach can work for small databases.

But enterprise databases are different.

They contain thousands of tables, inconsistent naming conventions, incomplete foreign keys, multiple business definitions, and relationships distributed across different systems.

The problem isn't just retrieving the right schema. It's preserving what the system has already learned about the data.

1. Schema Retrieval Is Not Data Understanding

A common Text-to-SQL architecture looks like this:

User Question
     ↓
LLM
     ↓
Schema Retrieval
     ↓
SQL Generation
     ↓
SQL Execution
     ↓
Answer

This is a reasonable starting point.

But schema retrieval usually focuses on finding relevant tables and columns based on names, descriptions, or embeddings.

Consider these tables:

customer_master
sales_contract
sales_order_detail
invoice_header
payment_transaction

A user asks:

"Which customers have the highest recognized revenue?"

A retrieval system might find:

customer_master
sales_order_detail
invoice_header

But what if recognized revenue is stored in another financial table?

What if the query needs to pass through a contract or invoice relationship?

What if sales_order_detail.amount represents booked order value rather than recognized revenue?

The retrieved schema may look relevant while still being insufficient for a correct query.

There are three separate problems:

1. Finding relevant physical data.
2. Understanding the business meaning of that data.
3. Understanding how the data objects actually connect.

Treating these as one retrieval problem creates unnecessary uncertainty.

2. Separate Semantic Understanding from Relationship Discovery

A more reliable architecture should distinguish two kinds of knowledge.

Business Semantics

The system needs to understand concepts such as:

• Revenue
• Active Customer
• Contract Value
• Sales Region
• Gross Margin

These concepts may have multiple definitions depending on the business domain.

For example:

{
  "metric": "recognized_revenue",
  "definition": "Revenue recognized under Finance rules",
  "source_field": "revenue_ledger.recognized_amount",
  "time_dimension": "recognition_date",
  "owner": "Finance"
}

This tells the query planner what the business means.

Physical Data Relationships

The system also needs to know how physical tables connect.

For example:

customer_master
      |
      | customer_id
      |
sales_contract
      |
      | contract_id
      |
sales_order
      |
      | order_id
      |
invoice_header
      |
      | invoice_id
      |
payment_transaction

This tells the query planner where the information lives and how it may be accessed.

The two are complementary.

A semantic definition cannot automatically establish the correct physical JOIN path.

Likewise, discovering a relationship between two tables does not establish the business meaning of their fields.

Business semantics explain what the data means. Relationship intelligence helps establish how the data connects.

3. Build a Reusable Data Context Layer

Instead of making every query start from schema retrieval, introduce a reusable data context layer.

The idea is straightforward:

Discover and organize useful metadata and relationships before they are needed by individual questions.

A simplified architecture:

                 Enterprise Databases
                         |
                         v
                Metadata Extraction
                         |
              +----------+----------+
              |                     |
              v                     v
       Semantic Layer       Relationship Engine
              |                     |
              v                     v
       Business Metrics      Candidate Join Paths
       Business Terms        Table Relationships
       Field Mappings        Field Relationships
              |                     |
              +----------+----------+
                         |
                         v
                 Structured Data Context
                         |
                         v
                  Text-to-SQL Agent
                         |
                         v
                  Query Planning
                         |
                         v
                   SQL Execution

The important change is that the agent no longer needs to rediscover everything from scratch.

It can retrieve previously established semantic mappings and relationship information as part of query planning.

4. What Should a Reusable Data Context Contain?

A practical data context should contain more than a list of relevant tables.

For example:

{
  "business_context": {
    "metric": "recognized_revenue",
    "definition": "Finance-approved revenue",
    "time_dimension": "recognition_date"
  },
  "physical_mapping": {
    "source_table": "revenue_ledger",
    "source_column": "recognized_amount"
  },
  "relationships": [
    {
      "source": "customer_master.customer_id",
      "target": "sales_contract.customer_id",
      "relationship_type": "one_to_many",
      "status": "candidate"
    },
    {
      "source": "sales_contract.contract_id",
      "target": "sales_order.contract_id",
      "relationship_type": "one_to_many",
      "status": "candidate"
    }
  ],
  "query_constraints": {
    "allowed_sources": [
      "revenue_ledger",
      "customer_master"
    ]
  }
}

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

Notice that relationship status is explicitly represented.

A candidate relationship should not automatically become an approved JOIN instruction.

The query planner must still consider the business question, available evidence, data grain, and applicable rules.

The purpose of the context layer is to provide reusable knowledge and evidence—not to remove the need for query validation.

5. Why This Architecture Matters for Multi-Step Queries

Consider a user asking:

"Which customers generated the most revenue, and how did their performance change over the last four quarters?"

This is more complex than a single SQL query.

The agent may need to:

1. Identify the approved revenue metric.
2. Find the customer dimension.
3. Retrieve the relevant relationship path.
4. Determine the correct reporting date.
5. Generate the initial aggregation.
6. Compare quarterly results.
7. Explain significant changes.

If every stage independently retrieves schema and infers relationships, the system can introduce inconsistencies.

For example:

• The first query uses recognized revenue.
• The second query accidentally uses invoice amounts.
• The customer relationship changes between steps.
• The date dimension is interpreted differently.

A reusable context provides a common foundation across the workflow.

The agent can carry forward the resolved metric, selected dimensions, relationship evidence, and applicable constraints.

This is especially important when multiple queries contribute to one final answer.

6. Don't Turn Reusable Context into Stale Context

There is an important engineering trade-off.

A reusable context layer should not simply cache everything forever.

Enterprise databases change:

• Tables are added or removed.
• Columns are renamed.
• Business metrics are revised.
• Data relationships change.
• Permissions are updated.
• Some historical mappings become obsolete.

A practical implementation should track:

Data Source ID
Schema Version
Metadata Timestamp
Semantic Definition Version
Relationship Discovery Version
Approval Status
Applicable Scope

When a relevant source changes, the system should identify affected metadata and relationships and determine what needs to be refreshed or reviewed.

Permissions must also be evaluated at query time. Reusing metadata must never mean reusing another user's data access rights.

Reusable knowledge is valuable only when its freshness, scope, and authority are understood.

7. Where Semora and IntaLink Fit

This is closely related to the architecture we are developing with Semora and IntaLink.

Their responsibilities are deliberately different.

IntaLink focuses on data relationship discovery.

It manages data-source metadata and discovers relationships between tables and fields. These relationship results can be exposed through APIs for downstream systems.

Semora focuses on business semantics and intelligent querying.

It manages its own data connections and permissions, maintains business terminology, metrics, dimensions, and semantic mappings, and uses relationship information from IntaLink during query processing.

The integration uses data-source ID mapping rather than transferring data ownership between the platforms.

A simplified flow:

User Question
      |
      v
    Semora
      |
      +---- Business Semantics
      |
      +---- Data Source Mapping
      |
      v
 IntaLink API
      |
      v
 Relationship Information
      |
      v
 Semora Query Planning
      |
      v
 SQL Generation & Validation
      |
      v
 Query Execution

This provides a foundation for separating business meaning from physical data relationships.

It is important to distinguish this existing division of responsibilities from the broader architecture proposed in this article. A fully automated context registry, cross-source freshness management, and comprehensive relationship approval workflow would require additional product development and validation.

8. What Should We Measure?

If a reusable data context layer is introduced, measuring SQL execution success alone is insufficient.

I would track:

Metric	What it measures
Context Reuse Rate	How often existing semantic and relationship information is reused
Schema Retrieval Precision	Whether relevant tables and fields are retrieved
Relationship Path Accuracy	Whether the selected path matches the intended business relationship
Context Freshness	Whether retrieved metadata is current
Semantic Consistency	Whether multi-step queries preserve the same business definitions
End-to-End Query Accuracy	Whether the final result matches the intended business question

One particularly useful experiment is to compare two approaches:

Approach A: Retrieve schema and infer relationships independently for every question.

Approach B: Reuse existing semantic mappings and relationship information.

Use the same database, questions, and evaluation criteria.

Then compare accuracy, latency, retrieval cost, and the number of incorrect assumptions.

This provides measurable evidence of whether a reusable data context layer actually improves the system.

Final Thoughts

The next improvement in Text-to-SQL may not come from a larger model or a more sophisticated prompt.

It may come from changing how the system manages what it already knows.

Enterprise AI should not have to rediscover the same business definitions and data relationships every time a question is asked.

Discover once. Validate carefully. Reuse intelligently.

The goal is not to give every query more context.

It is to give every query the right context, with the right evidence, at the right time.