BroadOakData.UK

Enterprise Text-to-SQL: Moving Beyond “Guesswork” with Semantic Routing

Written by

broadoakdata

in

Introduction: The “Probabilistic” Trap in Data Engineering

The promise of Generative AI for data analytics is seductive: put a natural language interface on top of your Data Warehouse and let non-technical users ask, “How is Q3 revenue looking?”

In practice, this often fails spectacularly. When an LLM is connected directly to a database, it acts as a probabilistic guesser. It guesses table names, it guesses join keys, and most dangerously, it guesses business intent. If a user asks for “Tech headcount,” the LLM doesn’t inherently know if “Tech” refers to the Engineering Department or the Technician Job Role.

To resolve this, standard architectures often force the AI to run “exploratory queries” at runtime (e.g., SELECT DISTINCT department FROM...). This creates a deadly trilemma for enterprise systems:

  1. Latency: Exploratory queries kill response times (often exceeding 10+ seconds).
  2. Instability: High-concurrency exploration can unintentionally DDOS the production database.
  3. Hallucination: Without grounded facts, accuracy plateaus around 40-50% for complex queries.

We successfully implemented a “Semantic Routing” architecture that solves these pain points by decoupling reasoning from knowledge. This article details the technical approach, architecture, and findings from our implementation.

The Core Shift: From Runtime Exploration to Pre-Computed Knowledge

The fundamental principle of Semantic Routing is simple: Do not let the LLM guess. Make it retrieve.

Instead of asking the database “Do you have a ‘Tech’ column?” at query time, we pre-compute these mappings into a local Vector Index. When a user asks a question, we first “route” their intent through this index to retrieve the exact schema mapping, and only then ask the LLM to construct SQL.

The Architecture

  1. Ingest (The “Learning” Phase):
    • We run a scheduled “Sampling Scanner” that profiles the database (e.g., top 50 values per column) and ingests metadata definitions.
    • This maps business terms (e.g., “Fraud Ops”) to physical locations (e.g., legacy_roster.unit_id) and stores them in a local Vector Store (e.g., LanceDB).
    • Critically, this happens offline, protecting the production DB from user load.
  2. Retrieval (The “Routing” Phase):
    • When a query arrives (“Show me Fraud Ops roster”), we embed the prompt and search the Vector Store.
    • The store returns a “Semantic Context”: “Mapping: ‘Fraud Ops’ is a value in column ‘unit’ of table ‘legacy_roster’.”
  3. Generation (The “Reasoning” Phase):
    • We construct a prompt containing only the relevant schema (schema isolation) and the retrieved mappings.
    • The LLM (we tested Qwen2.5-3B) uses this context to write deterministic SQL without needing to explore the DB.
  4. The Firewall (The “Security” Phase):
    • The generated SQL passes through a Python-based PII Firewall. If a sensitive column (e.g., ssn) is detected in the projection, it is physically masked before data is returned to the user.

Technical Techniques & Implementation Details

1. Zero-Touch SQL Generation

Traditional “LangChain SQL Agent” approaches often give the LLM tools to execute SQL LIMIT 5 to inspect data. We strictly forbade this. By providing the data samples in the prompt via RAG, the LLM generates the final SQL in one shot.

  • Result: Reduced database load by ~90% (1 query vs. 5-10 exploratory steps).

2. Tenant Isolation via Schema Partitioning

For multi-tenant SaaS environments, reliance on “prompt instructions” (“Do not show Company B’s data”) is insufficient.

  • Technique: We implemented a hard-coded Schema Access Control layer in Python. When a user from ORG_A queries the system, the LLM is only provided the schema definitions for ORG_A. It physically cannot hallucinate or leak ORG_B‘s tables because it does not know they exist.

3. PII Schema Firewall

We moved security out of the “probabilistic” layer (LLM) and into the “deterministic” layer (Code).

  • Technique: Metadata columns are tagged sensitive: true. The execution engine parses the resulting DataFrame columns. If a tagged column is present, the values are overwritten with ***REDACTED*** at the bytecode/dataframe level before the response is serialized.

4. Few-Shot “Brainwashing”

Smaller, faster models (like 3B parameters) are often “chatty” and prone to syntax errors.

  • Technique: We used strict Few-Shot Prompting, providing 3 perfect “Question -> SQL” pairs in the system prompt. This forced the model to abandon conversational filler (“Here is your query…”) and output raw, executable SQL.

Results & Findings

We benchmarked this architecture using a simulated HR & Finance data warehouse with complex joins (Fact vs. Dimension) and legacy schema mappings.

1. Performance Metrics

  • Latency: The end-to-end response time dropped from typical 10s+ (exploratory) to ~1.4 seconds.
    • Breakdown: Retrieval (20ms) + Generation (1.1s) + Execution (0.3s).
  • Efficiency: The 3B parameter model (Qwen2.5-3B) outperformed the 1.5B model significantly in reasoning.
    • Finding: The 1.5B model was easily distracted by irrelevant context (hallucinating filters based on “noise” in the RAG retrieval). The 3B model correctly ignored irrelevant context and focused only on the user’s specific intent.

2. Accuracy & Hallucination

  • Complex Joins: The system achieved 100% success in generating correct 3-table JOINs (e.g., fact_payroll -> dim_employees -> dim_departments) by relying on the semantic context rather than guessing foreign keys.
  • Ambiguity Resolution: The system successfully distinguished between “Tech” (Department) and “Tech” (Job Title) purely based on the retrieved context, eliminating the primary cause of previous failures.

3. Operational Stability

  • Database Health: By moving the “vocabulary learning” to a scheduled offline job, we eliminated the risk of “Exploratory DDOS” during peak hours. The production database now only processes valid, final analytical queries.

Conclusion

The transition from a naive “Text-to-SQL” chatbot to a Semantic Routing Engine transforms Generative AI from a novelty into a production-grade data tool. By treating schema mapping as a retrieval task and enforcing security at the code layer, organizations can achieve the trifecta of Speed, Accuracy, and Safety that CTOs demand.

This architecture proves that you do not need massive, expensive models to solve enterprise data problems—you need better architecture.

More posts