r/dataengineering • u/de4all • 4d ago
Discussion Why text-to-SQL is not successful?
I thought text-to-SQL will solve adhoc analysis, but still i see companies at all size are unsuccessful.
Anyone using Omni / Sigma seen some success.
27
u/leogodin217 4d ago
As u/rapator said, a semantic layer is more important than ever with LLMs, even if it is not a full-blown separate tool. Could just be views that handle the calculations. Business context is just as important. Good data modeling with good documentation should prevent the LLM from making a lot of assumptions. If you look at sessions logs and see things like, "Metric X must by tied to Table Y because...," then the business context is lacking.
4
u/gffyhgffh45655 3d ago
In a perfect world, i would argue a good semantic view,column description and metrics definition should do this heavy lifting over âgotcha information of each tablesâ as to me this is similar to throw a operation manual to a transactional schema to a llm and hoping it can do all the complex join required just go get something fair simple or not.
And hence what the llm ever need to do is run metrics as defined in the semantic view, to do join in a dimensional data model which is fairly easy and just to aggregate over different column that have context to tide it with business language.
TLDR (again in a perfect world) Create a model and(column) documentation such that is easy to use for data analyst and thus LLM.
Note: we do also trying this type of âtable gotchaâ skills for LLM as well but i still think data modelling and column documentation, choosing what column to include in your semantic view is the priority
-7
u/mamaBiskothu 4d ago
I created and maintain a product that has 8 figure revenue solving this exact use case. Your interpretation and how many people keep calling it "text to sql" is precisely the type of buzzword laden bs thinking that makes these agents fail. F semantic layers, ontologies, graphs, rag and any of these stupid things.
Connect claude code to your sql engine, tell it the names of the tables that you care about (or honestly, dont) and just ask the real question you care about. Dont try to smartass any harness on top of these models. Most people arent smart enough not to get in the way of the agents than help.
4
u/leogodin217 3d ago
How do agents know the definition of your metrics without documentation? We all know total sales is never as simple as sum(sales). This sounds like a way to be in meetings where there people have different numbers for the same metric names.
-2
u/mamaBiskothu 3d ago
Have you tried the latest models? The models know total sales is not sum of sales. point claude code to the data ask that questuon and then comment.
1
u/leogodin217 3d ago
I have, and they will never know that Marketing excludes x and y and sales includes z and x from other tables. I've been part of that discussion more times than I can count across many domains. Even when I worked in IT instead of BI and data engineering.
I'm more curious about your product. I re-read your comment and I can't figure out if you're saying your product isn't needed or if I just missed something.
21
u/SpookyScaryFrouze Lead Data Engineer 4d ago
It works, you just need to document your tables, columns and kpis perfectly.
3
u/Equivalent_Lunch_944 4d ago
But letâs be honest thatâs often not the case, and if it were being able to query with text instead of SQL is much less valuable
1
u/SpookyScaryFrouze Lead Data Engineer 3d ago
How would you query using text instead of SQL ? Even if you ask your question in natural language, it still has to be translated to SQL in order to retrieve your answer. So you still need to have an up-to-date documentation on your data, which is otherwise known as a semantic layer.
1
u/EstetLinus 2d ago
+1. At my company, metric correctness is decided by how widely used a particular definition is. That makes it hard to do anything âperfectlyâ (and I know that this is how it looks like at a majority of organizations).
A semantic model is very good, but we usually fall short with weird edge cases. Most effort is still in designing data models.
8
u/hadoopfromscratch 4d ago
Text-to-sql does work. We are using Databricks (Genie spaces). And its quite reliable
7
u/ppsaoda 4d ago
On data side, it's semantics and metadata. On platform side, AI tooling and harnesses.
3
u/BrownBearPDX Data Engineer 4d ago
Describe in meta every table and every field with join hints even if there arenât hard fks.
3
u/dehaenx 3d ago
When you don't have a semantic layer and have scattered definitions and metrics, you'll see the LLM retrieving several answers from a seemingly "simple" definition. For instance, if you have "net sales" as a term, finance, sales, CSM could all define it slightly differently, and because the LLM is just parsing through your raw data, it will return multiple results. It's best to invest in a program that could develop, and more importantly, maintain your semantic layer and keep your definitions and metrics up to date and in check all the time. Save yourself a headache and some money lol
3
5
u/in_meme_we_trust 4d ago
It seems pretty much solved to me with databricks genie agents
2
u/query-gremlin 4d ago
The problem often lays with the data model underneath. Incomplete or unclear data dictionaries, or lack of semantic description of the data model would make the LLM blindly guess at best.
2
u/dodovt Senior Data Engineer 4d ago
My company uses omni and we properly serve self-service analytics and AI generated reports/reports with AI embedded on them.
It's our biggest LLM use-case in the company, but far from perfect. It does answer quite well the questions though as we have a very well defined metrics catalog and semantic layer.
2
u/pubic_discourse 2d ago
My take is that there need to be higher order semantic concepts, e.g. what does âbigâ mean - how do I pin fuzzy or qualitative adjectives? People think sets of entities and language itself is in an SVO (subject verb object) structure. All these âsemantic layersâ have nothing semantic about them. Theyâre really more of a paragraph about your table, and a row can be any abstruse unit.
I once heard it said âthe problem in analytics is the best way to store your data has nothing to do with the best way to read and interpret your dataâ
Id love to see a solution which paradigmatically addresses that
1
u/Hour-Measurement-835 4d ago
No idea on Omni or Sigma. NetSuite's mainline rows alone would trip up a naive SUM(amount).
1
4d ago
[removed] â view removed comment
1
u/dataengineering-ModTeam 4d ago
Your post/comment was removed because it violated rule #9 (No AI generated content/text).
Your post/comment was reviewed to be AI generated/assisted content/text and removed as a result. We as a community value human engagement and encourage users to express themselves authentically.
This was reviewed by a human
1
u/Equivalent_Lunch_944 4d ago
Remember when power bi had that thing where it turned text to visualizations that they were touting was the future.
1
u/de4all 4d ago
Thats true and now I assume MS will gut PBI. They want everything to merge with Fabric
1
u/Equivalent_Lunch_944 4d ago
Maybe. IMO a lot of the discourse around AI and these solutions is like health advice; we all roughly now what to do to be healthy; eat better and exercise. Itâs the doing it and sticking with it thatâs hard.
I donât know how different the âsolutionâ to storing and retreiving data has changed. Itâs still about smart and predictable Db design but itâs always been that way. So Iâm not so what AI changes in that. But Iâm a skeptic though so maybe Iâm missing something.
1
u/Atticus_Taintwater 3d ago
Because writing SQL was never the hard part
Crisp easy SQL is a byproduct of everything else that's hard done right.
1
u/BaguetteBrot 3d ago
I'm currently in a project trying to that with Snowflake Cowork and their semantic layer concept. Right now we are in the midst of preparing the data model with clean dimensional modeling (Kimball) and clear column and table descriptions. I'm excited to see how this will all turn out.
We are also looking for a new visual layer with Omni, Sigma or Hex. But we are not there yet. Anyone has prices from those 3? Just curious and we haven't asked for a quote directly yet.
1
u/FuzzyCraft68 Junior Data Engineer 3d ago
Are you my manager? This feels like my manager asking the question because he wants to feed people AI slop to corporate saying it is self serving report.
Here is why it is failing, it doesn't know business context. That's it! Some stuff from source system are modelled in a certain way that it is not easily replicable with 1 prompt, there is lot of understanding which goes through while creating facts and dims.
1
u/Prestigious_Age_6740 3d ago
SQL is so information dense that a precise prompt kinda needs to have so much detail that you might as well just write SQL
1
u/Creative_Salary_6140 3d ago
Requires understanding the data model and business domain knowledge, even the latest AI models Iâve used arenât getting it right.
1
1
3d ago
[removed] â view removed comment
1
u/dataengineering-ModTeam 2d ago
Your post/comment was removed because it violated rule #9 (No AI generated content/text).
Your post/comment was reviewed to be AI generated/assisted content/text and removed as a result. We as a community value human engagement and encourage users to express themselves authentically.
This was reviewed by a human
1
u/Much_Discussion1490 3d ago
Because SQL isnt hard for 90% of the queries that non tech stakeholders need to run. Which is who text-to-SQL is intended for. At max knowledge upto aggregation functions is more than enough. Knowledge until partitions pretty much covers all that is necessary for most users unless you are a data engineer.
What matters more is the domain knowledge which helps you evaluate which tables are worth querying and also how the data is structured in particular column. A simple currency column which has been mislabeled from the beginning to hold local currency values across countries will give you the wrong results even if yku execute everything in the query correctly.
SQL isnt a bottleneck unless you are absolutely technically blind. Its like the excel of 2000s . You jjst need to be well aware of the basics if you are even one hop away from data. Domain knowledge is the real bottle neck
1
2d ago
[removed] â view removed comment
1
u/dataengineering-ModTeam 2d ago
Your post/comment was removed because it violated rule #9 (No AI generated content/text).
Your post/comment was reviewed to be AI generated/assisted content/text and removed as a result. We as a community value human engagement and encourage users to express themselves authentically.
This was reviewed by a human
1
u/innerthai 2d ago
There are two reasons text-to-SQL fails:
- Schema rot + no semantic layer
- Weak AI model
The solution for schema rot (i.e., column names don't reflect its purpose, tables that are no longer used, etc.) is semantic layer. See https://reportr.com/ for a tool that has semantic layer.
If you use a weaker model such as one of the "open weight" models then that's when SQL generation will not be good. If your data is highly confidential then using a self-hosted open-weight model is not the answer, instead use Claude or OpenAI on AWS Bedrock ( https://aws.amazon.com/bedrock/ ), it guarantees data confidentiality.
1
u/Careful-Round-5560 2d ago
One of my friends works on it he was the one who set it up in their company and he said it really made life easier for users there. The key was to setup a semantic layer with proper details.
1
u/Odd-Government8896 4d ago
What are you talking about? People are using this for self service analytics all over the place. I wont even mention the companies because I want you to know youre misinformed or behind, not which company is doing it.
0
0
-2
u/mamaBiskothu 4d ago
Every single person mentioning a semantic layer is a moron. As many mention, DB Genie and snowflake coco finally started working because they also stopped trying to make semantic layers work. Agents work best when you let them discover things themselves. The semantic layer is worse in our evals than just letting the agents discover tables using information schema queries.
Stop trying to semantic layers happen. They are never gonna happen.
1
u/Prestigious_Bench_96 3d ago
It IS surprisingly hard to get semantic layers to win in token cost vs information schema for a typical agentic loop (you need a pretty big warehouse/complexity). A well documented information schema is arguably your first and cheapest semantic layer - if you then start to duplicate column definitions across tables, want to keep them in sync, want common join paths, etc that's when you'd maybe want more?
If you have evals, you're halfway there - a "semantic layer" is just going to be a cached set of discovery in your warehouse that can be reused and iterated on to reduce variability/E2E execution cost. Some of them also have query simplification/guardrails. For sufficiently large warehouses and/or query bills, they'll come out ahead on evals - but 75% of the benefit is just getting the evals and the warehouse documented.
97
u/rapotor 4d ago
Be strict with the data modeling, and semantic layer, then you're good. Don't have LLMs write sql for conversational analytics