Understanding the Effects of Noise in Text-to- SQL : An Examination of the BIRD -Bench Benchmark (2024)

Abstract: Text-to-SQL, which involves translating natural language into Structured Query Language (SQL), is crucial for enabling broad access to structured databases without expert knowledge. However, designing models for such tasks is challenging due to numerous factors, including the presence of noise, such as ambiguous questions and syntactical errors. This study provides an in-depth analysis of the distribution and types of noise in the widely used BIRD-Bench benchmark and the impact of noise on models. While BIRD-Bench was created to model dirty and noisy database values, it was not created to contain noise and errors in the questions and gold SQL queries. We found that noise in questions and gold queries are prevalent in the dataset, with varying amounts across domains, and with an uneven distribution between noise types. The presence of incorrect gold SQL queries, which then generate incorrect gold answers, has a significant impact on the benchmark’s reliability. Surprisingly, when evaluating models on corrected SQL queries, zero-shot baselines surpassed the performance of state-of-the-art prompting methods. We conclude that informative noise labels and reliable benchmarks are crucial to developing new Text-to-SQL methods that can handle varying types of noise.

## introduction text-to-sql with large language models facilitates broader access to structured databases without requiring expert knowledge. to develop such models, high-quality open datasets and benchmarks are essential resources, and over the years, several benchmarks and datasets have been created. early benchmarks, such as wikisql @xcite , modeled simple scenarios, often with single-table queries, and following datasets attempts to closer approximate real-world scenarios: complex queries with join-statements over several tables @xcite , unseen domain-specific datasets @xcite , and noisy questions @xcite . bird-bench, a recent and challenging benchmark, aims to further close the gap between text-to-sql research and real-world applications by for example containing large and dirty database values and requiring external knowledge @xcite . ## related work datasets wikisql is a large text-to-sql dataset containing only simple select and where operations without nested queries or join operations @xcite . spider @xcite was later developed to approximate real-life scenarios more closely, requiring models to construct complex queries and understand the database schema. while complexity is a critical aspect of real use cases, variations of spider have been created to contain noisy questions @xcite and domain-specific questions @xcite . bird-bench was created to close the gap between academic research and real-world applications by introducing large and dirty database values, questions requiring external knowledge and optimizing sql execution efficiency @xcite . ## qualitative analysis of noise even though bird-bench was not intentionally created to contain noise in questions and sql queries, our analysis reveals that noise exists in all studied domains to different extents. the financial domain exhibits the highest levels of noise at 49% closely followed by the california schools domain at 45%, as shown in the categories and absolute frequency of noise per dataset are presented in a more severe issue is that all domains contained incorrect sql queries, which are used for generating gold reference answers. an example of an erroneous sql query is shown in figure 1 . these types of errors question the reliability of the benchmark to accurately determine model performance, which is explored in the next section. ## impact of noise on model performance we apply models to the original dataset, a dataset where sql has been corrected, and a dataset where both sql queries and noisy questions have been corrected. figure 3 presents the results of a single evaluation for all models on all datasets. mac-sql slightly outperforms din-sql and the zero-shot baselines on the original dataset, where noise exists in both questions and queries. however, correcting sql queries decreases mac-sql's performance, tying it with din-sql as the poorest performers. surprisingly, even the zeroshot gpt-3.5 baseline outperforms the more advanced din-sql and mac-sql. the dataset with corrected sql queries could also be considered optimal since gold labels are correct and noise in questions is represented. given the drastic re-ranking of models, it is relevant to question if bird-bench is a reliable assessor of models and a useful tool to assist researchers in developing new methods for text-to-sql. when evaluating models on the dataset with both questions and sql queries corrected, the accuracy of all models increases significantly. while zeroshot gpt-4 performs the best, the remaining models perform similarly with din-sql slightly ahead. compared to the ideal scenario where only sql queries are corrected, the presence of noise noticeably impacts all models' accuracy. however, models are not equally affected by noise as some models have a more pronounced increase in accuracy. ## conclusions and future work this paper analyzed the quality and distribution of noise in the bird-bench benchmark for text-to-sql. we show that noise in both questions and sql queries are prevalent, and noise is unevenly distributed across noise types and domains. errors in gold sql queries were common, decreasing the reliability of bird-bench. surprisingly, when evaluating models on corrected gold queries, zero-shot baselines surpassed more advanced prompting techniques. these findings highlight the necessity for developing benchmarks that can guide researchers in designing models that are more resistant to noise. therefore, a significant improvement would be to label noise types across the dataset. in future work, we plan to study how large language models can be applied to noise classification, a new task that could also be critical in systems where text-to-sql is employed. overall, this study provides a deeper understanding of how noise is expressed in text-to-sql tasks and how noise and models interact, pinpointing areas for improvement in the bird-bench dataset.