The company blog of Cube, an open-source semantic layer and analytics framework, covering headless BI, AI interfaces, and data tooling.
HN commenters recommend a semantic layer such as Cube for giving LLMs reliable context, and one team uses it between its warehouse and Superset.
Nope, those are the primary components of a semantic layer. Most other semantic layer products have three main components: input data sources -- typically SQL queries or table names, configuration -- the semantic layer describing all the things you mention above, relative to the input data sources, and the access layer -- usually a non-SQL API that consumers must use to consume data that has been modeled by the semantic layer. Check out the docs for Cube [1] for an example of this. Cube also has a SQL API, but it's not fully fleshed out yet.
What makes Malloy shine is that all 3 of these things are integrated in the same language, so users don't have to jump between different tools to model and explore their data. You can query/explore data in Malloy, iterate on functions to express your business logic, and immediately view the results. Doing this in something like Cube would require you to: (1) write SQL queries to prototype the function, (2) update the Cube configuration files with your changes, and (3) hit the REST API with a request to view results. In Malloy, it's all just writing and running Malloy queries.
[1]: https://cube.dev/docs/query-format
Found the term: https://cloud.google.com/terms/service-terms
> 7. Benchmarking. Customer may conduct benchmark tests of the Services (each a "Test"). Customer may only publicly disclose the results of such Tests if (a) the public disclosure includes all necessary information to replicate the Tests, and (b) Customer allows Google to conduct benchmark tests of Customer's publicly available products or services and publicly disclose the results of such tests. Notwithstanding the foregoing, Customer may not do either of the following on behalf of a hyperscale public cloud provider without Google's prior written consent: (i) conduct (directly or through a third party) any Test or (ii) disclose the results of any such Test.
It looks like that is less restrictive than it used to be. I found a blog post from last year mentioning an additional requirement to obtain Google's prior written consent prior to publishing (for all customers, not just fellow cloud providers) which no longer is included. https://cube.dev/blog/dewitt-clause-or-can-you-benchmark-a-database
From the article:
> There's a lot of information out there, including from myself about the history and rise [2022], comparing it to an MVC-like approach, or explaining its capabilities. That's why in this article I focus on the why and showcase how to use it in a practical example in the next chapter.
[1] https://www.ssp.sh/blog/rise-of-semantic-layer-metrics/ [2] https://cube.dev/blog/exploring-the-semantic-layer-through-the-lens-of-mvc [3] https://cube.dev/blog/universal-semantic-layer-capabilities-integrations-and-enterprise-benefits
My one line definition that I use atm:
> A semantic layer acts as an intermediary, translating complex data into understandable user business concepts. It bridges the gap between raw data in databases (such as sales data with various attributes) and actionable insights (such as revenue per store or popular brands). This layer helps business users access and interpret data using familiar terms without needing deep technical knowledge. https://www.ssp.sh/brain/semantic-layer#semantic-layer-definition
Edit: I'm the OP.
the short answer: use a semantic layer.
It's the cleanest way to give the right context and the best place to pull a human in the loop.
A human can validate and create all important metrics (e.g. what does "monthly active users" really mean) then an LLM can use that metric definition whenever asked for MAU.
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 last company wrote a post on this in 2021[2]. Looks like the acquirer stopped paying for the blog hosting, but the HN post is still up.
0 - https://www.definite.app/
1 - https://cube.dev/
2 - https://news.ycombinator.com/item?id=25930190
There is a new approach of using static site generators to make BI pages feel instantly responsive.
- Evidence.dev (https://evidence.dev/) - Observable Framework (https://observablehq.com/framework/)
I have found that both the speed and the frontend control either of these tools gives you is pretty good (with Evidence looking better out-of-the-box just in my personal opinion).
The main problem I always had with the embedded versions of Tableau, Looker, etc. is that they felt super canned (it was obvious it was a poorly/"lightly" white labeled solution -- when you see an embedded Tableau dashboard, you _know_ it is Tableau, etc) and they were slow.
More on this here: https://magis.substack.com/p/an-observation-on-dashboard-speed
PS -- I would add that the "headless" version of the above tools that I have seen is https://cube.dev/
Yeah, similar to what you and the other commenter from Definite said, we (Delphi)[0] find semantic layers way better for this kind of work than just going straight to a database/data warehouse.
One thing you really need with LLMs is consistency. Text-to-SQL kind of lets the LLM do whatever it wants - join tables that shouldn't be joined, define aggregates one way in one query and another way in the next.
Because semantic layers define how tables should join, measure definitions, etc., they mean people get consistent results from one query to the next, which builds trust in the LLM.
Cube (which was mentioned in another comment and has a great open-source semantic layer) has a good article about that here: https://cube.dev/blog/semantic-layer-the-backbone-of-ai-powered-data-experiences.
[0] https://delphihq.com