Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Any text-to-SQL benchmark should address difficulties of real-world data stores

cacm.acm.org

Post reply on HN
Print view View original

11–20 of 24 posts

Re: Any text-to-SQL benchmark should address difficulties of real-world data stores

#11

Post by mrtimo »

programmertote wrote: post #3

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…

you should checkout https://github.com/malloydata/malloyyo - it's great at what you trying to do.

Re: Any text-to-SQL benchmark should address difficulties of real-world data stores

#12

Post by brotchie »

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.

Re: Any text-to-SQL benchmark should address difficulties of real-world data stores

#13

Post by Diogenesian »

semiquaver wrote: post #4

> It is widely known that an LLM can only find data it has seen before. What does this mean? It’s obviously not literally true, so what is the author trying to convey?

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.

Re: Any text-to-SQL benchmark should address difficulties of real-world data stores

#14

Post by tqi »

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.

Re: Any text-to-SQL benchmark should address difficulties of real-world data stores

#15

Post by nchammas »

fivetenpen wrote: post #6

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 l…

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.

Re: Any text-to-SQL benchmark should address difficulties of real-world data stores

#18

Post by bob1029 »

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.

Re: Any text-to-SQL benchmark should address difficulties of real-world data stores

#20

Post by efromvt »

nchammas wrote: post #15
fivetenpen wrote: post #6

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 l…

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!
Post reply on HN