Source-linked AI summary
From Benchmarks to Production: A Text-to-SQL System for Complex Financial Data
Arijit Sehanobish, Bruno Gomes Coelho, Guillaume Michel, Sophia Zhi, Valerie Faucon-Morin, Kristen Howell
TL;DR
Production financial databases encode concepts as opaque IDs across normalized tables, a setting where existing Text-to-SQL systems often return incorrect data despite successful execution. FLINT combines value lookup, retrieval, and schema linking for this setting, and outperforms seven academic baselines on two proprietary financial benchmarks. Its configuration depends on domain experts and curated examples, and its schema-linking design targets fixed, curated schemas.
Problem
Production financial schemas use opaque integer keys and deep joins, and seven evaluated methods stayed below 50% accuracy despite over 99% SQL execution success.
Source: §1 · p. 1Method
Results
FLINT outperforms the seven academic baselines on two proprietary financial benchmarks.
Source: §5 · p. 7Takeaways & Limitations
FLINT’s domain knowledge is maintained in configuration artifacts that experts can update without retraining or ML engineering involvement.
Source: §1 · p. 1Domain rules require subject-matter experts, retrieval depends on a curated query bank unavailable for schemas without historical queries, and schema linking is suited to fixed, curated schemas.
Source: §3.4 · p. 4, Limitations · p. 8
Abstract
from arXiv · showhide
General-purpose Text-to-SQL systems achieve strong performance on academic benchmarks like Spider and BIRD, where schemas are relatively shallow and column values are often human readable. In production financial databases, where concepts are stored as opaque integer keys rather than human-readable strings, these methods fall below 50%, as even simple queries require multiple joins and filter predicates reference opaque IDs. We present Financial LINking Text-to-SQL (FLINT), a domain-specialized Text-to-SQL system that closes this gap through three key components: (1) a lookup agent that dynamically resolves natural-language concepts to question-specific reference table constraints, (2) embedding-based retrieval of structurally similar query templates from a compact, expert-authored bank, and (3) schema linking that prunes a large table schema to the relevant subset by traversing foreign-key chains, rather than relying on name similarity alone. We evaluate on two datasets totaling 359 questions over production financial schemas. FLINT outperforms various state-of-the-art baselines using the same LLM. The system is deployed in production as part of a financial data retrieval service.
1 Introduction
Production financial schemas encode important concepts as opaque IDs across deep joins, exposing a gap that academic Text-to-SQL benchmarks do not measure. FLINT targets this value-grounding bottleneck with lookup, example retrieval, and schema linking.
1 Introduction
- Financial metrics, period types, and currencies are opaque integer foreign keys across dozens of normalized tables, and a simple time-series question can require a six-table join chain.Academic benchmark values are self-describing, joins average 1–2, and schemas fit in one prompt.Source: §1 · p. 1
- FLINT combines a lookup agent for question-specific reference-table constraints, retrieval of expert-authored structural templates, and schema linking that selects relevant tables and bridge tables.Its model-independent configuration layer lets domain experts update artifacts as schemas evolve, without retraining or ML engineering involvement.Source: §1 · p. 1
- 76–96% of errors on normalized production-style schemas are wrong reference-table filter IDs, making value grounding—not SQL generation—the dominant failure mode.Seven state-of-the-art methods on two production financial datasets stayed below 50% accuracy despite over 99% SQL execution success.Source: §1 · pp. 1–2
- FLINT also introduces a three-tier semantic evaluation pipeline that distinguishes equivalent queries from genuine errors.The stages are AST canonicalization, column-aligned comparison, and an LLM judge.Source: §1 · p. 2
2 Related Work
Prior Text-to-SQL work addresses structural errors through specialized stages, exploration, and execution feedback, but does not directly resolve opaque reference-table IDs. Benchmark evaluation also differs in how it handles equivalent reformulations and semantic errors.
2 Related Work
- Recent multi-agent systems target candidate selection, interactive database exploration, and execution-feedback refinement, but lack dedicated resolution of opaque reference-table IDs.On normalized financial schemas, that shared gap is identified as the main source of error.Source: §2 · p. 2
- Spider uses structural exact set match, while BIRD evaluates query outputs; each can misclassify equivalent reformulations or semantic errors in different ways.SpotIt hypothesizes that up to 20% of BIRD evaluation data may be incorrect because test databases can coincide on non-equivalent queries.Source: §2 · p. 2
- FLINT’s benchmarks differ from Spider and BIRD through deep normalization, opaque IDs, mandatory multi-table joins, and implicit domain conventions.Its three-tier evaluation combines AST canonicalization, output comparison, and an LLM judge to accommodate valid reformulations.Source: §2 · p. 2
3 System Architecture
FLINT divides Text-to-SQL into parallel and sequential stages that resolve values, retrieve query patterns, link entities and schema, generate SQL, and reflect on execution failures. Dataset-specific configuration supplies the domain knowledge used throughout the pipeline.
3 System Architecture
- Lookup and similar-query retrieval run in parallel, followed by parallel entity and schema linking; the pipeline’s median end-to-end latency is 10–20 seconds.The six stages are lookup, retrieval, entity linking, schema linking, SQL generation, and execution with reflection.Source: §3 · p. 2
3.1 Lookup Agent
- Lookup pre-filtering maintains 98% recall against gold queries, and its SQL WHERE-clause constraints are passed to later stages.For Financials, metric resolution narrows 11,412 items to about 2,500 curated items, then to about 50–100 per-question candidates; Transactions tables are passed whole.Source: §3.1 · pp. 2–3
3.2 Similar Query Retrieval
- The query bank retrieves the three most similar expert-authored examples by cosine similarity, supplying structural templates for schema linking and SQL exemplars for generation.Examples cover distinct query patterns, and the bank is disjoint from the evaluation set.Source: §3.2 · p. 3
3.3 Entity Linking
- An external entity-disambiguation service maps company names and ticker symbols to canonical integer IDs, which are injected as concrete WHERE-clause constraints.This avoids unreliable fuzzy matching across more than 20 million entities.Source: §3.3 · p. 3
3.4 Schema Linking
- Schema linking selects tables, columns, joins, and filters using the question, lookup constraints, IDs, and retrieved examples, including intermediate tables needed as join bridges.The LLM produces a structured representation of columns, joins, and filters; the design is suited to a fixed, curated schema because it sees the full schema.Source: §3.4 · pp. 3–4
3.5 SQL Generation
- SQL generation uses the schema links, entity IDs, exemplars, and domain rules to produce a query, while a safety validator restricts access to allowed schemas and tables.Prompt rules include applying IDs as filters, adding contextual columns, and following the target SQL dialect.Source: §3.5 · p. 4
3.6 Execution and Reflection
- After execution failure, reflection gives the LLM the question, failed SQL, and database error so it can regenerate with targeted corrections.Only one reflection attempt is allowed by default to meet latency constraints, but the limit is configurable.Source: §3.6 · p. 4
3.7 Dataset Configuration
- FLINT adapts to new datasets through four configuration artifacts: schema documentation, reference-table lists, a 15–30-example query bank, and domain-specific prompts.Experts author the artifacts; configuration takes roughly one person-week for moderate-complexity datasets and two to three weeks for complex ones, with no implementation changes required.Source: §3.7 · p. 4
4 Experiments
Across two production financial datasets, FLINT is evaluated against existing Text-to-SQL systems using a multi-stage semantic-equivalence protocol. The experiments show strong baseline gaps, identify the components and error types that matter, and test generalization beyond finance.
4.1 Datasets
- The 359 expert-authored questions cover two normalized datasets: Financials has 221 questions and 24 tables, while Transactions has 138 questions and 18 tables.Typical queries require six joins in Financials and five in Transactions; complex Financials screening queries require up to 13 joins.Source: §4.1 · pp. 4–5
4.2 Evaluation
- Accuracy is based on a three-tier protocol that executes queries, matches canonicalized ASTs and compares aligned columns and values, then uses an LLM judge for remaining cases.Heuristics resolve almost 60% of cases; the judge decides about 40% and agrees with human judgments at least 94% of the time on a validated sample.Source: §4.2 · p. 5
4.3 Comparison with Existing Methods
- 68.8% (Financials) and 65.2% (Transactions) are FLINT’s accuracies; no baseline exceeds 50% on the production financial schemas.All methods achieve at least 99% execution success, but incorrect reference-table filter values dominate baseline errors.Source: Table 2 · p. 6, §4.3 · p. 6
- With only minor configuration changes, FLINT outperforms CHESS and ReFoRCE on external scientific and medical benchmarks while remaining almost 5× faster.The ScienceBenchmark comparison covers CORDIS, OncoMX, and SDSS; generation time excludes database execution.Source: Figure 3 · p. 6, §4.3 · p. 6
4.4 Ablation Study
- Removing lookup drops accuracy by 14 points on Financials and 32 on Transactions, the largest component effect reported.Retrieval is the second-largest contributor; schema linking matters especially for Transactions, while reflection recovers 15 of 22 failures there.Source: §4.4 · p. 7
4.5 Error Analysis
- Of FLINT’s 117 incorrect predictions, 71 involve column selection, while 30 occur after correct table and column selection because of lookup-constraint errors.A manual audit treats many partial-match failures as presentation-only, raising effective accuracy to approximately 85% on Financials and 76% on Transactions.Source: §4.5 · p. 7, Table 4 · p. 7
5 Conclusion
FLINT targets production financial databases with deep schemas, opaque integer keys, and implicit domain constraints. It outperforms seven baselines on two proprietary financial benchmarks and generalizes across four scientific and medical domains with minimal changes.
5 Conclusion
- FLINT combines schema linking, example retrieval, entity resolution, domain-specific prompt rules, and execution-based reflection in a single-pass pipeline with 10–20 seconds per-query latency.The system targets production financial databases with deep schemas, opaque integer keys, and implicit domain constraints.Source: §5 · p. 7
- FLINT outperforms seven academic baselines on two proprietary financial benchmarks and generalizes across four additional scientific and medical domains with minimal changes.The authors suggest the gap between academic benchmarks and production deployment concerns surrounding domain knowledge, including table selection, value meaning, and question intent.Source: §5 · p. 7
- The authors present FLINT’s architecture as one reusable pattern for addressing the gap between academic Text-to-SQL benchmarks and production deployment.Source: §5 · p. 7
Limitations
The appendix illustrates why these financial benchmarks challenge Text-to-SQL systems: even routine questions involve opaque reference IDs and long join chains, while complex cross-country queries add currency normalization. It also describes FLINT’s practical scope and the benchmark’s intended statistical use.
Limitations
- FLINT’s domain rules require subject-matter experts, its retrieval needs a curated query bank, and its single-turn design does not support conversational refinement.The evaluation’s LLM judge also has imperfect agreement with human annotations.Source: Limitations · p. 8
A.1 Difficulty levels
- Join count and CTE presence define three difficulty levels, but join count is an imperfect proxy because implicit domain knowledge can make shorter queries harder.The levels are simple (1–3 joins, no CTE), moderate (4–6 joins, no CTE), and complex (7+ joins or CTE).Source: §A.1 · p. 9
A.2 Financials Dataset
- A standard time-series financial query requires six joins, and its metric and period-type IDs must be resolved through lookup tables.The passage identifies this join chain as the backbone of most Financials queries.Source: §A.2 · p. 9
A.3 Transactions Dataset
- Transactions examples range from resolving IPO type and cancellation status through reference tables to M&A queries that combine currency conversion and industry-ID filtering.The complex example uses seven joins, including three in a currency-conversion CTE.Source: §A.3 · pp. 10–11
A.4 Key Observations
- The benchmark’s minimum time-series financial retrieval path requires six joins, while cross-country comparisons add a three-join currency-normalization CTE that requires domain knowledge.Even simple questions can require lookup-table resolution of opaque integer IDs.Source: §A.4 · p. 11
A.5 Benchmark Size
- The 359-question benchmark uses two fixed schemas, and its authors argue that a 95% confidence interval at 77% accuracy with n=221 is narrow enough to distinguish FLINT from all baselines at p < 0.001.They state it is not intended to measure fine-grained differences between systems within a few points.Source: §A.5 · p. 11
A.6 Comparison with Existing Text-to-SQL Benchmarks
The financial benchmarks differ from Spider and BIRD in opaque values, deeper join structures, and implicit domain conventions. The appendix also discusses configuration effort and why standard evaluation measures can misjudge query correctness.
A.6 Comparison with Existing Text-to-SQL Benchmarks
- Financial benchmark questions use opaque integer IDs, require deeper joins, and rely on domain conventions that are not explicit in natural language.Examples include “revenue” implying annual reporting and cross-country comparisons requiring currency normalization.Source: §A.6 · p. 12, Table 6 · p. 12
B Configuration Cost
- Configuring a dataset involves authoring expert query-bank examples and other artifacts; the authors estimate roughly a person-week for moderate complexity and two to three weeks for a complex dataset.They report that domain rules are a prerequisite and that tightening a rule tends to help more than adding examples beyond a small, diverse bank.Source: B Configuration Cost · p. 12
C Evaluation Pipeline Details
- Structural matching can reject equivalent SQL, while execution comparison can flag output differences unrelated to semantic correctness, motivating an evaluation pipeline that addresses both limitations.Production-scale schemas also make result equality sensitive to row ordering, null handling, and floating-point precision.Source: C Evaluation Pipeline Details · p. 12
C.1 Comparison with Standard Benchmarks
The appendix explains the evaluation pipeline and how baseline systems were adapted for normalized financial databases. Its comparisons distinguish shared infrastructure from each method’s original strategy and note several adaptation-related caveats.
C.1 Comparison with Standard Benchmarks
- The evaluation addresses structural and execution-comparison limitations by combining column alignment, order-invariant comparison, AST canonicalization, and an LLM judge for semantic equivalence.The paper notes that financial queries admit valid reformulations that structural matching alone can reject.Source: §C.1 · pp. 12–13
C.2 Three-Tier Pipeline
- A three-tier pipeline first canonicalizes query ASTs, then compares executed outputs when AST matching fails, and finally uses an LLM judge when outputs still differ.The judge achieved 94% agreement with human annotators on 50 sampled decisions.Source: §C.2 · p. 13
D Baseline Adaptation Details
- Baseline adaptation required infrastructure and architectural choices because these production schemas average six or more joins and contain at least 16 reference tables.All methods received shared Snowflake access, while pipeline methods also received appropriate entity linking, post-processing, and evaluation infrastructure.Source: D Baseline Adaptation Details · p. 13, §D.1 · pp. 13–14
D.2 Direct Prompting (Zero-Shot and Few-Shot)
- Direct-prompting baselines receive the full schema and entity IDs, while the few-shot version additionally receives three retrieved examples; the comparison with FLINT isolates the broader specialized pipeline.The shared entity-linking service resolves company names for both configurations.Source: §D.2 · p. 14
D.4 DAIL-SQL
- DAIL-SQL and DIN-SQL are implemented with shared lookup, entity-linking, and retrieval infrastructure, while retaining their respective prompt-level example-selection and classification-aware self-correction strategies.The authors characterize these infrastructure choices as making the comparisons conservative.Source: §D.4 · p. 14
D.6 MAC-SQL
- MAC-SQL explores the database interactively, whereas Chase-SQL generates multiple candidates and selects among executable queries through pairwise judging.Chase-SQL’s implementation receives no lookup agent or entity linking, leaving it more vulnerable to reference-table resolution failures.Source: §D.6 · p. 15, §D.7 · pp. 15–16
D.8 FlexSQL
- The authors preserve FlexSQL’s five-stage pipeline and ReFoRCE’s iterative exploration and refinement loop, injecting retrieved examples in their three-shot configurations.ReFoRCE is implemented with up to five refinement iterations and early stopping on success.Source: §D.8 · p. 16, §D.9 · p. 16
D.10 Summary of Adaptations
- The adaptation discussion flags that Chase-SQL omits its original value retrieval and that MAC-SQL uses an LLM selector instead of embedding-based schema selection.The authors state these changes may disadvantage Chase-SQL on entity-resolution-heavy queries and may affect MAC-SQL’s schema pruning performance.Source: §D.10 · p. 16
D.11 Failure Analysis
The failure analysis identifies opaque reference-value resolution as the dominant error source, while also tracing schema-selection and query-complexity failures. The latency analysis reports FLINT's production response time relative to the compared systems.
D.11 Failure Analysis
- FLINT resolves 94%/75% of required IDs on Financials/Transactions; without lookup, ID accuracy falls to 10%/7%, and 64–96% of Financials failures involve incorrect filter values.DAIL-SQL and DIN-SQL resolve IDs in only 21–29% of queries despite receiving candidate IDs; domain-specific prompts are needed to disambiguate them.Source: §D.11 · pp. 16–17, Table 8 · p. 17
- CHESS reaches 46% ID accuracy, but self-consistency cannot correct filter errors shared across its samples; Chase-SQL lacks value retrieval, and ReFoRCE cannot repair semantically wrong IDs through execution feedback.MAC-SQL resolves some reference values (30–39% ID accuracy), but its five-query budget can expire before complex queries' lookups are complete.Source: §D.11 · p. 17
- FlexSQL on Transactions resolves IDs correctly 69% of the time but selects the right tables only 65% and right columns 46%, pointing to missed structural paths.It is an exception: fewer than half of its errors are attributable to ID resolution.Source: §D.11 · p. 17
- FLINT's Transactions schema-linking errors cluster around dropped buyer/target relationship paths and over-selected currency or status tables.The analysis suggests more explicit join-path and currency-usage guidance in the schema-linking prompt for Transactions-like schemas.Source: §D.11 · p. 18
- FLINT's accuracy peaks on moderate queries (75%/70%); complex queries score 54%/52%, while simple queries are lower despite their reduced structural complexity.The audit raises effective accuracy to ∼85%/∼76% after discounting presentation-only failures; remaining simple-query errors include entity-linking mistakes and over-engineering.Source: §D.11 · p. 18
D.12 Latency Analysis
- FLINT’s median latency of 10–20s enables interactive production deployment, beating all compared Text-to-SQL systems; CHESS’s latency, driven by sequential candidate generation and database exploration, makes it infeasible for real-time use.Source: §D.12 · p. 18
E Ablation Details
The ablations isolate lookup, schema linking, retrieval, and reflection by disabling one component at a time. The supplied results highlight how often reflection can be triggered and compare FLINT's latency with other systems.
Lookup Agent
- The lookup ablation replaces dynamic resolution with static mappings for the 100 most common Financials IDs and 50 most common Transactions IDs, covering about 78% of queries.The mappings are injected as pre-resolved constraints to test question-specific resolution against static knowledge.Source: E Ablation Details · p. 18
Schema Linking
- The schema-linking ablation gives the generator the full schema; table overlap remains 0.96, but column selection degrades.Lookup, retrieval, entity linking, and reflection remain active in this condition.Source: E Ablation Details · p. 18
Few-Shot Retrieval
- The few-shot retrieval ablation returns no examples, leaving schema linking and SQL generation in zero-shot mode while lookup constraints remain available.This isolates the contribution of structural query templates.Source: E Ablation Details · p. 18
Reflection
- The reflection ablation submits the first generated SQL without retry, regardless of whether execution succeeds.Each ablation disables exactly one component while keeping the rest of the pipeline intact.Source: E Ablation Details · p. 18
- On Financials, fewer than 1% of queries trigger reflection, while 16% of Transactions queries initially fail execution and could be candidates for recovery.Figure 5 reports FLINT's 10–20s median latency, almost 2× faster than the next-fastest Financials baseline and almost 100× faster than CHESS.Source: E Ablation Details · p. 19, Figure 5 · p. 19
F Query Bank Overlap Analysis
The overlap analysis tests whether similarity between retrieved examples and evaluation queries could explain FLINT's accuracy. It argues that the bank supplies reusable structure rather than answer-level matches.
F Query Bank Overlap Analysis
- Baselines given the same query bank still trail FLINT by ∼20–30 points, while the evaluation questions and bank examples are disjoint by construction.Bank examples use a fixed canonical entity, so similar structures still differ in entities, metrics, or dates.Source: F Query Bank Overlap Analysis · p. 19
- High structural overlap reflects a shared join backbone and currency handling, while the content-aware measure shows essentially no near-duplicates on Transactions.Overlap is reported as 1 − TED, using normalized tree-edit distance over canonicalized SQL ASTs.Source: F Query Bank Overlap Analysis · p. 19, Table 10 · p. 19
G Generalization to Other Domains
FLINT improves over few-shot across the evaluated external domains, with gains attributed to retrieval, domain rules, and execution-based reflection. The paper also describes production safeguards, monitoring signals, and how its schema linking addresses bridge-table traversal.
G Generalization to Other Domains
- On SM3-Text-to-Query, FLINT reaches 97% accuracy on multi-table joins and 97% on unanswerable-question detection, versus 90% and 42% for few-shot.The reported gain concentrates on the hard subset: +11pp over few-shot.Source: G Generalization to Other Domains · p. 19, Table 11 · p. 20
- ScienceBenchmark tests CORDIS, OncoMX, and SDSS, spanning schemas of 19, 25, and 6 tables, respectively.The databases cover EU funding, cancer biomarkers, and astronomy; their challenges include many-to-many joins, deep foreign-key paths, and domain-specific type lookups.Source: G Generalization to Other Domains · pp. 19–20, Figure 6 · p. 20
- FLINT improves over few-shot in every evaluated domain, by as much as 14 points, using configuration changes rather than architectural changes.The reported mechanisms include retrieved join patterns, domain-specific rules, and reflection after empty results.Source: G Generalization to Other Domains · pp. 19–20, Figure 6 · p. 20
H Deployment Considerations
- FLINT serves production traffic against a multiterabyte financial database, where deployment lacks gold queries for direct correctness checks.The deployment therefore relies on safety controls and proxy signals to detect failures and degradation.Source: H Deployment Considerations · p. 20, §H.2 · p. 20
H.1 Query Safety
- A read-only connection, entitlement checks, and a structural validator block unauthorized references, mutation statements, unbounded scans, and joins above a configurable fan-out threshold.Warehouse limits also constrain timeout, memory, and result-set size.Source: §H.1 · p. 20
H.2 Monitoring and Quality Signals
- Monitoring uses execution failures, result-set anomalies, user feedback, and rolling aggregate metrics because deployment has no ground truth.Sustained degradation triggers review of the retrieval index and prompt templates.Source: §H.2 · p. 20
I Expanded Related Work
- FLINT's schema linking targets bridge tables needed for foreign-key traversal, beyond selecting only the tables and columns containing answers.It uses join paths, resolved lookup constraints, and retrieved examples to guide bridge-table identification.Source: I Expanded Related Work · pp. 20–21