# Building Blocks: Advanced Semantic Data Model Layering

**Channel:** AI Council
**Source:** https://www.youtube.com/watch?v=DZkXvdzYlVs
**Transcript page:** https://www.withtranscript.ai/video/DZkXvdzYlVs

## Chapters

- 0:00 — Introduction and Semantic Modeling Basics
- 4:08 — Why Semantic Layers and Gen1 Overview
- 10:02 — Next-Generation Malloy Query Language Features
- 13:47 — Malloy Demonstration and Explorer Interface
- 27:35 — Conclusion, Availability, and Q&A Session

## Transcript

**[0:00]:** So I'm going to start with a little story about cars.

**[0:05]:** What's interesting about cars is that there's a new model year that comes out and you've got a design team that works for years before that. They finally put the car out and then during that lifetime of the car, they refine it. During the lifetime of that model, they refine it over and over again, make it better. So buying the last year of the model year of the car, you've got all of the fixes in it and then all of a sudden there's going to be a new platform and they design another car and they take all the stuff they learned from the last car and they start working on the next car. If you're lucky, you've got a team of people who are working together and they learn together and build the same thing over and over again. And if you look at my career, I've been building the same car over and over again for 30 years.

**[0:51]:** And actually, many of the people who work with me, I always say that software is a team sport. It's not one person. You look at me and you say, oh, I'm the founder of Looker. Lloyd did all this. It's actually not true.

**[1:05]:** It's all of the people I work with on these teams, in these teams, making it smarter. I may be the collector of a lot of the ideas that happened, but as you move forward, you learn things. I mean, I'm sitting in the room with Julian Hyde and I, and Malloy actually has a lot of ideas that I sat and learned with Julian. And as we move forward, we learn more and more. So I built my first database compiler in 1987, and I was the CTO of a bunch of different companies, and I built data tools so that people could understand what was happening in real time.

**[1:40]:** And every time I went to a new company, I built the same tool again, but I built it a little better the next time. And eventually I founded Looker and I built it better that time. And it was good enough that it eventually sold to Google and is Looker. And now I'm doing it again with Maloy. So what you're seeing here is just a culmination of a lot of work about thinking about.

**[2:00]:** And what is the work? The work is how do you make it so that normal human beings can access complicated data sets and make sure they're getting accurate results. That's my career. That's what I'm trying to do. That's what we're going to talk about today.

**[2:13]:** So Semantic data modeling. Okay. Who knows for sure what this is? Okay, okay.

**[2:26]:** Who's actually used one? Who's like used lookml. Who's used. Okay, great. Okay.

**[2:32]:** Who knows SQL. Yay. Good. You're not going to be bored. Okay, so semantic data modeling is.

**[2:44]:** Well, actually this is my thing. So really, what is it? It's a way of create some code that you write that encapsulates reusable calculations for aggregate calculations for measures, and also the join relationships between data. So it's really easy to query one table, it's really hard to query two tables. We're going to talk about that. But one table is really simple. The queries are very readable. But as soon as you get two queries in there, the SQL has all kinds of side effects and you have to be very careful with how you do it. And aggregate calculations can't be reused across queries if there are joins involved. And we're going to talk a little bit about that.

**[3:20]:** But the semantic layer, what it does is you define the dimensionality, you define the aggregates, you define the joins, and then you have an API to query data. And what does that API look like? You describe what you want as opposed to how to do it. You describe that. I want a list of, I want the table to look like this, I want to filter it by something and I want to sort it. That's really what I'm going to call a Gen1 semantic layer is. It's really very simple. Just kind of describe the table in terms of columns that you want, add some filters and sort it, and then that goes into a display. You can graph it, you can link a bunch of these together with the same filters. And that's how you build.

**[4:05]:** That's how Looker worked.

**[4:08]:** But.

**[4:12]:** Okay, who's a software engineer here? Okay, great. Okay. And the first time you encounter data, what's the first thing you do is you build tools to make the data easier to use. Right.

**[4:25]:** So if you look at. When I start using a programming language, my first impulse is not to build tools for the programming language, but software engineers. The first impulse is to start to build tooling because data feels like it's missing some things. The things that it's missing are encapsulation of a module. You build something and you want to be able to reuse that module.

**[4:47]:** There's abstraction, which is I want to create an interface to the data that's simplified, that's very hard to do in data. So the semantic model is part of that. But you miss a lot of the ability to reuse code, then version control, testing. All of that stuff seems to be missing mostly in data. So with Maloy, we're focused on making sure that all of these features are available.

**[5:10]:** We'll talk about that today. So I'm going to call that a second generation semantic model.

**[5:17]:** I spoke here before. Let me take you quickly through why semantic models are necessary. The problem is SQL's reusability is limited because of rectangles. And what do I mean by that? We start with an orders table. This is pretty simple, right?

**[5:35]:** There are four orders here and I have an items table that I'm going to join together these two things together and I want to be able to reason about the orders and the items. And I want to calculate two things. I want to calculate the total shipping and I want to calculate the total revenue from these two tables. And so at Looker, we interviewed a lot of people, we told them to go learn SQL first. I present these two tables to them and I'd say write a SQL query that shows me these two calculations.

**[6:05]:** And they would write this, and that would be the result. And this would be wrong. And why is it wrong? It's wrong because of the fan out problem. As soon as I take orders and I join items, it becomes an items table and I cannot compute shipping in that query.

**[6:22]:** Every one of us has probably encountered this is the join problem. This is the problem that makes SQL hard. This is why LLMs have a hard time writing SQL.

**[6:34]:** These two things, how do I end up writing it in SQL? I end up writing the first query which produces the result, the second query which produces the result, and I join the results. That's how I would write this query. If you ask a human to write it, they have to write it this way. Or if you have to, if you ask a machine to write it, they write it this way.

**[6:54]:** Am I missing a slide? Okay, okay. So traditional data warehousing, what we end up doing is we end up materializing lots of little tables and joining them together. So that's kind of how you do it in traditional data warehousing. The first generation semantic layers, what they do is they make the promise that join relations don't affect aggregate calculations. That's the core, that's the core technology behind this. And what that enables you to do is have dimensional freedom, which is that you can group by anything. You can group by anything in any of these tables, compute any of the aggregates, they're always going to be guaranteed to be correct because you're just describing the table that you want to see. Right. And this object that you start with is called a look Explore. In Looker, it's called a source. In Maloy, it's called a cube. In Cube, it's called.

**[7:41]:** I don't know what it's called in dbt, but essentially it's the thing that you're going to ask the questions of. So you describe it in a network and let's see.

**[7:56]:** So in Malloy, we call this a source, a semantic model for this orders table looks just like this. We know what the dimensions of the table are because they're just the columns in the table. So anything that's a column becomes a dimension. We do a join and we calculate.

**[8:16]:** So we first describe the join relationships, then we describe the aggregate calculations. So total revenue is the sum from the items table. Total shipping is the sum from the main order table. Relatively simple. And it produces a semantic model that looks like this.

**[8:34]:** And then you can pick whatever you want. You can pick from this, this thing to be able to describe the output table that you want. You set a filter and you're done. That's the semantic model. Is this making sense? So far? Yeah. Good. So a query is just the fields in the filter and how we're going to sort the data. So in order to query by order date, we can do it this way. We just do it by order date. And if we want to look at it by user id, this is what a query looks like. Or if we want to just get the total revenue, it looks like that. So the queries become really simple as opposed to what you have to do in SQL because the queries are so simplified, you can ask an LLM to give you the data and it doesn't have to figure out how to get the calculation correct, it can give you what you want.

**[9:22]:** So LLMs are bad at math, but they're really good at giving you what you want. And if you can describe what you want simply and there's a language to describe what you want, then it becomes simple to query. And because it's not an API that you're querying, but a language that you're querying, you can do more complicated things.

**[9:44]:** This is what the query that Maloy will write for you. Which, okay, so that brings us to the first generation of, of semantic models. And so what you saw, like that's Malloy doing what Looker did.

**[10:02]:** Simplified. So what did we want to do with our next generation? We wanted to make sure that anything that you could do in SQL, you could do In Malloy. And that's a pretty big ask, right? So it's that the query language. So the query language, the first layer is just a list of fields, filters and sorts. And in Malloy, we wanted to give you a full query language that lets you do the kinds of things that you would do in SQL, like transformation, complicated transformations. Complicated transformations and joins. So the primitives in Malloy look very much like SQL. The language is more JSON like, for a better word, just like lookML is more JSON like, but the primitives are the same. You can select, you can group by, you have window functions. We have some things that we stole from other places, like ungrouped aggregates.

**[10:56]:** Thank you. Thank you, Julian. Again, this is a way of escaping grouping. We have nesting and we have pipelined queries. So the things that you can do, which is like CTEs and all of that stuff, are available in Maloid.

**[11:15]:** Who's used arrayag in in SQL, who's un nested something in SQL, who's un nested something in two different SQL dialects. Okay, so SQL is the same through the select group by and it is radically different. As soon as you enter the world of structured data or unstructured data, every SQL dialect is radically different. Maloy actually looked at this problem and we came up with a really beautiful simplification of this with our nesting. But it's essentially doing all the array AG stuff and making that much easier and actually more universal.

**[11:57]:** The other thing that you do in semantic models is that you often have views that you want to make sure are accurate. So if you've got a semantic model for your business data, let's just say it's we're looking at our sales pipeline and this is the way we want to look at the sales pipeline. It's going to be grouped by the rep and have a bunch of different columns in it.

**[12:24]:** That table definition lives outside of the semantic model. We said, no, no, no, that needs to exist inside the semantic model because the relationship with those things always break. As soon as you have the semantic model, somebody makes a change in there and then all of a sudden my table doesn't work. And if that view definition is inside the semantic model, you can guarantee with testing that it doesn't break. So when somebody goes and looks at the report, the report's always accurate.

**[12:48]:** So you need to make sure that the use of the code and the common views of the code are tested along with the semantic model. So we put views into the language.

**[13:03]:** Malloy supports inheritance support. So you can take A source and you can extend it. So let's just say I have my sales pipeline and I want to extend it to my Southeast sales pipeline. I can take the source and put a filter on it. And then I have the Southeast sales pipeline and when I query that, I'm only getting the pipelines from the Southeast.

**[13:26]:** So it's a way of actually reusing all of the code that you've just built and specialization. And so basically object oriented. We also have really the composition that we do with our views is really incredible. And I'll show you that in a minute. Okay, so let's.

**[13:47]:** Instead of talking about it, I'm going to show you which is going. Let's see. Option.

**[14:02]:** Hang on.

**[14:07]:** There we are. Okay, here is the model that I just showed you. It's a semantic model. If I look at the preview of this table, it's just a CSV file that we saw before. So this is the items table and it has the data in it.

**[14:28]:** And by the way, I'm running in VS code. Maloy runs in VS code. The full development environment for Malloy is VS code. It's an extension you just install, comes with DuckDB built in. Thank you, Hannes.

**[14:40]:** And so basically all you need to do is download VS code, install the Malloy extension, and maybe download our samples and you can be up and running Malloy in minutes. So this is the items table and this is the orders table. And I join the two. And so now when I look at the schema for this, this is the schema that we saw before. The dimensions are the order date. It reads that from the table. The calculations are the semantic calculations that I added the total revenue and total shipping and the items is joined. And so it has the dimensionality for that. Make sense so far. Okay. And now we're just going to explore this data. So if I hit explore, I can actually I'm going.

**[15:38]:** Why is that not working? I don't know. Here, do it this way. Okay. So the dimensions, I can do the same.

**[15:46]:** I have dimensions and measures. So I can pick some measures. Oh, I'm exploring the wrong one. That's why I want to explore order items.

**[15:58]:** Okay. It has two measures. So let's add those to the query.

**[16:04]:** Right. And then if I go back and I dimensions and we can go by order date and we can group by and we can run this query. I'm going to hide this. And here, let's actually let me.

**[16:23]:** Great. I can change what I want to group by. Let's group by.

**[16:35]:** The item we'll run that and we can see. Terrific. Pretty simple. Got a semantic model, has the joins. It's writing the SQL for me.

**[16:43]:** It's that complicated SQL that we talked about, but it also wrote the Maloy query for me. I can take this Maloy query. I can go put it back in here. In Malloy, when you run a query, you have a run statement, you have the source that you're going to run from and then you specify what the shape of the things that you want. It's really simple, right?

**[17:04]:** So if I go ahead and run this, there's my order and if I wanted to change it to order date.

**[17:18]:** And I can run that.

**[17:21]:** So Malloy has a couple of. So one of the things I want to show you is that there are better primitives in the language. So, like the Ungroup thing is really kind of fun. I can make a measure called all revenue is all of total revenue, and I can add that to the query.

**[17:46]:** If I run this now, I can see that the sum of the revenue columns is 11. Right. So I get the total count of revenue. What's really useful for this is being one thing to notice is that in a source declaration I can use other measures as part of the calculation. I don't have to redefine like you do in SQL.

**[18:05]:** It's over and over again. And then I can make another one, which is percentage of revenue.

**[18:12]:** Are they legal functions? Yes.

**[18:18]:** This is the level of detail. Calc. Yeah.

**[18:23]:** Is total revenue over all revenue.

**[18:32]:** Let's just change this to percent of revenue then.

**[18:38]:** Percent of revenue. We run that. Okay, so we can see that. We can see the breakdown and we can actually control. There's a tagging language which lets you assign properties to each of the. To anything that you're declaring. And in this case I'm going to say this is a percent. And so when I run this, it knows that when it sees percent of revenue, you should treat it like a percentage and it renders it that way. Okay, so this is not complicated, right?

**[19:13]:** You've just learned how to use Malloy.

**[19:16]:** Okay. It's open source, MIT licensed. It's being built into different people are using. A lot of the folks who are building it are building semantic models and then having LLMs write queries against it. And they're being very successful with it.

**[19:31]:** We're all at meta. We're trying to make meta successful with these toolings we're building. The explorer that I just showed you is available as a separate part of the repo. It can be included in your application.

**[19:43]:** And so. And I'll talk a little bit more about that in a minute. Okay, so that's great. So that's a really simple thing. And now I'm going to go open another.

**[19:53]:** Something a little bit more complicated. Airports. Malloy and Carlos is going to yell at me for doing the same thing over and over again because everybody's tired of hearing about me in my airports. If you've ever seen my talk before, I have this data set that I've been using since. Since 2011, and there was old Ben, But it's a real data set and so it's fine. So I have an airport. Basically what I want to show you is, okay, this is a really. There's an airport stable. And if we look at the schema here, the semantic model that it builds is ignore the views for a second. It has the dimensions and has the measures that I've just declared.

**[20:37]:** I can go open it and run queries against it. But you'll notice that built into this, I've actually built some views. A view looks just like one of those run queries, except it's like a member function if you know object oriented programming, it's like a member of the source. So it's validated. This is the way that you should look at by facilities.

**[20:57]:** This query is the way you would look at it if you want to look at it by facilities. And if you want to look at it by state, we group by. This is the way we want to look at it by state.

**[21:10]:** The view is generally built up in terms of the measures and dimensions above. And it's the way that you wanted to do it. And you can load this into the. And I'll show you what you can do with it once you have it built.

**[21:23]:** We have by region. These are pretty simple. Okay, let's go explore this now.

**[21:33]:** Actually, let's see if we have enough room here to do this. So I can pick a view. Let's do it by region. Okay. And so now if I run this, I can run this, and it's by region.

**[21:47]:** You'll notice that the Maloy query is much more simple. Right? It's just source view. You can apply filters here. It'll add filters to it. It's so you know that when you're looking at this and you load the Explorer, these are the ways that the data person expected you to use this data. Right? And if you change the model and you're using this and we decide that we need to be looking at another metric in this view, it'll show up in all the places that that view shows up. So you have. It's coded once. You can. Okay. The other really cool thing here is I can add a view to this, and we can look at this by state.

**[22:25]:** And now what I'm doing is I'm looking at it at my top level by region, and now I'm grouping by state. These are the states that are in each of these FAA regions. So now I have a sense of what they are. The other thing is, you'll notice this little.

**[22:43]:** This table here. I can actually change the way this looks. I can change it to a shape map and run it, or I could change this to a bar chart. Where's bar chart?

**[23:00]:** All right. I can't change it to a bar chart.

**[23:05]:** There you go. I went off script once, and there I am. Okay.

**[23:10]:** I was feeling good, then the demo gods got me. Okay. But no, it should work. Well, let's do this. We're going to do it this way. Okay, so this is the query, right? I can copy this. I can go back to airports. Malloy, I can go to the bottom, and I can run this query. Great.

**[23:34]:** And there is my. And I can take this and make it a bar chart.

**[23:42]:** I don't do it. Okay.

**[23:50]:** Okay, so the Explorer interface lets you take here. Let's go back to airports again and explore it.

**[24:04]:** And we'll bring it over here, and we'll close this, and we'll do the add a view, and we'll do the by region, and we'll nest.

**[24:17]:** We'll ask by state, and we'll do by facility type, and we'll make this whole thing a dashboard. And boom, there we go. Oh, let's make this thing. Oh, I can't make it a bar chart, but. Okay, so I can nest as many things as I'd like in the query. So for each of the different types, we can see that there are 5,000 heliports, and these are the ones in Texas. Okay. And so this is one module. This is the airports module. And now I want to show you how you take this like you would in another language and use this like a function library in another.

**[24:53]:** Okay, so we now have.

**[25:01]:** How am I doing on time? I'm good. Okay, great.

**[25:08]:** We'll go to flights. Nope, these flights.

**[25:15]:** Okay, so what's cool here is that I'm importing airports from that semantic model, right? And if I go look at the schema for airports, it's the same schema that I've got, and it has all of the measures that are in it already predefined that I can use. Then I just join it in twice in flights it's guaranteed. Now when I look at the schema for flights, it has by carrier and state, some things that are defined in here. It has the dimensions that are in the table, it has the carriers which are the nickname, it has the destination, which is airports, not once but twice.

**[25:57]:** Once is origin and once is destination. But all of the things that I defined are in there and encapsulated and reusable.

**[26:08]:** When I run my bar chart for by carrier I can run that and that's going to show me by carrier. And when I run my by state it shows me all of the things that I wanted to know about state like what percentage are delayed and what percentage of flights. And all of these things which are traditionally really hard to calculate are computed correctly. So.

**[26:38]:** And then this whole thing is just explorable in the same way that it was before. So if I come in here and I let me get rid of some of these things, we can pick a query, we can add a view, we can go, let's do by state and let's add by carrier and let's turn this thing into a dashboard. Now we're going to get a dashboard by state of here. Let's make this smaller. Now for California, we can see how many flights we've had. We see how many carriers we have. Percentage of flights going to California and bar chart of the breakdown by carrier we're talking what is this, 30 lines of code?

**[27:31]:** Okay, so that's the taste of Malloy.

**[27:35]:** Okay, there's a lot more to this. There's pipelining queries, there's search indexes which come up with dimensional values. There's a whole bunch more. But I just wanted to say it should be. It's. Hopefully you'd see that it's approachable.

**[27:55]:** So we support DuckDB Postgres Presto Trino. The team is at the. Like I said, the team is at meta. We've implemented all of these different databases, so if you use them, Maloy works the same. Actually there's a core set of Maloy functions that are guaranteed to work the same across databases. So you don't have this cross database problem. If it's Maloy, it works. But we also support the native functions of the databases so you can use them too. And this is where we're available to play with it. I wanted to introduce Kyle who's building a way of actually a hosted version of Malloy so that if you wanted to, and he's just getting started.

**[28:35]:** If you want to use Malloy where you work, you can talk to Kyle. They're very early, but they'll help you set up Maloy for your business, if you want. And there'll be a hosted version of it and this thing called Malloy Publisher. And I want to introduce Carlin, who. Who is my co conspirator in Malloy.

**[28:55]:** He'll answer any questions for you about Malloy.

**[29:00]:** And we're available on Slack, so if you actually ever want to talk to me, I answer the questions on Slack, just like I used to at Looker, so. Okay, that's what I had to say. Any questions?