Proactive Data Quality Management: Implementing Automated Checks with Akshay Jain at AIE 2024

Everyone. I'm, I'm a data engineering manager at the Unic uk. It is a company that provide a venture capital to the, uh, for, uh, to the organizations that required a fund to make their growth.

Uh, I work over there as a data engineering manager and driver three kind of projects. Uh, one project is a kind of data migration project. Second kind of is a data architecture related projects, and I'm also helping them building the AI related capability for the topping modeling and the related activities.

Now come back to today's topic. It is a proactive data quality management, implementing, uh, implementation using a DQ and the machine learning techniques. So in today's presentation, I'm just going to share some of my past experience in terms of how we have implemented the data quality checks for the large batch processing systems, and what are some of the things that we have considered over there while doing those implementation.

Uh, I'm just going through walk you through some of those examples over there. And, uh, we will be, uh, going further in the session in that particular manner. Now, in this particular session, I expect that, that the people have a basic understanding of how big data processing works, how the spark job works, and you know, the data get process in a big data processing pipeline.

And with that assumption, you guys should be able to understand, uh, each and everything. What I would be explaining also, I would be, uh, using some of the statistical techniques over here and some of the algorithms. So if you have those kind of, uh, knowledge around that, it would be good for you.

But if you don't have still, you should be able to understand, I guess majority of the stuff, what I'm going to say about. So yeah, let's begin and let's go ahead in case you guys want to connect with me further or anything, then this is a QR code, scan it and, uh, get connected with, with me on the LinkedIn and would be happy to connect with you over there for any, uh, particular queries or question you might have. So, yeah, uh, going into this particular one, uh, in the organization generally we have some kind of a data process flow where the initial stage start with the data generation process where there would be some kind of, uh, source systems who are generating the data.

This can be some kind of, uh, transactional systems in a place. We would be having some kind of legacy data. And what we generally do is, as a part of a data processing methodology, we acquire that data into the ization layer by having a staging layer or something like that.

We acquired data, we make a respective transmissions, whatever required, like if it's a batch data we take as it is, if it's a realtime data, then we need to do the stream processing and take that data accordingly. Post that, we generally apply some kind of data pre-processing as well if required in order to make the necessary changes in the formats or, uh, storage structure to optimize the processing or something like that. And we store data over there.

In the data position layer, most we have a data and a data position layer. We generally go for a data processing layer where basically we implement our business lodging to calculate the data in a certain way, in a traditional way. It's a more kind of a dimensions and fact related thing.

But in a, in a big data processing, we do the similar thing, but the structure in the model become different. But essentially in this layer, basically we do all the business related data processing and do all the data manipulations. Also in this layer, we sometime implement a machine learning or AI models to meet a specific business need.

And sometime we create a layer of a data warehouse and the data Mars and the things like that for the data distribution purposes. Once we have this layer, then in the end we have a reporting or analytics use cases where generally we expose our data to a user or a reporting or analytics purposes, either in form of some self-service BI thing, or a visualization kind of, uh, dashboards and the things around in that way. So this is what the traditional data process flow looks like in most of the big data projects.

Now, in this particular data process flow, what generally happen is that, uh, in the data position layer or in a data processing layer, we generally see some kind of data quality issues, uh, while, uh, processing the data. And that particular data quality issue might impact your reports heavily, uh, and cause issue with the analytics use cases, which might have a potential impact on the company's business as well. So let me just explain you two of the use cases where we have a data being processed and that data has a core implication on how the company generates revenue and why we need a data quality checks over there in the first place to resolve that particular thing.

So the remaining presentation make more sense to you. So in our first use case, I used to work with an e-commerce company where that e-commerce company used to crawl and scrape, uh, 8,000 plus websites. It used to crawl, scrape it's data.

And that data is something that used to get processed by a different teams for a certain business use cases. So there are seven to eight different teams who used to take this crawl and scrape data, and they used to process it to meet certain business requirement. And post that, that data used to go for, uh, reporting purposes and got sold to the external parties for the data consumption and the related use cases.

Now here, the problem was that, that all the data that we are getting that is coming from a crawl in the script processes and purchase data from the offline, you know, parties and the competitors like that. So in those kind of use cases, it's very difficult to say whether the data quality is good or not. So when your crawler running continuously, how we can competently say that, that yeah, our crawler is able to, uh, crawl all the processes with the success and there is no issue happening in the crawl in terms of, uh, uh, some security changes on the company's website or something like that.

Similarly, the second thing is on the scraping side that how we can see in a timely manner that our scrape processes are not working properly. There are chances that the company changed the website layout, and in that case, we are not able to scrape the data from it as for the defined configuration and that process break over there. So in those kind of cases, uh, if the data is going into the continuous crawl manner, it's become difficult to identify those particular problem.

And if that data, again, move out for analytics purposes, it has a direct implication on the business in terms of a bad data. So here we come across a need where we need to implement, uh, some data quality checks at each and every layer. Seeing that, that each of the processes is generating a data is expected and that data is moving to the next layer.

And if it is going into each layer in an expected manner, then we are good to expose that data to end user for the analytics use cases. So here you can see that business value proposition where the data quality was a core part to check the data first and then make it available. Now, previously this data quality checks used to happen manually, but as the website got increased at a scale, that validation was not possible, and hence the need come to implement some kind of framework which help us validate that data in a automated manner so that we can publish the data sets, uh, as in when we get it generated and publish it to our end user.

The second use case was, uh, very similar to that line, but the domain was different. Here we are working with one of the financial company, which is one of the largest FinTech company, and it basically do the credit and debit card transactions. So our goal was to have that credit and, uh, debit card transaction stored and process it in a certain way.

And after that data get processed and before we make it available for the analytics use cases, we just need to validate that that whether that data is good from the quality perspective or not. So as the company's 5% revenue was dependent on this particular analytics data, uh, from the business perspective, it was very critical process from them to ensure that the data quality is good and then make it available to the business user. So here again in the big data batch processing, we need to implement a similar kind of, uh, use cases and the developer framework, which help validate the data at a particular stage and then make it available to the analytics use case if nothing goes wrong for that particular data set.

So the other kind of business use case we were trying to solve, and in order to solve this kind of business use cases, we basically come up with a framework, uh, where we have implemented those data quality checks in both our data equalization and the data processing layers. And in order to implement those data quality checks broadly, the three kind of mechanism is being used. The first kind of mechanism was purely around a rule based validations.

So if there are certain business rules which we want to validate before checking the data, then those kind of rules needs to be required over there. The second kind of, uh, validation was more on the kind of anomaly detection based validation where we want to check whether there is an abnormal spike in the data or abnormal, you know, uh, lower, uh, down in the data where the data is getting decreased drastically for a certain columns and things like that, and those kind of things. We want to just validate using this particular data quality framework.

Now, in order to implement this particular data quality framework in this particular way, we have approached it in a three steps where in the first step what we have did is we have just tried to understand that in which data set, what attributes are important to the business and what attributes are good from the reliability perspective. Uh, because the, when we have started developing this framework, one of the thing what we have face is that we have a very large number of data sets. And for some of the data sets, we don't have a clear domain knowledge in place that what each and every attribute represent because the attribute count was also very large under those data sets.

So in those kind of scenarios, how we can say that, that, you know, what are the critical attributes or what are the reliable attribute versus what are not the reliable attribute from the business user's perspective? And we need to get some feedback from a user to say that, that, yep, these are the attributes on which we really want to implement some kind of checks and we want to create an alert on it in case something goes wrong. So our initial, uh, thing started with the data profiling where we have created a process to profile each and every data sets that we onboard.

And based on the historical data trend, we provide business a recommendation that these are the attributes which are more reliable to use for the reporting purposes. These are the attributes where the data may or may not be available based on historical data. And those kind of confidence score is something that we have provided initially as an onboarding of that particular data site.

And based on that feedback we have received, like what are the actual attributes on which business want to use and they want to implement the alert and the related mechanism to take a respective call on the support related activity. Later in the phase, in the second step, we have tried to identify like how to implement now the anomaly detection techniques or some kind of validation techniques on this particular identified attributes and how to make it available at a scale as in framework so it can get validated in an automated manner and its result is something that can be stored somewhere. And after we have this particular, uh, attributes getting validated, the third step was to set up a functionality where end user can configure the LRM depending on whatever threshold they want to support in case of any anomaly to move it further.

So in this particular way, we have defined this three step process, and in that three step process, we have basically approach to solve the data quality related problem. Now in the first step when I say we want to perform a data profiling for that particular purposes, we have used one of the tool or a library, which is called a DQ library. And this library is something which is developed by Amazon developer and it is available in the open source where it is used for primary, the data profiling purposes and to implement a unit test kind of thing on the data.

Now this library is available in the both, uh, Java Scholar and uh, Python version. So you can use with either PI spark or uh, scholar spark frameworks and you know, you can use it accordingly wherever you need. Uh, and it runs on the big data platform using spark computation.

What this framework basically provide is this framework provide us a mechanism where it basically perform all the aggregation operations on the data sets and on those aggregated, uh, numbers, it also provide a functionality where we can implement some of the statistical functions and we can implement some of the rule-based validations to perform the unit testing kind of thing on the data when it's flowing in the pipeline. So this is a framework which is being, which we have selected because this is a complete need of creating a module where we need to compute all the automated metrics and the things like that and move it further in a certain way. So let's just take one example and let's just understand how this, uh, data profiling can happen with the DQ or how it can help us automate some of those activity around the data profiling and implementing the unit testing kind of thing with an example.

So in order to explain this, uh, particular detail, what I'm going to do is I'm just going to take an example of, uh, uh, one of the data sets, okay? Now, before going into that particular example, these are some of the metric that we can calculate on the data set attributes. Like for example, if you're producing any data set and if you want to know that what are the number of record we have in the data set, or the particular attribute in the data set, how many distinct value we have, what is the minimum and maximum value we have for this particular attributes?

What are the distinct count we have? What are the unique value we have for that particular attribute? All those kind of metrics, whatever is displayed over here in the screen, those all can be calculated with using this particular, uh, DQ library.

Uh, and in that way it'll basically save a lot of time to write those kind of codes and, uh, do and maintain it in a particular way. Now, let's go and take one example to understanding in a better way how it works. So here is an example of one of the data set.

Well, let's say we have a data set of a movie from the imdb and it has an attribute, like let's say ecos is a primary key on that particular data set. And along with that, we have some other values like title type, uh, start, year and year, um, number of words, average rating, et cetera. These are data set we have now, let's say on this particular data set, we want to identify details that whatever data has been published today or whatever new movie review that has come today, what is the kind of, you know, uh, data looks like, uh, on this attributes in that particular data set.

So what we can do is we can just write a very simple spark code, but in that spark code we can just, uh, use this function like analyze runner, we pass our data frame to it where we have all the data, uh, for this particular movie data sets, uh, uh, for this particular movie data set. And then we can just add like whatever you wanna compute, like let's say we want to take a record count of that particular data set. We want to identify the completeness of attribute average rating.

Completeness is basically the ratio of, uh, uh, null values to the total record, uh, like normal values to the total record count. So you may want to identify like what is a completeness or a coverage we have for that particular attribute. We can identify like how many, uh, what is a uniqueness of this particular column.

So if there is some column where you are expecting, uh, primary key kind of thing, then you can just validate whether the value in this column is unique or not. You can take for some attributes the statistical values, like what is, is the mean of this column, uh, average rating, what is the standard deviations and things like that. So you can just, uh, calculate those details directly over here with this function.

0 or you know, what is the maximum and minimum value. So all this kind of thing, whatever you want to validate at attribute level from the metrics that is displayed over here in this particular slide that you can just compute over here by just, uh, using this analyze runner function on that particular data frame. And as an output, it is pro, it is going to provide you one, uh, data frame, and in that one you would be able to see the details like for those particular attributes, what are the details you, uh, what are the count you have for that particular dataset?

Like for example, we can see over here that for this particular dataset, the record count is this much, or we can see the details. 85%. The average rating attribute is something which is available for the, uh, data set while almost around 86% of the time the value is null for this particular column.

So we can get this kind of insights and this insights basically help us identify that what is the characteristic of the column. And if let's say on a day-to-day basis, we are seeing that, that this completeness value is, uh, uh, changing a lot drastically, then maybe we can see that, that this is the kind of attribute which is not reliable to get a value on a day-to-day basis. So it may not be, uh, highly appropriate to use for a reporting purposes.

It's good to have a inform metric kind of attribute, but may not be good to have some kind of, you know, primary attribute for a reporting or a filtering purposes or something like that. So those are the kind of, uh, identification we can do and those kind of, uh, connection we can just leverage by reviewing this particular profiling data. Also, along with this thing, it is also providing us a detail in terms of, uh, for a particular, uh, rating, what is the mean rating and what is the standard deviation over here?

3. So based on that, we can just identify that that in which particular portion of the majority of the reviews and ratings are coming. And that will just get us provide some more insight into it.

Also, we get an information over here like, what is the maximum and minimum value for that particular column. So this kind of value can help us to identify that, you know, on a good day how the range of value look like for a particular attribute. And we can define maybe some business rules on the top of it that if those values are going off, then we may want to send an alert to a user or take a respective action over there to resolve it in a certain way.

So these are the kind of, uh, things we can just do with the, uh, this particular data quality framework, uh, like this DQ library and we can just, uh, profile our data in a certain way. Now, after profiling this data, if we want to implement some kind of, uh, unit testing on our data, well, let's say we want to produce a result and we want to say that that, you know, I want to know whether whatever data has been processed in that, uh, data, we, let's say expect only ros or we expect that you know, that the ID column should never be null or ID value should always be unique. So if you want to implement any those kind of checks, then we can just use this verification sheet function where we pass our data frame to it and we can just, uh, do this kind of validation by using this particular DQ library.

And it will straightforwardly provide as a detail that whether today's data set, it's satisfying this particular, um, rules or not. So for example, let's say if ID is a unique column and we are expecting that ID to be uni, we can just write this particular check, we can check the output of this particular validation, and based on the validation research, we can either decide to publish this data set for a further processing or we can reject and create a tickets or LM to process a data in a different way. So in those particular manner, we can just create very quickly or data validation rules by writing a, uh, unit testing rules in this particular manner, uh, whether you want to perform some kind of constraint checks or a non-negative checks or do some kind of comparison to see whether the value belong to a certain, you know, value range or not and things like that.

So all these things are possible over here, and with that we can implement this kind of, uh, uh, unit testing in the DQ framework. So this is a core functionality of a DQ framework, and in this way it's basically help both from the profiling purposes and also from the purposes to implement the unit testing with a very minimal amount of code. And by using the buil library where we can just do all this kind of validations in a quick manner.

So this is one of the way where some of the automation can be happen to automate this kind of data quality checks by writing a BQ rules in a certain way over here and then leveraging it in your data pipelines to validate the data, uh, as in when you are doing a data processing and after that, doing a publishing kind of operation. Now the second kind of, uh, validations which can be happen, uh, those comes into the anomaly detection way of doing things. And in that, basically we can do the validations in general either by using some of the statistical methods or by using a time series algorithms.

So in our use cases we have used a statistical method a lot because in our use case we don't have a reliable historical data to use. So initially we have started with the statistical methods and then we move to the time series algorithm after the, uh, six to seven months of time once we have, uh, some reliable data to build a model on from the time series perspective. So let me just give you an example and that example make going to make more sense to you in order to understand like how we are, uh, using the statistical methods and the techniques to, uh, identify the change in our data sets for a certain attributes like record size or like the record count or a coverage kind of use cases.

So let's assume that, you know, we are doing a crawl of a certain, uh, websites and in that call we are always seeing that the website has a web pages in the range of let's say 10,000. So always, let's say whenever I go to that particular website, what I can see is that on that particular website, we always have a 10,000 pages over there, something like that. So anyway, we are gonna perform a crawl activity on it in our dataset, more or less, we are going to record around 10,000, uh, records for that particular website.

So over a period of time, let's say we are always seeing this kind of variation, well, let's say sometime company add some product, sometime company remove some product, but overall the range remain around 10,000 numbers. So over here, by taking the aggregated count of this particular records on a day-to-day basis on that particular website, what we can do is we can just identify the change that was a change happening, the over day on, uh, this particular record counts or page counts on that particular website. So when I'm going to calculate that rate of change that how the page are changing on that particular website, I would be able to always see that, that that change is always going to be oscillating around the 0%.

So it would be either going some more record than 10,000 or less record than 10,000, but it would be always oscillating around the zero and it would be going in upper or lower direction in a way with some expect being some, uh, special seasons or holidays or something like that. So it's always gonna be a graph like this where you, its meaning is going to be a zero around zero and there is going to be a certain deviation in terms of how the changes happen over a period of time. So based on this kind of chart, what we can derive is that if we put this now numbers onto a, then we would be able to see that, that for this particular kind of data, we would be always having, uh, change around 0% with some standard deviation.

4%. So almost 90% of the days you would be having a change in that particular threshold while there would be some days where the change can be higher or very lower. 5 standard deviation.

And if anything is changing beyond that range, I would like to raise an L RM to see that, you know, uh, maybe something is not working or there is some, uh, failure happening in the process where it is not able to, uh, patch the data or processor data in an expected manner. So if you want to just track your record count, and if you have this kind of use cases, then maybe like this is one of the mechanism that might work for you to, uh, apply the record count variation where you can first just do the data profiling using a, the two kind of library or by using your own computation numbers and post that you can just use this kind of statistical matter to identify the change and whatever transformation work for you. And you can just, uh, uh, define your threshold accordingly to raise an anomaly alerts and things like that.

The other way to implement the similar kind of use cases is that, that you can use some of the time series based algorithm over here and using those time series algorithm, you can just predict like what is the forecasting value that has to be there for the next day or for the next, uh, uh, week or something like that. And if that value is matching with the actual value and that threshold is in an acceptable range P values, then you know, you can just move further with those kind of implementations as well. So this one of the methodology that we have used initially and then we move to the more arima based kind of a time series framework, which has basically worked for us to identify the acceptable range and the threshold and to control and identify the, uh, alerts in our data processing.

Now, uh, the, on the similar side, I have one more example where in that example we are, let's say validating a completeness variation. So let's say we have a certain business attribute, and for this business attributes is critical from the reporting purposes. And what we wanna ensure is that if this particular attributes value or the recording that particular column, if it's going above a certain threshold, uh, going below certain threshold, then we may want to flag, you know, some gap in the process and we want to do a double check over there in that case.

So in this particular scenario, basically we have computed, again, the completeness metric by using a DQ framework. And when we have projected that co completed metrics across a day, the graph come out something like this. Whereas per this graph we always see that, that generally the completeness metric is around 95% and then it is oscillating, uh, in a certain range around it in that particular way, as you can just see in this particular graph.

So if I go and if I just compute the absolute change across a D for this particular metric, and uh, if I just, uh, plot a graph along it, I'm just going to get the details like this, uh, which where I can see the changes zero and it is oscillating around zero on the completeness metrics. 4%. 9%.

And if something going off from this range that there is an issue with the one of the process where this particular attribute is not getting computed as expected. So in those cases, you can just identify those kind of rules and alerts and you can implement, um, alert Sonic to go further on that particular side. So in this particular manner, you can just, uh, implement those things.

Now Jo, if you want to advance it further, you can just use the advanced checks, uh, using, uh, some of the time series maps like, uh, moving everything exponential smoothing or a RIMA models or a hot LERs model if you have a seasonality kind of factors in your data set as well. So you can use those particular models and you can use it, uh, either you can build those model or if you're using some kind of cloud modes like, uh, uh, AWS anomaly detection model, which essentially use this Hot Winkler matter over there for a prediction purposes. Or if you are using some kind of tool like promo or something that basically support the time C is related algorithms as well.

So you can just publish your metric data over there and you can just automate it in that way as well. So in this particular way, basically you can implement your animal detection checks as well and you can perform, uh, data validations and, uh, respective thing, uh, as you go further. So yeah, that's all about, uh, this particular talk, but I just want to cover a brief overview about, uh, what, like what is the kind of data quality related problems and things can happen and how basically we have sorted out and work on some of these problems over there.

Uh, if you like to learn more about it or if you'd like to discuss on this particular topic, uh, feel free to connect with me on our LinkedIn by extinct this particular QR code, and I would be happy to talk more on this or, you know, partner with any of the ongoing work or implementations that you are having on the signed over data quality checks. So that's all from me. Thank you everyone, and thank you for, uh, listening to, uh, my presentation.

Thank you.