DQX: Lightweight Data Quality Framework for Spark Pipelines

0:05hey today I'm with Marsen and Alex to talk about dqx but first let's get to know more those two awesome guys so hi everyone uh my name is mar vka and I'm senior resident Solutions architect at Databricks so part of

0:27Professional Services uh we basically deliver uh projects for customers and I'm also working on uh on data big studing uh and I'm the co-author and Tech lead of the dqx project and hello everyone I'm Alex I'm L solution architect at data breaks uh

0:52like Marx I'm working with customers but more before the Professional Services is involved uh beside my day to-day work work I'm closely working on things likeone provider and many of datab project and for those who are watching this session um just fun fact about Alex out I remember when I joined the company

1:15whenever I was looking on before chat gpg uh whenever I was looking for an issue about spark I go to um stock workflow and every time that was Alex who was responding to those questions so uh let's now just talk quickly or like the topic of this session uh dqx can you tell us more uh about this framework and

1:39what was the let's say the objective behind building this open source framework yeah I think I can start so dqx uh is a data quality uh check and enforcement uh framework that uh was built based on the real voltage uh experience working with our customers

2:04so the uh history of project started almost four years ago uh when uh we uh started like to get more requests about like getting the uh clear data for machine learning and other projects so that don't spend too much time on the data engineering uh outside of the

2:26responsibilities and we've identified the number of inefficiencies in the existing uh open source Frameworks like DQ and uh uh Great Expectations especially for the streaming work worlds and uh we started to build uh slowly the functionality and then uh as we get it

2:50adopted by the more customers uh it was expended and uh for the things like uh uh even if you have the uh building functionality like Delta life tables expectations there we still had some gaps like GK is uh filling in one of the besides the data quality enforcement and

3:13like Curren in the B data uh report detailed reporting one of the objectives was like to make the data quality uh very easy to implement on the existing F quote so especially for the Stream and for example this linked to the creation of the components like profiler

3:36that simplifies the uh creation of data quality rules because it's very tuse work to add some more uh data quality rules if you write in them manually but it's much more easy when you get a profiler you identify what could be the unique values what that you have any missing data or outwire and then you

4:00just review the post data quality R candidates and uh accept them and put into your pipeline and then we are product finalizing it by uh uh saving the uh text in the files in the Delta tables there so you can reuse between the multiple pipelines you can organize the uh librar of the data quality checks

4:24they can apply across some multiple and really the we work on some kind C when we had hundreds and thousands of the pipelines applying the data quality rules but also having some unique uh data quality enforcement for specific pipelines and what's the um what the difference uh with the uh Lakehouse

4:48monitoring because I know this also way to be able to track the quality of a model of table is it something that will need to like in addition to Lake housee monitor or is they say competitor like what's the difference between those two Frameworks uh W house so di complement lake house monitoring so we uh really

5:12closely work with lake house monitoring uh product team to intercorporate necessary functionalities that we observing uh by the in the customer requirements and having the uh things that are still maybe not implemented in Lakehouse monitoring uh for things like streaming

5:34work quotes there because uh Lakehouse monitoring uh at some degree is uh working with a whole data set it allows to uh Implement some data quality checks that are such as anomal detect and there that are impossible to or very hard to do with a streaming work quote uh but

5:59really we have cely working to incorporate as much of the functionality into the lake house monitoring and corresonding products like Delta life tables they so in Course contact So eventually we are hoping to get everything uh be a part of the and so far um like the theqa

6:24support python SQL Scala what are like the languages that you currently support with dqx so uh primary uh usage of dqx uh right now is python uh so our like we had the implementation for SC as well it's not here publish be concentrating

6:46on uh python uh some of the components uh like profiler can generate a SQL expectations as well so you can uh profile your uh data set generate a data quality rules candidates and then uh you can plug in like just copy paste the generated code

7:10into your dcq pipeline or streaming table something like that and you get at least use speed up the Sy uh python on other side a bit more flexible when we talk about like meta programming automatic generation of the code ET ET loading the uh libraries of expectations

7:33from the uh Delta tables or files so it's simply not possible to do with the SQL but they can uh easily complement and uh they have the people are apply GLE yeah that that being said uh we do have actually like we do support like SQL Expressions that means you can

7:56actually do the checks using SQL but the execution has to be from python basically so we support Python and kind of signal but the execution has to be from python but you can certainly do like SQL based checks so we just support like SQL Expressions um and what are the

8:19requirement that I need to run uh DQ the dqx for example if I say if I install this like let's suppose I have spark uh on premise I have like all everything installed like just I can install dqx and get started or I need to have a data bre account to use it yeah you actually do need to have

8:42data breaks accounts of all data breaks La for require uh basically it's a Databricks uh license we have datab bricks license that means in production uh uh we you require uh the QX to be run on Databricks uh the installation process

9:04itself is super simple and I will also demo this uh you just need to peep install it so one package uh there is also another way to install it let's say more more as a tool in the workpace uh so using data brick CLI you can also install the QX uh and yeah the

9:27simplest basically you just need uh python uh installed on uh in your environment and just Pi install it and that's that's pretty much it okay and for I think for folks who gonna be watching this recording keep in mind that dqx is a databas lab which means if you're running some issues do

9:49not open a ticket just go to GitHub and raise an issue or you can talk with Alex marstin and all the different contributors to this project and I think you have a demo for us that you can show like how you can get started quickly with the dqx let's do that uh let me show my

10:12screen I won't show everything but I would say the most important things just to help just to get started with the QX uh I hope you can see my screen now yeah all right so um here I am in datab Bri workspace uh and the only thing and this

10:37is basically a data breakes notebook to install the QX I just need to pep install the QX Library uh that's all I have to do actually uh uh inside the workspace and then I just restart the uh python environment and then I can work with uh

10:59with teix uh I can also use it basically wherever I am in the deab B workspace I can use it so it could be also a drop doesn't have to be a notebook so let's start with um profiling so basically whenever you have a data

11:21set uh you probably want to get an idea what is the profile of the data and uh based on that we can generate quality rule candidates so this will be uh so we'll look at the data and profile the data and then generate

11:44quality candidates and those candidates then you can uh you know use directly in your application or you can adjust as needed so um in this example I'm just creating a simple data frame in spark uh so nothing fancy I create the workspace client and initiate this DQ

12:06profiler uh object and uh using that object uh I profile the input data frame and that gives me some summarized statistics and profile this is probably not something you will necessarily need but uh probably need need to use uh but it's there if you need basically it gives you some Statistics over the data basically

12:30just profiling uh uh statistics and based on that statistics then uh using this DQ generator we can generate uh the rules candidates So based on this data I had very simple here it identified that column one uh

12:54should be uh should be not null and if it is not null then we should uh uh uh we should say this is an error shouldn't happen uh and if and column one should also be between one and two this is basically what we see based on this simple data frame uh and and so on right

13:18so it will give me give me the initial set of rules I can use uh directly in my application to do quality checking just to understand the goal is to use a a clean data or a sample of like what the data should looks like and then use this profiler and this will generate the uh all the rules that I need that I should

13:40have exactly exactly this gives you basically candidates that you can use you don't have to but probably give you a good set of initial rules you can include uh it will give you a better idea of you know what is the data that you have and and give you the this initial set of rules checks that you can

14:04use uh so this checks uh we can then save in the workspace uh uh basically with this method uh and then we can use it in the application uh what I want to Al show here this is what Alex mentioned we can also generate DT expectations so uh in SQL python or

14:28python dig so we can just take those and use with dldt directly so basically just uh just take this and use this in in the DLT pipeline uh and that also can save you quite a lot of time uh you know when you have no idea how you know what is the profile of

14:51your input data uh so that's that's uh kind of the uh what typically user would start uh with as a first step profile the data generate the candidates and then they can apply it so here again I have another data frame very simple just to

15:15Showcase this functionality I create this DEQ engine I load the checks that I had previously uh you know generated and I probably have to run it from Total bom just do that again there some inputs I haven't

15:38included all right so now we can see that uh once I load the checks I can then apply those by met the data and split that means I will get the good data frame and the bad data frame uh in this case I name it quarantine so the good data frame contains all the records that have no

16:00issues so we'll just have you know the records that have no issues and the quarantine data frame or bad data frame will contain a reord that have issues in this C this case column free is null and it should be not null uh and this is reported as additional column uh in the data frame so very simple we

16:25get uh we take the original data frame we apply the rules and we report it back with additional cols and we can either split it into two data frames good and bad or valid in quarantine or put it in the same data frame that's another option in this case all the original data will be

16:48there plus the reporting columns so we want be splitting uh this is just to uh run over the data that you have and generate the um and and quality and apply quality checks so this is the you know uh the simplest um way of using uh the

17:11QX uh what we can also do um then if we manually create those checks Define those checks uh we can also validate if they are correct so typically the user profile data would generate the quality rule rules and proba probably also make some manual adjustments maybe you know

17:34update the rules or add more checks and after that the user can also validate those uh in this case we validate the checks and if there is an issue in the configuration uh we can report that back and that would be typically run you know from cacd process uh during deployment uh so when we deploy the

17:57application we deploy the checks uh we also validate that they are correct before we use them uh now there is also a way to define uh checks in the code directly either as the yo file or as uh you know theq classes so let's start with Theo

18:20definition uh it's basically the same what you've seen in the generated by the uh profiler and Generator but we can also Define it manually here uh so I have a few rules here uh I expect column one and column two be not null I also expect column

18:43three to be not null and not empty uh I expect column 4 to also be not n and not empty but also but only for column where column one is less than three so this is also so something can do we can filter so apply the rules for for specific uh you know specific rowes or

19:08uh specific part of the data at the input data and this is uh basically a SQ expression here uh and so on we have about 25 rules at the moment you know uh and we are as we are getting feedback you know we are implementing more uh uh more of this so

19:32expect you know to see see more of this and these are also documented you know in our uh documentation and how we use it is exactly the same I I have some input data frame and I can of course load it from table or I can load it from pu uh then I apply the checks so I

19:57Define those checks here then I apply apply those on this input data frame and I get two data frames back or one data frame back depending on the method I use and and then here we see the results right so we have some errors and warnings uh and there is basically

20:19distinction uh between errors and warnings so when you define criticality as warning it means it's just a warning that means uh we will include this record in the good uh data frame and when it's Error we will only

20:42include it in the quarantine data frame so this is how you can basically Mark uh Mark uh certain issues as just informational as and some as uh important and uh that should never be put uh to the output uh in this case we have two issues you know for one one row uh

21:07column two and you know is uh is null and column one uh is uh not in the allowed list uh so should be one or two and uh it has some different uh different value and um then we can also Define the rules as theq ex classes

21:29so uh very simple uh we Define this DQ rule uh check uh we provided a name which is optional can also skip it provide criticality and what kind of checks and for what column you want to make it uh and this is basically the same uh that you've seen uh in the y definition

21:53just as code uh and it's really a user preference which method to use and how we apply the checks is exactly the same just use the apply checks and spit or P checks uh and uh and then again you get this data frames

22:15back here just a little little bit bigger in know example with more checks uh so how you would typically apply uh these checks in the Lous you know medallion architecture I would say majority of cases would be to do it between bronze and silver so as you processing

22:37bronze you would like to make sure that um uh the quality is uh correct and you don't want this bad data to be uh persisted in silver so you typically do it there uh so this is just an example and here we are using uh new yor taxi data set and uh we are using you know not we are

23:02checking n no also some you know values and here is uh an example of SQL expression for example where we compare two columns so pick up date time should be greater than drop off time uh as an example uh and this is an expression so I can put any AQL kind of expression

23:24here and uh so when we read the data this is like a let's imagine this is like a bronze data from the bronze layer uh we apply some Transformations we do the quality checks uh as before and this is the uh the good data

23:46frame basically the silver data and this is the Quan timee data set and we see there are actually quite a lot of issues found in this data set uh as per our checks and then we can write you know this uh this will be our our civil data set that we can write out uh and quarantine is probably something we

24:09would persist in a different place and investigate uh the issues before you know we can curate maybe this data and reest the silver all right so uh there one more thing I want to uh cover uh here is to Define your own checks uh this is quite powerful in dqx

24:33so you can Define custom checks so if so I mentioned we have like a lot of rules standard rules already implemented in the QX but if there is something missing um first of all we would encourage you to create a pro request to add those uh so that more people can benefit but uh if this is a rule uh just

24:58for you like a special you need some special conditions uh you can Define your own checks uh and you just have to make sure that uh you return this make condition uh and you can Implement your own logic in this case we check if the column ends with full um we don't have this check you know in

25:21the QX so it can uh implement it yourself and then uh we can apply this directly so you can see here uh we use check ends with f ends with f uh is Ry our Custom Custom check that we defined here uh and then we can apply it the same way as

25:44before apply checks and uh yeah we'll have a custom rule uh implemented and here another example of SQL expression so you have the choice of either creating this reusable function the check or you can create a SQL expression uh of course with SQL expression it's probably something that

26:07you have to you know if this is something that you want to reuse you have to kind of copy and paste while this function you can reuse um but these two options exist uh it's quite quite powerful uh last but not least uh really just just the last thing here I want to cover uh today is to that you can Define

26:32your you can customize certain things for example you can customiz the customize the columns right uh that you want to uh report the issues in uh so it doesn't have to be just underscore errors you can actually Define your own uh your own uh things there and if you want to know know more uh we

26:56have if you go to GitHub page you'll find a link to the documentation and you'll have like a user guide which explains pretty well how the QX works and can just follow along we also have deos uh the one I've just showed is available here as well so

27:18you can download it uh you can basically just uh import it in the in the notebook in the Databricks as a notebook directly and try it out all those links are going to are will be in the description of uh of this this recording uh I think it's super super

27:41useful and I I can I I'm already starting thinking about like the next maybe the next step would be maybe to have like a dashboard like thinking of all those metrics and Records can be stored in a table and then display them over a dashboard can even like put me some alerts like in case you have an outlier or something that send an alert

28:03to web hook or whatever uh you can think about uh yeah it could be built it's really one of the power thing powerful thing for the GK that you get a very detailed information about which Tex failed and why it's failed so basically what kind of because uh uh I have had a

28:25customer with a s framework they and then you have a table with a 2,000 columns and you have 10,000 data quality rules applied to this table uh you need to understand what really fail because it's not simply just is now of specific call or something like but could be very complex uh business uh rules especially

28:48like when you have a rules of covering multiple columns and then we can have a really like can build a de board say like how many uh rules are failed by on day or which world for example I I worked with one uh car manufacturer in Germany for example we built the

29:11dashboard where each uh open of the uh Pipeline on specific Factory inside the company can go look into the like how much data because was really very difference in the data quality between the different factories uh but the was important that it was not simply just okay we have clean data or

29:34something it was possible to identify the machines that were misconfigured so basically the people we can track uh that okay for example the date uh and time is set incorrectly on this specific machines it's reporting this and then we can say okay like somebody needs to go and fix the settings of this machine for

29:57example because it's it's not only the uh uh data impact it will be also the impact of the part that I produced this machine examp and this was really possible to do with GK functionality uh just maybe to add to this as well uh we do have actually like a simple dashboard uh this is something we just start with

30:20a few weeks weeks back uh if you install tqx in the workspace via data pix I so basically just Databricks Labs install the QX it will install like a profiler drop you know it will install dashboard uh uh this is a very simple

30:42dashboard at the moment but this is probably a good starting point if you want to build something more sophisticated uh you can use that as a starting point as well and will be you know uh extending the dashboard certainly uh in the next uh relases that would be amazing but I mean

31:06I'm super excited by this uh by this uh this framework and um it just shares with as many as as many customers as possible and should in so they can give it a try because like I've seen a lot of people like having lot of data but they keep struggling with the quality and sometimes some other framework are not

31:28good enough and they start doing this DIY and the goal is rather than implementing something that already has been done wiin then the will just use something uh like such as your framework and uh they can like gain L of time and focus on what bringing the value rather than just doing some task to clean the data and make sure that it's good and

31:50ready to be used thank you I think also what the Q probably out is uh Simplicity uh if you take you know the other Frameworks Alex mentioned a few you really build like another project and sometimes it's bigger than the

32:12actual business logic you know and it becomes really really big and huge uh so we focus really on Simplicity making it really easy for users to use and I you know uh what whatever functionality we are adding we try to keep it as simple as possible because uh we know from

32:35other projects this can quickly uh become very very complex uh and on the same topic as well so one of the uh goals of D was to uh enable a non engineer to apply the data quality to Z data and really always a

32:58bit of like not a TI off but uh similar things like uh data openers very of their business uh people but they know they data domain much better than the developers and like uh as we need to have the developers working closely with this people but the all Sites could be

33:22very busy there or we can give something like selfservice and then the people and easily uh add the data quality to the pipelines and what we have seen very often is a lot of functionality UK field that we can integrate it with a uh what I call metadata driven uh pipelines when

33:44you have like standardized pipelines but maybe the data quality rules could be slightly different they give this uh uh framework to people who are not Engineers but they can quick customize and start to uh get the clean data for

34:06Downstream all right that's perfect anything else to add Mar um no I just encourage people to try it out uh I think it's uh uh yeah it's as you very simple to install just try out impo uh I would probably start with one

34:28of the demos uh you know get a feeling of the two and uh yeah if you have any feedb uh we would love if you you know report any you know any new ideas or any you know feedback uh in uh GitHub we have discussions and we have issues I think

34:51it's a good way to to interact and uh yeah happy to see more people using the C perfect thank you so much Martin thank you Alex and maybe see you for the next release yeah thank thank you for having us