Six Tables Out of 260

Background and Context

Text-to-SQL systems create a strong impression of simply converting natural language into queries, but the reality of deployment hides a step that few engineers write about. Before a model produces any SQL, something must decide which tables it is allowed to see. A real database can contain 260 tables, and a prompt window cannot hold all of their schemas, nor should it, since cramming them in dilutes attention and raises latency and cost. The system must therefore filter down to a small candidate set before generation.

This filtering step is almost always treated as a retrieval problem. Each table's schema is encoded into a vector embedding, the user's question queries those embeddings, results are ranked by similarity, and the top six tables are selected. This pipeline performs adequately in demonstration environments, where the schemas tend to be tidy, well-named, and clean. Questions map directly onto table names, so the natural hit rate of retrieval stays high, and the industry has quietly assumed the problem is solved.

Deep Analysis

The author recognized the gap between this demo-level confidence and production-level fragility, so they deliberately constructed a schema designed to break the mechanism and ran their own library against it. The result was a recall figure they would rather not have published, which turned out to be the most valuable finding. The core problem is that retrieval relevance is not the same as query correctness. RAG-style table selection depends on semantic similarity between the question text and the schema, but SQL actually requires logical reachability between the table and the query intent.

A table may share nothing in its naming or fields with a question yet still be essential to answering it. Consider a table named audit_log, asked in service of the question about how many users changed their password this month. The real answer may depend on a record in a user_actions table where a column is hardcoded as action_type=7, while the words password and change never appear in the schema at all. Text-similarity retrieval will discard it without hesitation. Conversely, a table whose name happens to collide with a keyword may be pure noise. The situation worsens with foreign keys: correct answers often require joining multiple tables, yet top-K retrieval scores each table independently and knows nothing about its neighbors, so it cannot detect structural dependencies like the need to select table B alongside table A.

Industry Impact

This finding undermines an implicit assumption behind a large number of existing text-to-SQL products. For companies building BI and data-analysis tools, table selection is the upstream gate that determines final accuracy. If that gate fails, no amount of downstream SQL generation or error correction can recover, and users simply see a system that cannot answer questions they know are answerable, without understanding why.

Small businesses and internal tool teams are especially exposed, because their databases often carry messy naming, heavy historical baggage, and highly implicit semantics, exactly the conditions where a deliberately broken schema excels. The distance between demo performance and real experience becomes glaring. For the developer community, the analysis redirects attention away from whether a model is large enough or how a prompt should be written, back toward the more fundamental, understudied engineering concern of schema retrieval and organization itself.

Outlook

Several directions deserve monitoring. The most important is whether join relationships, constraints, and field-value distribution information can be folded into retrieval rather than relying on text similarity alone, which may be the key to breaking the current ceiling. A second question is whether dedicated evaluation benchmarks will emerge, using deliberately constructed trap schemas to measure the true floor of recall instead of reporting only attractive demo numbers. A third is whether hybrid retrieval and reranking can genuinely address structural dependencies, for example by coarsely filtering first and then expanding relationships using graph structure.

For teams building or selecting a text-to-SQL system, the most practical move is to stop treating demo-schema metrics as conclusions. Instead, run table recall against data that matches the real complexity of your own database and observe how end-to-end accuracy degrades when needed tables are simply not selected. That number often says more than any model benchmark.