Automatic Metadata Extraction for Text-to-SQL

1 INTRODUCTION

• The authors identify understanding database contents—not writing SQL—as the hardest part of query development, citing undocumented, outdated, ambiguous, and dirty data and schemas.Examples include unclear value formats, multiple fields with similar meanings, complex join paths, obscure business rules, and default values.Source: §1 · pp. 1–2
• The authors report that their profiling-only submission held the highest BIRD score both with and without oracle information at the times stated.The paper regards the no-oracle result as more indicative of practice because oracle hints are not available there.Source: §1 · p. 2
• 77.14 was the test score of the hinted submission that reached the BIRD leaderboard’s #1 spot on March 11, 2025.The paper also reports a 67.41% score without the oracle, 10.28 percentage points above the next-best submission at the time of writing in January 2025.Source: §1 · p. 2

1.1 Contributions

• The paper investigates profiling, query-log analysis, and LLM-based SQL-to-text generation for automatic metadata generation in text-to-SQL.BIRD is used for evaluation; its test database provides no query logs, so the leaderboard submission uses profiling alone.Source: §1.1 · p. 2, §1 · p. 2

2 Profiling

• Basic profiling collects table record counts and field statistics, including nulls, distinct values, value shapes, common values, and minhash sketches.The study restricts itself to basic profiles rather than more complex profiles such as multi-field keys and functional dependencies.Source: §2 · pp. 2–3

2.1 Using Profiling Information for Text-to-SQL

• An LLM uses a field’s profile, supplied metadata, table name, and neighboring field names to generate a short description of its meaning and format.For CDSCode, the description identifies unique 14-character numeric school identifiers and expands CDS as County-District-School.Source: §2.1 · p. 3
• Long profile descriptions can include value-level details, but providing them for every field may exceed prompt limits and cause middle prompt content to be ignored.The paper notes that these descriptions can help the model choose appropriate literal values.Source: §2.1 · p. 3

3 Schema Linking with Profile Metadata

• Long profile-generated field descriptions can exceed LLM token limits when supplied for every field in a database.The authors also observed that, in long prompts, LLMs tend to focus on the beginning and end and ignore material in the middle.Source: §3 · p. 3
• The schema-linking process uses short summaries for linking and long summaries for SQL generation, while literal matching can prompt SQL revisions to use fields containing question literals.The authors favor recall over precision, preferring to include more fields in the prompt when feasible.Source: §3 · p. 4
• Schema linking identifies fields relevant to a question, but the authors found direct field- or table-selection techniques unsatisfactory and instead gather fields referenced by generated SQL.Their approach reflects an observed task-alignment difference: LLMs were better at generating SQL than directly selecting relevant schema elements.Source: §3 · pp. 3–4

1. For every field f,

• For BIRD, the algorithm samples up to 10,000 values per field for approximate literal matching with an LSH index and uses FAISS over long profile descriptions for semantic search.The authors contrast their moderate sample with indexing all field values, which they say is not scalable beyond small benchmark databases.Source: §1 · p. 4
• The algorithm generates SQL across five schema/profile combinations, collects referenced fields and literals, and returns the accumulated field set as query context.The combinations vary focused or full schemas with minimal or maximal profiles, plus a focused schema with the full profile.Source: §1 · pp. 4–5
• When a generated literal is absent from the fields in its query, the algorithm uses indexed values to find fields containing it and asks the LLM to revise the SQL accordingly.The procedure adds newly identified fields to the schema and repeats the literal check within its retry limit.Source: §1 · p. 5

3.1 Example

• In the California Schools example, matching the Fresno County Office of Education literal leads the revised query to constrain schools.District rather than the original County Name field.The final field list is returned after all literals match fields in the query.Source: §3.1 · p. 5

4 BIRD Submission Details

• The submission retrieves eight similar masked question–SQL examples, generates three candidate queries, checks them, and uses majority voting when candidates agree.Candidate variety comes from changing the random seed or randomizing schema-field order; detected bad constructions can receive up to three correction retries.Source: §4 · p. 5

4.1 Experiments

• The methods described for the submission are reported to have reached the BIRD leaderboard’s #1 spot, though the passage’s parenthetical current-rank statement is incomplete.Source: §4.1 · p. 5
• Profiling metadata yields higher accuracy than BIRD-supplied metadata, while fused metadata provides the highest accuracy, with or without hints.The authors also report that hints are the most powerful metadata in BIRD, though they are unavailable in practice.Source: §4.1 · p. 6, Table 1 · p. 6
• Schema linking improves accuracy over using the full schema, while perfect schema linking produces a larger jump in scores.The full-schema condition corresponds to no schema linking; the authors call for further schema-linking research.Source: Table 2 · p. 6

4.1.1 Detailed Discussion

• Profile hints can produce incorrect predicates: the hint maps “infinite power” to power = '*', but the intended predicate is power = ’∞’.A text-typed RF field also requires CAST(T2.RF AS REAL) < 20 rather than the uncast comparison; the authors attribute such cases to LLM-answer instability.Source: §4.1.1 · p. 6
• Differences in schema length can affect evaluation: for Q1505, linked and perfect-schema SQL are correct, but the gold SQL is not.The authors also report that formatting yearmonth.Date differs between linked/perfect-schema and full-schema outputs for Q1480.Source: §4.1.1 · p. 6

5 Query Log Analysis

• Query logs can provide SME-derived clues about important fields, join paths, and business logic without extensive interviews.The logs may also include timestamps, user IDs, success status, performance metrics, or query plans, though this study focuses on query text.Source: §5 · p. 6
• Dataframe workflows such as PySpark may produce no logged SQL text, although platforms such as Databricks retain query plans that can be handled similarly.Snowpark is cited as another database context where such dataframe workflows are becoming popular.Source: §5 · p. 7

5.1 Query Log Processing

• The processing goal is to trace query fields to source tables and substitute subquery formulas for referenced output fields.For example, current_spend_and_planned resolves to accounting_table.trans_amt + sum(planning_table.planned_amt).Source: §5.1 · p. 7
• An SQL parser converts each statement into an AST, and a subquery summary table maps each returned subquery field to its computing formula.The algorithm resolves subqueries in the FROM clause before resolving formulas in each top-level SELECT field.Source: §5.1 · p. 7

5.2 Query Plan Processing

• PySpark workloads can produce optimized query plans instead of textual SQL, making plan processing relevant when query logs contain no SQL text.The paper notes this issue from its work with Databricks and points to similar execution environments such as Snowpark.Source: §5.2 · p. 7
• A plan can be represented as a table scan, Select, Project, and Output operators, with each operator treated as a separate subquery.The paper says its field-formula resolution algorithm applies to well-annotated query plans as well as SQL.Source: §5.2 · p. 7
• After optimization, simple selections and filters are pushed into the scan.Source: §5.2 · p. 7

5.3 Feature Extraction

• Extracted features include named and unnamed select fields, constraints, join paths, group-by formulas and sets, WITH subqueries, table and field sets, and query complexity.Join constraints can include multiple field comparisons or transformations, and a subquery's name can indicate its purpose.Source: §5.3 · p. 8
• For accounting and planning tables, features expose a spending formula, country constraint, group-by field, and join path.The named field spend_and_planned can connect a question about current and planned spending to its formula.Source: §5.3 · p. 8
• Extracted join paths can help when primary/foreign-key information is missing, while few-shot examples may lack needed details or have the wrong query shape.Source: §5.3 · p. 8
• Query features can express relevant information in less text than few-shot examples, reducing prompt size.Source: §5.3 · p. 8

5.4 Experiments

• The experiments use the BIRD dev 9-23 and dev 6-24 query sets, compare extracted features with provided metadata and hints, and investigate equality joins, other joins, and named formulas.BIRD provides no query logs for its ranking test database, so the dev queries serve as the log-analysis test suite.Source: §5.4 · p. 8

5.4.2 Other Join Constraints

• Query-log analysis surfaced multi-field joins, computed join keys, multi-table join paths, and differences in which date fields queries constrain.For California schools, opendate appears in six queries, closeddate in three, and lastupdate in none.Source: §5.4.2 · p. 9

5.4.3 Interesting Formulas

• Queries expose nontrivial formulas such as monthly consumption, iodine counts, and differences between counts of positively and negatively labeled molecules.The authors note that Q221's formulas are not clear from the field metadata, while hints suggest the formulas in the other examples.Source: §5.4.3 · p. 9

5.4.4 Summary

• BIRD is fairly simple and readable: suggestive field names and question hints make its query-log setting unrealistic, and few gold queries explicitly name formulas.The authors propose a better test: list query features and require submissions to link them to questions.Source: §5.4.4 · p. 9

6 Sql to Text

• Given SQL and its full schema, the method extracts referenced fields, builds a focused schema, and asks an LLM to generate long and short questions.The authors describe query analysis as providing perfect schema linking for this SQL-to-text task.Source: §6 · p. 10
• For few-shot selection, an SME identifies feature sets of interest; the process returns queries containing those features from the query log.The authors propose sampling representative queries rather than using every distinct query.Source: §6 · p. 10

6.1 Experiments

• Using BIRD Minidev’s 500 question/SQL pairs, the evaluation selected every sixth entry (first, seventh, thirteenth, etc.), generated both long and short questions from SQL, and subjectively graded the better result.The authors cited time-consuming grading and limited human resources as reasons for selecting a subset.Source: §6.1 · p. 10
• The rating scheme ranges from Bad to Good+, with Good+ indicating a question judged better than the supplied question in accuracy and readability.The authors note that Good+ can include cases where the supplied question and SQL do not agree.Source: §6.1 · p. 10
• The four metadata conditions are no metadata, BIRD metadata, an LLM field-profile summary, and fused BIRD-plus-LLM metadata.Source: §6.1 · p. 10
• With fused metadata, generated questions are significantly better than human annotation; without metadata, performance is almost as good, and human questions are worse in 13 of 83 samples (16%).Source: Table 3 · p. 10
• In one example, LLM metadata led to interpreting A11 as population, while fused metadata correctly identified it as salary.The generated question using fused metadata was rated good; the authors also show a case where fused metadata made a question more accurate.Source: §6.1 · pp. 10–11

7 Related Work

• Spider and BIRD submissions use methods including similarity-based few-shot retrieval, schema linking, query-plan analysis, and diverse SQL candidate selection.Examples include CHESS [TPCM+24], Chase [PLSC+24], and Spider work using a vector database [GWLS+23].Source: §7 · p. 11
• The paper examines established database profiling and query parsing, alongside SQL-to-text generation as a newer metadata-extraction technique.Query parsing and feature extraction have been used to find join paths, while SQL-to-text has been used in candidate selection [PLSC+24].Source: §7 · p. 11

8 Conclusions

• The paper evaluates profiling, query-log analysis, and SQL-to-text metadata strategies for text-to-SQL generation.Source: §8 · p. 12
• Combining profiling results with basic table metadata let an LLM summarize field meaning, including discovering a field's JSON format in one example.Source: §8 · p. 12
• Profiling metadata yielded higher BIRD accuracy than supplied metadata, with or without hints; fused metadata performed best, and the submission reached #1 with hints during two periods.It also retained the highest leaderboard position without hints at the time of writing.Source: §8 · p. 12
• Query-log mining found 25% more useful equality constraints than documented, plus multi-field and computed joins, field-constraint formulas, and SELECT-clause formulas.Some features appeared in hints unavailable in actual text-to-SQL tasks, while others were undocumented.Source: §8 · p. 12
• With fused BIRD/profiling metadata, SQL-to-text questions scored significantly better than human-generated questions.Without metadata, the LLM scored nearly as well as human-generated questions; the authors attribute the fused-metadata result likely to mistakes in the laborious creation of 12,000+ query pairs.Source: §8 · p. 12

Suggestions for Future Benchmarks

• BIRD is more complex than Spider, but its schemas still have suggestive names and join paths, unlike the more obscure, dirty industrial databases described by the authors.Industrial databases may contain thousands of tables, many with 500+ fields, and outdated documentation.Source: Suggestions for Future Benchmarks · p. 12
• Per-question BIRD hints are unrealistic; a database-level table of applicable query features could test query-feature linking alongside schema linking.Source: Suggestions for Future Benchmarks · p. 12
• Execution-efficiency rankings depend heavily on the DBMS optimizer, which varies across systems and changes with updates.The authors note that systems should detect or warn about performance bombs, such as joins inflated by overlapping default values.Source: Suggestions for Future Benchmarks · p. 12

Appendix

• For Question 1471, the Bird-only generated questions were rated good+, while the base, LLM-only, and fused questions were rated good.The Bird-only rating notes that one question is succinct and expands the currency acronyms.Source: Appendix · p. 13
• In Question 1525, the LLM-only and fused generated-question sets each received a good+ rating because the supplied question did not match the SQL.Source: Appendix · p. 15
• For Question 1338, base, LLM-only, and fused questions were rated good+, while the Bird-only question was rated good; the supplied question did not quite match the SQL.The SQL returns approval status for expenses tied to the October Meeting on October 8, 2019.Source: Appendix · p. 15
• For the April Speaker budget query, the base, Bird-only, LLM-only, and fused questions were all rated good while asking for category amounts in ascending order.The SQL groups the event’s budget amounts by category and sorts them in ascending order.Source: Appendix · p. 16
• The male-admission examples were rated good- for base, Bird-only, and LLM-only questions, but good for fused questions that distinguished hospital admission from outpatient visits.The weaker questions were described as accurate but missing the admitted-versus-outpatient distinction.Source: Appendix · p. 17
• For the thrombosis query, base and Bird-only questions were rated good- for missing that score 2 means severe, while the fused question was rated good and explicitly identified severe thrombosis.The LLM-only question was also rated good- for the same reason.Source: Appendix · p. 17
• For outpatient bilirubin testing, Bird-only and fused questions were rated good, while base and LLM-only questions were good- for missing field meanings.The fused question described patients as having bilirubin levels below the normal range; its rating was good.Source: Appendix · p. 18
• For the top-ten heading-accuracy query, all four question variants were rated good- because they missed the requested ordering.The generated questions described the highest average heading accuracy but did not express the descending-order requirement.Source: Appendix · p. 20
• For the 2007 Bahrain Grand Prix question, all four generated variants were rated good+ because the supplied question was not proper English.The ratings cover base, Bird-only, LLM-only, and fused versions.Source: Appendix · p. 22
• For Question 875, the fused wording was rated good as more accurate, though the improvement was described as unnecessary.The fused wording refers to the Formula One season introduction page associated with race 901.Source: Appendix · p. 22
• For Question 892, the base wording was rated good- for implying a specific race, while Bird-only, LLM-only, and fused versions were rated good.The base rating notes the distinction between a specific race and any race.Source: Appendix · p. 22
• For Question 901, the Bird-only wording was rated good- because it omitted an output field, while the base, LLM-only, and fused versions were rated good.The base question was rated good; the LLM-only and fused questions were also rated good.Source: Appendix · pp. 22–23
• Other examples receive good or good+ ratings for variants asking about circuit reference codes, race results, and superhero attributes or powers.The circuit-reference-code variants are rated good; the second-place finish-time variants are rated good+ because of a typo in the supplied question.Source: Appendix · pp. 23, 25
• Good+ ratings flag a typo or ambiguity in the supplied question, including ambiguity about whether to return a count or a list.The typo in the Chinese Grand Prix question is noted for all variants; other examples identify ambiguity about count versus name or count versus list.Source: Appendix · pp. 23–24
• Good ratings note that generated questions omit requested information when the supplied question asks for a score or the more prolific publisher, but the SQL does not return it.The Monaco Grand Prix ratings note the missing score; the Marvel-versus-DC ratings note that the SQL does not identify which publisher has more superheroes.Source: Appendix · pp. 24–25

• For the DC-versus-Marvel count difference, all three generated variants receive good+ ratings, with a caveat that they ask who created more superheroes although the SQL computes a count difference.Source: Appendix · p. 27
• For identifying a post’s last editor, BIRD-only, LLM-only, and fused questions ask for the editor’s display name or identity, and each is rated good.Source: Appendix · p. 27
• For the query counting users with more than five badges, base, BIRD-only, LLM-only, and fused questions all ask about users exceeding that threshold; the shown ratings are good.Source: Appendix · p. 28
• For the comment-count query, BIRD-only, LLM-only, and fused questions each ask how many comments the top-scoring post has, and each receives a good rating.Source: Appendix · p. 29

Appendix

• For a single-comment, zero-score-post question, all four variants were rated good, and the fused version’s “no upvotes” wording better matched field metadata.The base, Bird-only, and LLM-only versions also received good ratings.Source: Appendix · p. 29
• For Commander-legal cards with unknown power, Bird-only and fused questions were rated good, while base and LLM-only versions missed that * means unknown.The fused rating passage also gives a reason about “no upvotes,” which does not match this example.Source: Appendix · p. 29
• All four variants received good- ratings for asking about Benalish Knight’s format, but the ratings noted that “format” means “rule” in the metadata.The supplied question asks for the card’s rule of playing.Source: Appendix · p. 30
• For black Artifact cards with foreign-data entries, all four variants were rated good, but the ratings noted that foreign_data does not imply a translation.The sample query identifies cards with corresponding foreign_data entries.Source: Appendix · p. 30
• Other examples show good+ and good ratings for questions about premodern cards with triggered-mana rulings and textless cards with normal layout.The premodern example’s ratings cite poor grammar in the supplied question.Source: Appendix · pp. 30–31
• For cards in Hauptset Zehnte Edition, all four variants were rated good, with the ratings clarifying that the artist field refers to the illustrator, not the designer.The generated questions describe the cards as illustrated by Adam Rex.Source: Appendix · p. 31
• For the Coldsnap query, base, Bird-only, LLM-only, and fused questions were rated good+ because the supplied question did not match the SQL.The SQL counts cards with both Card Kingdom identifiers, whereas the supplied question asks about powerful cards.Source: Appendix · p. 31
• Bond-type and double-bond element questions received good ratings across base, Bird-only, LLM-only, and fused generations.The examples ask for the most common bond type and for elements in molecules with double bonds.Source: Appendix · p. 32
• For the triple-bond molecule query, base and Bird-only ratings were good-, LLM-only was bad+, and fused was good; the issue was interpreting the '+' label.The fused question describes the molecules as carcinogenic, matching the intended meaning of the label.Source: Appendix · p. 32
• The base, Bird-only, and LLM-only questions were rated good- for not picking up on the meaning of the label, whereas the fused question was rated good.Source: Appendix · p. 33
• For the chlorine-in-molecules query, base and LLM-only were rated good-, while fused was good; the weaker ratings cited failure to interpret the '+' label.The fused question refers to chlorine atoms in carcinogenic molecules.Source: Appendix · pp. 33–34
• For the sat>=1500 question, Base, Bird-only, and LLM-only each received good- ratings for missing that the score means excellent; Fused received good.Source: Appendix · p. 34
• For the school-name example, base and Bird-only questions omit school names and are rated good-, while LLM-only and fused questions include names and are rated good.The supplied question asks for school names along with scores and phone numbers.Source: Appendix · p. 34
• The Fresno average-test-taker questions are rated good despite the supplied question being vague, and the birth-date district questions are rated good+ despite its presupposing exactly one client.The cited examples illustrate that generated questions can be judged positively while noting shortcomings in the supplied question.Source: Appendix · pp. 34–36
• In the Orange County school example, base, Bird-only, and LLM-only questions miss the meaning of school district codes, while the fused question names unified and elementary districts and is rated good.The other three approaches are each rated good-.Source: Appendix · p. 35
• For GSoffered, base and LLM-only questions misread the field as a graduate program, whereas Bird-only and fused questions interpret it as grade span and receive good ratings.The fused wording is described as awkward but accurate.Source: Appendix · p. 35
• For North Bohemia, base and Bird-only questions do not interpret A11, the LLM-only questions miss its meaning, and the fused questions identify average district salary and are rated good.Base and Bird-only are rated good-; LLM-only is rated bad.Source: Appendix · p. 35
• For the second-highest A15 case, base, Bird-only, and LLM-only questions do not identify the field’s meaning, while fused questions connect it to crimes in 1995 and are rated good.The other three approaches are rated good-.Source: Appendix · p. 36
• The base questions ask for the percentage change in loan amounts for male account owners between 1996 and 1997, and are rated good despite SQL stability problems.The two phrasings ask about percentage change in the sum of loan amounts and the increase or decrease in loan amount.Source: Appendix · p. 36
• BIRD-only, LLM-only, and fused questions rephrase the same 1996–1997 loan-amount comparison, and each is rated good with SQL stability problems.The fused versions refer to approved loans; the LLM-only versions include a question about loans taken.Source: Appendix · p. 37