[mritchie712]

text-to-sql is a dead end. There's no way for a model to correctly interpret the meaning of every column in a real world database using the `information_schema` alone. Most cloud warehouses (e.g. Snowflake) don't use foreign keys, so you don't even know the joins.

Imagine you hire a highly skilled data analyst (e.g. 9 out of 10 proficiency in SQL) and start asking them questions about your database. They won't answer them, they'll ask you more questions. The conversation would go something like:

you: what is our churn rate by channel?

new analyst: where do we store "channel"? what do we use to process payments? where is that data stored? do we include discounts in MRR / churn? etc.

If a human can't do it, an LLM can't either. An LLM isn't able to write the SQL from scratch get the right answers without a ton of additional context. We're working on an approach using a semantic layer at https://www.definite.app/ if you're interested in this sort of thing.

[toddmorey]

Agreed, but perhaps more semantic meaning could be expressed in metadata for tables and columns, extending beyond what's typically found in information_schema. (This may be the semantic layer you are talking about.)

Here it seems MQL isn't a query language as much as it's a text-to-SQL translator and you're right... without a bit more understanding of the data's role and purpose and intent it's a hard job for anyone, human or AI.

It strikes me that as I write an sql statement I'm not only using knowledge of sql but also knowledge of domain and database structure that I don't even think about until I need to show someone else how to do the query.

[dragonwriter]

> There's no way for a model to correctly interpret the meaning of every column in a real world database using the `information_schema` alone.

Why would text-to-sql be limited to information_schema alone? Human analysts would use additional documentation, why wouldn't an LLM-based text-to-sql system?

[mritchie712]

I should have clarified. There's a large number of apps that are:

1. taking info strictly from SQL (e.g. information_schema, query history)

2. taking a user input / question

3. writing SQL to answer that question

An app like this is what I call "text-to-sql". Totally agree a better system would pull in additional documentation (which is what we're doing), but I'd no longer consider it "text-to-sql". In our case, we're not even directly writing SQL, but rather generating semantic layer queries (i.e. https://cube.dev/).

[boredemployee]

Yes. And also, don't forget that different stakeholders ask in different ways, using different words, which turns out the situation in a nightmare. But I think it's possible to make it to work with mid-size databases.

[tosh]

providing some context about the data, the schema + samples from the entries works quite well, definitely room for improvement but already quite usable imho

[mritchie712]

Agreed, very usable if you know SQL and iterate from whatever the LLM spits out.

[sjtly16]

agree, with familiarity with SQL one can use it as a reference for generating the first draft or even the final query