The biggest issue with business users (who don’t know SQL) writing SQL with an LLM is that there is no one to validate that query and now that business user will treat that LLM response as canon to share in meetings, presentations, and with clients. The LLM may have forgotten a filter, used the wrong definition of revenue, or misunderstood the user’s intent by writing a query that answers their question in the most literal way.
That is the crux of the issue. No amount of semantic layers and context will help this until an LLM can read the user’s mind to remove ambiguity in the prompt.
I see most of the benefits of LLMs to be used by analysts who know SQL to work more productivly.
Fair point, but before business people relied on other people to pull it for them. Sure, it was checked by human, but a technical human often lacked the business context and there's been plenty of instances, where the business person knowing their revenue and sales numbers challenged the analysis of a technical person.
Yeah with self-serve analytics all the rage (for good reason) for a bit, the bar from some places I've worked wouldn't be "does the agent beat a good analyst" it's "does the agent beat the a business person with SQL access who might have an overworked analyst glance over it", which is a much more tractable break-even point - and as you said, the business person might be even better at sanity checking numbers at that point.
also, the same metric can be used in different ways and for different purposes across a business, so you might need different validation rules and definitions.
Maybe one separate semantics validation layer could help, but costs 2x or possibly Nx if you need to recover and turn a wrong query into a correct one
Users can't "own" queries they don't understand. An LLM helping a user avoid annoying syntax errors is very useful; but helping a user write a query they ultimately cannot understand is fraught with the problems you describe.
This was a very enjoyable read! Constraining the language surface is helpful, but the lost expressiveness can bite unless you’re in a constrained domain - which this seems like it was!
Interesting... at my current job, my team and I are solving this problem of how to make sure LLM understand our SQL data warehouse to answer analytical questions for clients. We have a 20-year old database with rotting schema as the blog post describes. So we had to rebuild the database in a way that is well structured and governed. Once that hard work is done, we slap dbt models on top of business metrics with model YAML files (and some common MD files) carrying a lot of semantic and metadata info for them.
Then our software engineering team ingest the dbt models (we have to tactically create dbt models; that is, always think "what would make LLM hallucinate less" as we are implementing them) and info from the semantic layer to build context for the LLM, and use that to answer analytical questions. So far, it's been promising. The accuracy isn't zero like the blog's author suggested though. We have built like 30 metrics in dbt and semantic layer in the last quarter, and asked the research and analytics teams to do internal testing on the LLM app. I will find out how accurate this approach is from the feedback soon.
SQL generation is quite good, 90% of the problem is data quality.
The best thing you can do for your data agent is build a clean frontroom around your specific desired use cases -- with very clear documentation and obvious idiomatic join patterns built on clear marts. It is hard work, but I've been doing this a while and this is the only way.
A full top down rebuild is rarely feasible and can take years. What I suggest is focusing on rebuilding your base layers and introducing a versioned schema/model approach (to isolate breaking changes). Even with the data/compute redundancy it introduces, it is so much easier to move the sale report from customers_v1 to customers_v2 than it is to evaluate what happens when you remove the salesforce id as primary key from customers into all the downstream dependencies.
SQL generation has gotten very good, and most of the problems listed are no longer as big of a problem. addressing schema rot was always a tedious chore, but now is easily ameliorated by delegating to coding agents. Drifting definitions are solved by giving the agents access to the pipeline code.
However, in my experience the answer a business user needs is rarely the answer to the question they initially ask. Asking for clarification, pushing back on poorly framed or conceived asks where most of the value comes from. LLMs are still too eager to jump into the code, and the sycophancy problem is especially challenging in this space.
Ideally you ask the agent the thing you want to know / business question you want answered.
An agentic loop then runs. The agent can look at the schema, look at any existing SQL scripts available that query similar tables, run a few limit 10 probe queries. Analyze the data, do some joins, check the data again. Show you the data, ask for feedback, etc.
Pure zero / one shot SQL generation isn’t the solution and isn’t how humans do things. We look at the schema, run some queries, do some joins, spot check the totals / row counts, etc.
I think/hope that most benchmarks have moved to an agentic loop - I'd still call that 'text to sql', since you're going from the business question to one or more SQL queries that provide the answer.
With a loop you can get extremely high results on a clean DB with a clear question. (I'm usually seeing high 9s accuracy.). A messy/ambiguous DB schema then degrades that again (which is what companies actually have, where you get closer to 20-30% rates pre-context engineering), which a clean semantic/presentation layer can bring back up. Then you're just left with the problem of underspecified/poorly formulated question - which you can partially solve with the agent pushing back, but also can be solved by improving the human side of things.
I think in context they meant something like 'it is unsurprising that an LLM can find data it has already seen before.' The intent is to point out that benchmarks tend to use public data. In context I don't think they were actually trying to claim LLMs are useless at unfamiliar data, just that the benchmarks have a specific flaw.
The problem with AI benchmarks is that they can/will be gamed. You pretty much have to keep the criteria a secret for it to mean anything. I guess that's a long winded way of saying I don't trust any benchmarks -- sql or otherwise -- because I don't think they prove much of anything.
Text to sql can absolutely work if you design the system correctly.
I will concede that single shot is a dead end path. However, if you run a research agent with parallel hypothesis generation, multiple outer loops, etc., the chances you encounter the correct query go up dramatically. The game is to force a supervisor to compare two queries that attempt to accomplish the same report but have different results. Involving the human operator in this loop can take you the remainder.
Friend of mine built a startup around this, allowing non-techies to "query their database using natural language": https://www.blazesql.com/ Not sure how he achieved it (if TFA is to be believed), but it's my impression that his query generation and results are fairly robust.
this kind of benchmark is particularly susceptible to a narrow grader, where the input request is vague and therefore a significant range of SQL output would be valid, yet graded incorrect.
The biggest issue with business users (who don’t know SQL) writing SQL with an LLM is that there is no one to validate that query and now that business user will treat that LLM response as canon to share in meetings, presentations, and with clients. The LLM may have forgotten a filter, used the wrong definition of revenue, or misunderstood the user’s intent by writing a query that answers their question in the most literal way.
That is the crux of the issue. No amount of semantic layers and context will help this until an LLM can read the user’s mind to remove ambiguity in the prompt.
I see most of the benefits of LLMs to be used by analysts who know SQL to work more productivly.
Fair point, but before business people relied on other people to pull it for them. Sure, it was checked by human, but a technical human often lacked the business context and there's been plenty of instances, where the business person knowing their revenue and sales numbers challenged the analysis of a technical person.
Yeah with self-serve analytics all the rage (for good reason) for a bit, the bar from some places I've worked wouldn't be "does the agent beat a good analyst" it's "does the agent beat the a business person with SQL access who might have an overworked analyst glance over it", which is a much more tractable break-even point - and as you said, the business person might be even better at sanity checking numbers at that point.
also, the same metric can be used in different ways and for different purposes across a business, so you might need different validation rules and definitions.
Maybe one separate semantics validation layer could help, but costs 2x or possibly Nx if you need to recover and turn a wrong query into a correct one
I recently shared a similar rationale for why a custom query language can help users even in the age of LLMs.
https://nchammas.com/writing/custom-query-language-design#wh...
Users can't "own" queries they don't understand. An LLM helping a user avoid annoying syntax errors is very useful; but helping a user write a query they ultimately cannot understand is fraught with the problems you describe.
This was a very enjoyable read! Constraining the language surface is helpful, but the lost expressiveness can bite unless you’re in a constrained domain - which this seems like it was!
Interesting... at my current job, my team and I are solving this problem of how to make sure LLM understand our SQL data warehouse to answer analytical questions for clients. We have a 20-year old database with rotting schema as the blog post describes. So we had to rebuild the database in a way that is well structured and governed. Once that hard work is done, we slap dbt models on top of business metrics with model YAML files (and some common MD files) carrying a lot of semantic and metadata info for them.
Then our software engineering team ingest the dbt models (we have to tactically create dbt models; that is, always think "what would make LLM hallucinate less" as we are implementing them) and info from the semantic layer to build context for the LLM, and use that to answer analytical questions. So far, it's been promising. The accuracy isn't zero like the blog's author suggested though. We have built like 30 metrics in dbt and semantic layer in the last quarter, and asked the research and analytics teams to do internal testing on the LLM app. I will find out how accurate this approach is from the feedback soon.
you should checkout https://github.com/malloydata/malloyyo - it's great at what you trying to do.
SQL generation is quite good, 90% of the problem is data quality.
The best thing you can do for your data agent is build a clean frontroom around your specific desired use cases -- with very clear documentation and obvious idiomatic join patterns built on clear marts. It is hard work, but I've been doing this a while and this is the only way.
A full top down rebuild is rarely feasible and can take years. What I suggest is focusing on rebuilding your base layers and introducing a versioned schema/model approach (to isolate breaking changes). Even with the data/compute redundancy it introduces, it is so much easier to move the sale report from customers_v1 to customers_v2 than it is to evaluate what happens when you remove the salesforce id as primary key from customers into all the downstream dependencies.
SQL generation has gotten very good, and most of the problems listed are no longer as big of a problem. addressing schema rot was always a tedious chore, but now is easily ameliorated by delegating to coding agents. Drifting definitions are solved by giving the agents access to the pipeline code.
However, in my experience the answer a business user needs is rarely the answer to the question they initially ask. Asking for clarification, pushing back on poorly framed or conceived asks where most of the value comes from. LLMs are still too eager to jump into the code, and the sycophancy problem is especially challenging in this space.
Text to SQL is the wrong framing of the problem.
Ideally you ask the agent the thing you want to know / business question you want answered.
An agentic loop then runs. The agent can look at the schema, look at any existing SQL scripts available that query similar tables, run a few limit 10 probe queries. Analyze the data, do some joins, check the data again. Show you the data, ask for feedback, etc.
Pure zero / one shot SQL generation isn’t the solution and isn’t how humans do things. We look at the schema, run some queries, do some joins, spot check the totals / row counts, etc.
I think/hope that most benchmarks have moved to an agentic loop - I'd still call that 'text to sql', since you're going from the business question to one or more SQL queries that provide the answer.
With a loop you can get extremely high results on a clean DB with a clear question. (I'm usually seeing high 9s accuracy.). A messy/ambiguous DB schema then degrades that again (which is what companies actually have, where you get closer to 20-30% rates pre-context engineering), which a clean semantic/presentation layer can bring back up. Then you're just left with the problem of underspecified/poorly formulated question - which you can partially solve with the agent pushing back, but also can be solved by improving the human side of things.
Maybe an editing snafu? It is odd.
I think in context they meant something like 'it is unsurprising that an LLM can find data it has already seen before.' The intent is to point out that benchmarks tend to use public data. In context I don't think they were actually trying to claim LLMs are useless at unfamiliar data, just that the benchmarks have a specific flaw.
The problem with AI benchmarks is that they can/will be gamed. You pretty much have to keep the criteria a secret for it to mean anything. I guess that's a long winded way of saying I don't trust any benchmarks -- sql or otherwise -- because I don't think they prove much of anything.
Text to sql can absolutely work if you design the system correctly.
I will concede that single shot is a dead end path. However, if you run a research agent with parallel hypothesis generation, multiple outer loops, etc., the chances you encounter the correct query go up dramatically. The game is to force a supervisor to compare two queries that attempt to accomplish the same report but have different results. Involving the human operator in this loop can take you the remainder.
>On this benchmark, a pure LLM generated an accuracy score of zero. Adding RAG, prompt engineering, and agentic AI raised accuracy to the 10+% range.
Oouch!
> On this benchmark, a pure LLM generated an accuracy score of zero. Adding RAG, prompt engineering, and agentic AI raised accuracy to the 10+% range.
That's awful. The AI in Databricks is much better than that.
FCO-IM elegantly bridges the gap between data and language. Has anyone tried/compared SQL query generation based on that?
Friend of mine built a startup around this, allowing non-techies to "query their database using natural language": https://www.blazesql.com/ Not sure how he achieved it (if TFA is to be believed), but it's my impression that his query generation and results are fairly robust.
this kind of benchmark is particularly susceptible to a narrow grader, where the input request is vague and therefore a significant range of SQL output would be valid, yet graded incorrect.