When we started building the query layer for TableAI, we underestimated one specific problem: the gap between producing syntactically valid SQL and producing semantically correct SQL. These are not the same thing, and conflating them is one of the most common failure modes in NLP-to-query systems. This post describes the architecture we landed on and why each layer exists.
The short version: turning a plain-language question into a correct multi-source query requires at least four distinct subsystems. Each one solves a problem the previous layer cannot. You cannot skip ahead.
Layer 1: Schema indexing and metadata enrichment
The foundation is a read-only schema index. Before a single question can be answered, the system needs a complete, current representation of what data exists across all connected warehouses. This means table names, column names, data types, inferred relationships, and where available, column-level descriptions.
This sounds straightforward. In practice, warehouse schemas are rarely clean. Column names in production systems tend to accumulate historical naming decisions: cust_id, customer_id, and user_id may all refer to the same concept in different tables. Date columns named created in one table mean event timestamp; in another, record creation time in a different timezone. The schema index needs metadata enrichment on top of raw schema introspection to be useful.
We handle this through a combination of statistical analysis (columns with high cardinality and small integer ranges in certain positions often serve as identifiers) and description inference using column name patterns and sample value distributions. This is not perfect. Ambiguously named columns get flagged for manual annotation. The enrichment improves query accuracy significantly on most schemas while leaving deliberate escape hatches for unusual naming conventions.
Layer 2: Intent parsing and entity extraction
A question like "what were the top-selling products in Q3 by revenue, broken out by region" contains several distinct components: a metric (revenue), a dimension (products), a time filter (Q3), a grouping dimension (region), and an implied sort order (top-selling = descending by revenue). Each component needs to be extracted and matched to real schema entities.
The intent parsing layer takes the raw question, identifies these components, and produces a structured query intent representation. This is not a direct LLM prompt-to-SQL pipeline. Feeding raw questions directly to a text-to-SQL model without an intent step produces erratic results on anything beyond simple aggregations. The model needs a structured representation of what is being asked before it can reliably generate the right SQL.
The entity matching step within this layer is particularly sensitive. "Revenue" in the question needs to map to the right column and aggregation logic: is it a pre-calculated column in a summary table, or a derived field computed from quantity * unit_price? "Region" might be a column directly on the orders table, or a dimension table joined via a geography key. Resolving these correctly requires both the enriched schema index and contextual signals from the question itself.
Layer 3: Source routing and query decomposition
For single-source queries, this layer is simple: all matched entities live in one warehouse, generate one query, execute it. For cross-system questions, it becomes the hardest part of the architecture.
Cross-system questions require deciding which entities belong to which source, how the results will be joined, and what execution order minimizes latency. A question like "compare this quarter's campaign spend with order volumes" may have spend data in a marketing analytics system and order data in a transactional warehouse. They share no common tables. The only join key is a shared time dimension.
The router has to decompose the question into two sub-queries, identify the time-based join key, execute both queries (concurrently where possible), and specify how the result assembly works. Entity resolution is the hardest part of this: the router needs to know that campaign spend is grouped by campaign_date and orders are grouped by order_created_at and that these represent the same temporal dimension when aggregated to weekly or monthly granularity.
We deliberately do not attempt entity resolution that involves guessing. If the router cannot confidently identify a join key, it returns a clarification request rather than assembling a response from a non-matching join. A wrong answer that looks right is worse than no answer.
Layer 4: Dialect-specific query generation and execution
Different warehouses speak different SQL dialects. Snowflake's date arithmetic differs from BigQuery's. Redshift handles window functions differently than PostgreSQL. MySQL date formatting requires different function calls than any of the above. A cross-system query that targets two warehouses needs two separately generated, dialect-correct queries.
Our query generation layer takes the structured query intent and the target warehouse dialect as inputs and produces executable SQL. We maintain dialect templates for each supported warehouse and apply them during generation rather than attempting post-hoc SQL translation, which tends to produce subtle errors in complex queries.
One point worth emphasizing: query generation is the last step, not the first. Every step before this one exists to make query generation reliable. If you skip the schema enrichment, entity matching produces wrong mappings. If you skip intent parsing, query generation receives an underspecified input. The architecture is sequential by design.
Failure modes and where they appear
Each layer fails in characteristic ways. Schema indexing produces errors when warehouses have permission boundaries that prevent full introspection or when schemas change faster than the index refresh cycle. Intent parsing fails on highly domain-specific terminology that has no common counterpart in the schema metadata. Source routing fails on questions that span systems with no shared identifiers. Query generation fails when the structured intent representation is incomplete.
Handling these failures correctly matters as much as the happy path. A system that silently returns a wrong answer on an ambiguous question erodes trust faster than a system that returns nothing. Our design principle is: when confidence in any layer drops below a threshold, surface the uncertainty to the user as a clarification question rather than proceeding. This adds latency on edge cases but produces better outcomes on the questions that matter most.
What this architecture does not solve
This architecture handles the question-to-query-to-result pipeline well. It does not address downstream interpretation. Getting a correctly formed table back from the warehouse does not mean the person who asked the question will interpret it correctly. Return rates by region, correctly retrieved, can still be misread if the analyst does not know which regions were recently reorganized and therefore have incomplete data for the comparison period.
Data literacy and interpretation remain human work. The architecture removes the technical barrier between asking and receiving. It does not remove the judgment required to act on what you receive. We think that is the right division of labor: the system handles the translation, the person handles the thinking.
We are also not claiming this approach is the only viable architecture for NL-to-query systems. There are simpler pipelines that work well for single-source, well-documented schemas. Our four-layer approach is designed specifically for the cross-system case, where schema heterogeneity and multiple dialects make simpler approaches break in ways that are hard to debug. If your setup is one warehouse with a clean schema and a technical team, a lighter-weight approach may suit you better.