Semantic Layers: Why LLMs Need Them for Accurate Queries

0:00that's our focus is like to make semantic models build themselves you have to have the skills where you know enough about the data in your Lakehouse um you know enough about the business like what is how do you define customer turn to be able to Define that semantic model businesses are complicated the semantic modeling language has to be complete enough to be

0:22able to define those semantics all right folks so today we are with Dave and Dave is the CTO of AD scale thanks Dave hi Quenton thanks for having me yeah no you're welcome so I think it's your second time here with us H you had a call with youf talking about that

0:44scale and what you guys are doing uh and today we want to focus a bit more on the new project that you have but maybe before we go there um yeah can you can you give us an update for those who don't know at scale and what you folks are doing yeah so uh at scale we sell a universal semantic layer so semantic layers have been around for a long time but they've always been tightly coupled

1:07with the bi tool so you know you create a semantic layer for that bi tool well there's a lot of different ways of consuming analytics today including you know not in just bi tools but in applications and data science and now in geni um with tools like Genie while you're doing text to SQL and what a semantic layer does is it's a business friendly representation of the tables in

1:31your Lakehouse so uh so things like Revenue gross margin product customer sort of those concepts are are what's exposed to the consumer and so the consumer can deal with very business-friendly terms and then the at scale semantic layer turns that into datab brick SQL and then does all the nastiness of finding the right tables

1:54doing the right calculations doing the right joins so that it so you always get the same correct data every time without having to be a database programmer so that's what a semantic layer is and uh and last time Quenton we announced uh a the community edition of atale so anybody can use at scale for free um and we now announcing or just

2:19just released our semantic modeling language or SML is now open source so we took our yaml for defining a semantic model that makes all this work and we put it in the public domain so that we can establish a standard for describing uh semantics um for businesses nice now

2:42that's great so so wait so let's say you have for example let's say uh customer churn and then you have the tables where you could find the churn information so the semantic layer is going to kind of translate the churn and say okay if you want a compute churn that's where you have to find information is that is that the ID yeah so the semantic model would be where you

3:06would Define churn as a calculation and that calculation may may be made up of uh uh data fields from multiple different tables and and those tables have obviously relationships with each other but when somebody asks for churn they're not just asking for gross churn right they want to see well what's my churn by uh this this this customer and

3:28this type of customer in this region over this time period compared to last year um and uh and and so in by gender uh you know by uh demographic uh by income level all those things are different dimensions and so if you think about it that churn calculation needs to be calculated across all that

3:51dimensionality so that people can basically slice and dice and and and ask a question about churn in almost unlimited sort of possibilities in terms of unlimited ways okay that makes sense and what so you you mentioned you have a new open source project to get the semantic layer properly what so I like I feel a bit of

4:15shame but I don't know I don't know the ecosystem really well like what's what's out there currently do we like what are people using outside of at scale I mean do do we have any any major other open source project or is it like brand new and nobody has that so you know so when it comes to um semantic layers so we were the first to start it so I founded the company 11 years ago and this whole

4:37idea of a universal semantic layer was novel um again because semantics semantic layers have always been embedded in in in bi tools or they've been embedded in databases and so we're the first to say let's keep it separate well 11 years later so um there's there's now other companies like powerbi has their semantic layers in

5:01powerbi um uh Google has their semantic layer in looker um and and then there's DBT announced their semantic layer um um about about a few years ago so there's now several sort of different options for semantic layer platforms including ATS scale and so what we want to do is we wanted to um is to allow all these

5:23different semantic uh layer platforms to interoperate um and we also wanted to provide the ability of taking those embedded semantic layers like in business objects and micr strategy and cognos and be able to translate them into this modern view of a universal semantic layer so so that so Quon it

5:48just there there was no standard for semantic modeling languages and so what we've done since we've been around for 11 years we sort of have a suet of all the functionality out there of all these different platforms so we decided that we're going to you know we're going to make this happen and we'll introduce a spec for how you define a semantic

6:11model that will incorporate basically everything that that you might want to do and then we'll create a community and a working group which is what we're working on now to get all these vendors to work together so that they can support that standard yeah looks looks super exciting like you should definitely try to work with the catalog Community see if you can somehow plug that within Unity

6:33catalog so that it becomes the like even more of a standard would be that would be amazing so so it's yeah let's talk about that Quinn because that's a good point so you know um at your at your last conference you announced uh Unity catalog metrics um so uh so even at Databricks and then snowflake has a similar thing that they're doing with cortext

6:55analyst they have a semantic semantic file definition so everybody's so now the it's it's clear that to make geni work you need to sort of cross that boundary from the general internet to a company's own terminology and definitions because the you know an llm is trained on the general internet right it doesn't know what uh you know what um

7:20uh Home Depot uh uh the definition of a a gross margin means that's that's that's particular to that company's um uh data so the sematic layer bridges that Gap so uh so so that's really you know that's that's really important is to bridge bridge that Gap yeah and I think yeah I think you're right

7:43especially now with all the llms I feel like it's getting really key like used to be I feel like semantic layers used to be something you know everybody would talk maybe five years ago but nobody would really take it seriously um some way but now now with the llm because W it's going to power really the like how D LM is going to answer and understand the data like it's getting more and more

8:05important um yeah and and so you have to so and here's the thing right it's like to build a semantic layer is really hard it's hard because you need to the semantic modeling language needs to be expressive enough to basically encode all the um um the details of the

8:27business um and and so and and businesses are comp complicated so you know we just we're talking about you know we're talking about uh churn customer churn and gross margin those are in those are those aren't trivial calculations for any any kind of Enterprise company so the thing is like it's um the semantic modeling language has to be complete enough to be

8:50able to define those semantics and more importantly it's not just the metadata of that it's also how you get the answer so what a lot of people don't understand uh Quinton which I want to really make a point of is that the semantic modeling language is there so that you can define a semantic model for your business but you've got to couple it with a semantic

9:12engine that will go and produce the right cql um based on that model um because we know that if there's you know that most questions are are are more than five tables are are needed to answer that question and we know that an llm trying to guess what those relationships are without the

9:35semantic model to guide them is nearly impossible to get the right answer and to get the right answer every time because if a business user asks a question and says what's my gross margin and what's my customer turn you know over this period compared to last year and they answer the second time and it's a different answer yeah for sure you want consistent that's it and that's

9:58like it's they'll never use that interface again yeah like if your like if your dashboard changes every quarter just because the query change it would be like who you be like what the heck's going on right so so how does one company create the semantic lers because it seems like you have a mix of really business level information which is hard

10:20to get when you are you know like data engineer but at the same time you need to be you know knowledgeable about the tables and all the transformation and the SQL queries so what's the what's the strategy here how how do you build one like do you ask business user to write down what they need what's how do you do that yeah so so that's a great question because you're asking about okay who builds that semantic model um because

10:43it's a you have to have the skills where you know enough about the data in your Lakehouse um you know enough about the business like what is how do you define customer turn to be able to Define that semantic model um so typically that Persona looks like the person who's building the dashboards in a powerbi or

11:05a tableau and when you think about it when they're talking those guys understand those people understand that they're tied directly into the data platform and they're modeling in that particular workbook or in that particular dashboard they're building the model so that they they can have that calculation and put it in some kind of report or some or calculation so that

11:29same Persona we're just asking him don't do it in the bi tool do it in the semantic layer platform do it once and then use it across everything so powerbi Tableau Excel um uh uh thought spot looker whatever uh your Jupiter notebook uh your application where you embedded

11:53those calculations into those applications with your own visualization or your own sort of um workflow flow it's always going to be the same um and so you build the semantic model once and you use many man I I really wish you managed to find a way to make it like a stand out that everybody use and you put that into

12:15UC so that you just create all the information once and then boom you have it everywhere I guess so we're we're working with it so stay tuned Quenton so we're working with your team um to uh to have the at scale semantic modeling language SML have that be a native part of UC metrics so stay tuned there we're gonna

12:38um we're you know we're we're going to be doing a really interesting um um integration so that anybody who has built an SML model um Can can couple that with UC metrics and a Lakehouse and you get all that goodness for free yeah wow all right right so let's let's go maybe into little bit more details can

13:00you like what do you have in the EML exactly do you define the tables and then SQL queries and and just words describing the like the output what how does it work yeah would you um uh let me share something I maybe maybe take you through it yeah okay so let's let's go ahead and share

13:22screen uh and start here with uh the open source repository so uh what you're seeing here is uh semantic data layer is the organization and SML or semantic modeling language is the is the repository so what so what you see here is um you can see a definition of what SML is um and then in the repository we

13:48basically have the language specification we also include pre-built semantic models um so you can just plug and play and go to town um we also have helper classes that we adding soon so that you can it will help you do um serialize and uh and deserialize uh SML and then this is really interesting

14:11we're we are publishing semantic translators so this is what I was talking about before Quinton where you can go from um a legacy embedded semantic model that's in cognos or business objects or micro strategy for example or even looker and then turn it into SML so you can make it a generic semantic model to be used uh across

14:35different tooling oh so it me that so if you have the semantic layer on powerbi then you would be able to kind of dump it with the translator and and sa exactly exactly precisely right and then what it will do is it will take that in that case powerbi so powerbi um is one of our first translators um that we'll be adding to

14:58the repository we actually have it in um in trial now and what it will do is it will create for example this is an example of an SML model so this is called internet sales you can see the object type is a model um you can see a label and then you can see that there's relationships that are set between a fact table called fact internet sales

15:21and a date Dimension and then the date Dimension has a role play um called so it's an order date dimension and then you have some other just straight dimensions and then here's a couple metrics so that's the ultimate and very very simple simple model um and then what you'll see here is you'll see the object hierarchy so there's lots of documentation if you want to learn about

15:44just what goes into building a semantic model so there's the sort of the uh the ER diagram and then we have for each object type so these are the key objects like a dimension is a key semantic uh SML object um and so here's an example of what a dimension object would look like uh you can see this is the

16:07store Dimension definition you can see we have levels and hierarchies defined um and then you can see the ER diagram of what a dimension looks like a dimension is probably the most complex SML object because um a dimension has all kinds of things like relationships um it has attributes

16:29um those attributes can be primary or secondary it has hierarchies hierarchies have levels um and hierarchies can also have you can also tie metrical attributes to a hierarchy so you can see it's uh and then here's the definition of all the different properties that you see in in those um in in that particular

16:53object also what you see here is you see our model Library so we have some very common models including Adventure works so this is the standard uh ssas SQL Server analysis Services model in addition to tpcs and tpch so in you know in data brecks right

17:15you ship with a TP tpch data so you can just go ahead and pull up the tpch model this is what it looks like visually um and here it is in the repository here's the model objects including all the metrics um and the metrics definitions and uh it's all here

17:37ready to Plug and Play So how do you see these how do you use this well you can go ahead and load up um uh and get a a free version of the ATS scale semantic layer platform that is obviously s SML compatible by going to ATS scale.com clicking on community and and you can

17:59download the Developer Edition for free and what you'll get is you'll get um you'll get what we call Design Center you'll get an IDE like you see here here's a particular model I'm looking at visually uh and that model also I can see the code behind that model so you can work either in code or you can work

18:22with dragon drop visually to create and look at these semantic models and all you have to do is just add in the repository reference that you see right here in the open source reference so I just add that in and I instantly get uh a uh get a a particular model so uh so and so it's really easy for example to

18:46uh get my uh my adventure Works model that you see here um is this is what it would look like uh once you load it into um into at scale Design Center so there's there's the model so that's everything in adventure works that is basically the base model for uh you know for ssas that's the training model so

19:10this is what the user sees over here here's the different metrics in different folders uh here's the dimensions like here's my different date Dimensions um there's my different uh here's my customer data with all the demographics so everything is done and ready to go so in

19:32a I don't know maybe let's say in one or two years once we will have all the LMS being you know super super smart like how do you see the future like you just have data analyst going here and just adding facts and information on the model and then you load everything to the to the actual engine which is going to understand the like the semantic layer and then then

19:55you just answer the question so you you don't even have to write any any SQL not not even I guess have to build any dashboard anymore well I guess we'll see that well you know so with text the SQL right and with Genie you can just ask a question you don't need to drag and drop and to build a dashboard because you can just ask for it what you want and what I where I'm excited to see those that that interface

20:20exposed through um through the messaging platforms like a slack or a Microsoft teams so really anybody who has access to those to those platforms which is everybody in the company yeah can now run analytics um I think that's really powerful when you basically you know to today I'm very proud that we have an Excel interface that lets anybody who is

20:44an Excel jockey be able to do analytics on the datab bricks Lakehouse um just imagine we can take that and take it a big step forward to say you don't even have to understand or have Excel you can just ask questions through slack or through Microsoft teams and it's and it's I mean it's so interesting that if you think about it just a few years ago like all the kind

21:08of value was on how do you have folks in your team who know how to write SQL queries to be able to answer my question and now it feels like it's it's like shifting into more value discussion where you know the key part will be do you have folks who can explain what you have in your you know business metrics and how you map that to your actual data and that's it and then you can just have everybody using it it's like it's

21:31interesting interesting switch so you know Quenton with SML is for the first time we have the ability to have a semantic model in code um which means that it's in since it is just code it's just yaml code we can now extend our data pipeline work we can extend our ETL to actually create the semantic objects through code so I want

21:55to I I Envision a day where you don't need the human or maybe you can use the human for that last mile of assembling the building blocks but the ETL pipeline spits out the semantic objects that allows that business analyst just to do that final sort of composition yeah of their semantic model without having

22:17to understand where those objects came from or how they were defined that's so interesting so the I feel like to be honest I used to think that semantic layer was kind of you know know a little bit a few years ago I was like what is that again you know but now now I'm like okay makes sense like the the new llm stuff really makes sense to me yeah it's I don't know I just don't

22:40know how you can do it without that translation I mean you can do it and you can do it once and maybe you'll get lucky and get it right um but you know we' done some research so as uh want aada from data. World all it takes is four or more tables to join and things things go uh you get 0% accuracy so it's

23:02it's it's someday Ai and ml alone will hopefully solve this problem um but for now um having that that human touch to sort of give the llm not just you know hints to be able to allow it to do its work um is really critical I think oh yeah that's for sure and I see it in all the like in

23:26jny on the air bricks but honestly anywhere if you start asking basic question like hey you know do the average on a given field theur it's going to work but as soon as you go and say you know hey you know get me ins sight of you know the churn like how do you know the churn there is no way you can know that so you always have to add extra extra layer yeah um and and the you know the one thing I think that back to like

23:49the semantic engine for a second so what happens with the semantic layer is that you have the logical The Logical query comes in and the llm and Genie can do that right it can generate that logical query that says show me um customer churn you know by uh C by by gender by

24:11location this year versus last year so that's now a that's that's now a query that is um you know one metric in like four different dimensions now what the semantic engine will do is it will take that and you have a your semantic model if it's not just built on customer your semantic models also got product as a

24:34entity it has obviously time as an entity um it has orders as an entity um and so on and so forth right and so what what we do at least in our semantic engine is we build a graph and so that graph based on all the where the nodes in that graph are all the different um combinations of that

24:57dimensionality along with all the metrics and so when somebody asks that question we don't produce a query that joins in uh for example orders and um you know and product because you didn't ask for that you just said what's my customer turned this year versus last year you know by gender so uh so we don't have to join in

25:22product so what our Optimizer will do is it will prune away all the unne necessary nodes and it will create the correct query that only references the tables to calculate churn customer turn and the customer entity along with time because you asked this year versus last year so that's how we're able to

25:44actually compute that query that becomes a datab brakes SQL query and that path is going to be the same every time so do do you see like what what's going to be the next step um because you have the semantic layer and then it feels like so you have

26:07so I understand you have your engine Computing all the um like kind of analyzing and creating the queries knowing which table to Curry but if you think about the world where everything is going to be llm based are we like it's like you have an extra step to go from the layer that you have in y into some instruction to send

26:30to the llm to actually do the magic do you think that's going to be at some point also some kind of open S spec that they really use and train and Benchmark using the same kind of data sets it is it is it crazy um you know look I think that um it's that there needs to be that at least for the time being until we can solve calculate the customer turn

26:54through Ai and ML and do that reliably we're going to still need that translation layer and so what we're really focused on and I'm really focused on building that semantic layer as much using AI so can I show you something real quick let me show you one other thing so um this is this is this is the future we

27:16call it we call it oneclick modeling okay so what you see here is it you see an existing model called internet sales and it's made up of it's a pretty complicated model it looks simple but if I look at this customer Dimension here and I just double click on it you can see that my customer Dimension is actually itself a model that's made up

27:38of three entities another table and Databricks as well as two other dimensions if I drill through now you see the breadcrumbs now I'm on the geography Dimension and the geography Dimension has two different hierarchies rolling up city state country uh ZIP and zip coding country so two different ways are rolling it up and you can see that that

28:00model is basically built on these building blocks so the customer Dimension is made up of a geography Dimension and a gender Dimension the internet sales model is made up the customer Dimension which pulls all that in so now that we have a library and this is all just in a repository so this

28:22is all just in GitHub and and I'm working directly off GitHub in my case so now what I can do is I can go in and go into my Databricks uh my Databricks data source here so I'm going into my data sources and I'm I'm going into my um my Lakehouse and so so now we have these semantic objects already defined

28:45this is this Unity catalog here and I can go and I can say you know what let's go and let's look at uh and build a new model off of this this new internet sales data and I can do that with one single click just by saying generate new model so what it does right is it actually builds all that model out

29:09and there it goes it did it all behind the scenes and now if I go and I look at this my new model yaml definition it's I have my new model that's been completely defined and defined you know using AI so uh so so that's really the the key here is that once you have a library uh

29:31Quenton of existing semantic objects which have relationships and those relationships and to other other uh entities or table tables we can then use AI to automatically build new semantic models and do that consistently reusing all the semantic objects that already have been

29:54defined and are included in your GitHub repository so that's our focus is like to make semantic models build themselves let the user that semantic modeler give them as many tools using Ai and ml as possible so that they still at

30:17the end of the day need to approve it but let's let the machine do the heavy lifting yeah I love it all right um so let let's um well let's I feel like we should do a recap like of what people can get where um so you have the you have the open source project which is the like the

30:42spec of the sem de clearer can we can we say that it's it's the spec as well as pre-built models based on SML so you can look at the spec but more importantly you can go and load those pre-built models and you can you can load them into the free community Developer Edition of the at scale semantic layer platform and do what I just did yourself

31:06end to endend you can then connect it up to your bi tools like Excel and powerbi and Tableau and query it you can see just how it works you can see an inbound logical query and you can see the outbound query going to Databricks uh your datab bricks um Lakehouse cluster um and you can see it all working end to end uh so it's

31:28um you can hook it up to a jupyter notebook and use our python interface you can use our MDX interface Excel you can use our Dax interface with uh Power VII you can use our SQL interface with Tableau or any other tool so all those endpoints are available to you so you can pretty much do anything you want um and build applications around your

31:52semantic layer all right awesome well do you have one last uh I don't know one less comments uh one less thing you want to say before we close it um so I guess the only thing I'll say in addition to what was already said is that um you know when it when we started the company 11 years ago really the sort of primary use case for a semantic

32:15layer was Data democratization it was basically allowing people to get easier access to business-friendly data rather than requiring them to be a database developer um I think now with the Advent of chat gbt and now llms I think the the real use

32:37case going forward in the future is not just democratization and making access to data easier it's actually changing the Paradigm of how you do analytics to allow you to do text to SQL using an llm but using the semantic layer to bring business context for your business to allow it to work with this really super

32:59powerful new technology so I think that that's the killer use case for a semantic layer is you're investing in semantic models um those semantic models will pay dividends whenever you're doing AI um and machine learning not just in consum consuming it with with bi tools but actually using geni uh to do and

33:22build real machine learning models that work with your business yeah in that case I 100% agree and I think it's kind of underrated I like people underestimate how key having all the semantic layer being properly done really is the secret magic to make all the like the end use cases work like all the llm all the AI and all the Tex to

33:44SQL stuff and stay tuned because you'll be able to see that integration right through your Databricks console you'll be able to actually run queries against the outscale semantic layer right through uh Genie so be the look out for that all right exciting well thank you so much Dave it was amazing to have you and I hope we will um we'll host you again soon so

34:08that you can share all the updates and all the integration you have thanks quen and thanks everybody for listening