Hacker News Reader

Comment by d_watt | Hacker News Reader

For an article about LLM+OLAP, it doesn't spend much time on that part. Specifically it seems like their strategy is around using an LLM to generate a DSL query for an unnamed semantic layer, then everything downstream of that is normal warehousing, with the semantic layer handling actual SQL creation.

I wish it spent time on talking about how they trained their LLM to reliably generate parsable queries for the semantic layer, and what the accuracy rate of what the user intended vs what they got.

I do think the only way a LLM based analytics tool can succeed is via a semantic layer rather than direct SQL, since database schemas fail to encode a lot of information about the data (EG a warehouse might not even know user.customer_id = customer.id).

Malloy could be an interesting target here.

View on HN

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-powe....

[0] https://delphihq.com

What is an example of a "semantic layer" in this context.

Cube (https://cube.dev) is a good one.

Others include AtScale[0], dbt's MetricFlow[1], Google's Looker[2] (also a BI tool but powered by a semantic layer), and Propel[3].

[0] https://atscale.com

[1] https://www.getdbt.com/product/semantic-layer

[2] https://cloud.google.com/blog/products/data-analytics/introd...

[3] https://www.propeldata.com

They're kind of an updated version of OLAP cubes if you're familiar with those.

Typically semantic layers sit on top of a data warehouse, let you define metrics using code or a UI, and provide APIs or SQL connectors so that you can query them.

Ibis could also be a target. It compiles queries written in python to multiple dataframe libraries, and SQL targets.

https://ibis-project.org/

It looks like https://github.com/tencentmusic/supersonic is a component. I'm trying to figure out what they are doing too.

Agreed, that's exactly what we're doing with Definite[0]. We spin up Cube[1] for all our customers and the results vs. directly generating SQL are much better. Cube has some other really nice out of the box features too (e.g. caching).

0 - https://www.definite.app/ 1 - https://cube.dev/

Is your SQL generation and cache layer open-source?

Eh, many of them have some way to provide markup even when its informational only, because a data catalog or dictionary is required to use most large olap products.

eg Snowflake lets you declare all the foreign keys you want, but does nothing with that info except let you use it.

Sure, some OLAP databases let you add the same metadata that a OLTP database gives you as constraints, especially enterprise ones. A lot still don't, like Clickhouse, afaik.

No OLAP database I know of would let you encode other semantic layer things like aggregations or metrics. EG defining a DAU/MAU metric as "The distinct number of users logged in that day vs the distinct number of users in the 28 days before that day."

Those types of definitions usually live in the semantic layer or bi layer, which a LLM analysis tool would need to solve for.

Reply on news.ycombinator.com