¹

1 Introduction↩︎

Information retrieval over code has emerged as a distinct evaluation target, with benchmarks such as CoIR [1] and CoQuIR [2] measuring how well embedding models retrieve code given a natural-language intent. SQL increasingly falls within this scope: text-to-SQL, long studied as a generation task on benchmarks such as WikiSQL [3], Spider [4], and BIRD [5], is now also framed as retrieval, in which a question is matched against a collection of SQL statements or code snippets. One retrieval step specific to databases, however, remains largely absent from these benchmarks: given a question and a database, retrieving the tables and columns that the question refers to.

This schema retrieval step is indispensable at scale. Enterprise data warehouses routinely contain hundreds to thousands of tables and tens of thousands of columns [6], far more than fit in a model’s context window or than a downstream component can reasonably process. Selecting the relevant schema elements first, a form of schema linking [7], is therefore a prerequisite for database tasks over large schemas, and it constitutes a natural retrieval problem: ranking schema documents by their relevance to a query. Nevertheless, schema retrieval is rarely evaluated as a retrieval task in its own right; existing code- and SQL-retrieval benchmarks target whole programs or queries rather than the schema elements underlying them.

In this work, we establish schema retrieval as a first-class task. Our contributions are as follows:

• A benchmark that casts schema linking as document retrieval at both table and column granularity, derived from five text-to-SQL datasets spanning academic (Spider), realistic (BIRD), enterprise (BEAVER), and large live-database (LiveSQLBench) settings, under two document representations (§3).

• An evaluation of general-purpose, code-specialized, and instruction-tuned embedders spanning 0.1B to 8B parameters, showing that even strong off-the-shelf models transfer poorly to schema retrieval (§5).

• Corpus-adaptive fine-tuning (§4): given only the target schema corpus, we synthesize grounded queries and mine granularity-aware hard negatives, raising a 305M-parameter embedder from 60.4 to 75.6 recall@10, the best result under one billion parameters and within three points of 4–8B models; the same recipe also improves an 8B state-of-the-art embedder (§5).

• Validation: leave-one-corpus-out experiments and a query/label leakage audit (§5, Appendices 7–8) demonstrate that the improvements stem from generalization rather than memorization.

2 Related Work↩︎

2.0.0.1 Text-to-SQL and schema linking.

Cross-domain benchmarks [4], [5], together with recent enterprise [6] and large-database [8] settings, have driven rapid progress on text-to-SQL, but evaluation has centered on end-to-end execution accuracy. Schema linking, identifying the schema elements that a query requires, is a well-studied sub-problem [7], [9], [10]; as schemas grow beyond what fits in context, it is increasingly implemented as a retrieval step whose output is passed to an LLM, which motivates evaluating it directly.

2.0.0.2 Dense retrieval and embedding models.

Dual-encoder retrievers [11], [12] and contrastively pre-trained text embedders [13]–[16] dominate first-stage retrieval, with late-interaction [17] and code-specialized [18], [19] variants developed for structured inputs. Standard text- and code-retrieval benchmarks [1], [2], [20], [21] evaluate zero-shot transfer but do not cover schema retrieval, a setting in which we find that even strong general-purpose and code embedders underperform.

2.0.0.3 Synthetic data and domain adaptation.

Generating queries from documents to train retrievers has a long lineage, spanning document expansion [22], LLM-based query generation [23], [24], and generative pseudo-labeling for domain adaptation [25]; hard-negative mining is central to the quality of the resulting training data [26], [27]. Our method belongs to this lineage but is tailored to schema retrieval: it is granularity-aware, jointly handling table- and column-level documents, and it adapts the embedder to the target corpus.

3 The Schema-Retrieval Benchmark↩︎

3.0.0.1 Task.

Each database defines a corpus \(D\) of schema documents. At table granularity, each document describes one table; at column granularity, each document describes one column together with its table context. Given a natural-language query \(q\), the relevant set \(R(q)\subseteq D\) comprises the schema elements referenced by the gold SQL; we obtain \(R(q)\) by parsing table and column references from the ground-truth SQL. Systems rank the documents in \(D\) for each query. We adopt recall@10 as the primary metric, since downstream generation requires the needed schema elements to appear among the top-ranked candidates, and additionally report nDCG@10. Retrieval is performed per database group, mirroring deployment, in which a system retrieves within the schema of the database at hand.

3.0.0.2 Sources and representations.

We derive the benchmark from five datasets (Table 1): Spider [4] (cross-domain, simple schemas), BIRD [5] (realistic schemas with value semantics), BEAVER [6] (private enterprise warehouses), and the base and large variants of LiveSQLBench [8] (large, obfuscated, real-world schemas). For BEAVER, BIRD, and Spider we include two document representations of each table: a schema-metadata form (DDL with keys plus sample rows) and a value-only form (sample rows alone).

4pt

4 Corpus-Adaptive Fine-Tuning↩︎

In in-domain retrieval, the corpus of schema documents is available at indexing time. We exploit this standard assumption to adapt an embedder to a target corpus without any labeled queries, in three steps (Figure 1).

4.0.0.1 (1) Document-anchored query synthesis.

Prompting an LLM to write queries for an entire schema yields uneven coverage and biases generation away from abbreviated identifiers. We therefore anchor generation on target documents: we sample schema elements (individual columns, coherent column groups, and foreign-key-connected table sets) and prompt an instruction-tuned LLM to write a natural-language query answerable by exactly those elements, without naming them literally. Because the target elements are fixed in advance, the gold set is known by construction, with no SQL parsing required, and coverage of every table and column, and of both granularities, is guaranteed. We additionally generate queries for both document representations. The full prompt templates are given in Appendix 9.

4.0.0.2 (2) Granularity-aware hard negatives.

We embed all queries and documents and, for each query, retrieve the nearest non-gold documents as hard negatives [26]. Crucially, negatives are mined within the same (source, granularity) pool against which the query is evaluated: table-level queries draw table-document negatives, and column-level queries draw column-document negatives, excluding the gold columns’ parent tables and sibling columns. This design avoids a failure mode that arises when negatives are mixed across granularities, in which table documents become negatives for column queries and vice versa, degrading retrieval at the opposite granularity.

4.0.0.3 (3) Contrastive fine-tuning.

We fine-tune Arctic-Embed-M [15] (305M parameters) with a standard contrastive objective, using one positive and six hard negatives per query, and mix the synthesized in-domain data with existing schema-linking pairs to preserve general retrieval ability. Implementation details are given in Appendix 6.

3.5pt

3.5pt

5 Experiments and Results↩︎

5.0.0.1 Setup.

We compare general-purpose [14], [15], code-specialized [18], [19], and instruction-tuned [28] embedders ranging from 0.1B to 8B parameters, together with a BM25 lexical baseline, against our corpus-adaptive models (Tables 2 and 3). We report recall@10 and nDCG@10.

5.0.0.2 Schema retrieval is challenging even for strong embedders.

Among off-the-shelf models, only the 4B and 8B Qwen3-Embedding models exceed 75 recall@10 (Tables 2–3); smaller general-purpose and code embedders trail substantially. The BM25 lexical baseline is weakest overall (47.7 recall@10) and collapses to 5.5 on LiveSQLBench-Large column retrieval, where abbreviated identifiers share almost no surface forms with natural-language queries. Scale alone is not sufficient even within a model family: the 2B CodeXEmbed (65.7 recall@10) underperforms its 400M sibling (71.7), and the 8B Qwen3-Embedding trails the 4B model on the largest schemas (LiveSQLBench-Large). The value-only representation, which exposes only sample rows, is consistently the hardest setting, with most models below 45 recall@10 on BEAVER.

5.0.0.3 Corpus-adaptive fine-tuning is parameter-efficient.

Our recipe raises the 305M Arctic-Embed-M from 60.4 to 75.6 recall@10 (51.9 to 68.0 nDCG@10), making it the strongest retriever under one billion parameters and competitive with the 4B and 8B Qwen3-Embedding models (78.4 and 77.8 recall@10, respectively) despite being more than an order of magnitude smaller; it surpasses the 2B CodeXEmbed by 9.9 recall@10 points. Gains are largest where schemas are hardest: on LiveSQLBench-Large, table retrieval improves from 59.1 to 74.1 recall@10 and column retrieval from 20.5 to 33.5. Training on both document representations sharply improves value-only retrieval (e.g., BIRD: \(75.3\rightarrow97.5\) recall@10), confirming that representation coverage matters.

5.0.0.4 Corpus adaptation also improves a strong backbone.

The recipe is not specific to a small encoder. Applying the identical pipeline, data, and objective to Qwen3-Embedding-8B (Appendix 6) raises its average recall@10 from 77.8 to 78.4, matching the strongest model on the benchmark (Qwen3-Embedding-4B), and raises nDCG@10 from 69.7 to 71.7, the highest of any model we evaluate. As with the 305M backbone, the recall gains are largest on the hardest, value-only settings (BEAVER \(52.1\rightarrow56.7\), BIRD \(95.4\rightarrow98.7\)) and on LiveSQLBench-Large tables (\(69.5\rightarrow72.5\)), partly offset by a decline on LiveSQLBench base (\(91.7\rightarrow86.5\)). That adaptation improves even a near-state-of-the-art 8B embedder suggests that it complements scale rather than substitutes for it: the headroom our benchmark exposes is not closed simply by adopting a larger off-the-shelf model.

5.0.0.5 Ablation.

We ablate the two ingredients that distinguish our recipe from naive document-anchored synthesis: table-level positives and granularity-aware negatives. Removing both (i.e., column-only synthesis with negatives mined across granularities) collapses table-level retrieval from 80.9 to 52.4 recall@10, below the base model’s 65.4, while column-level retrieval declines moderately (\(33.5\rightarrow29.2\)) yet remains well above the base model’s 20.5 (Table 4). Jointly modeling both granularities and isolating negatives by granularity is thus what preserves table-level retrieval.

4pt

5.0.0.6 The gains reflect generalization, not memorization.

In leave-one-corpus-out experiments (Appendix 8), removing a dataset entirely from synthesis retains most of its gain: a model that never sees BEAVER still attains 44.3/57.7 (value-only/schema-metadata) recall@10, compared with 12.5/42.2 for the base model, and a model that never sees LiveSQLBench-Large retains 69.3 (table) and 32.0 (column). A query-overlap audit (Appendix 7) finds that no synthetic query reconstructs an evaluation query with matching gold labels in five of six settings, and that only 2 of 2,147 do so in the sixth.

5.0.0.7 Error analysis: ordering vs.representation.

We examine where gold documents fall in the ranking (Table 5). For table retrieval, every gold table lies within the top 1000 for all four models, and most misses sit just outside the top 10 (ranks 11–100): the relevant tables are present in the embedding neighborhood, and the errors are errors of ordering. Column retrieval is harder and is partly a failure of representation: some gold columns never enter the top 1000 at all. This failure diminishes with scale, from 15.7% of gold columns for the 0.3B base model to 5.5% for the 8B base model. Corpus adaptation closes most of that gap for the small model (15.7%\(\rightarrow\)​8.8% unretrieved, approaching the 8B base) while also lifting its top-10 recall. For the 8B backbone, adaptation improves top-10 ordering (\(27.7\rightarrow29.4\)) but increases the unretrieved fraction (\(5.5\%\rightarrow11.2\%\)), suggesting that adapting an already-strong encoder trades some representational coverage for sharper ordering near the top. Column-level headroom is therefore largely a matter of surfacing identifiers that the embedder cannot represent, a failure that scale alleviates and that corpus adaptation addresses at a fraction of the model size.

2pt

5.0.0.8 Abbreviated column names drive difficulty.

Most gold columns are not named in natural language: 71% of gold-column names are abbreviations or codes (e.g., mtbfh, coordopsref, snopr_cd) rather than dictionary words. Table 6 reports column recall@10 by name type. All models degrade as names become less word-like, but the corpus-adapted models close much of this gap: they achieve the best results on the code bucket (31.4 for the 0.3B model and 32.3 for the 8B model), exceeding every off-the-shelf model there, including the 4B and 8B Qwen3-Embedding models (both 26.2). Larger general-purpose models retain an advantage on word-like names (Qwen3-Embedding-4B: 34.2), but corpus adaptation yields its largest improvements precisely on the abbreviated identifiers that dominate real schemas, and it does so at both backbone scales.

4pt

5.0.0.9 Difficulty scales with query breadth.

Recall declines steeply as a query requires more schema elements. For table retrieval, our model’s recall@10 falls from 97.3 on single-table queries to 38.0 on queries requiring six or more tables (compared with a decline from 82.0 to 32.7 for the base model); for column retrieval, it falls from 50.0 to 25.9 (40.9 to 13.4 for the base model). Adaptation helps across the entire range, nearly doubling recall on the hardest queries requiring six or more columns (\(13.4\rightarrow25.9\)), but queries that touch many tables or columns remain the most challenging setting. Adaptation most often recovers columns whose names share no token with the query: for a question about plants “losing more than a quarter of their potential power and being offline more than one day out of every twenty,” the base model ranks mtbfh (mean time between failures) 11th, whereas our model ranks it 8th, with similar recoveries for coordopsref (\(11\rightarrow1\)), AnonLevel (\(12\rightarrow2\)), and stockturnrate (\(11\rightarrow6\)).

5.0.0.10 Discussion.

Our findings have three implications. First, schema retrieval is not solved by scale: even multi-billion-parameter embedders leave substantial headroom on enterprise and large-database settings, warranting its treatment as a first-class task rather than as a byproduct of text-to-SQL training. Second, corpus adaptation is an inexpensive, label-free, backbone-agnostic complement to scale: it makes a 305M model competitive with 4–8B embedders, improves even a near-state-of-the-art 8B embedder, and, because it requires only the schema corpus, is directly deployable in the retrieve-then-generate pipelines that motivate this work [9], [29]. Third, because the recipe requires only a document corpus and an LLM, it should extend beyond SQL to other forms of structured-resource retrieval, such as selecting APIs, functions, or knowledge-base entries for tool-augmented LLM agents.

Limitations↩︎

Our study is limited to English schemas and questions. Corpus-adaptive fine-tuning assumes access to the target schema corpus at adaptation time; this assumption holds for indexed retrieval but does not address cold-start databases whose schemas are unavailable. Gold relevance is derived by parsing references from ground-truth SQL, which can miss columns that are used implicitly. Synthetic queries are produced by an LLM and may not fully match the stylistic distribution of questions posed by human analysts, particularly terse ones. Finally, we validate the recipe on two backbones, a 305M encoder and an 8B decoder; whether it extends to structured retrieval beyond SQL remains an open question.

6 Implementation Details↩︎

Synthetic queries are generated with a proprietary instruction-tuned LLM served via Snowflake Cortex; the recipe does not depend on a specific generator, and we leave a systematic comparison of query-synthesis models to future work. Hard negatives are mined with a long-context embedding model (voyage-multilingual-2) using exact nearest-neighbor search within each (source, granularity) pool; we skip the top-1 neighbor as a guard against false negatives and ensure that each query receives at least one lexically overlapping negative. We fine-tune Arctic-Embed-M with a contrastive objective, using one positive and six hard negatives per query, and mix the in-domain synthetic data with schema-linking pairs derived from the public text-to-SQL training splits of Spider and BIRD [4], [5]. Training uses AdamW with a learning rate of \(3\times10^{-5}\) and a batch size of 32, for one epoch on 8 NVIDIA H200 GPUs. We apply the same pipeline and contrastive objective to Qwen3-Embedding-8B, changing only backbone-specific settings: last-token pooling, left padding, and a reduced learning rate of \(5\times10^{-6}\).

7 Leakage Audit↩︎

For every evaluation query, we retrieve its most similar synthetic training query within the same source, scoring both semantic similarity (cosine similarity under an independent embedding model) and lexical similarity (token Jaccard and sequence ratio), and we check whether their gold sets overlap. The mean nearest-neighbor cosine similarity is 0.55–0.62 per dataset, reflecting shared in-domain topics rather than duplication. Combined leakage, defined as a synthetic query that is both a near-duplicate of an evaluation query (cosine similarity \(\ge0.95\) or sequence ratio \(\ge0.9\)) and shares more than half of its gold set, occurs for 0 queries in five of the six settings and for 2 of 2,147 queries in Spider (0.09%). Synthesis never accesses evaluation queries or relevance labels.

8 Leave-One-Corpus-Out↩︎

Table 7 reports held-out performance when a corpus is removed entirely from synthesis (its documents appear as neither positives nor negatives). Performance on the held-out corpus remains close to that of the full model and far above that of the base embedder, indicating a transferable schema-retrieval ability.

4pt

9 Prompt Templates↩︎

We use two synthesis prompts, shown below. Both condition only on schema documents drawn from the target corpus (field descriptions, column lists, and sample values that are part of the indexed documents); neither references any evaluation query or relevance label. The explicit instruction not to mention column or table names further discourages surface-form copying, complementing the leakage audit (Appendix 7). Braces denote slots that are filled per specification.

You are a data analyst writing ONE realistic natural-language question
for the database "{DB}".

The question must be answerable ONLY by using ALL of these fields together
(it must genuinely require every one of them, not a subset):
{FIELD CARDS}   # one per gold column:
                #   `table.column` (type): description  e.g. v1, v2, v3
{JOIN LINE}     # join specs only:
  These fields live in DIFFERENT tables connected by foreign keys;
  the question must require combining them (a join).

{STYLE}         # one of:
  plain : Phrase it as a clear, direct business question a real analyst
          would ask.
  idiom : Use figurative / colloquial business language (e.g. 'pulling
          its weight', 'red flags', 'going against the grain'); it should
          still be a precise request underneath the figurative wording.
  kb    : Frame the question around this domain concept (ask for it in
          plain words, do NOT name the formula or its columns): {CONCEPT}
{FORBIDDEN}     # when hide-tokens are set:
  FORBIDDEN words: do NOT use these words or close variants in the
  question, describe what they mean instead: {TOKENS}

Hard rules:
- Write a real user request in natural language (1-2 sentences).
- Do NOT mention any column names, table names, SQL, or the database name.
- The question must require ALL the listed fields.

Output ONLY a JSON object: {"query": "<the question>"}

You are a data analyst. Write ONE realistic natural-language question
whose answer requires the following data from database "{DB}":
{GROUNDING}     # single table: "a single table whose fields are: c1, c2, ..."
                # join: one line listing each connected table's fields
{NEED}          # single: "The question should be answerable from this one table."
                # join  : "The question MUST require combining data from ALL N
                #          tables (a multi-table join)."
{STYLE}         # short (BIRD/Spider): "Keep it SHORT and direct, about W words"
                # otherwise:           "About W words, a realistic analyst question"

Hard rules:
- A real user question in natural language.
- Do NOT mention table names, column names, SQL, or the database name.
- Ask about the real-world entities / data these tables hold.

Output ONLY a JSON object: {"query": "<the question>"}

References↩︎