Designing a Production-Grade Text-to-SQL Pipeline

Text-to-SQL demos usually look like this:

Question → LLM → SQL → Result

That is fine for a controlled dataset.

It is not enough for a production system.

In a real warehouse, the model has to deal with duplicated concepts, undocumented joins, multiple date fields, inconsistent naming, and tables that were never designed for AI access.

The hard part is not generating SQL.

The hard part is building the context the model needs before generation.

Start with the question, not the schema

Take a simple request:

Show revenue by customer segment for last quarter.

A model can turn that into SQL quickly. But before it does, the system needs to answer a few basic questions:

• Which revenue definition should be used?
• Which date field represents the reporting period?
• Is customer segment current or historical?
• Which customer table is authoritative?
• Which join path avoids duplicating revenue?

Those decisions should not be left to the model to guess.

A better pipeline resolves them before SQL generation.

A more realistic workflow

A production flow looks closer to this:

User Question
    ↓
Intent Parsing
    ↓
Semantic Mapping
    ↓
Metadata Retrieval
    ↓
Relationship Discovery
    ↓
Join Path Selection
    ↓
SQL Generation
    ↓
Validation
    ↓
Execution
    ↓
Explanation

Each step has a separate job.

1. Intent parsing

Extract the actual request:

{
  "metric": "revenue",
  "dimension": "customer_segment",
  "time_range": "last_quarter"
}

This is also where the system should detect ambiguity.

For example, “revenue” may refer to booked, invoiced, recognized, or paid revenue.

2. Semantic mapping

Map the user’s language to governed business definitions.

{
  "metric": "recognized_revenue",
  "formula": "SUM(invoice_line.recognized_amount)",
  "time_field": "invoice_line.recognition_date"
}

This prevents the model from choosing fields based only on similar names.

3. Metadata retrieval

Retrieve only the relevant tables and columns.

Passing the entire warehouse schema into the prompt usually creates more noise than value.

The model should receive a narrow working set:

customer
customer_segment_history
invoice
invoice_line

4. Relationship discovery

This is where many systems remain weak.

Foreign keys are useful, but enterprise databases often have missing, incomplete, or misleading constraints.

A relationship layer should provide more than table names. It should include:

• source and target columns
• relationship direction
• cardinality
• confidence
• known fanout risk
• preferred usage

For example:

{
  "from": "invoice.customer_id",
  "to": "customer.customer_id",
  "cardinality": "many_to_one",
  "confidence": 0.98,
  "fanout_risk": false
}

5. Join path selection

There may be several valid paths between the same business entities.

The shortest path is not always the safest one.

A good system should prefer a path that matches the query grain and metric definition, not just one that happens to connect the tables.

For the revenue example, joining directly to a current customer table may produce a valid result but lose historical segment accuracy.

The correct path may require a segment history table and an effective-date condition.

Validation has to be explicit

SQL execution is not validation.

A query can run successfully and still return the wrong answer.

At minimum, the validation stage should check:

- Are the selected tables approved for this metric?
- Does the join path match the required grain?
- Can the join duplicate fact rows?
- Are filters applied to the correct date field?
- Are permissions respected?
- Is the aggregation consistent with the metric definition?

Some checks are static. Others require running a small test query.

For example, a join can be tested for row multiplication before the final query is executed.

SELECT
    COUNT(*) AS rows_before,
    COUNT(DISTINCT invoice_line.id) AS distinct_rows
FROM invoice_line
JOIN customer
  ON invoice_line.customer_id = customer.customer_id;

If those numbers drift unexpectedly, the pipeline should stop.

Clarification is part of the system

One of the most useful behaviors in Text-to-SQL is asking a question instead of generating one.

Do you mean recognized revenue or invoiced revenue?

That is not a failure.

It is often the safest possible response.

A production system should know when the available semantic or relationship context is not strong enough to proceed.

Keep the reasoning visible

The final response should include more than the result.

A useful explanation might show:

Metric: Recognized Revenue
Time Field: recognition_date
Tables Used: invoice_line, customer_segment_history
Join Path: invoice_line.customer_id → customer_segment_history.customer_id
Validation: No fanout detected

That gives analysts a chance to review the logic and gives data teams something they can audit.

The model is not the whole pipeline

The LLM is still important. It can parse questions, generate SQL, explain results, and handle conversation.

But production reliability comes from the surrounding system:

Semantic context
+ Metadata
+ Trusted relationships
+ Validation
+ Governance

That is the difference between a query that looks reasonable and a query that can be trusted.

At Arisyn, we split those responsibilities across two layers: Semora handles business semantics, query reasoning, SQL generation, validation, and explanation, while IntaLink provides the table and field relationship context needed to choose safer data paths.

The SQL is generated near the end.

Most of the real work happens before it.