Metric Views in Databricks: The Missing Layer for AI Agents

AI is only as good as the layer underneath it. That layer in Databricks is called a Metric View: a semantic model that defines your measures, your joins, and the rules for how data fits together. 

In this episode, Stefan walks through the full stack end-to-end on a real LEGO dataset: building Metric Views in Databricks, generating them with dbt, deploying through Databricks Asset Bundles, and finally exposing it all through a Genie Space that business users can chat with. Along the way we get into some deeper questions:  what the limits of metric views look like, how access control works, what BI "shifting left" actually means for engineering teams, and how you organize this in data products.

If you saw our Snowflake Intelligence episode, this is the Databricks counterpart. The Snowflake episode can be found here: https://www.youtube.com/watch?v=Gp-BntPgpcU

Resources:
- Demo code: https://github.com/datamindedbe/demo-technology-exploration
- [episode-specific links here]

---

Click here to watch a video of this episode.
Full playlist: https://www.youtube.com/playlist?list=PLJ_da7qdfL80rA7byzC_CmyrfJWjcCTnb

• (00:00) - Chapter 1
• (00:00) - Intro
• (00:50) - Meet Stefan
• (01:43) - Demo: talking to your LEGO data
• (07:17) - Why semantic layers? Data products explained
• (11:20) - What's in a metric view?
• (14:15) - Creating Metric Views in Databricks
• (16:29) - The LEGO data model
• (18:17) - Data Products & Metric Views in Databricks
• (22:07) - Are we limited to predefined measures?
• (23:16) - Building: native Databricks SQL bundles
• (29:12) - Building: dbt
• (31:16) - Maintenance and BI shifting left
• (33:59) - Deploying dbt on Databricks
• (35:29) - Creating the Genie Space
• (38:24) - Access Control and Permissions in Data Queries
• (39:28) - Writing SQL on top of Metric Views
• (40:10) - Takeaways and favorite Lego

---

Data & AI: Technology Explorations is a biweekly show from Dataminded. Each episode a Dataminded engineer demos a tool or technique worth knowing about -- working code, honest takes, no hype.

Music by Aleksandr Karabanov from Pixabay

Creators and Guests

Host

Jonny Daenen

Head of Knowledge at Dataminded

Guest

Stefan Van Raemdonck

Team Lead at Dataminded

What is Technology Explorations in Data & AI?

Deep dives and practical demos on the technologies shaping modern data and AI development. Join the Dataminded team as we explore, unbox, and critically review the latest tools, from building AI agents and RAG systems to optimizing cloud costs and accelerating data pipelines. We cut through the hype to show you what actually works in real data engineering practice, complete with demo code!

Jonny Intro (00:00)
Databricks introduced Genie Spaces. It's a way to skip SQL and query your data using natural language. For business users, this is a game changer because you no longer need to wait for an analyst or a BI ticket to get insights. You can just ask the AI. To make this possible, you need to feed that agent with a semantic layer, information about how to calculate your metrics and how to piece data together. In Databricks, these are called metric views. We tested them end to end.

Stefan built metric views, dbt integrations, and a gd space on top of that. In this episode, he walks you through all of it. My name is Jonny, knowledge lead here at Dataminded, and welcome to technology explorations in data and AI.

Jonny (00:50)
Hi everyone, welcome to technology explorations at Dataminded. In this series, we give you a quick look into new or interesting technologies. The topic of today is Databricks. How can you talk to your data in a Databricks environment? For this topic, I've invited Stefan. Welcome, Stefan.

Stefan (01:07)
Hello Jonny.

Jonny (01:08)
All right, before we dive into the demo and all the details, could you quickly introduce yourself, you do at Dataminded, what's your role,

Stefan (01:15)
I'm working as a team lead at Dataminded, basically helping companies and clients to build more data centric organizations and everything what's needed for that. I work at different levels on the engineering level, ⁓ implementing use cases, a bit of platform, but also on a strategical and a more technical level. So sometimes a lot of switching, but I like to keep in touch with the engineering part as well.

Jonny (01:43)
Okay, Stefan, let's get started. So you will demo us today Databricks and how to talk to your data. What does it look like talking to your data?

Stefan (01:51)
Well, the idea is that you have data set or a use case, let's say. So for example, here I took a data set from the internet. It's a data set describing all Lego sets and all parts and pieces since, I think, the 70s or so. It's from the re-brickable site.

this is the website. So it has an inventory of all the different sets with...

Jonny (02:13)
nice.

Stefan (02:18)
with all pictures and so on. What I took here as a source is, the download page. So basically you can download all the data as full zip files and ingest them into your data platform. And actually it gives a pretty neat data set because everybody knows LEGO, And it's pretty structured. have a set there, you have some parts,

sometimes you have more parts, sometimes you have less parts and so on and so on. So it's pretty nice data set to work with.

Jonny (02:48)
And you turn that into a talk to your data data set, right?

Stefan (02:53)
Yeah, correct. So what we did is for this video, we worked on Databricks. So we ingested the data onto Databricks. We built a semantic layer on top. I think we will come back to that later. But that's basically...

not exposing the raw data, but building some more information on top. Like for example, if you would need to count the amount of parts in a set, you would need to take the sum. if you want to have the distinct amount of parts, you need to do a distinct. These are the types of things that live within a semantic layer.

Based on that semantic layer, we have built a talk to your data application, let's say, and in Databricks, that's called a Genie Space. And we connected to the data assets in Databricks. And based on that, you can ask questions about the data. So for example, can you...

Give me the...

distinct amount of sets.

that were released.

Jonny (03:51)
Okay, yeah.

Stefan (03:53)
Let's see if that works.

Jonny (03:54)
something

you could quite easily write as a SQL query, but now we just do it in natural language.

Stefan (03:58)
Yep.

Yep.

Jonny (04:00)
And so what is it doing now behind the scenes? I guess it's making a query to query the model that we will show later, but to query the model you prepared.

Stefan (04:05)
Yep, yep. Correct, correct.

Yeah, indeed. So what's, let's say the difference with the, let's say a very simple chat GPT, right? You could hypothetically copy all your data into a chat window and then just ask the question. That's typically not something very smart to do. Why? Because you will push a lot of data models. It will be expensive. And for a model, it will also be difficult to really like understand what's inside.

Jonny (04:22)
Yeah.

Stefan (04:34)
Here the model has access to the data, but it can also query data. It can create SQL queries on the data or it can query the data because the model also knows SQL, right? So it can reason, it can translate your question into a query.

a targeted query, for example, because here I would assume that it can generate the query to get a distinct amount of sets. So actually it's a lot smarter than just, let's say, pushing all your data to the model.

Jonny (05:05)
Otherwise

you would consume all the tokens. Tokens are also getting quite expensive. And so it would also be quite unreliable. And here you take advantage of the technology that's there. Data still lives in Databricks, it's easy to query and you use the model to create a query. And in this case, I see also a visualization.

Stefan (05:08)
Yep.

Yep. Yep.

Yeah, correct.

Yeah, indeed I didn't ask for it here, but okay, it's nice that we have it. Yeah, and it gives some key trends. You see the trend is going upward. Yeah, that makes sense.

Jonny (05:26)
Yeah.

Does this

match your feeling of datasets per year?

Stefan (05:36)
Yeah, think there were a few more difficult years for LEGO. I think it might be indeed the second half of the 2000s, where you see indeed a bit of a decline, but then there was a sharp increase again since 2010. So I think, yeah, feeling-wise it makes sense.

Jonny (05:52)
Yeah, okay, nice. This is really useful if you want to get a quick, ad-hoc question and get an immediate visual. This really helps.

And I also see we get some interpretation like in the bottom part, we get like an analysis or an analyst would give you as a high level summary, we get like some trends.

Stefan (06:05)
Yep.

Yeah,

yeah, indeed. Now, for this agent, it only has access to the data, right? And in the data, no company history is there, only the data is there. So I think these things are more interpretations from the agent.

I think maybe as a next step you could also give it access to, I don't know, more descriptive information about the company, to complement this information as well.

Jonny (06:30)
Yeah.

But it uses

something for grounding. see like it has this one footnote attached to it. Is that referring to something?

Stefan (06:39)
This one?

Jonny (06:40)
Like

the blue ones after each sentence it has like a small footnote thingy.

Stefan (06:45)
yeah.

Jonny (06:46)
so it uses the data to give that.

Stefan (06:46)
Yeah. Yeah.

So I think here it's made a query. It made a query for the amount of distinct sets per year, right? You can also see the code. So it is indeed like a count distinct from the semantic layer. Yeah.

Jonny (06:58)
Yeah, makes sense.

Okay, very nice. And so this is one of the ways we can now query data. But this does not come out of the box. Maybe we can dive a bit deeper like what brought you here and what is the overall idea behind this.

Stefan (07:15)
Mm-hmm.

So as mentioned in the beginning, yeah, there is a lot of data in companies. The question is how do we get information from that data and how can we talk to our data?

We see in many customers that often simple questions take a long time to answer. But it's something that we want to, let's say, make easier at Dataminded. We want to be able to allow our customers to have a fast time to value if there is a question. It should not be difficult to get the answers to the question. What we...

typically used as a data architecture, that's the data product architecture or data product thinking. The idea behind that is the fact that we have, let's say, confined scopes of

data. So we are not integrating all data in a very big model because that will typically become a bottleneck for answering questions later on. We want to link data sets, confined data sets to specific business entities. can be specific owners, can be teams, can be the partners. So we want to bridge the gap between the data and between the business. think that's basically the

core idea. And there are like many ways to do that and there are many different steps, but that's the core idea. Here you see the typical lineage view. So you start from source applications. Source applications will result in source aligned products. So this ball typically represents the data product, You might have multiple source aligned products.

products. Important is the fact that for these products we know who are the owners in the business, who are the key users of those applications, who in the business knows this data very well. It's important to have a close connection to that. From the source line products we can create integrated products or aggregated products if it's needed. If we see that combination of certain sources we are using a lot, okay, maybe it makes sense to create an integrated product from that. And then

And on the right you see more the consumer line products. And these are products that are fulfilling a business need. So they are an answer to a specific business need.

for example, for us the Lego data, we can create a consumer line product that is, let's say, targeted at a specific need.

And also in this part, the semantic layer lives mostly because it's something that's typically not close to the source. It's more towards business metrics like the KPIs and the metrics and so on. So this is, let's say, the structure that we're using.

Here I'm referring to an article that a colleague of ours wrote,

on semantic layers. So where can you use a semantic layer? As we mentioned, it's mostly, let's say, on the right side, mostly on the downstream side. I think in the article he proposed multiple options. You can create more kind of a centralized semantic layer if your context requires that. For example, if it's important to have metrics that are reproducible over a lot of business domains, OK, maybe then you need to centralize the semantic layer. But sometimes it's also not

need. is sometimes you just for a specific use case you want to have a specific metric and you have specific business logic and in that case you can have the semantic layer as an output of your ⁓ data product. ⁓

Jonny (10:32)
So it's a matter of how

much do you put inside the data product and how much do you then get centralized as one big layer and finding a balance there.

Stefan (10:39)
Yeah,

yeah, indeed, indeed. So there's no, let's say, one answer fits all. It really depends on the context you are working within. For our example, we basically used a separate data product to generate semantic layer. And from there, we made some analytics. So this is for our demo, how we approached it.

Jonny (11:01)
Okay, very clear. And so what you will construct in the demo or what have you constructed for the agent was something that resembles the thing on the right. You built a data product that harvests the data from Lego website or from the dump of the Lego website. And then on top of that, you build a semantic layer. Now, could you tell a bit more, what is that semantic layer? What is inside of that?

Stefan (11:15)
Mm-hmm. Yep.

Yep.

I will show you, let's say the basics of the semantic layer. So yeah, this is Databricks documentation site. And this is an example of a semantic layer. So you can see it's a SQL statement.

On Databricks, a semantic layer is implemented as a metric view. So that's like technology specific implementation. I think in other data products, it might have a different name. ⁓

Jonny (11:52)
It's like their

terminology that they use. think in Snowflake it's called semantic view. Here they call it metric view.

Stefan (11:56)
Yeah, A

metric view, yeah, indeed. So it's a specific asset within the Databricks Unity catalog. So you also created...

you use a SQL statement to create it, create or replace view with metrics. And then basically there's a YAML SQL to create it. But the basic ideas for many of these, let's say implementations on different data platforms, it's often pretty similar. You start from a certain source, main source.

Jonny (12:10)
Yeah.

Stefan (12:26)
you might join data together. You might join different tables together. We'll show you later how we did that for our data set. And you define a bunch of dimensions.

This one shows orders. So for example, you have an order status, have some dates, keys. This is typical dimensional information. And then you define a bunch of facts or measures. And measures are basically...

the definition of the fact, but also on how do you calculate a measure based on a fact. So for example, if you want to have a count, you need to do a count. If you want to have a total revenue, you need to calculate the sum of a specific column. So this is basically a fact plus how do you calculate a metric from a fact. I think this is the pattern that you see in most semantic layers. Also, what's important is the fact that you have comments here. This is of course nothing special to a semantic layer.

You also have this in a table or in a view. But this also creates extra metadata that can be used by humans or by, let's say, agents like the Databricks Genie Space.

Jonny (13:32)
Yeah. And this will then be used to interpret or to translate a natural language query also and to match that with specific calculations, I guess.

Stefan (13:42)
Yeah, correct. So suppose we would ask the agent, give me the total revenue filtered on specific years. The agent will say, okay, I will look in the semantic layer. I see there's a column called total revenue. It already tells me how I need to calculate it. So the translation step for the agent becomes less to get the final query. Yeah.

Jonny (14:05)
Yeah. And you mentioned that this is applying to one table we saw at the top. It's also possible to have multiple tables then. Okay.

Stefan (14:10)
Yep.

Yeah, correct.

is an example here you can see the joins. So for example, you select a source, which are the orders. Basically, it's kind of a fact table and you will join this with dimensions, with the customer dimension. And here you say how you will join it. And you can also chain the joins. So for example, you have your join to the customer and here you have

Jonny (14:23)
Yeah.

Indeed.

Stefan (14:38)
join from customer to nation, right? And then you say how you join it. you have like this type of chain joins that will be combined in one metric view. Yeah.

Jonny (14:41)
yeah.

Okay, in this case you would have view that spans multiple tables. Would you then also need to create one for the other tables or is that just one metric view that you offer to your agent in this case?

Stefan (15:01)
That really depends on the business logic. I think a metric view you can compare with a star schema. So if you have a typical star schema, as you know, you have dimension tables and you have effect table. This is also more or less represented in a metric view because you have your dimensions and you have your metrics and your measures.

If you would in reality create multiple star schemas, I think you also need to have multiple metric views. One reason for doing this is if you have...

facts or measures with different granularity, for example. If you have very similar effects with same granularity, maybe you can just add them together in the same metric view. But if you have multiple granularities, yeah, you cannot combine them.

Jonny (15:44)
Yeah.

Stefan (15:49)
That's an example. Another example I will show you later is that in our data set we have two metric views. One is for the parts itself and the other one is for the minifigs because a minifig is

an essential different thing than a part. A part has a color, a minifig has different attributes. So we could try to force it in the same metric view, but here I think it's better to separate it because then you have like two clear metric views with two different purposes.

Jonny (16:10)
Okay.

It depends also on the questions you would want to ask, right? The minifig questions would be a bit different than the normal brick questions, I imagine.

Stefan (16:22)
Yep. Yep.

Yep. Yep. Yep.

I can first shortly show the data that we use.

so here you see the data model. So basically one box here is it's a table that you can download from the Rebrickable site. And what you see here, you see

few important tables, the sets. So you can see a certain set has typically a code.

There is a team linked to a set. So you have like the classic sets, you have the Technic sets. set is linked to an inventory. An inventory has a version and is linked to a set of parts. And the parts is, what is the part number? What's the color?

What's the quantity and is it spare? For example, if you would have like, let's say four by four squares, plates, and you have 10 of them of the same color, they would be in the same inventory parts. And if you would then have like the same part, but then a different color, it would be a different record in this table. That's how it's separated.

Jonny (17:28)
So different records

have different inventory IDs as well.

Stefan (17:32)
Yeah, yeah, indeed. And here you can see then multiple other dimensions. A part can have a category. I don't know, blades or bricks or whatever. A part can also have a color. The RGB values and also is it transparent part? Yes, no. there are also relationships between parts. So you have like...

child-parent relationships and so on. And here you can see, coming back to the discussion we had before, minifigs are actually a different type of thing. So a minifig is also linked to a set, but it just has a name and it has a number of parts. It does not have a color. It does not have a category. So it is a different entity. And that's why in our case, I also separate it into a different metric view.

Okay, so how does that come together in Databricks? So here you see our Databricks workspace. So we're working in a.

basic Databricks workspace. We have a catalog here where we're working in. In that catalog we have multiple schemas. How we typically work is that for a data product we have two schemas. We have the internal schema and we have an external schema. The internal schema contains really internal data. So for example, if you would download files and you need to

store them for one or the other reason, you would do it in the internal schema. And then if you expose data-specific tables, then you would put it in the external schema.

Jonny (19:03)
Yeah, the eternal one you

never want to expose beyond the team that works on the data product.

Stefan (19:06)
No, no,

indeed. And the external one should be quality controlled, needs to be documented and so on and so

So here you can see the source. So we have a source aligned re-breakable download product where we have downloaded the different tables. These tables correspond to the model I showed you earlier. sorry. And we have, there's also an external layer. So again, here super simple. We just have the tables in turn and we expose them one on one.

externally. This is a source line product. On top of that, we have built a consumer lined semantic layer product. Like in reality, you would never call a product semantic layer, I think, but for this demo, that's okay. In the internal layer, there is nothing really. There's just like a one-on-one view on the source-aligned product.

But in the external layer we created the different metric views.

So you see we split it here the part and the minifig metric views. We also see here an underscore dbt, I will show you that later. We created the metric views with let's say native Databricks syntax and we also created it with dbt semantic layers which is a dbt entity that is now also available for Databricks.

Jonny (20:20)
if we look at the agent demo you gave before, this is what you gave to the agent, right? These semantic models. Okay.

Stefan (20:25)
Yes, correct.

Correct. So here I gave, I think, the part and the minifig model I gave to the agent.

If you look here, what is inside. So if you have a metric view on Databricks.

you do not have the classical view where you can sample the data or where you get just the list of columns. No. You have the measures specified and you have the dimensions specified. So the measures are basically as I showed before, the different measures you can calculate and see it as calculations based on top effects. And the dimensions are the different dimensions as...

as shown earlier on the documentation website. So for example here you see the set number, it's a dimension, you have a name, release year, of course it's important, the team ID, team name, and maybe team ID is not so relevant because it's maybe a more technical dimension, but the name for sure, maybe you want to search for specific words in the teams, and so on and so on. So typically you will have quite a

of dimensions. We have here 16. And here you have the measures, like the amount of sets, the total quantity of parts, the amount of distinct parts, and for example the spare quantity, was the total amount of parts. ⁓

Jonny (21:43)
Yeah.

I guess the info

box shows you bit more on how it's defined then, the info box at the end of the line.

Stefan (21:54)
Yes, indeed, you see there's an expression always and that you will also see that in the definition of the metric views. So this is shown here. ⁓

Jonny (21:54)
yeah, so that has the expression that we saw in the YAML files.

Does that also mean, Stefan,

that we are limited to these kind of measures? Because you defined four here. What if I want to have something else, like the average number of parts in a set or something like that, which is a different kind of measure.

Stefan (22:22)
Yeah, that's a very good question. Yes, indeed, you're limited. So I think that's a bit of the flip side of metric views. Metric views or semantic layers are very powerful because you can give a lot of business information and KPIs with that. But the amount of information you give is also limited to the amount of information you give, right? So if somebody wants to calculate completely different metrics that you didn't think about,

Jonny (22:42)
Yeah.

Stefan (22:48)
Yeah, you would need to add them here.

you can very well think of questions that cannot be answered by this metric view. Where I work now, we also have a data set, for example, with a lot of history inside. And this history is very difficult to capture in a metric view.

Jonny (23:02)
Yeah.

Stefan (23:06)
while it is very relevant for a lot of use cases.

Jonny (23:09)
Okay, very nice. So you basically made sure you had these semantic layer metric views in place. You fed that to the agent and you had a way to calculate those metrics views, right?

Stefan (23:15)
Mm-hmm.

Yeah, indeed. So let's switch to the code. So here we have, let's say, organized our repository for data products. We have here the consumer reliant layer and here we have the semantic layer data product, right? Again, not a good name to use in real life,

Jonny (23:37)
Yeah.

Stefan (23:39)
I used two ways of, let's say, implementing or deploying these metric views. Let's start maybe with the most straightforward one. It's one where we just execute the Databricks SQL code. How we often work is that we have...

a bunch of assets in a data product or let's say the see it as the logic and the code. It can be Python files, can be SQL files, it can be dbt projects. we deploy these with asset bundles. For example, here you can see the job file where we execute a specific file. So that's how we, deploy this file to data.

outbreaks

and we can schedule it or whatever. But in the end, what we are doing is we're just executing this code. And this is basically a notebook task. So it's like Python code. And the SQL statement, it's basically in here. So we say create or replace view. We have some

some parameters in here to make sure it ends up in the right catalog, in the right schema. But this is the same as we saw in the documentation. So here you see, for the minifigs, we start from the sets, which is our set.

We join with the themes yes, because it's always important to understand the themes Then we join with the inventories.

Jonny (24:58)
Yep.

Stefan (25:03)
And from inventories we go to the minifigs, inventory minifigs, and we go to the minifigs. So if you look here, basically what we are doing is we are going from here to inventories and then to the inventory minifigs and minifigs. These are basically the tables that we are joining and transforming into this metric view.

So this is basically the SQL statement. You see it's create or replace view and then there is specific syntax you see with the $2 signs and then we end up with YAML syntax. Yeah, yeah, indeed. Yeah, that's how Databricks implemented it. We have a second metric view that's a bit more complex. That's for the sets.

Jonny (25:32)
You inline the YAML as a SQL part,

Stefan (25:45)
We do the same thing. We start from the sets. We do the tour to the teams and then we go from inventory, inventory, inventory parts and then we go for colors, parts and the part categories. But this all corresponds to the, let's say the chain of tables you see here. ⁓ Yeah, correct. So actually I think as a result of this, also makes...

Jonny (26:01)
Yeah, the left side of this image.

Stefan (26:08)
your data set easier to understand. Because if you see this, okay, it's understandable, but you need to know a bit about the data, you need to do multiple joins. And in these metric views, these joins are done for you already. So I think it makes it bit easier to use the data as well, even if you're not an agent, if you're a human, right?

Jonny (26:31)
And so this is essentially when you deploy this bundle these scripts are executed as different steps and they perform the SQL operations.

Stefan (26:41)
Yeah, correct, So here I'm in the semantic layer folder. So we are in the correct data product. We have the asset bundles.

defined here. It's with the Databricks.yml. There are some variables defined. We have a link to the targets file which defines the workspace we are deploying to. And then we are taking these jobs. We will deploy jobs. And we can also deploy storage. This is something else. So let's do this. can...

to a Databricks bundle deploy. And now it's basically copying a bunch of files to our Databricks workspace. And then in a second step, it's deploying these files as Databricks resources. So that's kind of a...

Jonny (27:21)
Yeah.

Stefan (27:31)
deployment mechanism internal to Databricks. But that's how you can deploy resources in Databricks.

Jonny (27:39)
Okay, so the effect of this

operation is that they're actually executed as well and the semantic, no, not executed.

Stefan (27:43)
No, no, they are

not executed. Probably you can configure it somewhere, but for now they are just deployed. So the assets are in Databricks.

So for example, if we go to the jobs, you can see.

the semantic layer, for example here. So you see I did quite some trials before. But there are basically a bunch of tasks here defined. And the ones that I showed, like create set minifig, that's now a task, and that's in here. And it's basically executing.

Jonny (28:01)
yes.

Yes.

Stefan (28:20)
Python file which is uploaded to Databricks now and if you click on this you will see the code. So this is basically the code that we had in the... So this is a bit like kind of small detour to a deployment mechanism but that's how we typically deploy. If we want to run this, suppose that we want to run one metric view, we can just run this one.

Jonny (28:29)
⁓ yeah.

Stefan (28:50)
and now it starts running this. Yeah, yeah, correct. And also I selected one task here, so typically you will run everything together. Now I just run one task

Jonny (28:50)
And so this you could also then schedule, but now you're doing a manual run.

Stefan (29:01)
It's using serverless compute now, so it should go quite fast. But this is basically the code that it's executing.

Jonny (29:08)
Okay, so when this is done, the metric view will be created.

Stefan (29:11)
Yeah, yeah. There is a second way we can create the symmetric views, and that's by using dbt. So also here you can see, created the dbt project.

We like to use dbt because it's like a nice framework to do your transformations in. You can do a lot of, let's say, configurations scaffolding. It's very nice to use. So also we wanted to see, can we define this metric views in dbt? And yeah, answer is yes. But not in, let's say,

full release yet. So you have dbt-core and then dbt-core has a bunch of adapters. So for example for Databricks you have the dbt-databricks adapter which allows us to run dbt on Databricks and there is currently one version here it's in a pre-release that's this version that has support for metric views.

it will go into a full release soon but for the moment this still in a pre-release. How does that work? I can show you here in the models.

external layer. So we define a dbt model as we always do. But we say that the materialization is metric view. And for the rest is the same as the Databricks language. So like the create view with metric statement is here handled by the dbt Databricks adapter. ⁓

Jonny (30:32)
Yeah, so

essentially you paste in the YAML in here, but you can also use then the dbt macros that you have access to and you generate YAML, which is then interpreted by this ⁓ adapter and it makes you a Metafiew. Would it be similar to just write plain SQL and then like you did before and just paste it in a dbt file?

Stefan (30:40)
Yeah, correct, correct.

Yeah. Yeah, correct, correct.

No, I don't think that will work. Because dbt takes care of the, let's say, the create or replace view type statements. And that's something that we cannot put in here. But you see that the syntax here, I think this is like very, very similar to the YAML part of the native Databricks SQL statements.

Jonny (31:03)
Yes.

Would this then require specific maintenance? This seems like somebody needs to maintain this, keep it up to date. This particular metric view.

Stefan (31:25)
Yeah, correct. It's like I would say any other query or table you have living on your data platform that needs to be maintained. This one is typically closer to business side. So I think in an ideal case, if you have...

business people, be it business analysts or business data engineers that know very well the business logic, the ideal thing is that they are able to maintain this. And I think in that sense, it's also these metric views, okay, it's YAML, so there's a pretty low threshold, I think.

Jonny (31:57)
Yeah.

Stefan (31:57)
to start working on this. Well, if you would have complex SQL joins or let's say even Python code, it's more difficult. So I think here the knowledge you need to work with this is a lot more shifted towards the business knowledge. Like what exactly do you want to calculate? That's the main question. Not necessarily that all the technicalities behind.

Jonny (32:04)
Yeah.

If you can give a YAML editor that takes care of spacing in a nice way, I think this is possible. But this does mean historically these models, semantic models, metric views, they were built inside of BI tools, right? They were built as part of a Power BI package or whatever, or workbook. This is now shifting more towards the left, let's say, towards the actual data pipelines. And it seems that could cause some friction, no?

Stefan (32:42)
Yep. Yep.

Jonny (32:47)
Because people would need to still learn new technologies, new concepts, a new way of working you see any friction on that when you speak to people?

Stefan (32:57)
where I'm working, not really. I think it depends a lot on the context. If you have business analysts that have been working in the same tool for like decades, yeah, of course you will have friction. but if you have, let's say people who are open to learning new technologies, then there will be less friction, of course.

Jonny (33:03)
Yeah.

Stefan (33:17)
But that's, think, also a bit like how the data world works, things are evolving all the time. ⁓ And you need to keep up with technology and I think you need to do the right thing. And in this case, the evolvement of AI and the large language models.

Jonny (33:23)
Yeah.

Stefan (33:36)
I think are a clear push to having this type of logic more on your data platform rather than in a BI layer where it's like just used for one specific visualization in a report and so on.

Jonny (33:42)
Yes, indeed.

Exactly. Yeah.

Yeah, exactly. That, that feels like locked in into that one Power BI dashboard. Well, now we pull it back to the data product itself. So it can be used by multiple of these dashboards.

And so you did this in dbt and to deploy it on Databricks. Does it also follow the bundle principle? Do you do also Databricks bundle deploy?

Stefan (34:06)
Yeah,

that's similar. basically with this deploy, we also deployed the dbt part, by the way. Yeah, it's just, yeah, it's one product. And basically it's a different task. So you see, we called it the dbt build task and we need to give some information. We need to give it

Jonny (34:15)
yeah, that's part of this package, or this bundle.

I see.

Stefan (34:30)
project directory and we need to give the commands so we say dbt build we give it some extra parameters here that depends on your setup Databricks recognizes this as a dbt task so it will execute let's say the dbt parsing and the dbt

Jonny (34:42)
Yeah.

Stefan (34:47)
running part, and then after it will execute the SQL commands as well on Databricks. So.

Jonny (34:53)
Okay, yeah.

So it follows the same deployment and then you also do the run and you get the same model that you showed us earlier.

Stefan (35:01)
Yeah, indeed. Here we also have the dbt task. This is basically, you call it dbt build task. It is a dbt task. So it is recognized as a dbt task in Databricks.

Jonny (35:14)
Yeah. And so this you can run as well. And then out comes the model.

Stefan (35:19)
Yeah, yeah, indeed.

Jonny (35:21)
Could you show the end result again, Stefan?

Stefan (35:23)
Yep.

So here you have the semantic layer. Yeah, they're completely equivalent.

Jonny (35:26)
Yeah, I

And so we spent some time on how to get from the source, the Lego bricks data that you have to make a source line product on top of that, the semantic data product. How do you then create that Genie Space or that agent on top?

Stefan (35:33)
Mm-hmm.

Mm-hmm.

That's actually super easy. You can just create a Genie Space. Actually, it already directly asks you connect to your data. And it has a predefined filter for metric views. So as you see, it's a bit opinionated towards metric views. So let's...

Jonny (35:52)
Yeah.

Yeah, but you could also have

like non-metric fields you could have normal tables attached to it. Okay.

Stefan (36:03)
Yeah, yeah, indeed,

These are the non-DBT versions. Now it created a new space. So yeah, you can change the name if you want.

Jonny (36:08)
okay.

Stefan (36:20)
So you see here the space. What is needed for space? You need to have an owner, of course. It needs to be connected to a warehouse because as we mentioned in the beginning, the agent will typically interpret your question and write SQL code to...

to get the data, so it needs to execute the SQL code somewhere, therefore you need a warehouse. People using this warehouse, using this space of course also need to have access to this warehouse. ⁓ It needs to have access to data.

Jonny (36:48)
Yeah.

Stefan (36:52)
You can add more data here as well. And then you have a bunch of metadata or let's say instructions for the space. So you can give it general instructions

like explaining what is Lego or maybe indeed giving some company information. You can provide joins. So as you mentioned before, here you can like propose some joins that are useful. You can also provide standard SQL statements like a filtering and then you can give it extra metadata.

Jonny (37:06)
Yeah.

No.

Stefan (37:22)
and these are example SQL queries you can give it.

Jonny (37:25)
Yeah,

this is interesting. think in Snowflake, for example, you ship that as part of your semantic model. Here you ship that as part of the agent, because you can plug in tables in the agent and add the joins, add the examples, not as part of the model, but as part of the agent instructions.

Stefan (37:29)
Hmm.

Yeah.

Yeah, yeah, I have the feeling that the Genie Space is not super opinionated, right? If you create it, then it directs you a bit towards metric views, but you don't need to use metric views. can also use tables and give some instructions on how to join tables.

Jonny (38:04)
Yep.

Cause in the UI you could just say these tables, they're joined like this. Here are some example queries. There we go. Okay.

Stefan (38:13)
Yeah, correct, correct.

So there are different approaches. I don't think there is like one approach that's clearly the best you need to follow. But there are different ways of achieving your result.

Jonny (38:24)
Yeah.

And what I was then also wondering is about the access model. You mentioned you need to give people access to the agent. Do they also need access to the underlying data then or is access to the agent enough? Okay. So you need to have the agent permissions and in this case, the semantic model data product. Okay. So the agent is doing the query with your credentials.

Stefan (38:33)
Yes, yes, no, no, you need to have, yeah.

Yeah, correct, And also...

Yes, ⁓ And you also need to have access to the correct compute. So there is compute needed to execute the queries upon. And yeah, there you also need to have access. Yeah.

Jonny (38:48)
cut it.

yeah.

Yeah,

okay. So you need to ideally automate part of that because if you want to give people access to an agent, yeah, you have to have several things in place. Okay, got it.

Stefan (39:03)
Yep.

Yeah, correct,

maybe to pick in on that, because indeed we show here, you can make a genius space in like 10 seconds, but to maintain it and to make it available to like say, broader audience, I think these are, this is another level, right? So there you need to take a few more steps, but that's outside of the scope of today.

Jonny (39:23)
Yeah.

Stefan (39:28)
Maybe one last thing I prepared. It's pretty small, but we also created a query. We created basically a kind of analytics data product to get specific information. for example, what we wanted to know is the amount of distinct sets per year. And we made that based on the source aligned data and based on the

Jonny (39:44)
Yeah.

Stefan (39:50)
semantic model. Here you can see the query. So for the semantic model, it's basically selecting a release here and then you measure the set count, the distinct set count. And for the plain query, it's more or less the same here because it's a pretty simple question. You need to do a count distinct, right? So the count distinct, it's something that's already in the metric view.

Jonny (40:07)
Yeah.

Yeah. Okay. If you would have to summarize your experience up to now, what would be your main conclusions?

Stefan (40:16)
Well, that you can, let's say, use the semantic layer.

in our existing setup on Databricks, would say, with data products with CI, CD implemented. Now here you see we do a manual deploy, but you can also do this with the GitHub actions or DevOps pipelines. So we can basically integrate the semantic layers pretty straightforward into our existing systems, our existing setup, our existing architecture.

like interested to go further on this and to understand how the Genie Space is really interact with the data because we saw now the metric views. Okay, it's interesting. It gives like an extra layer on top of the data.

But do you need to use it or can we also do like with tables with some join proposals and some extra comments? Is that not like a better approach for the agents? I think that's interesting follow-up work to do.

Jonny (41:09)
Yeah.

Yeah. Okay. Yeah. From what I've seen today, I've seen, you can do talk to your data in Databricks. You have your data already set up and all you need to add is either a semantic model. You can use dbt for that or the more native approach, if I may call it so.

Stefan (41:23)
Mm-hmm.

Jonny (41:32)
to publish those models. You hook them up to the agent and you're good to go. Of course, these Genies Spaces still require more exploration. But next to that, I also saw in the agents, if you want to use a UI, you can also click it still together to experiment. You can add tables, you can add the joins. That's also still an option if you quickly want to explore.

Stefan (41:39)
Yeah, correct.

Yep.

Jonny (41:50)
Okay, and then one final question remains Stefan. Of course I want to know what's your favorite Lego set?

Stefan (41:55)
That's a difficult one. I'm always in favor of the the buildings, like the modular buildings. is one every year, I think, even on the 1st of January, there is a new one released. So I would need to pick one of them. I think at one point in time there was the 10th year anniversary, which was a bit bigger. So I will go for that one.

Jonny (42:09)
Okay.

Stefan (42:20)
Assembly square, that's it.

Jonny (42:22)
Okay cool.

Thanks a lot for the demo. Very cool to see how Databricks works and how you can attach agents to your data using these metric views.

Stefan (42:25)
Yeah.

Jonny (42:31)
All right. Thanks a lot everybody for watching. I hope you enjoyed on Databricks and we'll see you next time. Bye bye.