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