Is Long Context All You Need? Leveraging LLM’s Extended Context for NL2SQL Experiments, Analysis and Benchmark Paper (2025)

3.1. Generate

In a typical NL2SQL pipeline, the relevant schema elements (table column names and values) are retrieved based on question embedding similarity. Accurate schema linking is critical for accurate SQL generation (Floratou etal., 2024). Prior research works focus on improving schema linking accuracy via more accurate table and column selection (Caferoğluand Ulusoy, 2024; Talaei etal., 2024). Accurate schema retrieval can uplift the NL2SQL performance, but there is still a lot of headroom (see AppendixA.2 for more discussion). In fact, table schema retrieval can have missing tables and columns – which prevents LLMs from generating correct SQL queries.

Figure2 illustrates table schema retrieval simulation results mimicking a production table retrieval setup. Note that the recall does not reach 100% and plateaus at around 82% with increasing $K$.In some production environments, users may ask about any database without specifying which DB they are referring to, rendering table retrieval significantly more challenging (see AppendixA.1 for more details on the simulation setup).In this simulation setup, the retrieval service does not achieve perfect recall at $k\geq 13$, which is the maximum number of tables per database in BIRD dev. This is because tables are retrieved from all DBs (100 tables) without specifying the target DB. In Section4.3 we explore the impact of including all tables for each DB and across DBs to ensure perfect recall at the cost of lower precision.

3.2. Fix & Rewrite

We allow the model to fix its errors and rewrite the SQL output based on execution. If the generated SQL query returns an error during execution, it triggers correction modules (Wang etal., 2024; Caferoğluand Ulusoy, 2024) and retries until a valid SQL is returned within a fixed number of times. (Pourreza andRafiei, 2024) showed that LLMs can correct minor syntax errors in SQL. While the existing self-correction mechanisms and the latest LLMs themselves are effective in fixing SQL syntax errors, they do not address more nuanced semantic errors like invalid literal references and incorrect join paths. Semantic errors may persist after syntax error correction (e.g., referencing a valid literal value for a wrong column), and they are hard to detect without the ground-truth query results. We check for potential semantic errors if the query returns empty result; we ask the model to rewrite such queries by providing more extensive list of sample column values. There is a risk of false positives in relying on a empty results for detecting semantic errors. If the correct ground-truth result is empty, this could incur additional computational costs due to unnecessary retries. If the question is not ambiguous, the model should return the same correct empty result even when additional sample column values are included. However, if the question is ambiguous and the model re-generates a non-empty result, then we prefer that over empty result and move to the next verification step. Seeing the full list of column values enables the model to select the right keyword and columns and to reason about more accurate alternative join paths. Passing the full lists of sample column values inflate the context size significantly and requires long context and LLMs that can retrieve accurately over the long context. We evaluate the impact of this long context disambiguation strategy in Section4.6. This technique is expensive, but it can complement imperfect table & column selection and schema linking, which often leads to sub-optimal NL2SQL performance(Caferoğluand Ulusoy, 2024).

3.3. Verify

We use the untuned gemini-1.5-pro LLM to verify the correctness of the final output. The model is given the entire database table schema and question (with additional hints, if provided) to judge.We believe that fine-tuning a verifier or a picker for self-consistency(Wang etal., 2023; Pourreza etal., 2024; Zheng etal., 2024) can further improve accuracy. While we focus on leveraging the long context ICL for a typical NL2SQL pipeline with simpler techniques (self-correction, verification without fine-tuning), the aforementioned techniques are orthogonal and can be used in conjunction. We demonstrate how this verification step can impact the final performance and the generation cost in Section5.

4.1. Evaluation setup

We use the public Google Cloud (GCP) Vertex AI gemini API for all our experiments. This is important for reproducibility of our experiments and analysis. We use the latest publicly available gemini-1.5-pro-002 and gemini-1.5-flash-002 checkpoints. gemini-1.5 is a long-context LLM class with up to 2-million token context for pro and 1-million token context for flash. Our test VMs and Vertex AI endpoints are located in the same GCP region (us-central1-b).

We report the full pipeline performance on various NL2SQL benchmark datasets (BIRD(Li etal., 2024a), SPIDER 1.0(Yu etal., 2018), KaggleDBQA(Leeetal., 2021) and BEAVER(Chen etal., 2024)) in Section4.2. We run our micro-benchmark experiments using two widely used benchmark datasets, BIRD dev and KaggleDBQA test. BIRD is currently the most popular NL2SQL benchmark with a leaderboard (all the recent NL2SQL research publications use this dataset to compare their performance), and it contains the largest number of questions with varying degrees of difficulties spanning over multiple tables with varying schema complexity. KaggleDBQA is another benchmark dataset for Text-to-SQL tasks that focuses on real-world, cross-domain data sources.

For the performance and cost metrics, we use popular execution accuracy (Ex Acc) and the accumulated tokens per request ($L$), and/or normalized latency per request ($T$) as the absolute latency measure behind the public API endpoints can vary from time to time – a single gemini-1.5-pro request latency with 8k-token is used as a reference unit ($T$=1.0); we report floating-point values to one decimal place, ignoring differences below 10%.Likewise, we refrain from reporting the monetary cost directly, as it depends on the pricing model(Google Cloud, 2024a), which is subject to change. Instead, we report the accumulated number of tokens per request $L$, which is strongly correlated with total monetary cost and remains invariant to pricing model changes.

There is a strong positive correlation between $L$ and $T$, as discussed in Section4.8, and we report both in the ablation study. We report the average metrics across requests with low variance (margin of error/variability less than 2%); if the observed variance is large, we report the mean plus one standard deviation (e.g., $\bar{L}+\sigma$) to bound the majority of requests.

Our “full” pipeline employs all the contextual information and the techniques as discussed in Section3 and Section5; for the individual experiments, we use the “baseline” pipeline to study specific questions being addressed. The baseline pipeline includes entire DB schema, sample column values, instructions and hints, but excludes self-correction, disambiguation, synthetic examples and verification.

4.2. Benchmark evaluation

Table2 shows the full pipeline evaluation results across various benchmark datasets. The popular benchmark datasets (BIRD dev, SPIDER 1.0 test, KaggleDBQA test) provide a diverse set of questions with varying difficulties across multiple domains, allowing us to compare our performance with other state-of-the-art approaches.Our approach leveraging the extended context of gemini-1.5 model can yield competitive results across all the benchmarks, without techniques like fine-tuning and self-consistency (Table1).The newer benchmark for enterprise warehouse data (BEAVER dw) is particularly noteworthy because its business-oriented questions and real enterprise tables are significantly more complex and larger. Among the three widely used benchmarks, BIRD is the most complex, with questions averaging 0.918 JOINs over 6.82 tables per database. In contrast, BEAVER questions involve 4.25 JOINs over 105.0 tables per database, making it substantially more challenging. BEAVER is still in its early stages as a benchmark. The dw dataset we used had some issues, like UNIQUE constraint violations on primary keys for which we ignored and allowed duplicates. Our long context pipeline achieves Ex Acc of 60.41% (48.64% ignoring questions with null golden queries) with gemini-1.5-pro without using the golden tables, but the entire DB tables and the column mappings, similar to the hints in BIRD benchmark. The The best 1-shot Ex Acc on BEAVER (dw + 45 additional questions), reported by (Chen etal., 2024), is 2.0 in PL/SQL using gpt-4o with relevant table schema retrieval, and 0.0 without retrieval.

The normalized generation latency $T_{g}$ increases with both larger context size and task difficulty (question and schema complexity), as it includes self-correction and retries (using exponential backoff, with a maximum of 5 retries per request). For instance, BEAVER takes significantly longer to generate on average because our long context pipeline produces context size exceeding the limits (2-million tokens for pro and 1-million tokens for flash) or results in semantically invalid (empty) outputs, in which case the model retries a fixed number of times until it finds a better answer. Consequently, for complex large datasets, like BEAVER, flash can paradoxically take as long as or even longer than pro with more retries due to the smaller context size limit.

In general, gemini-1.5-pro is better at complex reasoning tasks like NL2SQL, whereas gemini-1.5-flash is more cost-effective (lower pricing per token).We have noticed that the latest releases of the gemini models (released for public access on Sep. 24^th, 2024) show significant performance improvements, and most notably, flash now performs more comparably to pro on the select benchmarks. This makes the more cost-efficient flash a highly attractive choice for production NL2SQL.

4.3. Schema linking with all database tables

We explore the impact of passing the entire table schema set collected from entire databases. Schema linking is such a critical component that we strive to achieve a high-recall in production. With a long-context model, we can include a significantly larger set of table definitions and schemas instead of limiting the input to only the most relevant top-K tables.As is standard practice, we retrieve the most relevant tables based on the NL question and the table DDL statement, using embedding similarity between the two. The top-k table retrieval is done across all the tables and DBs, mirroring the production setup illustrated in Figure2. Alternatively, we provide all the tables from a given target DB (benchmark datasets specify to which DB the question is referring to) or from all the tables in the dataset (across all DBs), without the top-k retrieval.The goal is to ensure near perfect (higher) recall at the cost of lower precision by providing more tables, if not all, via long context.

The results shown in Table3(b) demonstrate that the model does not get confused despite the presence of a large number of mostly irrelevant table definitions in the context. Thus, providing more tables, rather than solely relying on an imperfect table retrieval mechanism, can be beneficial—provided the context size allows for it.The result (for $k=1$) also re-affirms that schema linking is critical, since without all the ground truth tables in the prompt ($k=1$) the model cannot generate good quality SQL outputs.Conversely, providing all tables from the target DB ensures perfect TBR recall and results in higher Ex Acc in both BIRD dev and KaggleDBQA test. This means that TBR recall is more important than precision or accuracy as the model generation quality does not degrade much with many irrelevant tables (68.18 irrelevant tables on the average for BIRD dev and 14.875 for KaggleDBQA). The all tables / DB setting utilizes up to 13 tables per request (5 tables for KaggleDBQA), depending on the target database in BIRD dev; the average number of tables across different databases is 6.82 (2.125 for KaggleDBQA).It is interesting to note that the execution accuracy result for all tables / dataset is slightly higher than that of all tables /DB. The difference is more pronounced in the KaggleDBQA test (almost +3.8% Ex Acc), whereas it falls within the 5% margin of error for BIRD dev (we use medium temperature for LLM generation, and Ex Acc varies slightly per run).

This suggests that the model’s output quality is at least comparable, and the inclusion of additional irrelevant schema information does not degrade generation quality.

On the one hand, including as many tables as possible (e.g., user history, all other tables within the target database) ensures high TBR recall, which long-context LLMs can accommodate. On the other hand, the latency cost increases with larger context sizes (see Fig.5), and beyond a certain point, adding more irrelevant tables does not provide additional benefits to justify the increased cost. Furthermore, some production settings require processing hundreds of very wide tables, making it prohibitively expensive to include all tables in the context (see Section6.3 for more discussion). Thus, accurate retrieval and filtering remains desirable, and leveraging long-context models can serve as a compensatory mechanism to offset imperfect retrieval for higher recall.

4.4. Many-shot ICL with example selection

Here, we evaluate the impact of many-shot in-context learning (ICL) using examples selected from BIRD train dataset. The examples are retrieved based on the question embedding similarity.

Table4(c)(a) shows that the train examples did not provide much boost and instead resulted in worse performance. This means that the model’s ability to learn from similar examples from BIRD train is quite limited and it can be even distracted by them. Also, notice that the impact of distraction seems to be limited, say going from 1 similar example (11k tokens) to 100 similar examples (50k tokens).To further examine the model’s sensitivity to many irrelevant train examples, we injected 1 ground-truth example from BIRD dev in the “middle” of the example blob (the average context size is omitted as it should be almost identical to the previous table).While the model can select the ground truth example among many other irrelevant ones, the presence of those irrelevant train examples can confuse the model as opposed to just having that ground truth. The recall matters more than precision, but the model is still sensitive to bad examples (low precision) to a limited degree – note that the accuracy with 100 random examples and 1 ground truth is much higher than no examples at all.

It is also important to note that the model is robust to both the position and ordering of the ground-truth example.(Liu etal., 2024a) showed that LLMs tend to emphasize information at the beginning and end of the context window, highlighting the importance of proper ranking and ordering for retrieval-augmented generation (RAG).As in Table4(c)(b-c), where the GT is injected at different positions within the context window, we observe that with gemini-pro-1.5 the ordering of the examples (and the schema information relocated by the relocation of the examples in (c)) has a negligible impact, and the long-context LLMs can retrieve the relevant ground truth example (also the schema information) from anywhere within its context window.

The model’s ability to learn and generalize from random examples is limited: a single relevant example is far better than having many random examples. While it is crucial to capture the relevant examples in the context (relevant examples boost the generation quality), it is also very challenging to capture the relevant ones based on the user questions. In the next section, we also share our experience generating many synthetic examples per question to skip the example selection step.

4.5. Synthetic example generation VS. example selection

Fig.4 illustrates how synthetic examples improve the quality of generated SQL query and compares with other types of examples retrieved from different example pools. As is a standard practice, the example retrieval was done via question embedding similarity using gecko¹¹1https://cloud.google.com/vertex-ai/generative-ai/docs/embeddings/get-text-embeddings embedding model.Note that using the ground-truth SQL queries from dev dataset provides performance ceilings ($\sigma$(dev) and $\sigma$(train + GT)), but is not allowed without an oracle (i.e., using the ground truth queries from the evaluation dataset). While dev excludes the specific GT query for a given question, it can still retrieve similar examples directly from the ground truth DB in the dev dataset. The gap between $\sigma$(dev) and $\sigma$(train + GT) indicates that providing the GT as one of the many examples is better than retrieving dev examples that are similar to the GT.$\sigma$(train) is the common practice where the relevant examples are selected from available datasets, such as the training dataset (train). In the case of BIRD dev (a), providing relevant train examples does as least as good as the baseline (no example) with a slight improvement when fewer than 200 shots are provided. However, synthetic examples ($\sigma$(synthetic) and online synthetic) provide significant uplift over $\sigma$(train). The gap between $\sigma$(synthetic) and original synthetic indicates that one can be more economical with the context size (achieving higher performance with a fewer examples) by filtering the relevant synthetic examples. In case of KaggleDBQA test (b), both training and synthetic examples boost the Ex Acc, while train performs much better than the synthetic ones. This is because BIRD train and dev datasets are sampled from different database domains, whereas KaggleDBQA train and test datasets are sampled from the same domains. It is important to note that the many-shot ICL using synthetic examples can help further improve Ex Acc, and even more so in the cross-domain case (e.g., BIRD dev) where the available training examples are less relevant.

4.6. Self-correction with entire schema and more column values

Table5 shows how LLM’s self-correction can help with SQL generation and its refinement.We compare LLM-based self-correction (SC) with two more advanced techniques: disambiguation and filtered schema. disambiguation is the long-context enabled technique where an extended lists of sample column values are shown to the model upon detecting empty results from syntactically correct SQL; filtered schema uses the column selection results to filter relevant schema elements per user question, following the approach of (Talaei etal., 2024). Both disambiguation and filtered schema can help LLMs correct for any invalid literal and column references. One potential issue with disambiguation is that the extra column values can distract the model, especially in the case of false positive detection (e.g., when the correct answer is null). However, empirical results show that it generally improves performance, performing slightly better or at least as well as SC.While comparable in performance, the techniques vary in terms of the cost. The accumulated average number of prompt tokens used for correction increases significantly for disambiguation, as it includes full schema and extra sample column values.

Accurate column selection is crucial for improving NL2SQL performance, and filtered schema does not increase the context size during correction. However, preparing filtered schema requires an additional retrieval step, which incurs a cost (see AppendixA.2 for more discussion).filtered schema is a great strategy, if accurate column selection process is available.However, in our final long-context pipeline, we use SC + disambiguation to avoid extra retrieval/selection step and do everything in-context.Due to the high variance in token usage, we report the mean tokens per request plus one standard deviation (1-S.D) to capture the range used for most requests (68%).

4.7. In-context learning with SQLite documentation

We test whether the model can effectively learn from SQLite documentation and improve its SQL generation quality. We downloaded the entire SQLite documentation from its official homepage(sql, 2024). The original documentation comprises 16 million tokens across 792 HTML files, each covering a distinct theme. We applied two strategies to split the entire documentation into chunks so that we can augment the prompts with relevant information for ICL. The coarse-grained chucking strategy split the documentation by HTML file, while the fine-grained chucking strategy further separate each file by sections which ends up with 4125 smaller chunks in total. Similar to example retrieval, we embed the natural language questions and document chunks into vectors with the Gecko text embedding model(Lee etal., 2024b) and employ nearest neighbor search to identify the most relevant document chunks to the given question. We could further compress the context via summarization, but decided not to, since it drops important details, like syntax rules and examples.

We show the execution accuracy and context size for document retrieval in table6(b). The retrieval is challenging because the natural language question does not reveal the full structure and features of the corresponding SQL query; furthermore writing a correct SQL query requires a combination of concepts from multiple sections of the documentation. However, we believe that the imperfect retrieval is not the reason why the documentation adds little to no value to generation. It is actually the nature of the errors that the LLMs make, which are mostly semantic errors (e.g., executable queries returning semantically irrelevant results).

The Gemini-1.5-Pro model is already well-versed with the SQLite syntax and function details that are illustrated in the documentation. The SQLite documentation is likely already in the pre-train mixture. Furthermore, the model can self-correct itself for syntactic errors based on the error message and without any documentation or examples. A recent work (Talaei etal., 2024) based on GPT-4 also points out that there is no obvious errors based on simple syntax misuse (e.g., function names), but rather more subtle formatting (e.g., does not follow required date format) and semantic errors (output SQL is executable but incorrect w.r.t. the request). Reading the SQLite documentation would not help with such semantic errors – because the model is already producing executable queries that are semantically incorrect. While there is no accuracy gain, putting the documentation chunks increase the context size, thus the latency, significantly.

4.8. Long context and latency relationship

In this section, we look at the context size (tokens) and latency relationship. The relationship between context size and latency in Google’s gemini-1.5 generation API is primarily a function of the number of tokens and is agnostic to the benchmark datasets. We evaluate this relationship using sampled requests of varying context size from BIRD dev. Figure5 illustrates that there is near-linear relationship (strong positive correlation $R^{2}$=92.6). It is interesting to note that the latency and the variance starts increasing significantly beyond context size $>$32k. The larger context LLMs require inference computation scattered across multiple hosts and accelerators, introducing additional queuing delays. While we expect the smaller gemini-1.5-flash variant to exhibit lower latency, it also suffers from increased latency in the long tail due to queuing delays and differences in resource allocation. The generation latency increases significantly ($>>$ 4 seconds) with larger context size ($>$32k tokens), thus we should increase the context only if there is a clear gain in generation quality. Fortunately, the average relationship between context size and latency remains linear, making it easy to model.

Table7 illustrates the average latency difference for the two gemini-1.5 model variants: the larger, more expensive pro and the small, more cost-efficient flash. The average generation latency for BIRD dev is used as the unit for normalized latency $T$. The difference in verification latency is more pronounced because the context size is smaller, whereas the generation with larger context pro and flash requires more prefill computation, which results in increased and similar queuing delay. Both models experience increased and similar average generation latency. For the single verification latency (indicative of the smaller context latency) flash is almost 75% faster than pro.

6.1. Error analysis

To gain a deeper understanding of the discrepancies between generated SQL and ground truth SQL, we randomly sampled 20% of the instances where our baseline output deviated from the provided golden SQL in the BIRD dev. Figure6 presents a breakdown of the observed error categories and their respective proportions within this sampled subset.

The errors are categorized hierarchically. The inner ring of the chart depicts three high-level classifications: ”Vague Question” representing instances where the generated SQL was broadly correct but was marked incorrect due to the vagueness of the corresponding NL question; ”Incorrect Generated SQL,” indicating cases where the generated SQL contained fundamental errors; and ”Incorrect Golden SQL,” encompassing scenarios where the provided golden SQL itself is incorrect.

The outer ring provides a finer-grained breakdown of the error types within each high-level category. For example, within ”Incorrect Generated SQL,” we observe subcategories such as ”Join”, ”Logic”, and ”Aggregation,” each highlighting a specific type of failure in the generated SQL. Specifically, ”Logic” refers to cases where the generated query failed to logically understand the intent of the user question. The distribution of errors across these subcategories reveals that issues related to joins, filtering, and aggregation contribute significantly to the overall error rate.

6.2. Further performance improvement via self-consistency

Self-consistency is another very popular technique for NL2SQL(Wang etal., 2023; Talaei etal., 2024; Pourreza etal., 2024; Dong etal., 2023; Gaoetal., 2023; Gao etal., 2024), where multiple candidates are generated, and the most consistent answer or the best one is selected by a fine-tuned picker. As demonstrated by the recent state-of-the-art approaches(Pourreza etal., 2024; Gao etal., 2024) on the BIRD leaderboard, self-consistency has become a crucial technique for achieving high accuracy in NL2SQL. This involves generating multiple candidates using different generators (a.k.a. NL2SQL pipelines) and each generator trying multiple times, and choosing the best one.One of the state-of-the-arts, CHASE-SQL, in Table1 uses our long context pipeline as one of the three candidate generators, contributing to its top-tier performance on the BIRD leaderboard.One caveat of self-consistency is that it can quickly become expensive in terms of latency and the number of LLM calls. Self-consistency is orthogonal to our work, and can further improve the NL2SQL performance if used together with our pipeline. Table11 illustrates the impact of generating multiple output candidates from our long context pipeline using a generation temperature of 0.5. With an oracle picker (i.e., at least one of the candidates is correct per question) we can uplift the accuracy beyond 70%. In (Pourreza etal., 2024; Gao etal., 2024), the candidate sets are generated using multiple pipelines, as different strategies result in more diverse outputs, further boosting the final accuracy of self-consistency.

Self-consistency is orthogonal to the use of long context and falls outside the scope of this paper.In this work, we focus on generating one high quality candidate and study the performance implications of leveraging long context (i.e., extra contextual information), that has not been previously explored for NL2SQL.

6.3. Long context in production and limitations

Our study reveals (Table8) that a good chunk of requests (roughly 68%) from BIRD dev can reach 400k accumulated tokens and take up to 130 $T$ units. This would likely be prohibitive for most production services. And the cost will be even higher with techniques like self-consistency. Long context is a great means to add more appropriate information, but it has to be used with some care. To leverage long context properly in production, one may pick and choose which extra information and techniques are appropriate based on our analysis. For instance, one can skip disambiguation and online example generation to bound the latency of the majority of requests to at around ¡1.5 normalized seconds. The example can be generated offline, and as shown in Section4.5, example selection works well with synthetic examples. Using flash can further reduce the cost, roughly -20% in latency and -94% in dollars per request given the current pricing and the observed latency.

One limitation of our study, as we analyze the impact of long context and additional contextual information on NL2SQL, is the absence of benchmark datasets that accurately model enterprise use cases. Such cases typically involve hundreds of very wide tables with thousands of columns, as well as schemas with less descriptive and similar table and column names, making schema linking significantly more challenging. These scenarios not only pose inherent difficulties for schema linking, but further exacerbate the cost of long-context techniques due to the increased amount of schema information.To address this, we incorporated multiple challenging NL2SQL benchmark datasets and evaluated how the long-context model performs in cases with very low density information (e.g., providing full schema details and sample values from tens of irrelevant tables and DBs, while only a few columns are relevant, as shown in Table3(b)).We observe that extra information is generally not distracting, and the long context techniques perform as well as the baselines without them. While filtering and ranking the most relevant information remain crucial for both accuracy and cost, our findings suggest that, unless retrieval is highly accurate, increasing recall by including more information - even at the expensive of lower precision - is a beneficial strategy. We expect that these insights will hold for more extreme use cases and become increasingly evident as long context LLMs continue to improve. For instance, our long-context NL2SQL pipeline yields significantly better results on the enterprise data warehouse benchmark BEAVER compared to the baseline results from(Chen etal., 2024) (see Table2).

While this work focuses on evaluating the impact of providing various forms of contextual information directly within the LLM’s extended context window, we acknowledge that the explored information is not exhaustive. Notably, we did not investigate the utilization of knowledge graphs for enhancing NL2SQL performance. While it is expensive to build a comprehensive knowledge graph for a given domain, recent works(Allemang andSequeda, 2024; Sequedaetal., 2024) have shown promising results using knowledge graph-based question answering over enterprise datasets. In future work, we are interested in exploring the integration of additional contextual sources, such as business ontologies and knowledge graph, to further leverage the capabilities of long-context LLMs.

A.1. Table retrieval simulation

For a realistic table retrieval simulation, we ran the BIRD bench against BigQuery’s SQL Code Assist feature(Google Cloud, 2024b). The result differs from the standard BIRD bench setup, as it mirrors the challenges of serving production users of BigQuery. Conventionally when running the BIRD bench, each query is linked to a single database containing up to 13 tables. BigQuery operates differently; users may ask questions about any table that they have access to, and BigQuery’s retrieval system uses user interaction histories to reduce the search space. For realistic results the user interactions were seeded to match the distribution of queries in BigQuery’s production traffic, with a bias towards session-starting queries (less repeated queries), as these present a more difficult retrieval problem.

For each example in the benchmark, table retrieval operates as follows: 1) The simulated user view and query interactions narrow the search space down to 1-100 candidates; 2) The candidates are embedded and re-ranked with Gecko (Lee etal., 2024b) for relevance to the user query; 3) A fixed top-k candidates are passed on to the NL2SQL stage.At the time of test, the recall of the first stage was 82%. As such, 82% is the maximum attainable end-to-end recall, and represents a near perfect result from the re-ranking stage.

A.2. Impact of good column selection

We evaluate the impact of schema selection on execution accuracy using the BIRD dev. We compare a filtered schema against a perfect ground truth schema. The filtered schema, constructed similarly to CHESS(Talaei etal., 2024), incorporates relevant tables and columns, their descriptions, and relevant example values. 12 reports execution accuracy (Ex Acc), as well as table retrieval (TBR) and column retrieval (CR) recall and precision. While the ground truth schema achieves perfect performance (100%) on all metrics, the filtered schema demonstrates high recall, correctly identifying most relevant schema elements (97.69% table retrieval recall and 97.12% column retrieval recall). However, precision is lower (89.72% for table retrieval and 69.43% for column retrieval), indicating the inclusion of some irrelevant columns/tables.It is important to note that the schema filtering algorithm incurs substantial cost, requiring an average of 78 LLM calls and 339,965 tokens per request on the BIRD dev.