Semantics Layer for LLM Text-to-SQL Applications

[louismagowan]

Hi there!

My company are starting to look into developing some internal text-to-sql bots/applications to enable more self-serve analytics and reduce the amount of small requests that our Business Intelligence/Analytics team receive. Semantic layers have been shown to greatly improve the accuracy of text-to-sql queries.

Has anyone used SQLMesh's semantics layer for this purpose? Can anyone recommend any other semantics layer tools that worked well for them in this setup? DBT semantics layer is paid (and far too pricey).

Thanks!

[StuffbyYuki]

@louismagowan SQLMesh's metrics layer is an experimental feature I believe. Cube.dev has open source version. And nowadays I believe you can make your own yaml files and feed them into AI apps, if they are the only things you need

[cmgoffena13]

@louismagowan - I have an idea of utilizing SQLMesh's metadata to create an integration for Cube. I think it could be really beneficial and provide good synergy. Just needs more visibility and support. You can see more here: #5997

[mday-io]

We use cube, too, but we should prioritize Ossie for development @cmgoffena13

[cmgoffena13]

Oh what the, just found this: https://www.snowflake.com/en/blog/apache-ossie-open-semantic-interchange-incubator/ -- this stuff is moving too fast for me lol -- do you want to comment on the RFC? I can adapt it. Obviously better to support semantic layers in general than one product

[allenjiji]

@louismagowan late reply, but since you asked for recommendations, try 0sql (disclosure: I work on it). It is a semantic layer built for exactly the self-serve bot use case, and it is free during early access.

The difference from writing your own YAML and feeding it to an LLM is that the LLM never writes SQL. You describe your SQLMesh models once in YAML, including the tables, how they join, and which measures to use and how to aggregate them. The bot then sends a spec like:

{
  "projections": [ { "field": "region" }, { "field": "net revenue" } ],
  "filters": [ { "field": "order date", "predicate": "greater_than_or_equal_to", "value": "30d" } ]
}

It gets back a single SQL statement for your warehouse, with the joins and measure definitions already in place. If a field doesn't exist, the planner returns a refusal with the nearest valid fields instead of letting the bot invent a column.

0sql never connects to your warehouse, so your application executes the SQL. There is also an MCP endpoint if the bot is an agent.

SQLMesh builds and tests the tables; 0sql plans queries on top of them.

Try it free: https://0sql.io/