Trust Rules to validate your data, AgileData Engineering Pattern #3 - Episode #69

Foreign.

Welcome to the Agile Data Podcast, where we talk about the merging of agile product and data patterns in a simply magical way.

Welcome to the Agile Data Podcast. I'm Shane Gibson.

And I'm Nigel Vining.

Hey, Nigel. Another episode of Agile Data Engineering Patterns. Today we're going to talk about trust rules and a high level this time because this one has patterns within patterns within patterns. So let's start from the beginning.

Cool. So trust rules, one of those bane of engineers lives. But it was something that we believed in passionately. We believed it should be easy, largely automated and baked into the product from day one. Trust rules was something that we did by default, not as an exception after the fact. So trust rules, effectively they're data validation rules. As I talked about on our previous bytes, when a table is loaded, when new data turns up, we refresh a table. Then as I said, we use pub sub to say, hey, I'm done. What do you want me to do now? So one of those things that we do off the end of a table refresh because new data is turned up, is basically to turn around and say, cool, new data's gone to the table. We need to run data validation on that data to check it's fit for purpose. So what we then do is we turn around and we basically run a series of checks. Largely they're automated things like, are the business keys unique? Do we have a business effective date for every row? Those run every time regardless. You can't turn them off, you can't change them, they are baked in. Because if we have duplicate rows, then we've got a problem all the way downstream. The second level of checks are user specified ones. A user may say, hey, I really care about this email column. I need to make sure all the values loaded into here are valid emails. So the user will effectively say, hey, run an email validation check when this table is loaded. Once we've run those checks, we basically grab the results. We persist them into our reporting layer to notify users if any of them fail and how many of them fail. And we keep track of them so we can see the history for every table down to a column level, what validation rules have been run against it, and all the successes and passes. That's in a nutshell.

So effectively we define the trust rules which sit in our context plane. We then execute them when they need to be executed. We store the results of every execution as if it's data. And then we surface that data. Those results, we call them in any way we want to, maybe in the app Maybe a report, maybe an alert coming out via Slack, depending on the severity of that failure. It's interesting to name trust rules. You often hear them called data quality, data validation. Why did we call it trust? Actually these are the things that help people trust the data they're seeing. So that's what we want to do. We want to make sure it's trustworthy. And that's why we picked Trust Trust rules as a name. And then the other thing is if you're a data engineer, there's a bunch of automated tests you always have to build. Like you said, are the keys unique? Do we have duplicate values? All those kind of things. And as part of the trust rules pattern, we just automated them. As the user of the system, I don't have to care if I do something dumb. The system tells me I've done something dumb and it stops me. I don't actually have to keep remembering that there's a core set of things that I need to do because the machine does it for me. And I think that's part of it, is that I don't need to do the effort, I don't need to remember to do it. It just gives me a lovely message when I'm doing the wrong thing and I change what I do. So I think that was actually one of the biggest wins for us when we implemented this pattern.

Yeah, exactly. The built in rules. By default it's fantastic because we can onboard new data, you can chuck it in, you can tick a few boxes and get that pipeline basically productionized within an hour or two. And then you don't have to worry about, you don't have to go, oh, I need to make sure that's unique. Oh, I need to make sure that's not null. Oh, I need make sure that's a proper sequence. Because effectively we say Shane has just onboarded a history tile. These are the things that a history tile has to have checked to be safe. And we do those checks and it doesn't actually matter what data's going into that tile because we still need to make sure we've got a unique business key for those rows. We need to make sure that the effectivity of those rows is correct. And we're checking for nulls in some strategic places. So all of those things, there is no context, none of worrying about it. It's not an afterthought, it does it automatically. So you just load your data and use it. The check's been taken care of for you.

And then there's some things I do care About. So then I use the user defined trust rules and I can create, is it an email address, is it a phone number, all those rejects field to make sure it's got what I need, all those kind of, not custom, but those commonly applied ones, but I don't want them applied every time. And one of the keys that we found was that the logic for those user defined trust rules, effectively the regex or the masking or the formula to say is this thing, this thing is readily available out there. There's a large number of open source products where basically you can grab that library of data quality rules and you can apply it yourself. You don't actually have to reinvent the wheel for that one, but from memory. In the beginning we did actually use a Google cloud.

To define and build and execute all the data quality checks. And then a while ago, I think it was a year or two ago, we actually brought it back into the core of our context plane and our execution patterns. Why was that?

Yeah, that's right. We started out using an open source product that Google wrapped. It was basically a data quality engine defined by YAML files which described all the tests, which are basically the SQL that would be run and that was pointed at tables and it executed those tests and it produced the results and stuff. And that sort of got us up and running and gave us a flavor of how these things work. But it became apparent that it was quite technical to maintain it and run it. And we wanted to pull basically trust rules back into the app and make it as simple as ticking a box rather than have to configure the separate product outside. The second thing was it was quite expensive, relatively a little bit more expensive. With the way the validations were run. We had a little bit less control over partition pruning and using clustered columns. So effectively we took the pattern, the open source pattern, and we basically wrote our own version of it. We followed some of it, we wrote our own and effectively we sped up the tests. We can do a lot more tests in less time because there was less building on the fly happening and they were tended to be cheaper because we were doing a lot smarter partitioning, pruning, because we knew the watermark of the data we just loaded. So we effectively could pass that straight to the test and say, hey, we've just loaded 14 days worth of data. You don't need to check prior to that 14 days because we've already done that. So we did a whole lot of smart stuff to just speed up those tests and make them cheaper and that's effectively what we've had now for. It's probably come up two years, it's quite a while.

I think that's probably the core anti pattern that we see, isn't it? Is data quality on everything, everywhere, every time. So that idea that you have to scan the full table for every data quality test is an anti pattern because there are certain tests where the data becomes immutable, that test will never change. So you can run it, store it. And then there are other tests where, for example, you want to check how many distinct values there are in a particular column, then you're going to have to table scan or do something smart to reduce the cost of running that.

Yeah, and that's what I meant by we know those because we have architected the tables themselves. We know which tables are partitioned and those are the ones we will partition, scan for the data. We know which ones are clustered. So they're the ones typically where we're going to look at every key. Like a concept table of customer IDs, because we would automatically cluster the customer ID to do a is unique check on that clustered column is basically a freebie because under the covers the database knows that column is unique. We don't have to do a lot of work to do that. So on a concept we only care that the key is unique and that's an easy test for us. Whereas on a detail, we are scanning a window of data and multiple partitions because we're looking at actual attributes, like you said email addresses, but we don't need to look at the email addresses from last month because we've already checked those. We only need to look at the email addresses that have turned up today, yesterday and the current window. And we can do that very cheaply because basically we're saying just grab the email address column for 2 days, 3 days and check them. And it's a tiny shard of data and it works really well.

I think that's one of the benefits of using a column or storage engine under the covers like BigQuery, because we just grab one column out of the entire table and records for a small period of time. And therefore the compute cost, the query cost is tiny. And the other one is when you do the everything everywhere, every time is the noise problem. When I first started off, because it was so easy for me to create trust rules, I would add them everywhere and never look at them. And then I'd start getting massive amount of alerts because actually a column I really didn't care about that much. Or we didn't need to validate for any trust reasons would just keep giving me constant problems. And it wasn't that the data was untrustworthy, it was that the data didn't quite match that rule. But nobody cared about that rule. Nobody was going to go fix it in the source system. And so that noise to signal ratio is always a problem. And we can go through lots of examples in more detail around how we do trust rules. But one of the trust rules we'll talk about in the future is the idea of the load patterns. So using the load patterns for tables to determine when we may be missing the expectation level from the organization of when data should turn up. So again, you can use this trust rule results data to solve many problems, but the key is only put it on where it has value. Otherwise you spend money where you don't need to and you've got to go manage thousands of messages and you're just going to ignore them and miss the ones you really care about.

Exactly.

Yep.

Keep it simple.

Yeah, reduce that complexity. All right. That's another agile data engineering pattern in the can. I hope everybody has a simply magical day.

And that Data Magicians was another Agile Data podcast episode. If you want to learn new patterns that you can apply to craft your own agile data way of working, head over to agiledata IO.