CORE-T: COherent REtrieval of Tables for Text-to-SQL

AI summary

Overview

Research area: Natural Language Processing — table retrieval for text-to-SQL, with connections to information retrieval and data-lake/semantic join discovery.

Technical level: Intermediate. The paper assumes familiarity with dense retrieval, top-K retrieval, foreign-key joins, and execution accuracy as an evaluation metric, but the pipeline itself is described without heavy mathematical machinery in the main text (scoring equations are deferred to appendices).

Scope: The paper proposes CORE-T, a training-free retrieval framework that returns compact, join-coherent sets of tables for multi-table text-to-SQL queries over pooled, "open-book" table corpora where database identifiers (db_id) and gold foreign keys are unavailable.

What This Paper Is About

Realistic text-to-SQL workflows often need to join several tables, so retrieving the correct set of tables — not just individually relevant ones — is a key bottleneck for end-to-end accuracy. Most prior work assumes a closed-book setting where the target database and schema graph are known and retrieval is confined to a small schema, whereas this paper studies an open-book setting where queries must be answered over large, heterogeneous table collections pooled from many sources with no clean scoping signals. The goal is a scalable, training-free retriever that jointly exploits query–table relevance and table–table compatibility to produce compact, join-ready schema slices.

Key Contributions

1. Training-free join-coherent table-set retrieval. CORE-T couples purpose-enriched query–table retrieval with cached table–table compatibility evidence to produce compact, join-coherent schema slices without db_id scoping or gold foreign-key annotations.
2. An offline/online split that keeps inference light. Reusable schema understanding — LLM-generated table purpose metadata and a pre-computed compatibility cache — is moved offline, leaving a single LLM call plus a two-step additive adjustment online.
3. Improved effectiveness and efficiency under pooled evaluation. Compared against DR, ReAct, and join-aware methods (JAR/ARM/REAR) on pooled multi-database evaluation across Bird, Spider, MMQA, and Beaver, CORE-T improves the precision–recall balance for multi-table retrieval, yields more coherent table sets for SQL generation, and reduces LLM usage (up to about 5× fewer input tokens than heavier multi-draft LLM generation methods such as ARM).
4. A two-step additive adjustment stage. Seeded compatibility-based augmentation plus greedy expansion restores strongly compatible tables that the LLM selector pruned, targeted specifically at recovering join-critical tables rather than performing generic expansion.

Main Findings

• Table-selection gains over dense retrieval. Across Bird, Spider, MMQA, and Beaver, CORE-T improves over DR by up to 22.7 points in table-selection F1 while returning up to 40% fewer tables, and by up to 24.4 points in multi-table execution accuracy.
• Bird results against join-aware baselines. With Llama-3.1-8B-Instruct as selector, CORE-T reaches 62.3 F1 versus ARM (53.5), JAR@5 (50.1), and REAR (51.0), while returning fewer tables than ARM and ReAct (4.1 versus 5.3 and 4.8). With Qwen-2.5-7B-Instruct it reaches 72.3 F1 versus ARM at 68.3.
• MMQA and Beaver. On MMQA with Qwen selection, CORE-T reaches 56.4 F1 versus ReAct (50.4) and REAR (39.2). On the noisier Beaver benchmark it achieves the strongest F1 with Qwen (43.0) and the highest perfect recall with both selectors (17.7 with Qwen, 15.3 with Llama).
• Downstream execution accuracy. With Llama-3.2-3B as SQL generator, CORE-T is the strongest non-oracle method on Bird, Spider, and MMQA (15.7, 34.4, and 18.9 EX_MT), with gains over REAR of +8.0 and +10.5 EX_MT points on Spider and MMQA. On MMQA it improves EX_PR over REAR by 8.9 points with Llama-3.2-3B (15.8 versus 6.9), showing that compactness and coherence matter even when the required tables are present.
• Headroom remains. The Oracle setting shows substantial room: on MMQA, CORE-T reaches 35.8 EX_MT with GPT-4o-mini while Oracle reaches 65.8. On Beaver, even the gold-table Oracle reaches only 6.8 EX_MT, indicating bottlenecks beyond retrieval such as schema anonymization and empty tables.
• Efficiency. On Bird, CORE-T uses 12.5M total selection tokens versus ARM's 52.4M (a 4.20× reduction), reflecting ARM's multi-draft LLM overhead. Across datasets it uses up to 4.99× fewer input tokens than ReAct and up to 4.16× fewer total selection tokens.
• Ablation: selection drives the gains. Moving from DR@10 to +Selection raises F1 from 31.0 to 80.0 on Bird with Qwen and from 25.7 to 59.7 on Spider with Llama, while cutting returned tables from 10 to 2.1–4.9. Adjustment then recovers recall with modest F1 cost — on Beaver with Qwen, F1 drops only 0.6 points (43.6 to 43.0) while PR rises 2.4 points (15.3 to 17.7); on MMQA with Qwen, PR rises 8.1 points (51.3 to 59.4).
• Execution-level ablation. Selection increases EX_MT in 20 of 24 selector–generator–dataset settings and EX_all in 19 of 24. Adjustment improves or ties Selection-only on EX_MT in 16 of 24 and EX_all in 13 of 24. The full pipeline improves or ties DR@10 in 22 of 24 settings for EX_MT and 23 of 24 for EX_all. The largest single gain is 26.3 EX_MT points on Spider with Llama-3.2-3B as generator and Llama-3.1-8B as selector (8.1 to 34.4).
• Threshold stability. Sweeping τ_comp over {0.3, 0.5, 0.7} changes F1 by at most 4.1 points, PR by at most 1.4 points, and the average number of returned tables by at most 0.4; the paper adopts τ_comp = 0.5 across all datasets and selectors.
• Adjustment as targeted repair. Adjustment recovers at least one dropped gold table in 9.1–57.1% of dropped cases and all dropped gold tables in 9.1–50.8%. For low-relevance join-critical bridge tables, it fully recovers dropped bridge tables in 22.5–57.1% of affected queries.
• Statistical testing. Across 19 dataset–baseline F1 comparisons, CORE-T is significantly better in 17, tied in one, and worse only on Spider against ReAct. Across 57 EX comparisons, it is significantly better in 30 and tied in 27, with no significant losses.
• Error profile. On Bird against ARM, CORE-T reduces distractor-table precision errors from 72.5% (1,112 queries) to 55.7% (855), with a modest rise in recall issues (22.6% versus 19.0%). When generated SQL uses exactly the gold tables, formatting errors drop from 7.4% to 0.1%, while schema-linking errors rise (7.0% versus 2.4%).
• Qualitative trade-offs. Adjustment can fully restore a missing bridge table (50.0% to 80.0% F1), partially restore a join path while adding noise (57.1% to 66.7%), or add an unnecessary table when all gold tables are already present (66.7% to 57.1%).

Methodology in Plain English

The paper treats retrieval as a systems design problem rather than a new neural model, splitting work into an offline phase and a lightweight online phase.

Offline, for every table: each table is serialized as a 5-row Markdown snapshot — five rows sampled uniformly without replacement, giving a lightweight value snapshot without feeding in full tables — and augmented with an LLM-generated purpose description summarizing the table's contents and typical use (key entities, attributes, granularity). The Markdown snapshot and purpose text are concatenated, embedded, and stored in a FAISS index.

Offline, for every pair of tables: the system approximates joinability without foreign keys using column-level signals. Each column header (table name plus column name) is embedded, and for each cross-table column pair the system computes header similarity (exact lexical plus embedding-based semantic), value overlap (Jaccard), and two relational constraints meant to mimic key–foreign-key joins: uniqueness and subset, all ignoring nulls. These are combined into a column-pair compatibility score in [0,1], but only for pairs where at least one column is unique and the values exhibit a subset relation — a hard constraint that suppresses spurious matches from generic columns like id or name. The table–table compatibility score is the best valid column match, with the argmax column pair cached; if no valid pair exists, the score is zero.

Online, given a query: dense retrieval over the enriched table embeddings returns a top-K candidate set (the paper uses DR@10 as the high-recall starting point). One LLM call, prompted as a "SQL schema analyst" and given the query, an indexed list of the K candidate tables, and compatibility evidence for all pairs with positive score, selects a connected, coherent subset parsed from JSON. Because the prompt instructs conservative pruning, this subset is typically smaller and more coherent but may over-prune compatible tables.

Online adjustment: the selected tables serve as a seed group. Each seed gets its strongest compatibility neighbor from the original top-K set added, but only when the cached score exceeds the threshold τ_comp. The resulting group is then expanded greedily: candidates are eligible only if they have nonzero compatibility with the current group, and among eligible candidates the one with the largest marginal gain — the combined improvement in mean pairwise compatibility plus mean relevance to the query — is added until no candidate yields positive marginal gain. A robust DR@10 fallback handles parsing failures.

The method is evaluated on dev splits of Bird and Spider (which allow comparison with join-aware retrievers), plus MMQA and Beaver as larger, multi-table-intensive stress tests. For Bird, Spider, and MMQA, tables are pooled across databases or question-specific schemas and db_ids are removed; Beaver is already open-book and only requires db_id removal. For MMQA, a stratified one-third subset of 1,105 queries is used.

Why This Matters

Impact on research. The paper reframes table retrieval for text-to-SQL from a query–table relevance problem into a query–table relevance plus table–table compatibility problem, and shows that a training-free design can outperform join-aware systems that rely on database scoping assumptions or multi-draft LLM overhead. It also provides a pooled, db_id-free evaluation protocol over Bird, Spider, MMQA, and Beaver that other retrieval work can reuse.

Real-world applications:

• Enterprise data lakes and integrated analytics corpora, where tables are pooled from many sources and clean scoping signals such as database identifiers are unavailable.
• Natural-language interfaces to structured data that let non-experts query relational tables in everyday language, particularly for analytical questions requiring evidence composed across normalized tables.
• Semantic join discovery and dataset search, where relevant and joinable tables must be found without a predefined schema graph

Authors’ abstract

Realistic text-to-SQL workflows often require joining multiple tables. As a result, accurately retrieving the relevant set of tables becomes a key bottleneck for end-to-end performance. We study an open-book setting where queries must be answered over large, heterogeneous table collections pooled from many sources, without clean scoping signals such as database identifiers. Here, dense retrieval (DR) achieves high recall but returns many distractors, while join-aware alternatives often rely on extra assumptions and/or incur high inference overhead. We propose CORE-T, a scalable, training-free framework that enriches tables with LLM-generated purpose metadata and pre-computes a lightweight table-compatibility cache. At inference time, DR returns top-K candidates; a single LLM call selects a coherent, joinable subset, and a two-step additive adjustment stage restores strongly compatible tables. Across Bird, Spider, MMQA, and Beaver, CORE-T improves over DR by up to 22.7 points in table-selection F1 while returning up to 40% fewer tables, and by up to 24.4 points in multi-table execution accuracy, and uses 1.64-4.20x fewer total selection tokens than LLM-intensive baselines.