With a semantic layer, you get the added benefit of writing queries in JSON instead of raw SQL. LLM's are much more consistent at writing a small JSON vs. hundreds of lines of SQL.
We[0] use cube[1] for this. It's the best open source semantic layer, but there's a couple closed source options too.
My schema is - 90+ Tables, 2500+ Columns, well documented
From your experience, does Cube look a fit? My use cases will definitely have JOINS.
with that many tables, you might want to use Views: https://cube.dev/docs/reference/data-model/view
In my use case, it's going to be exposed to various kind of stakeholders and there will be versatility of user queries. I can't pre-create views/aggregations for all scenarios.
Another alternative here is Veezoo [0], which combines the semantic layer (a Knowledge Graph) and self-service analytics into one integrated solution.
We built it specifically for the analytics use-case for both the "data persona" to manage the semantic layer, as well as for the "business persona" to analyze the data.
If you’re looking for a semantic-layer + (embedded) BI solution right out of the box. This could be a fit.
We use a pattern where we ETL things into tables that model the upstream source closely, then use SQL Views to tighten up the model and integrate across data sources where needed. Keeping this all inside one DB allows us to use tools that understand the schema for autocomplete, etc.
I expect the developer experience would be significantly worse if we started writing views in YAML instead of SQL… but you’ve found the opposite?
A semantic layer is the best way to enable self-serve analytics, but if you don't care about it, it's probably not worth the hassle.
We also use the semantic layer for other nice things like setting goals, column descriptions and other metadata.
It solves 100% cases where some manager requests dashboard never to look at it again after one day.
> It solves 100% cases where some manager requests dashboard never to look at it again after one day.
Better than having engineers build it for days (for the same result).
For complex queries/use-cases, we generally instead push our users to create agents that assist them in shaping SQL directly, instead of going directly from text to result/graphs. Pushes them to think more about correctness while still saving them tone of time (the agent has access to the table schemas etc...), but not a good fit for non technical people of course.
The tricky part is correctly supporting multiple systems which each have their own specificity. All the way to Salesforce which is an entirely different beast in terms of query language. We're working on it right now and will likely follow-up with a blog post there :+1:
--- start quote ---
prompt engineering is nothing but an attempt to reverse-engineer a non-deterministic black box for which any of the parameters below are unknown:
- training set
- weights
- constraints on the model
- layers between you and the model that transform both your input and the model's output that can change at any time
- availability of compute for your specific query
- and definitely some more details I haven't thought of
https://dmitriid.com/prompting-llms-is-not-engineering
--- end quote ---
This is the crux. Sure, for high level software (e.g. Web apps), many parts of the system will feel like black boxes, but low-level software does not generally have this problem. Sure, sometimes you have to deal with a binary blob driver, but more often than not you're in control of or and to debug most all of the software running on your system.
> Building a bridge
There should NOT be significant unknowns when you're building a bridge, this is how people die. You turn those parameters into "knowns with high confidence", which is not something you can even begin to do for the LLM parameters described above.
In absolutely every way that matters and in all the details that don't matter.
> Do you know everything in advance about the composition of terrain, the traffic, the wind and the earthquakes?
No, and there are established procedures and ways to establish those facts.
"This magical incantation that I pretend works better because the US is asleep and more compute is available" is not such a procedure.
Cursed mission. These sorts of things do work amazingly well for toy problem domains. But, once you get into more complex business involving 4-way+ joins, things go sideways fast.
I think it might be possible to have a human in the loop during the SQL authoring phase, but there's no way you can do it clean without outside interaction in all cases.
95% correct might sound amazing at first, but it might as well be 0% in practice. You need to be perfectly correct when working with data in bulk with SQL operations.