Data Observability and Data Quality Testing Certification Series (Part 1)

00:00:00

Ooh, Erica, I almost forgot to record. So there we go.

So, hello everyone. Um, my name is Chris Bergh, um, and I'm CEO of a company called DataKitchen. And so welcome to our four part, uh, data Observability and Data Quality Testing Certification series. Um, and so today actually we've had, uh, 243 people sign up for our class, uh, from over a hundred companies in 35 countries and in six continents, which is, uh, actually very, uh, I thought was pretty cool.

Um, so I, I'm Chris, uh, run DataKitchen. Been in the software and data field for a long time. Um, I'll, uh, kind of, uh, come from a technical background and, uh, Eric, say hi. Hey, everyone. Eric Geer Brooks, one of co-founders of DataKitchen. Uh, I run managed services over here at DataKitchen, and I'll be taking you through the second half of the session today.

Nice to, nice to see y'all. So what are we doing here? So, uh, our, our goal is to, um, and let me, I'm just gonna go through some slides here. I'll turn off my camera so you can see the big screen. Um, and our, our, our goal here really is to have, talk about what it means to do data observability and to do data quality validation testing.

And we're doing it in the form of a certification, and we're doing it kind of in two parts. One is the, or we're doing it in four sessions. Um, and really sort of two ways to think about it. One is that we're gonna use our, um, uh, our open source tools as a way to teach the ideas.

And, and really this is an ideas based, uh, presentation. Um, we're gonna talk about different ways and different tools to accomplish the same goal, but that's the goal is sort of, uh, for you, for you at the end to understand how to do data observability and how to validate data quality. And so we've broken it up into four sessions, uh, over ev every two weeks.

And just as a background, we're, we're gonna re record every session. Um, we're gonna share the slides and we're gonna post them. Um, and then in order to get the certification, you actually have to answer 10 questions, uh, at the end every time. So you're, you're welcome to audit and not answer the questions.

Um, uh, you know, the certification is something that will, will mail to you at the end. So, um, it won't be, I don't know if you can use it for college credit, but like, uh, it, it, it, it's pretty in depth.

So we're gonna go through four sessions and they're kind of broken out. What I'm gonna talk today is about the theme of each one of these, uh, at least our first three sessions. It's really kind of use cases for data quality testing and, and, and data observability. And then the last session is really thinking of it more holistically about roles and managements about, uh, a maturity model.

About benefits. So really the first three sessions are kind of, uh, use cases. And, um, so what I'm gonna start off is sort of talk about today. I'm gonna go through those use cases in general. So there's gonna be about 20 minutes of me, me talking. And then, uh, Eric's kind of gonna go into this first use case, sort of data testing, kind of pre-production patch pushback, or pass it on as Eric says.

So that, that's our topic today. Um, and so the, the first section is kinda like, well, why bother? Right? Why, why do you want to care about this? Um, and so, you know, uh, we've been in business about 10 years, uh, a profitable company, and we've really been focused on, i, I, I think one problem, which is waste and, and data and analytic teams.

I think there's a lot of wasted time and energy and trust and how we do our work, uh, collectively as data teams, analytic teams, data science teams, mainly, it comes in to project failures, uh, errors in deliverables, lack of data trust, and, and, and in general sort of unhappiness of, of data and analytic teams and, and to all those things, I think there's kind of a core reason, um, that we're gonna talk about in these next few slides.

The, the, the first is that there's just little boxes everywhere. You've got little boxes of tools, uh, acting upon data, ETL tools, BI tools. You look at a, a data architecture diagram from 10 years ago, from 20 years ago from today. There's just little boxes everywhere. And then there's sort of team organization boxes, right?

Uh, we, we have data teams, we have BI teams, we have data science teams. There's hub and spoke. They're centralized, they're meshed. And then, of course, as everyone knows, there's just lots of data, lots of, uh, lots of data happening everywhere. And, and so the result of this is that there's, we have a lot of waste and failure and poor results.

And the way we look at this is, it's really comes from a, a bunch of dis different sources.

00:05:00

One is that we are getting sort of bad raw data, or, you know, erroneous raw data that we have a lot of tools that we, all of us, as collectively have too much to do, and that our customers are, are, are seeing this, the strain. We've got custom visible errors or unhappy customers. And, and I think all of this is, uh, from a, a big theme, a 10,000 foot theme.

It really comes from focusing on what I call day one tasks, our immediate tasks, building with individual tools. It's about getting our job done. And not to say that there's anything wrong with that when we're, we're so stressed. But the, the theme here that we're gonna talk about is really focusing on day two and day three tasks.

Once you've built something, well, how do you run it so that you don't have any problems? And then day three, how do you change it, um, so that you can respond to customer requests. So, so a lot of our focus today is really on what our, what you call day two and d day three tasks.

And, and for us, we've, uh, written, uh, a couple books. We've been talking about this idea of applying agile and statistical process control and lean techniques to data and analytics for a long time. And, and they, they really come down to three points. One is that if you can decrease your errors in production and increase the rate at which you can change production, the amount of waste goes way down and your productivity goes way up because you're delivering things in smaller cycles.

Um, your customers are trusting errors and you're not having to rework things. And so, um, where we're gonna start, really, is to focus on day two, which is decreasing production errors. And that's kind of in general what we mean. Um, and, and so as part of it, just the last month, we released kind of two, uh, full complete open source data observability products.

And, and we're gonna use those as part of this, um, part of this certification. And so, sort of why, why really focus on day two? Well, you know, we build these systems, right? They have data, they have extraction and processors and databases and dashboards and predictive models. And in general, uh, you know, I know Eric knows this and, and because he's been doing it and, and I've been doing it, what can go wrong will go wrong, right?

Things will break, you will get bad data, or your data's perfect. Something will happen in the processing of the data. Something will happen once you integrate it with other data sources. Your dashboard or model may be wrong, and all these cases where things can go wrong. And, and so I guess the biggest theme that we're talking about is don't live with this.

Um, uh, be able to find these problems before your customer sees them. You don't have to live with reacting fast and being passive. You can actually be proactive and build systems on top of this so that you find problems before your customers. And so, um, I dunno if you're a, a lord of the Rings fan, but, but, uh, uh, what we're gonna talk here is like, so you don't wanna live with it.

Well, what does that mean? So I think with data observability and data quality, one does not simply check anomalies. And so what we're really gonna talk about are these five use cases. Um, and so, uh, they have to do with kind of the life cycle of how you deal with data. And so let's, let's, there's another slide where I talk through this.

So the first thing is, you get some new data, and maybe you have a whole data analytics system production, or maybe it's your first time building it, but you get a file, um, or you get some tables. What is in there? How do you understand it? Is it good enough to do analysis? And we're gonna talk about that today.

Um, and then the second part is, okay, I've, I've taken that file, I've evaluated it, I've put it into production, now I'm getting updates to it. I'm ingesting new changes to that data. Um, and then how do I tell if it's right? How do I make sure that there's no problems in it? Um, uh, you know, an example of that is maybe I'm loading data from my CRM system every 10 minutes.

Um, maybe I'm doing some weekly GA data loads. How do we actually tell if that's still good stuff to do analytics on? And then second is like, well, I'm, I'm producing reports or dashboards or a warehouse or, uh, some predictive models or feeding some other system. I've got a multi-tool, multi-step, multi hop data and analytic production process.

And so how do you run that production process? Like a good manufacturing line? How do you not, how do you produce Toyotas and not a MC Pacers from the seventies? Um, and then, uh, uh, uh, we're also gonna talk about the development process. How do you actually do, uh, when you hire that smart 23-year-old and they, you want them to fix that one line of code change, how can you build a system around that 23-year-old?

So if they make a small change, that person can immediately see the impact of that change in a bigger system, uh, in development, uh, during deployment, uh, before their customer sees it.

00:10:00

And, you know, they may be adding some Python or SQL or yaml. And then the last use case we're gonna talk about is, is everyone is always, you know, we, we go through, uh, very fashionable builds, you know, uh, from how we do things. Uh, you know, five or seven years ago, everyone was doing Hadoop and data lakes.

Now everyone's doing sort of, uh, you know, sort of Snowflake or lake houses, and we're constantly replatforming. So how do you actually do data migration? How do, if you've got a system in production and a new version of it, how do you actually do observability on those? So we're gonna talk through those five cases, get some new data.

Um, it's already in production or it, it, it's, it's changing all the time. I've got it in production. I'm gonna change my production, and I'm gonna actually move my whole development stack to a new system. And so we're gonna talk through those cases. And, and so what I'm gonna do now, uh, in the next few slides is sort of talk through each one of these cases.

And, and I'm gonna talk about this one today. And there's some links to a, a blog post where we kind of go through these things at the, at these slides. So the first case is, I get a new data file or a set of files, and I'm gonna evaluate data. I'm gonna find some hygiene issues and communicate that with your data providers.

So, uh, I'm, I, I'm a big fan of this tool called Ex Draw. So I've made some exag draw diagrams. So let's say there's a functional view. I get some files from an FDP, I put it in S3 bucket, I put it in a Redshift. You know, pick your favorite tools. Maybe it's, um, this is an example.

Now, the data view, it goes from an F three file into some raw tables and a scheduler view. This pretty much happens manually, right? 'cause you're loading the files. So you, you, you get some, uh, get some things in your bucket. You, you've got some tables in Redshift and I, you don't know what the heck's going on.

And so there's a bunch of questions, right, that you have about the data. And I won't go through each one of these questions, but like, does this data even make sense? Like, do I understand it? Um, is there something weird in it, uh, has, you know, has my data provider sort of screwed me over?

And so I'm not gonna go through each of these questions. And this is sort of a lot of what we're gonna talk about today is, is how do you understand data? How do you make sure that it's right and even sort of analytics worthy? Um, and so that's the first case. Now, the second ca and, and what, so what could happen with this?

Like, well, you could have badly quoted values, you could have leading spaces, you could have a zip code that doesn't have a zero on that. You could have many types of null values. There's just lots of cases of things that could go wrong. And how do you catch those, um, and make sure that they don't happen?

And, and remember, the, a number of, of a theme of this is if you find problems early, you save time later on. So if you find problems in data files before you're put in production, you're gonna save a huge amount of work later. Think of it as sort of five x 10 x amount of work if you find things sooner.

Um, and so that's, find the problem sooner. Focus on the day two problem right away, and you're gonna save yourself time and waste later on. And don't rush these things. Um, evaluating data is, and, and making sure that there you understand it and that you find any problems and perhaps pass or push back on your data providers, uh, is a good first step.

Now, the second case, uh, I'm gonna talk about is, oh, oh, I, I've jumped ahead here. So we're gonna talk about some tools to do this. Um, our sort of, uh, DataOps TestGen, it's free, it's Apache two, oh, we're gonna, uh, to get certified, we're gonna ask you to download it, um, and, uh, answer some exercises.

It's not the only way you can do this, but, uh, but we'd like it. So the, the second case is data ingestion. So imagine I've taken that file or set of files, I have it in production, and now I'm getting constant updates. And maybe the updates happens once a week or once a month, or once an hour.

Um, and so this is kind of going on all the time. And maybe you've got, maybe no one looks at this. Maybe your data scientist looks at these raw files or raw tables, or maybe, um, you, you've got, you've got an analytic team, you're an ingest team, and you've got an analytic team looking at it.

So what could happen? So let, let's look at a functional view. So I'm loading data, maybe I've got an Airflow job that's running, um, or a change data capture job or something similarly as an S3 bucket Redshift. And it has a similar sort of data flow, but, you know, maybe, uh, Airflow's running every hour to pull data from some source.

And so, well, what could happen? Well, I guess the, the, the theme here is, is there's something new that's strange. Did I not get the data I usually do? Did I get too much data? Did I get the exact same data I got last week? Um, you know, did, uh, the data I get, um, you know, did I get a new table?

Did the schema change? Just a lot of things could happen when you're constantly loading data. And so you want, again, find that out. It's much better to find out problems early, um, than catch them in a pro while you're doing production on a deadline, or worse yet when you have an embarrassing error in front of your customers.

And so, what kind of errors you could have?

00:15:00

Well, maybe the bucket weren't refreshed. Maybe you're normally getting five files, but you've got four. Um, maybe somehow the table schema changed, maybe some, something happened with a drift in the data, or maybe your job just failed. There's lots of cases where things could go wrong when you're constantly updating data. And we're gonna talk about how to do that with, um, TE scan and observability, how to set anomaly alerts, and how to set up volume schema and sort of data drift anomaly detection test to be able to, to check that.

It is a, it is a great way helping you make sure that your raw data is good before you, uh, do anything. Now. Well, we all have production systems, and some of those end up in production reports, but we have people who use this. And in general, it's sort of that multi-tool, multi hop data and analytic production process.

Um, and so what this looks like from an example, functional view, and this is just an example. So maybe you have an Airflow, maybe you have an A DBT job that does ELT. Maybe you've got, uh, a notebook and Databricks, and maybe you've got a Power BI dashboard all going against Redshift, or, or, you know, pick your, pick your favorite tools here in their category.

And, and if you watch it, you could, in the data view, it's sort of follow the bouncing files. I get an FDP file, I get something in S3, I get raw tables, I get process tables. I've got a femoral tables that show up in Databricks, and then I've got some extracts going in Power BI.

So sort of follow the bouncing data. And then you may actually have one scheduler. You may have multiple schedulers, Airflow may run, and then, you know, Databricks is set to run after it. And then the Power BI extract is set to run. And, and it, it's hard here, right? 'cause your customers see your result, they see Power BI.

So, and, and when problems happen, you wanna know where it is. And here's a bunch of questions. I've got two slides and and we're gonna talk a lot about this in our, our, our next session. Um, but in general, if we look at it from a picture standpoint, did the data load, did one of my DBT tests fails?

Uh, was I able to actually do the extract? Does the model have a prediction error? Um, is there some data that's missing or some column missing? Um, did the schedule never run? Is it actually late? A whole bunch of things could happen during your production process. And again, these are huge time sucks if you have these problems.

And finding the problem, uh, is an issue. And so, again, you want to be able to have another 23-year-old be able to quickly diagnose where the problem is, um, and be able to find it, not have to do war rooms to get 50 people in a room, and everyone's looking at log files for a day to find this problem.

It's, it's just unacceptable to your customers, and it should be unacceptable to you as a team. So, so how do you stop that? How do you observe a complicated system like this? And so we're gonna show how to do this with something called a data journey in DataOps observability, and then how you can actually auto generate the data quality validation tests and DataOps TestGen.

Um, and there's other ways to do this, and we'll talk about those. Um, and then the, the fourth use case here is development. And so this is the case where I'm changing something. I'm adding some SQL or changing some DBT model or changing, uh, some Python code. And in this case, I've got a copy of my production system, hopefully in development.

And instead of using production data, maybe I've got test data on it, but in any good development system should be some close reflection of production. So it should be a lot like what you're running. Maybe you've got only one schedule or things are are done. But it's, it's, uh, if you can get your development environment as close to your production environment, that's another source of error in data and analytics systems.

But you wanna be able to do something called regression testing or function testing. If you change something, how do you tell if it's right? And how do you actually have that 23-year-old find it? And so, again, here's a, a categories of errors, like maybe I've changed, added some new Python code and SQL code into Airflow and DBT.

Is the report empty? Did the sequel actually cause an error? Are there empty columns? Is there a low extract row count? Did something happen in my daily CI and CD process? Being able to see the system and make these changes and judge the impact is a huge, uh, enabler of you being able to make smaller changes and not have to sp spend months making work, uh, putting it into production only to find out your customer's needs have changed.

And so the ability to make changes quickly with, um, with low risk, uh, and, uh, be able to judge the impact of their, of problems is a huge way to increase your own team's productivity. Um, and so we're gonna talk about CI and CD testing and regression testing. Um, and then lastly, data migration.

And this is where, uh, as I spoke of before, you've got two systems. You're moving from perhaps traditional Informatica, SaaS, Cognos, Teradata with staging files.

00:20:00

Um, again, follow the bouncing ball with the tools to a cloud architecture. Maybe it's AWS or Azure. Pick your tools. And then how do you balance between those two to make sure, uh, things are right? How do you make sure that the new system is like the old system? And these are very hard projects.

So there's a, um, a, a a lot of wisdom that we've had having done these projects, um, and, and, and to talk about how it happens. And so we're gonna talk about how we can do it with our tools and, and other tools to make it work. And so lastly, sort of why should you care, right?

Why should you care about all these use cases? Well, to me, I think it comes down to the, um, saving time and lowering errors and improving the results so that your customers, uh, trust you and that you're able to make changes quickly. So it comes down to the sort of values and DataOps that we talked about, error rates, cycle time, lead to productivity, increase lead to happy customers, lead to more people using your data and analytics to get value and change the business, which is actually the real goal.

And so, um, we're gonna talk lastly about sort of what to observe. Do I check data? Do I check my tools? Where do I check them? And so, uh, we're gonna talk about that and we've, since we've got two tools, one that does sort of data validation checking, and the other does kind of data tool monitoring and brings the big picture together.

End to end, we're gonna talk about how to do that. And then, so that's my introduction. Uh, I took 23 minutes, Eric, I thought it would take 20. So, um, what I'm gonna do is turn it over to Eric, and he's actually gonna go through, again, we're talking about the first use case today of those five use cases, which is really about how do you evaluate data when you first get it.

Um, and we're gonna talk through the other use cases in our following sessions on, um, you know, two weeks and four weeks from now. Um, at the end, I'll sort of pull together and remind you what, what we have to do to get your certification. Um, again, if you have questions, feel free to type them in.

I'll, I'll walk that. So, Eric, you're up All, thanks Chris. Thanks for that great introduction. Uh, apologies. All right, here we go. So let me take control of the screen and, uh, we'll be off and running.

All right, so what am I gonna try to accomplish? So today, uh, I'd like to help you understand, um, some of the key things that you gather, uh, with, uh, data profiling. And then what do you, what do you actually use it for? Um, and really a lot of this context is gonna be, uh, thinking of profiling, uh, as a tool to figure out whether you're gonna pass, uh, pass, patch or pushback.

Uh, so what is, what, what are the three Ps pass patch pushback? So the, uh, you know, passing is when you've got data, it's good enough to use, and you can just send that along to your, uh, developers and users, uh, patching. You've probably, uh, the more experience you get, the more times you get into this situation.

You, uh, you find that there's problems with the data, that you really can't send it along to your end users, but it's kind of obvious what the problem is. So with some consultation with your downstream is probably, uh, downstream customer as well as probably the upstream data providers, you figured out that you can actually patch the data, uh, to make it usable.

And we'll look at some examples of where that, you know, might be a good thing to do. Uh, and then there's pushback. Sometimes you get data that, uh, you just, you can't fix it either 'cause something's missing or, uh, it's just messed up enough that the effort to fix it is just gonna introduce more problems, uh, than you really wanna have to deal with.

So, um, let me, let me turn into slideshow mode. Here we go much better. So, you know, uh, one situation that, uh, people run into a lot is, uh, you get new data, right? And customer drops it to you and say, Hey, here, I got some new data. And you're like, well, what is this?

It's like, oh, I got it from somebody. They don't have a good answer. They don't have a clear lineage of where this is from. Uh, and then so you ask nicely for data dictionary again. They're like, you know, we're not really sure. We'd just like you to figure out what's in it. So you end up with this data that sometimes it's just a blacks box, and your job is to figure out what's in there.

Uh, is it good enough to use? Uh, and, um, you know, can you get this downstream to your customers? Yeah, and I think, I think we all, uh, have had that experience of people dumping data on us and, you know, 'cause we're like, I think data and nalytics teams are kind of combination of plumbers and magicians and people's minds.

00:25:00

It's like, oh, you're the plumber here today. You can do all that stuff, but you're also a magician, right? I, I don't know what's in the day. You just figure it out and make some magic on it, aren't you? Great. So I know that ideally, you know, the situation in most companies is, it's not like that, but, uh, it does happen.

So more often than we'd like to think about.

Um, all right. So what, what is data profiling? So, one way to think about it is it's the first step in a data quality journey. And as we go through these different sessions, you'll see how the, uh, early work you do with profiling, uh, just sets you up for success as you start to move data, uh, into production and then actually run it in production.

Um, you know, data profiling is all about exi examining data that you have, uh, collecting characteristics and the statistics on it. Uh, and the goal for that is to understand its structure, uh, the content, what's in there, um, look for inconsistencies, uh, as well as anomalies. Uh, and we'll cover some of these, uh, very directly later on as we look into kind of doing some practical, uh, profiling.

Um, I find that profiling is a very valuable baseline, uh, before I put data into production. And what I can do with that baseline is it helps me set expectations for the future of what this data should look like and maybe how it's gonna behave, uh, which is, uh, a good foundation for me to build tests for the future.

Um, with profiling, uh, you know, I'm, I'm talking about that customer delivers you some data and you don't know what's in it, um, just 'cause you use it to get up and running with a data set. It's not something you should only do once. If you can incorporate data profiling, uh, periodically into your kind of overall quality and production strategy, um, it's really gonna help you head off, uh, problems, uh, as they develop in the future.

Uh, maybe learn some more about the data that you didn't know and, uh, really kind of make your, what you're delivering just of the highest quality.

Um, you know, I mentioned just understand what's there, right? A a lot of it is like, how is this organized? Um, do I have columns with similar names? Um, and are there possible relationships between them? Uh, you know, during the, uh, profiling, you gonna be finding duplicates in some cases, uh, inconsistencies. Um, and again, what you can decide is, am I gonna pass this data along again?

Am I gonna gonna push it down to my customer or am I gonna try to patch it, uh, before I use it? Uh, and this is going to help you build up a trusted data source, uh, for your customers. Um, a lot of what you do in the, uh, uh, profiling phase is gonna help you with the next step, which could start to be the, the data cleaning and prep.

Um, ideally you get nice clean data profiling, just, uh, help you set the baseline. There's no more work to do. But a lot of times you're gonna find missing values. Uh, might be some outliers that either need to be corrected or addressed or, uh, have some questions. Um, formatting and just inconsistencies with the content of the columns is always something, uh, that you wanna discover early, because once somebody is pivoting over n slash a versus not applicable, uh, downstream in their BI tool, um, you, you don't want them to be doing it down there.

You wanna fix it up, uh, upstream, uh, you know, profiling helps with data governance. Um, you may work in a larger organization that's been up and running for a little while and have some standards, uh, where you need to comply. And that could be anything from, uh, the way territory codes, uh, are managed if you're working at a pharma company or just the way you handle nulls.

Um, it, it also can help set standards. So if you're profiling new sets of data, uh, you may not know what's in there. So you can kind of, you know, as I mentioned, grab the baseline for the data, but you can then help set the standards for what comes next with that. Uh, and, and, you know, the number one thing, uh, almost all data quality work is really focused on, uh, obviously delivering high quality data to your customers.

But it's important you establish trust with people. Uh, your downstream users need to trust you. Um, I, I, uh, I, I usually coach my team to never promise, uh, that we're gonna be perfect or that we're gonna catch everything. But you want them to know that you, uh, you know, the downstream users, you want them to know that you, uh, you know, you have a process, you, uh, care about the quality and that you're gonna keep an eye out.

00:30:00

You can address things when you find them, uh, in the ideal case before it reaches them. But if not, you have strong processes to, uh, come back and catch it. And that's why data profiling up front, uh, helps set that stage. And then regular data profiling, uh, helps you stay on top of issues before they emerge.

Yeah, and then my, my, my 2 cents on this is like, you know, I just find it incredibly embarrassing when customers find problems. And I've had the misfortune of being yelled at or shamed by customers and saying, you know, what's wrong with you? And so a lot of the, of why we started this company is sort of shame avoidance.

And I think it's, it is, you can do something, right? It's sort of not acceptable to pour data into your data and analytics system, have no idea what it is, and then have no idea if it's gonna produce the right results, and then rely on your customers to tell you that something's wrong. I think there's, we can all do better, and we can do better by applying some of these observability and, and, um, data quality validations actively.

Um, and so the, you know, the big theme is don't be passive. You don't have to be passive. It's not that much work. And, and partly one of the reasons why we've open sourced the software is to make it make sure there's even no cost in involved for you.

Yeah, my lecture, Sorry, Eric. No, no. Keep the lectures coming. Yeah. This is where, uh, if, if everyone on the call, if you're, you're luck, if you're lucky, Chris, and I won't go too far on, uh, you know, tangents and experience, but if you're, if you like that sort of thing, please do encourage us.

So, uh, because we like to talk, um, okay, data profiling, I talked a lot about what it is, it's actually equally as important to recognize what data profiling is not. Um, it's not data cleansing, um, uh, depending on the tool you're using for data cleansing or, um, you know, how you're organized about who does what jobs.

You know, it may overlap, but data profiling is not about, uh, fixing the issues. We're just here to identify the issues. Um, you're not doing analytics, right? You're gonna, uh, look at the data, get some descriptive statistics, the, you know, the standard deviations, the means, the min, the max, and all that. But that's not analytics.

You're just gathering characteristics that you're gonna be able to use later. Um, you're not validating the data against predefined rules. Um, uh, this is where, you know, validated data against rules that really gets into testing. What you are looking for now are patterns and anomalies to help, help set the baseline, uh, maybe help define what the rules are, but you're not gonna be about enforcing them at this early stage.

Um, data quality, as I mentioned early on, it is part of, sorry. Data profiling, as I mentioned, is part of a overall like data quality journey to putting together, um, robust production processes that deliver high quality data to your customers. Um, it's not in itself, uh, a one, one and done, and it's not sufficient to ensure that quality data gets to your customers.

Um, it's just a par piece of the puzzle. It's certainly not the whole thing. Uh, and I've mentioned this a couple times, I'm probably hit on it again a few times more. Uh, it's not a one-time activity. Again, it's really important when you first get a new set of data, um, if it's the, the black box without any documentation or any clarity on what's there, um, you know, you, you definitely wanna be, uh, profiling it.

Even if you get a great, uh, um, uh, data dictionary, get a lot of documentation on it, um, it's really incumbent upon you to profile the data to verify that what the, the provider is saying is actually in there. Um, you know, we've, and, and, you know, when does this happen? Uh, we work with, uh, we do work with lots of different companies, but, uh, pharma companies, uh, especially on the commercial side.

Uh, they, they buy all this syndicated data. So these are these curated data sets from some of the, um, you know, biggest data aggregators in the world. They do really top-notch work. They put together just amazing amounts of rich data to look at, um, pharmaceutical sales in the us. Um, they have great internal quality programs, however, they make mistakes too, right?

And so, uh, anytime we start a new engagement, I insist we're profiling the data regardless of what the data provider's telling us is in there. And I can't count, uh, uh, I can't count. On one hand, I don't know. It's happened more than once where, uh, early profiling with a new data set has uncovered, uh, really serious issues, uh, in the data before it even got into production.

00:35:00

So, um, just don't trust the data from upstream. Now, if it's a great, uh, data, the data provider's really tough notch, you know, they have good controls, uh, you know, maybe don't put enough effort, put a, put as much effort into it, but you definitely, uh, you gotta do your own homework on this.

Um, so when we're profiling data, it's really, there's two parts to it. The first is, uh, gathering, uh, characteristics about the data, and then using those characteristics to look for anomalies. So focusing first on the characteristics, these are, these are like all the information you're gonna gather about the structure, uh, of the data, um, what's in there, like what the content is, um, you know, the quality of it.

Um, you know, does it have expected patterns? Do the patterns follow some kind of logic to them, uh, as well as just some basic statistical measurements to it. Uh, and these can, you know, they always start simple, and we'll look at some simple ones here, but they can get more complex. And part of it is, uh, gathering statistics, but then using them, uh, in an automated way when you can to look for anomalies.

Uh, and then sometimes just giving you basically like a, a feel and insight into the data that you maybe normally wouldn't get, um, just by reading a data dictionary or trying to walk through it in a results window or a spreadsheet.

Um, like I mentioned, these low level characteristics, there's, uh, you typically gather lots of them and they build this comp, uh, comprehensive picture for you.

Um, so there's a lot of different characteristics you can gather. Um, gathering them, organizing them, uh, keeping track of them is a, is a pretty big job in itself if you're doing it manually. Uh, and we're gonna look at, um, what this looks like maybe from a SQL perspective, uh, and then look at it, what it looks like from a, a tooling perspective.

And we will use SQL for the first part. And then the DataKitchen, uh, TestGen tool for the second part. Uh, what I got here, uh, is, uh, kind of making it concrete about what are the types of characteristics. So there's like 50 odd characteristics here. Uh, we're adding new ones all the time. Uh, when we find something valuable or, um, more often than not, um, when something slips through, if there's a problem that's found later that we're like, ah, you know what, if we just profiled, uh, this extra characteristic and used it into feed into anomaly detector, then we probably would've caught that.

So that's how we, we grow this. We grow it by learning. Um, you know, but this is where just, um, you know, how many embedded spaces are there, right? Are there leading spaces? Uh, maybe, you know, the text string, you gotta text field where the value is set to, uh, effectively a blob 60 4K, but the longest string is, you know, 10 characters.

Like, you know, this is all good information together. Um, it's gonna help your data engineers, uh, as they start building out processes. It's gonna help you get a sense of the quality. Uh, and it's just gonna help build up this overall picture for everybody with the data. Um, you know, you'll probably hear me talk a lot about things from a perspective of maybe a more technical, uh, data engineer who's slinging SQL or something.

But, um, a lot of this stuff is valuable for, uh, data stewards, uh, and data quality people who maybe are, um, you know, sitting up at a slightly higher level doing analytics, not slinging heavy duty sql, but they, they just have the intuitive feel for the data. They understand the domain they're working in, and a lot of these characteristics help feed into giving them a tool they can use as well.

Um, so I mentioned tools, right? Like, how am I practically gonna do profiling? Um, I like to think about these kind of five major, um, kind of tool groupings, and then there's like, you know, half a dozen or more different characteristics, and I try to rate them. And, uh, the best thing to do is to pick the tool that's, uh, compatible with your skillset, uh, and matches the, uh, the data and the sources that you're gonna be using.

Um, a lot of the discussion later, uh, you'll notice we have a database focus on things, um, you know, but, uh, if you are just dealing with files, they can be reasonably loaded and managed in Excel. Excel, uh, is a great way to do some lightweight, uh, data profiling. Um, you know, and obviously Excel's not free,

00:40:00

but, uh, is rarely an office that doesn't have Excel licenses floating around. Uh, they've got Power Query and some other, uh, profiling tools that are built in these days. Um, uh, sql, um, I, I love sql. It's, uh, ubiquitous. Um, even between the dialects, between databases, the differences, uh, in the case of something like profiling or trivial, uh, the differences that are there.

Um, it's scales. Well, uh, as far as like, uh, the amount of data it can handle. Um, just huge, huge benefits there. Uh, you know, Python, there's an enormous amount of, uh, you know, knowledge that's gone into making robust, um, Python packages for doing things like data pro profiling. Again, you gotta know Profi, you gotta know Python, gotta be proficient, but, uh, it's still there.

Uh, and then you get into the application world, there's, um, some tools that focus things, uh, focus on things from a command line perspective, uh, and then others that have a UI that, uh, uh, kind of make it easier for you. And again, the best thing to do is use a tool and then find a tool that just fits again, with your skillset, uh, the data you're working with, um, to help make things a little more concrete from a profiling perspective.

Uh, we're gonna look at two ways to do it today. Uh, the first is gonna be sql, and then a little bit later on, we'll look at the TestGen application itself. Well, but I'm, I'm Gonna put a, a, a plug for Excel. Like, my favorite thing was to do select part 10,000, load it into Excel and pivot around.

So, um, yep. You know, there, there's a lot of, I mean, there's a lot of tools for profiling. I mean, the market is like ETL tools, data quality tools, data governance tools. There's a lot of, um, data profiling tools out there. So usually the, the tool that you have is the best, or the tool that you're most familiar with.

Like, I'm more of an Excel guy, but like, uh, I think what Eric's gonna show you is probably the problem with Excel is you can't load a lot of data at it and, and sql you can. So I think what Eric's gonna show you is pretty cool. Yeah, exactly. Chris, thank you. That's a very good point.

The tool, you know, is the best one. So, uh, at least initially, um, okay with sql, so we, we talked about, uh, you know, I grew up that slide. I've got 50 different, uh, data quality metrics there, right? Um, one that I find, uh, valuable. It's almost like the first thing you should do is just tell me Roser on my table, right?

It, it seems so silly sometimes. It's like, I feel silly even telling people about this, but like, know how many rows are in your tables, right? Because this then becomes, uh, a, a baseline for how big a file should be. Uh, you gotta know whether it's like a full snapshot, an incremental, does it grow, you know, you gotta know the dynamics of it.

But that, that first, how many rows are in my table, is just so valuable. Um, and it's also on its own useful, but then it becomes important when you start mixing and matching it with, uh, other characteristics that you're gonna pull. So, selects, count star from my table. There you go. That, that, there, you've started data profiling.

Uh, you know, a next favorite is distinct counts. It's always good to know in a column, is this like a very, very sparse domain? Are there a lot of nulls? Um, or is it a code where I have a super limited number of codes, right? So again, on the SQL front, select count distinct, my product name, boom, got my second metric.

Uh, and now I can learn a lot by what that comes back. If it's, uh, um, you know, if it's an ID column, hopefully there's as many distinct as there are rows. Uh, if it's a product code, hopefully it's a, a much smaller domain, uh, than what's there. Uh, and now I get to this point where I can now take record count and start mixing at it.

So I can start to use distinct value count and record count, and now I've got another metric that I can use. Um, it's something you can start to track over time, and it starts to build into your profiling picture.

Um, you know, not to focus on text too much. I feel like give texts too much love sometimes, but you know, just numbers, right? Max, min, median, average. Just pulling all those statistics together, uh, when you've got a number column, uh, just again, gives you, uh, a sense of what the data looks like, uh, helps you look for outliers, and this is where, uh, you may see outliers on your own.

00:45:00

And then you need to understand what those outliers are and whether or not they're usable. That's where you might have to pull the domain people in. Uh, or sometimes the numbers are just so, so outta whack. You, you can kind of tell. Um, uh, so what's the problem with SQL from doing a very comprehensive, uh, kind of view of your data?

You gotta write a lot of queries, right? I like writing queries. I love meetings where I can open up a query editor instead of like, make making a deck, but even I don't like writing too many queries to do my profile. And, um, you know, I got a table with 50 columns, right? I gotta write 50 queries just to see what the distinct fill count is.

Um, I've got two tables. Each one of those is gonna need another query. Uh, the math on this gets a little crazy, right? I've got, you know, relatively small data sets, right? Um, few tables, a few columns, going to gather a lot of characteristics, talking thousands of queries, I've gotta look, uh, and manage and run and put that data somewhere.

So, uh, sql, fantastic tool for profiling. Uh, it just, you can't do, uh, the comprehensive query profiling that I, I think most data sets deserve and that I like to do. Even if I'm using another tool that does the comprehensive profiling, I'm still gonna load a table, do a couple, uh, you know, kind of quick and dirty ad hoc queries to get, again, those early ones distinct values, uh, row counts.

But I'm probably just gonna use those as a bit of informative while, uh, my profiling is running. So, so you don't wanna write thousands of SQL queries. So what do you, do? You use a tool? Um, you know, we mentioned there's tools all over the place. Um, as far as data profiling tools themselves pro, you're not gonna find a lot of, like, you know, X is a data profiling tool.

You're gonna find data profiling as a feature in a lot of other tooling, and that includes things like, uh, you know, you ETL tools. So SSIS has a profiling node. Um, our BI I think has data profiling in it, um, a little down the stream, down, you know, down the workflow. But still, um, data lineage, data catalogs, a lot of 'em have data profiling built into them.

So again, the number one tool to use is the one you have access to. Uh, and then does it fit your use case? Um, so, uh, let's talk about te data profiling in TestGen itself. Um, as Chris mentioned, data, um, uh, data profiling, uh, data TestGen itself, uh, it's an open source project. So, uh, please, uh, you know, uh, take a look at the repo star it, we're looking for stars.

I feel like I'm on YouTube looking for likes and, uh, and subscribers. So, um, to, uh, do the homework at the end as well as to set you up for like the next session you're gonna need to download and get TestGen running anyways, so hopefully we'll see you there. Um, we have, uh, issues open on GitHub.

We have a Slack channel, so don't hesitate to join both of those communities. Uh, if you see something that's broken, let us know. If you have trouble, let us know. If you have ideas about features or improvements, we, we wanna hear those as well. The more people we get banging on this, uh, in feeding back to us, uh, what we can do better, the, the better off we'll be.

So, um, so without further ado, I'm gonna jump into TestGen itself. Um, as you find, you'll find out once you download or run TestGen, the, uh, it runs in a local container service. Um, the installs really simple, uh, straightforward. Uh, it's, uh, a web-based interface. So we have customers that deploy this on a server, and then multiple users are coming in and using it.

Uh, just for this demo, I'm gonna be using this in the, uh, kind of running it locally. So, uh, with the data profiling, gimme just a second, I'm gonna sync up my slide over here. Do it justice. All right. Um, I'm not gonna go through the setup or, um, the demo data. And this is one thing you'll find during the install.

Once you do the install, there's a little, uh, command the run to run the, um, demo, load the demo data, and then run some profiling on it. And it's just a great way to get a real understanding of the tool before you start putting it on your own data. So, jumping to the data profiling tab, I can see that there.

I ran this yesterday, uh,

00:50:00

and I've got some profiling results. So let's take a look at, um, what we've got. Well, first thing I noticed, uh, I profile four tables and a total of 62 columns. So again, doing the math on my prior slide, that's a lot of SQL queries. Um, so what TestGen does, um, gives you schema, table column, uh, and then it starts to, uh, tell you some of the stuff it discovered in here.

Um, so let's take a look at this column, customer type. So what can I gain from this first screen? Um, I can see the table, it's a VAR card 20. Um, we've decided it's a functional data type is a code. Um, I won't get into that too much here, but, uh, if you look in the TestGen documentation, uh, based on the data that we find, we try to make assumptions about what, um, how are people probably using this column?

We're not always right, but at least it gives you a, a good way to kind of group organize and think about things. Uh, so, so let's see what we can learn or what we learned about this. So, um, again, it's, uh, uh, alphanumeric, uh, column types 20, uh, and then TestGen, looking at the data that I found made a recommendation and saying, Hey, you know what, um, based on the data I found in there, uh, all as well as the column type that it is already, you know, bar car 20 looks like it's a good choice.

Uh, this is a nice feature. Um, a lot of times you'll load data, uh, uh, sometimes you'll load data, you don't, again, you don't know what's in it. Um, I, a lot of times will just put everything into big text columns, load everything up, run the profiling, and then use TestGen to suggest to me, uh, what the schema might look like.

Or as I'm going through the profiling results, kind of craft my, uh, schema for it. Um, I can see there's 502 records, um, 502 values. So this means everything is populated, which is now interesting, right? How is that interesting? This could become a test. Um, where is this table or this column in this table?

Is it always populated? So now it's easy to say, you know what, if I get a data set and I start seeing nulls, I think there could be a problem. Um, I've also got distinct value count to four. So this is interesting, 500 rows. There's only four different values. Uh, over on the right, I can see here what they are.

Um, since we know these are customers, I'm assuming this is customer type, again, it's sometimes a little dangerous reading too much into what people call their data in their columns. But this is probably like the sources, uh, where people are coming in. Uh, and so you can see the rough distribution. Uh, again, this is helping me build up a picture of what this data looks like and possibly setting expectations and baselines that I can use for tests in the future.

So let's assume this, uh, I get, uh, uh, this file one day and there's no Amazon in it, or it's a hundred percent Amazon. Is that a problem? You probably have to watch the data, uh, for a few cuts of the file, but that's probably a test you would look at. Say, Hey, you know, the rough distribution is this.

Uh, if I see everybody, somebody disappear, or I see a jump to a hundred on somebody, then there's probably a problem with the file. Uh, and anyone who's worked in an environment where you're, you know, handling data, which I assume you all are since you're on this call, you've seen that before, right? Like, you have someone where they, uh, someone goes into Salesforce and sets the customer type to Amazon for everything by mistake.

But if you have a test that you built off your data profiling, you know that that's not a good situation. Uh, you don't have any nulls, um, no dummy values, no zero length counts. Um, you know, these are where, how do you handle nulls always becomes an important question, uh, in any data set.

Um, you know, dummy values, this is where things like missing, unknown, not applicable, uh, those are all dummy values, but, uh, sometimes you'll find that there's a mismatch of how people are applying those.

Um, you know, nothing we determine is a number. Nothing we determine is a date count or a date, right? So this, this data looks about what it is, um, this top patterns I find useful, maybe not necessarily here, but, uh, this isn't telling me that there's a string

00:55:00

of three a's in there. This is telling me that, uh, uh, you know, the biggest pattern is a string with three alpha numeric characters, or in this case just alpha characters probably. Uh, and if I scroll up to here, yeah, that makes sense. I've got two club and shop that are four in length, and that's like the biggest category.

So that all kind of makes sense. Um, yeah. And, uh, standard pattern matching. There wasn't any pattern really that was, um, picked up from here. Um, so it didn't look like a date or a zip code or a state or anything like that.

So that's, uh, that's an example of profiling where, um, there's not, uh, there's no anomalies here. I've built up a little picture of what's in the data. I've set some baselines of how big my tables should be, how many values should be there. Uh, I've also really given myself a good foundation for thinking about tests and we'll, we'll talk about tests later, right?

But, um, it's really everything for me ends up, like, what kind of tests am I gonna write? What kind of tests are on my data? Um, you know, Chris mentioned, uh, you don't want your customers to find the problems. You wanna find them. Um, so, uh, depending on what your production cycle or system looks like, you know, you, you may have tests.

Uh, we, we like to put tests in line, in production, uh, such that nothing gets delivered unless the, uh, you know, uh, suite of tests are run and they pass. Um, I know one of my big, uh, questions is if something does slip through the customer, is, did we have a test for it?

Um, you know, I don't know if this is a good or a bad attitude, but, um, if, if a customer finds something, I wanna know whether or not we had a test. If we had a test and someone ignored it, that's a process problem. We can fix that. Um, if we didn't have a test and it got through to the customer, this is where the building of the trust comes.

'cause I'm gonna say, you know, I, I, I establish with the customer very clearly. We're not perfect. Stuff's gonna slip through. What we guarantee is that something gets through, we're gonna put a test for it after the fact. Um, so this gives us a leg up on adding tests. So, all right, so let's look at, uh, something that actually had a problem.

So what I'm doing is I'm sorting the, uh, uh, just the results here, uh, by anomalies, and I can see that there are some,

uh, with anomalies. All right? So give me a second here. We're gonna jump back to the deck for a second. Um, a lot of my, uh, walking you through, um, the, uh, what should I call it? The, uh, the column over there, uh, is in the slides as well, but you'll have access to this, uh, uh, in test itself, so you'll be able to do it in there.

Um, okay, so I got a bunch of statistics characteristics. Um, I looked at some of my columns and I got a good picture of the data. Nothing seems off in the profiling. Uh, I have a good way to communicate with my, uh, downstream customers. You know, what they can expect from the data. And I've given myself a baseline, uh, for adding some tests.

Um, however, that's in a perfect data set. And the, the non-perfect data set, uh, we look at those characteristics and determine that maybe there's some anomalies. So, um, in TestGen, we're, we're focusing on TestGen. Now, the way it, uh, thinks about anomalies, uh, you know, we have like a few dozen different anomalies that we look for.

Uh, and that's all based on the characteristics. We're, we're adding new ones all the time, uh, because, uh, you know, the more we find early, uh, the better we are. And so, um, you know, what, what are some of the things we look for? Again, we look for discrepancies between the column type that you loaded it as, as well as what's suggested there.

Um, is the zip code funky? Um, are there columns without values? Um, uh, you know, there's tons of dead columns and data sets gets to the point of like, well, do we wanna carry this around? Um, you know, we look for little things like, uh, standardizing values. Um, you know, do they look the same?

Um, did I find, uh, an email in a text column where every, there's not a lot of emails in there. Um, that's always a fun one where somebody's, uh, just put data in the wrong column. So, um, I'm gonna jump back into test Jen,

01:00:00

and we're gonna go to the anomalies. So we're gonna focus on our data profiling run. Um, you know, I mentioned, um, you know, data profiling. It's a upfront activity, but also something you should do over time. Uh, in this case, we've just done it the once. So, all right, so what do we got for anomalies?

Uh, what we do is we've looked at all those columns and we've given a bit of a ranking or, uh, maybe a priority order. So a best way to think about it on how likely is this an actual data issue? Uh, and we do that because just saying there's issues is fine, but a lot of times you need, uh, you need focus, you need priorities, you need a task list.

And we found that this is a good way to do it. Um, you know, in the, uh, in the data in TestGen itself, um, you know, we like to think of this as a task list. 'cause hey, I should look through all these and determine whether these are real problems or not. Um, once you've actually looked at them, done some judgment on it, um, and address it one way or the other, you've either passed it along, pushed back to the customer, or you've patched it.

Uh, we just give you a easy tool to, uh, just kind of like, Hey, what's the action on this? So this is where I can confirm it's relevant for this run. Um, you know, the issue as it's not relevant, or, um, you know what, this is always showing up. I know it's a mess. It's non-logical.

Customer tell, uh, data source provider tells me, just ignore it. So, uh, give you a little way to keep track of things from this run. Um, let's, let's dig in to one of these here. So let's go to product type. So looking down a product type, um, what's the anomaly? First of all, similar values match when standardized.

So this is likely a problem. Uh, and so if we look down here, what does that actually mean? Well, uh, you know, you've probably all run into this where, uh, you've got, uh, a column where it's supposed to be like a fixed domain of results. Sometimes there's two spaces where there should only be one.

Sometimes people delimit things with a dash. Sometimes there's an extra period for some random reason, or the casings weird. Uh, and depending on what the usage of your data is, that could be a real problem. Um, you wanna fix it upstream as early as possible, such that downstream your code, uh, you know, all your data pipelines, uh, can start to work on just one expected value type.

Um, so that's one, uh, you know, using those characteristics, uh, this is one of the things we look for. Um, so this is great. We're explaining what it is, but like, well, what am I looking at? Right? Well, let's look at the source data. Uh, and then looking at the source data, uh, kind of becomes clear what the problem probably is.

Um, I can see all the way to the left. This is what's in the table and, uh, what the count is of it. So it seems like e-bike doesn't have a lot of upstream control. So whatever app they're using to gather this information doesn't have good controls over that value. So, uh, you know, what did TestGen suggest?

TestGen suggesting, Hey, you know what, this is probably e-bike. Uh, you know, maybe you really like it differently. You know, maybe you do want mixed case or whatever. But really what this is, is giving you, uh, visibility that you've got a product type column, which probably should be a domain, uh, like a limited domain of values, uh, as well as, um, you know, what a possible standard value is.

Uh, this, this is a, for me, I look at this, a clear case of a patch, right? So my, the way I would approach this is I use this information to recommend both to the downstream customer as well as the upstream. So downstream, I'd say, Hey, we got this data. Uh, it's real messy product type.

We think we should patch it, which means we're gonna, in our data pipeline, we'll add a step that just takes product type column, looks for all the permutations to e-bikes, and sets 'em all to this standard. Uh, now, instead of just showing this to the customer, or, you know, our downstream user, yeah, I'm gonna go to them with a plan.

I'm gonna say, look, here's what the data looks like. Um, I think we should just patch this and I can get this into production for you very quickly. Um, I guess the real question for you is, do you want all caps, e-bike, or do you want e-bike? Right now, coming to the customer with a plan on how

01:05:00

to patch this data takes a whole lot of work off of them. Um, typically in a situation like this, they're gonna say, great, let's get this into production quickly. Uh, the only thing that may change is how they wanna present it. You know, which standardized value they want. Uh, anything you can do to make it easy for the customer to, uh, make a decision, uh, is great.

And this is where you don't need to worry about being, uh, right or having an answer that's perfect. You just need to get something in front of them. 'cause if they have to make a slight tweak to the value that's there, instead of them having a hundred percent of the solution, you're talking about, you know, like 5%, just decide what you're gonna use.

Um, you know, this is a conversation probably that would go, uh, work well in conjunction with talking to the upstream data provider. Like, Hey, we're looking at your data and this is what we're seeing we're recommending to our customer, we just patch it. Um, and we're gonna use this value as the standardized value downstream.

Again, you go to the cut, you go to the data provider, they may be great, thank you. And they're done, right? They don't care. They don't have to do any work. They also may say, oh, that's great. Thanks for pointing these errors out. We're gonna fix them. Uh, 'cause of our release cycle. It's gonna be three weeks.

Um, and we're gonna standardize on e dash bike, right? So by coming into the customer with a patch proposal, getting, uh, agreement consensus from them, and then going to your upstream data provider saying, Hey, here's the plan. You've now taken the burden off of them. So instead of them rushing probably and possibly introducing more errors into their data process, you've given them, uh, you know, the solution, and you're telling them, just fix it when you get to it.

Until then, we're just gonna clean it up before it gets there. So now you've made both sides of that successful, and you've ensured that this project's, uh, you know, kind of helped this project a little bit more, uh, on making it. Um, let's, let's actually take, uh, let's look at the, uh, product type a little more.

So let's look at the profiling results for this too.

So if you remember, we looked at the profiling results for the customer type. Uh, now we're looking at, uh, you know, the product type.

So alphanumeric, it's recommending 50, uh, we have 50, you know, that's where PEs gen is likely to say, Hey, if you decided on 50, it fits everything I see here. I'm not gonna, you know, you're not paying by the column width anymore, so let's just leave it like it is, because a lot of times leaving something as it is, is the way to go.

Um, where this becomes important is, uh, you know, now I've got, uh, and I don't think we do this feature yet. I know it's in the, on the roadmap, but if I see product type as Fit Car 50 here, and I find another table where product type is CAR 20 or 25, now this is something that, um, you know, that roadmap feature in testing will flag.

It's saying, Hey, you got this column, this is what I'm seeing is possible. Uh, column widths. Uh, you should, this is an anomaly. You should look into this. Um, if we look at this, looks like a customer just sells three different major products, bicycles, e scooters, and e-bikes. Um, you can see this, um, value chart and distribution will get cleaned up quite a bit.

Um, you know, I mentioned the patterns, right? Um, again, we, we've identified e-bike as probably an error, but this is where the string patterns come into play, where we can see that, uh, you know, hey, I've got some stuff with a dash, some stuff with two dashes, some stuff without dashes. Um, just all it all feeds into what we can see there.

Alright? So let me see. How are we doing on time? All right, cool. Um, we, for me, good. We've got 20 minutes left, so we set now and a half. Okay? Yep. Cool. Um, so one of the, uh, jump back in here. So the, um, uh, you know, with the anomalies, we've got the column for you here.

Uh, you know, as you're working through this, this, then the anomalies themselves are typically, um, summarized for you, uh, at the top of each column. Um, I, uh, I apologize it doesn't link through yet. That's one's on the roadmap too. I know someone else is working on that. So, um, all right, so this was product type.

All right. Um, you know, we talked about this. Uh, you know, what, what's the, um, you know, kind of coming back to the three Ps at the beginning, um, patch the data, right? This is probably a patch. Um, depending on how much trust you've built with your customer, what the relationship looks like, you know, what the release cycles are.

01:10:00

Um, you don't wanna unilaterally patch all the time. You probably wanna go at it, uh, like I mentioned, like, uh, you know, a two-sided proposal. Say, Hey, here's what we recommend we do, and hey, here's the data we found. Um, you know, sometimes you run into data issues where there, uh, just isn't anything you can do.

So let's pop back in and take a look at last order. So we've got last order here. Um, and what is this? This is on my e-bike suppliers table. Um, and we've identified it as a date column. Uh, it must mean that we did identify the data in there as dates itself. Um, however, what am I looking at?

There's a lot of, oh, it's full. It's fully populated. Okay, that's good. Um, so there's 28 records, you know, you can vaguely, uh, derive from that. You get 28 suppliers, although we're not looking at the data here necessarily, we're just looking at the characteristics and anomalies. So that's, that's where your, uh, SQL comes in handy to dig deeper.

Um, so, so what are we seeing here? Are we seeing that we've got, you know, data back a couple years? Um, and no table dates within six months, most recent date, 8 19 23. That's what my maximum date is. Um, this is probably a problem, right? Uh, we've got like, uh, a reasonable number of records for suppliers, but, uh, in the table itself, the maximum, uh, is, uh, not there.

So let's, let's actually take a look at that anomaly. So we're gonna look at last order.

Pull this up, right? Uh, and so what is this test right among, um,

you know, all the, uh, date columns present on the table. Most recent date is, uh, uh, kind of, uh, Paul, six months to a year back, right? If you've got a date column and the rest of your data is fresh and alive, it's probably, uh, something we should look at. So let's take a look. Um, yeah, and it's just calling out that this is the last, uh, max, uh, data, uh, sorry, this is, this is the characteristic of the information that was gathered that the, uh, uh, test was built on.

So this is another one where, you know, most likely, what do you do now? You tell your customer, Hey, supplier's, table's messed up. Um, we're gonna go back to the, uh, uh, data provider and see if we can get some clarity on it. Um, you know, I'm a, a big fan of getting data into people's hands as soon as possible.

So this is where, again, this is an idealized demo data situation. But, uh, you know, I've had tons of situations where we've had data that's, uh, suspect that we think there might be problems with. Uh, and what I do always with the customer is I'm like, you know, we're in development. You gotta start building dashboards.

Um, we're gonna give you this data, uh, is a ton of known issues. Uh, the quality is low. Don't go show this to anybody, but I really wanna get this out into your hands. And so, just 'cause you have a data issue and it's a, even a data issue that everybody agrees is a real problem, uh, it probably pays to start moving forward with a whole bunch of your development and trying to get into people's hands.

Um, you know, there's so many different ways, uh, people work collaborative collaboratively on projects at companies, big companies, small companies, right? So some people, this is like a non-starter. It's like, Hey, we didn't pass profiling. This data's old. They're putting this on hold till the provider fixes it, right? Um, I usually try to push for agile environments where, um, hey, we've got data, which is always a big, you know, even getting the data sometimes is hard, right?

But I've got the data now, I need to push it out and, uh, I wanna get this to somebody so they can start building and developing against it. Uh, but we'll, uh, kind of qualify it with it's a problem, uh, in the future.

You know, I wanted to look at another column, and then, um, I think that will kind of move on to the next phases and open it up for a little bit of questions if we can. Um, so region code, right? Pattern inconsistency within the column. So I love this one. So this is where, um, if we find that there's a, a clear pattern to the data, typically a length or, uh, some, uh, some delimiters in there, what we try to do is call that out.

So if I look at this here, um, you know, I'm seeing that there's 27 that match a pattern of two alpha characters, da underscore two alpha underscore a number.

01:15:00

However, I have one that looks like this. Now, let's take a look at the source data. Uh, and I can see here that this, uh, this looks funny. Um, however, let's, how does it look funny? Let's look at it in context, kind of walking through my normal thing, uh, you know, 28 rows, 28 populated, right?

Uh, now I can see, um, what is this? This is my region, right? NAUS, I'm gonna go North America out on the limb. Gonna guess it means North America, US one, US two, and then underscore, right? Like this, this definitely looks like something's fishy there. So again, this is a, I, I'd probably put this in the patch category.

You, uh, it's a pretty trivial patch from a data pipeline, data engineering perspective. You go to your customer, you tell them, look, we found this anomaly, uh, in the data we're gonna propose. We put a patch in place such that, uh, we fix it. If we ever see this again, you go to your data provider and you tell them what you found.

Uh, and everyone's happy and everyone's successful.

Um, all right. Um, oh yeah. So, uh, you know, a lot of this has been me going through the tool, looking at these things, compiling, uh, uh, discussing, talking about answers, right? And as I'm going through these, I'm saying, yep, that's a problem. We're gonna talk to somebody. This isn't a problem. We're gonna talk to somebody.

You know, uh, this isn't an issue this time. Or like, you know what, just frame size is a mess. We're just gonna mute it. Um, you know, best case scenario, you get data stewards domain, uh, experts in TestGen, and they're actually able to look through this, uh, these results probably, you know, alongside whoever's doing the analytics on it or the an, uh, the profiling analysis.

Uh, and let them kind of make some decisions. More often than not, though, you're gonna have to send somebody a spreadsheet, uh, where we have a nice, uh, easy download for that. And, uh, it pops up, um, all the anomalies that you found. And so what is it doing? It's pulling in everything that we looked over there.

It's non-standard values, uh, almost certainly an issue. Uh, we describe, uh, what we found, kind of what does the anomaly mean? Um, we're not a, we're not a hundred percent on everything, but, you know, we try to explain our rationale for it in detail. Uh, and then what do we think, uh, suggested action is, right?

So this one's considered cleansing the column, uh, replace the variance that we found with something else, right? Uh, and sometimes we're like, you know what, uh, review your source data and your ingestion process. 'cause we, we see that this is a problem, and it's probably not something that we can fix. So we recommend you fixing.

Again, these are, these are our value judgements on things. They, they're not, uh, um, you know, a hundred percent. So you, you gotta, you know, uh, the, you gotta use this as an advisor and a tool to build that big picture of things. Use it to help you focus in on areas of concern as well as what the data is.

But at the end of the day, you, uh, as the owner of this step in the data quality process, you as the data profiling owner are gonna need to do the, the judgment, uh, either on your own or in collaboration with your customers on, uh, what to do with your anomalies that you find.

All right? So, um, that's the, uh, the demo portion of it. Um, in the homework, uh, you'll get to spend a lot more time with TestGen. And then as we go through different, um, uh, sessions, the test general come, come back for you. Um, you know, as we mentioned earlier on, there's probably a data profiling tool in your stack already.

Um, you've, uh, SSIS like, you know, ETL tool, uh, there, uh, I haven't used it in a long time. I know there was a data profiling note at one point. Talend used to do good on, uh, you know, data profiling, data quality. Uh, they've moved away from their open source though. So, uh, your data governance tool, your data catalogs, everybody's throwing profiling in 'cause it's part of a larger data quality activity.

Um, from the open source standpoint, obviously TestGen, uh, is one we like, recommend, um, you know, there's also great expectations. Uh, the y data profiling, I think it used to be called PANDAS profiling. Uh, data cleaners got a community edition, uh, open refine the old Google, I think it was Google Project,

01:20:00

uh, PAC's got a contribution to this space. Um, again, these aren't necessarily data profiling only tools, but there are tools in the data quality space that includes some level of, uh, data profiling. Um, you know, depending on, uh, what your needs are, what your, uh, technical environment looks like, technical skillset, um, you know, TestGen serves a need.

It's got a good ui even for, um, you know, SQL jockeys, uh, or even people that like using the other tools. We, we've tried to make TestGen be the tool that finds that balance of being sophisticated, useful, and fast, and giving you a nice ui, uh, that you can, um, uh, use. Uh, and then, uh, TestGen itself isn't just profiling, it includes, uh, testing and test suites, which then, as Chris mentioned, feeds into our data observability.

So it's part of a, um, part of more of a full solution, uh, for your testing needs. Testing needs. And so if, uh, the evolution path you get with TestGen meets that, then that's a good way to go. All right, let's talk about homework. So, um, in the deck, which Chris has sent the link from, I believe, 'cause I see a lot of people in there, um, you know, there's, uh, 10 questions, five are practical inside TestGen, and five are common knowledge.

Um, you know, what we'd like you to do is before the next session in June, if you could, um, you know, send your answers to me, Eric DataKitchen.io, and I'll, uh, I'll collate those and put them together with the rest of the sessions and the other homeworks that we get there.

So, um, I guess that's it. Chris. I don't know if you had a closing slide or not. Um, I know we're into questions. All right. So, um, let me just share my screen. Yep. My video on share, my screen share now.

Um, so yeah, the, the, the questions you can go through. Um, so our, our next session is June 4th, where we're gonna talk about when you've got data that's constantly changing and being loaded, how do you find problems in it, uh, anomalies. And then the third is like, you've got a whole system of tools and data that's in production.

How do you make sure that you don't have embarrassing errors? So that, I'm very excited about that one. That'll, uh, there's a, a lot of good, uh, stuff, uh, to learn there. Um, and you know, since we just released this last month, we only got 36 stars, so help us out with some stars on the repo.

Um, uh, you know, uh, if you, uh, would be so kind. Um, and so just going through the, uh, some details, I sent you an email on this, but just to repeat, uh, first if you miss a session, don't worry. We'll email the slides and the video to, for you to review. Um, second is, uh, you know, we've got a free DataOps cookbook, uh, 250 pages, um, or the DataOps manifesto, that's the idea.

Um, Eric's talked about how to install our data observability software and give the repo with star. Um, and unfortunately, you've gotta sign up for every session. I don't, haven't found a way to, uh, to do this where you don't. So you have to click on every one of those links and, and, and sign up and join.

And, um, if you, you know, if you don't, uh, just email. If you can't do it, just email me. I'll add you to the meeting. But like, it's not too bad just to go and click your, your login information should be saved anyway. Um, and then, yeah, if you decide to have homework, uh, send it to Eric, answer the questions, that's where you'll get the nifty certificate.

And if you don't, it's not a big deal. If you're just sort of auditing class or just wanna listen in, that's, that's fine too. Um, but we won't send you our nifty PDF certificate. So, um, you know, I, I, I imagine you, you'll be crushed. But, uh, again, appreciate you taking the time to do this, um, and, uh, we'll see you all again in two weeks.

And, and thank you very much for your time.

Thanks everybody. Great day.