Thank you.
Good afternoon, thank you so much for joining us today.
So we're going to talk about DPD score indeed, in terms of DPD metadata.
Before we introduce ourselves, let's start with an observation.
We've been measuring a metric of our Git repository for the past three years.
As you can see, there's a sharp incline since two years ago.
And what we've been measuring is the quantity of YAML code in this repository.
Now as you might know, YAML kind of became a programming language for data engineers, for some reasons.
And there's a strong correlation there with the introduction of DPD in that repository.
Now to reach 150,000 lines of YAML code is quite a feat.
And in this presentation, we're going to talk why this happened, how it can be problematic and how we serve that.
I'm a metric and a if you didn't notice yet, I'm French.
I live in Namsalam together with Yohan.
We don't live together, but we bottle.
All right.
Those are coding assistance.
And we worked, indeed, that technique.
I don't know in supermarkets, based in Namsalam.
So, in the spirit of free and open source software, I'm going to give you some takeaways for free already.
We do encourage to use declarative model properties.
We do want to enforce the consistency of those properties through automated lynching.
And finally, we do want to be able to customize those lynching rules for LGBT metadata.
We'll move the simplest user experience that we can build.
Now, this might sound a bit abstract.
But we will go over this outline first introducing DPD and the need for lynching DPD.
Finally, our solution for that.
Let's get started with DPD.
Anyone familiar with that?
Any user of DPD?
All right. Nice, a few.
For the rest of you, let me explain a bit.
Let's build the data platform together.
Starting with a place to store the data base data warehouse data lake.
You name it.
We have some data in there.
We do want to make it usable for analytics.
So, we are throwing SQL at it.
Building new tables, transforming data.
We're building some pizza analytics data warehouse.
So, let's create one table.
Let's create a second one, transforming more data.
Making it usable.
A third one.
And what that starts to be pretty usable already.
We can look at pizzas, pizzas, daily sales.
Once we have it, we are adding some components to this data platform.
Some notes book feature, for example, for machine learning experiments.
Some dashboarding tool to build splits.
Data catalog to find all the data in there.
Some pipelines in search of an extraction.
And the data app may be for some interactivity team.
This is looking pretty good until issues appear.
For example, data changes and we need to evolve the schema.
Now we have a new column somewhere on the table.
So, someone was writing some SQL query to add this column and back tracking the data.
Oh, one day there was, for some reason, no data.
So, someone inserted it manually.
Sure.
And another data was run.
So, someone fixed the data.
And this is problematic because it's hard to keep track of these changes.
If they are done manually, they've done right in large data platforms run by many people.
And this is only the beginning of some issues we can have.
Or there are issues include, and there are not limited to orchestration.
Half problem to solve.
Out of 100 data, lost data manually in put, as we saw just before.
Quality checks are the lack of quality checks.
Personal data that should certainly be protected at all costs.
If you nail all of this, then you have performance issues.
Because it's a lot to deal with.
And this is where the BTE checks in.
So, the BTE does not return to solve all of this.
However, it helps a lot to organize ourselves.
The BTE means data build tool.
It's a framework written in Python, which aims at giving data engineers and data users
and some organizations into how they build and transform data in data warehouses.
And in order to do this, the primary observation that the BTE does is to look at this.
Transformation SQL query.
That it is really made of two very different parts.
The first part is some metadata.
Basically, this creating a given table with its schema.
List of columns and their types.
And maybe some additional metadata, such as a primary key constraint.
The second part is logic.
Pure, decorative SQL transformation logic.
And what the BTE proposes is to split these two parts into different structures.
And SQL find that contains only the logic.
So only a select statement.
And a YAML file.
There we go, YAML, which contains exactly the same information.
Except organizing, organizing a more machine readable structure.
And what the BTE will do with that information is to reconstruct the SQL query.
The SQL query we saw before.
By grouping over the types of metadata, we have in the YAML file.
So far, so good.
Nothing magic going on there, just some templating mechanism.
And well, if the BTE was emitted to this, it wouldn't be very useful.
However, once we have a YAML file to store metadata, we can add anything else we want in there.
For example, some description of our columns and tables, in order to make them readable for humans.
This description can then be forwarded into the data catalog, automatically.
We can have some additional configuration.
We can define who has access to this data and build an SQL template that will use this information in order to grant access to a given table.
Maybe we can define an owner.
We can define some adopting system.
We could connect dashboarding system to this table and define heads.
Think to this dashboard.
And that dashboard, we automatically retrieve the data.
Just because it was declared here.
Columns can also be enhanced.
We can add additional metadata.
We can add tests.
We can say that, hey, this is given column.
It should contain unique values and so on and so on.
What I'm illustrating here is that data models are first class citizens.
In the sense that DBT allows us to program these models and enhance them with whatever automation we need.
So this is DBT to us.
It is a bit more, but this ID of programming models is pretty neat.
By default, DBT comes bundled with bundled with features to build data.
So generate the create table as with for the logic.
To automatically generate the SQL to run tests as defined in the ML file.
To generate a catalogue, a human readable catalogue to replicate the data schemas.
And with this ID, we can also build our own automation on top of this metadata.
Really for anything we want.
Let's jump over now for the need for lynching this metadata.
And let's look at these two models, pithas and pithas that we saw before.
We are forgetting the SQL for now.
Just looking at the metadata, the ML file.
And to the untrained I, it's pretty hard to spot mistakes in those ML files.
However, if I point them out, they can become a bit of just.
For example, the one on the right is missing capitalization in its documentation.
Of course, I'm bankshitting here, but it does not look good in the catalogue.
This one is a bit more important. There's a data leak.
We are granting red access to this data to all.
And that's probably an issue.
There's an invalid owner, null is no good.
There's no dashboard link to it, so it's this data even useful.
Oh, there's a sort of no primary key.
We have the data modeling practice missing test.
Zero quality, quality test on this data.
So as you can see, we already have six spreadsheet little details.
It only mistakes in this metadata out of 20 lines of the ML.
Remember this 150,000 lines of the ML in one repository.
That's the put on tool for lots of errors.
And this is basically our problem statement.
Quick summary.
DBT offers us the ability to have first class citizen data models.
And metadata management.
However, at scale with thousands of new models,
we need to enforce the consistency of the metadata.
We need to run quality checks on this metadata for values,
applications including data security,
which is probably one of the most important ones.
Now in order to tackle this issue,
I will leave the mic to your hand.
I'll buy one.
Thank you.
Yeah, that brings us to the main topic of this talk,
which is DBT score.
So I think much explaining it very well.
We are vivid users of DBT,
and we've been using it for quite some time now.
And at scale, the metadata becomes a mess.
You might have noticed this yourself already,
but we wanted to solve this issue,
and it doesn't work having to review each PR,
like the yellow files with a magnifying scope.
So we built a linter called DBT score.
You can find us on GitHub,
at picnic supermarket slash DBT score,
so free to have a look.
It's 100% written in Python,
which is a great, I think.
You might be asking why,
because maybe you're a Java developer,
and you don't care.
But chances are you are running DBT,
which is a Python environment,
so this is also easily installable in your Python environment.
The idea is that you install DBT score
in your DBT project,
and then you can really easily run it.
So it looks like this.
Pip install DBT score, it's that easy.
You can also use a package manager or something,
poetry, UV, and I don't mind.
You can easily install it,
and then it gives you a command line too.
So you can run it from the command line,
and it looks like this.
So currently, two main commands, lint and list.
Lint is, I think, obvious, that's the goal here,
and you can also list all the rules
that are in your DBT score project.
I will go over that later.
So let me demonstrate real quick what it does.
Once you run DBT score lint in your DBT project,
what it will do in the background is it will gather all the models
and sources in your DBT project,
and it will iterate over them.
Then in your kind of DBT score project,
so not DBT project, you have configured a set of rules,
and these rules are applied to each model and or source.
So it is a linter.
What do you expect from a linter?
Of course.
But then after applying all those rules,
there is a score calculated for each model.
So in the case of pithereons,
that was the ugly model that material was talking about,
you can see that two rules have failed.
So the first one is about columns not having a description.
As you can see, the ID column doesn't have a description,
and it is shown in the output.
And the other one is that the model is an owner.
It was no, I think, in the example.
So that is basically how it works, a score is calculated.
Based on the rules that have passed and failed,
then based on the score, a badge,
we call the emoji the silver medal a badge,
is awarded so hopefully to kind of gamify
getting your metadata a little bit in good state,
because nobody loves fixing their metadata, of course.
And in the end, your project is also scored by an average of models.
By the way, below is kind of the result of the linter command.
In this case, it is an error, because it is below the threshold
that we set in our project.
I will go over it later.
If you look closely, you can see that the project score is a nine,
while there is only one model with the eight,
so that doesn't make any sense.
But that means there is more happening in the background,
but we don't want to overwhelm the user.
So this is what it looks like with two models,
and as you can see, there is way more rules being applied
than was previously shown,
because we don't want to bother everyone with the OK statements.
They don't need fixing,
but you can see what is happening in the background.
The pizza's model scored a 10.0,
and was awarded with golden metal.
So this is like a holy grill for every DBT developer.
So that's great, and it gives a good insight on what needs to be fixed.
We think the DBT score should be highly configurable.
Namely, every data platform is different.
Everybody has different rules, ways of doing stuff.
So it's kind of, with other linters,
you can define a set of rules,
and then apply it to every project.
But for data projects, it's a little bit different in our opinion.
So this tool was designed to kind of give the power to the user,
and to set it up themselves.
So it's highly configurable with a,
well, we have some configuration in a pie project.
So you can kind of tell DBT score where to get the rules from.
You can also disable rules that are already packaged
in the, in DBT score, but you can also import a package of rules,
and then disable some of them.
Disguise the limit.
Then there's to fill under parameters or configurations,
and that's basically to tell DBT score when it needs to fill with a non-zero exit code.
So this is very useful when you want to run it in CI, for example,
which is what we do.
There's more the badges, so we are using metals,
but you can also use a carrot and stick.
Whatever you want, I don't judge.
You can use anything, but you, yeah, there's three metals,
and then the last one is kind of, well, working progress.
Oh, that was weird. Okay, looks good.
You can also configure rules, so each rule has a severity.
It's just kind of to calculate the score.
It's kind of the weight of the rule.
So that can be configured.
You can also configure some rules that have extra input parameters,
if you have a rule.
Like that.
For example, let's say you want to have a rule,
and it's used in multiple projects,
then what can happen is maybe in Project A,
you want to have a check for like a 300 max lines in the SQL code,
but in Project C you allow it to have 500 or something.
So it's more to kind of configure it more freely.
Last but not least, you can also skip rules based on model metadata.
So what we encountered a lot is that people set to us like,
yeah, but I have all these rules, but they don't apply
through the schema or something like this.
So you can very easily configure your rules in a way
that it can skip certain schemas or whatever models
that start with a Z or disguise the limit.
So let's go back to the example of my chair.
The Peter Rias one doesn't look good,
and mainly the goal is to help the creator of Peter Rias
to fix his model easily.
So let's try to create some new rules,
because I think this is the most important part of DVD score.
Creating rules is very simple,
because we think that every project has a different set of rules.
Probably depending on the company or whatever.
So let's try to fix two of them by creating new rules.
So this is a rule.
It's just a method, that's it with a rule decorator,
and that's basically all there is to it.
So the rule decorator does all the magic,
that you don't have to care about, it was implemented.
It tells the empty score, this is a function that should be a rule
and you need to apply it to all models and or sources.
So the name of the function will be the name of the rule.
The model needs to be the input parameter,
because you make the assumptions on the model metadata,
and it always returns a rule violation or not.
So you're kind of, that's the restrictions of creating a rule.
In this case, we are looking to create a rule to check if the description
is not capitalized, the one, yeah, the description.
So that's line number three, that should be a capital letter.
So what we do is basically we check the description of the model.
It's a property of model, and we check if the model dot description is title,
which is basically, does it start with a capital letter.
If so, then do nothing.
If it doesn't start with a capital letter,
we return a rule violation with a sensible error message.
So this is basically what shows up to the user when linkeding.
So that was easy, this is a little bit more complicated rule,
but still, business logic is quite easy.
As you can see, same concept, we still have the rule decorator,
but this time we configure the severity.
So this is a low severity rule, which means it's less important,
which means it has less impact on the score,
once a model fails this rule, if that makes sense.
Once again, model as input parameter, rule violation,
or none as a result.
So what happens here is that we kind of want to gather all the columns
that are missing a test.
So what we do is we loop over the columns, then we check,
does this column have a data test property.
You are all very familiar with this property, of course,
because you write a lot of tests in DBT.
Then if the column does not have this data test property,
you add it to the list of missing columns,
and then in the end, you kind of append it into one string,
so you can return to the user what columns were violating this rule.
So the user can immediately see, like, ah, okay, yeah,
those were the columns, and I need to fix that.
So I guess creating rules is very simple,
and the idea is that if you use DBT score,
that you create a set of rules in your own project.
So it looks like this after we run DBT score again.
As you can see, there's now more rules,
and Pizzeria scores even lower, because, well,
the metadata wasn't fixed yet.
So it only shows now that Pizzeria is not doing well.
So creating a rule just makes you,
makes it easy to find the problems,
but now it's up to the user to fix it themselves.
I'm not going to show you how to do that.
I think you will be able to do yourself.
But now Pizzeria is also scored as seven,
because now it has more failing rules.
It was rewarded with a bronze medal, which is not too cool.
So yeah, I think the owner of Pizzeria has some work to do,
although we don't know who it is, because, yeah,
it's a model lesson on her.
And in the end, the project also scored an 8.5.
Yeah.
Yeah, so this is basically how a user interacts with the tool,
but we also think it should be automatable.
So you want to integrate it with whatever system you have running,
or running else.
In our case, we like to use it in CI,
because we actually run DBT score as part of the pool request bills and stuff,
so we don't get shitty metadata in prop.
So we use this to kind of run DBT score,
then output it into a JSON file,
and then we can parse this JSON file easily.
We know how it looks like, and then we can just kind of check if all models are okay.
You can think of more ways to integrate it.
For example, using it in a data catalog,
is anybody using the DBT's built-in data catalog?
Yes, some people, okay. Yeah, we as well.
And yeah, we put the badge in there.
We think it's fun to have data practitioners build something,
and a show like the score that they got in the data catalog.
So that's really cool, I think.
Last but not least, we have an amazing documentation website,
so if you want to get started on this,
I literally everything I talked, I said today,
it's also here, but then it written format, and even more.
So feel free to have a look.
You can find links in this slide, if the black screen goes away.
So DBT score, the picking up tech is the documentation website,
and you can find us on GitHub.
Last but not least, we have a group of authors already,
and just so all of you know.
We love working together with others on this project,
so if you like Python, if you don't like Python,
you can also join, but maybe less fun for all of us.
Yeah, feel free to contribute with us.
We like working together, open an issue,
pull requests, whatever, we're happy to think along.
Thanks for listening, everyone.
Thank you.
And I don't know if there's time for questions,
but we'll have six minutes.
So if any.
You talked about links in yellow files.
How do you handle links in the SQL code,
as I think, in our picture,
or the platform, we have to connect to the database,
and then sometimes the code box out,
and the pages from once,
sit back to the files or another.
Okay.
Yeah, so the.
Sorry.
Repeat the question.
Yeah.
So how do we handle SQL Linting?
Actually, DVD score is not meant for SQL Linting.
We, however, use SQL.
Fluff.
As our SQL Inter of choice, but that's, yeah,
kind of a different topic.
We don't expect you to link SQL with this.
But in the static analysis, it's complex.
And SQL, especially with the many different flavors,
you can find some of them are not even free software.
It's still flake SQL, I'm no doubt.
So it's, it's tricky.
Yeah.
Yes.
Yeah.
Thanks for the presentation.
Any questions?
I can select your piece.
What was the question?
Of course, you cannot ask that.
But sometimes it's very straightforward.
Yeah.
I thought it was not kind of nice.
Yeah.
That's a good question.
So the question is, if we plan to add the, well,
TV score fix, the short answer is no.
The long answer is that we use the manifesto JSON
that is generated by dbt parse,
and that it's very hard to kind of move back to YAMO files.
So we kind of let dbt do the,
yeah, compilation from YAMO to JSON or, yeah,
JSON format.
So we don't really know exactly anymore,
but we don't really know exactly where the,
for example, the description came from.
So we're not using the real YAMO files.
We're using the compiled.
Stop.
Yeah?
Thank you.
Hmm.
Now, you can mention you have 150,000 likes of YAMO.
So I'm just, I'm wondering,
how much time this year it takes to let every single model
with every single whole year.
There you go.
What takes the longest is for activity to pass the project.
And for this particular 150,000 trends of YAMO,
I think it's 30 seconds to 1 minute for dbt to pass.
And then dbt score runs in seconds and on top of it.
Yeah, dbt score is really fast.
It's mostly the dbt being the button next there.
We did not switch any performance problems with dbt score.
Not yet.
Any other questions?
Go ahead.
So this one is more of a given dbt score.
Why do you need it in the example?
I should try it out.
It's one of the tools that you have.
Yeah?
I don't quite know what to do.
That's for, oh.
Why do we need a particular rule that every model must have
an exemplary skewed in the other documentation?
The answer is we went to enforce good documentation
practices for the catalogs.
By having an exemplary skewed,
we did use data users, originally an entry point
to even table and how we can start playing with this data.
Yeah, so we expect this is the data catalog of dbt
that we want all our models to have an example
sequel to kind of get you started with the model
that you're looking at.
So actually, there in the description should have been an example
sequel, at least that's our.
That's a GIF ID.
It shows that you can disable or only enable
for the big models depending on your constraints.
Thank you.
Yes.
Thank you.
Thank you as well.
Let's see.
What's the follow-up question?
I hope you get a follow-up question outside.
Yeah.
Maybe.
Is yours?
Yes.
Thank you.
All right.
We're going to the next.
That's a cool five-time time.
Okay.
All right.
Come on.
All right.
It's alright.
No, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no, no
no, no no, no, no, no, no. I call