Data Match, AgileData Engineering Pattern #7 - Episode #75

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 Data Engineering Bytes. Today we're going to talk about a feature that we term data match. So tell me what it is and why I care.

DataMatch came out of the age-old question, how do I know something in my source is in my target? Or as we like to say, what's in the left isn't in the right, or vice versa. This is generally called a data diff in a lot of places. Generally, it's a pattern of doing an increasingly granular match of something on the left, which is generally a table of data and something on the right. So we start off and we say, is the number of records the same? Yes, it is. Great, cool. Are all the keys the same between these two tables? Yep, they are. Cool. Now, is the date of births in the table on the left the same as the date of birth on the table? And we effectively, we go from the very wide row count down to something very specific. Do the actual values match from left to right. Now that all sounds very straightforward, and it technically is, but under the covers there's a whole lot of SQL and engineering patterns that are happening to basically run all those queries. So that's not something we would expect an analyst to generally do because it's a bit of a faff. So we came up with this feature called Data Match where we effectively let a user pick a table on the left, pick a table on the right, pick the thing in each table they want to double-check and hit go. Under the covers then we optimize those comparisons and then we produce a report straight back to the user saying these are all the things in the left that aren't in the right or vice versa. So we've made it a 1, 2, 3, 4, 5 click exercise and you can reconcile anything in your environment, and it's easy.

I think this one, from memory, was an interesting problem. So we had a customer that we were doing the data work for. They had a series of business logical rules that was in a fairly horrendous Excel spreadsheet. So we used our way of working and we extracted the concept of those rules and we modeled the data properly and we applied those rules. And whenever we were trying to reconcile the numbers we got with the numbers in the spreadsheet, we were always one thing out. So let's just say it was reconciling customers. They would have 100,001 customers. We would have 100,000 customers. So we'd manually go through, find that one customer, work out that it was a timing problem, make sure we ran it at the same time. There would be one customer out, we'd go and check it, and then there was a but a logic that they had, they didn't tell us about. So we had to add that rule. And somehow we just got into this loop where we always won a customer out and it was always for a different reason. But the cost of doing that manual reconciliation was horrendous. DataMatch allowed me to go, I can run that really quick. We grabbed the Excel data, I'd upload it, just dump it in like we do, get a new tile, compare it to the numbers we were producing consistently, and it would then highlight the one record that was out in a very short amount of time. And then I could spend all the time trying to work with them about why they had this record that we didn't or vice versa. So yeah, it just, again, took something that was many hours of frustration and made it minutes, which was great. The idea of layering those rules though, that's important because otherwise you're just going to brute force two very large tables, match everything, and that is going to cost you a shit ton of compute, a shit ton of credits, a shit ton of tokens, depending on how your cloud analytics database vendor is charging you for that compute.

Yeah, so we poked a couple of reasonably well-known open source libraries when we first started, because we're like, we're not going to reinvent the wheel, this seems to be a fairly solved thing. Surely there's just going to be a package we can pull down, point it to two tables and hit go, and that's— and we will run it. And technically there are, and we did start with one, and it did work. Where we tend to run into, where we ran into troubles was it needed quite a lot of configuration. So we effectively had to come up with a whole wrapper to pass it enough configuration to make it work. And that was fine. That was more just a bit of app development to give it what it needs. But then some of the problem was it was, as is usually the case, it had been developed to run on a particular database. I think it was Postgres from memory, which is, quite common, or MySQL. So it was heavily skewed towards a row storage database and how row storage databases work. And so it was optimized to the queries. So the queries, when we came to run them on BigQuery, they ran and that was fine, but we didn't really get any of the benefits of BigQuery being a columnar database and partition pruning and the like. So we played with it and played with it and it got closer and closer. In the end, we thought actually it'd be just quicker to write a template that would run on BigQuery and will effectively do the same thing, but we'll make a template and make it specific BigQuery. And we did, and that's effectively where we got to. So we can optimize what we give to BigQuery. So it's very efficient and it runs very quickly and it doesn't really cost us anything 'cause we, know where the performance and cost savings are with BigQuery. And that's how we got to our pattern. We effectively just took an open-source one, found the strengths and weaknesses, and then rolled a variant of it for us for BigQuery.

And I think the other thing is we only run this when we need to. So it's not baked in as a core trust rule for every movement of data through every layer for every tile, is it?

No, this is effectively an exception thing. This is when something goes wrong. This is somewhere where we can quickly go click and say, ah, there's 10 customers that aren't in this table where we'd expect them to be. So it's a really quick way without having to regress to SQL and go and customize something because it already has all the tiles in a catalog loaded. You can just go pick tile A, pick tile B, compare them, show me the differences, and go away. So it takes the first layer of context the overhead, sorry, of thinking about it, gives you your answer in a report. Then you can go and do, as you said, do the analysis, because now I've got a list of customer IDs that haven't flowed. I can grab one of those customer IDs and actually go specifically look for it. And that's a really quite simple proposition because that nicely flows on to some of the other features we've built around looking, tracing values.

I remember when we did that data migration use case, remember where we grabbed data from a legacy source system and then pushed it through us and then made it available available as an API so that the new vendor could migrate the old data into their new platform. And we had that gentleman's agreement, which was we'd do all the logic to match the new business rules for the new system. So effectively they'd hit the API for the data, grab the data, load it straight into their system, and there'll be no transformations between those steps so that we always knew when we needed to change the way the data looked, it sat with us. And when we did that test run and all of a sudden let's say customer again, we passed 100,000 customers out and only 80,000 turned up in their system. And we spent all that time manually trying to figure out why. And actually the answer was they had done some changes on their side between getting the data and loading it through their APIs, even though they said they wouldn't. If I had just been able to take the final result that they had loaded from their system and backflushed it in as a tile and then say compare, that would have told me exactly which records didn't make it. And then yes, I would still have to talk to them about how come they didn't make it? But again, that would've saved hours, if not days, of proving. We sent 100,000, you loaded 80, we know therefore it's somewhere between those steps and it's nothing to do with everything to the left of us. Would've saved us time if we had have built it back then.

Yeah, it's, that's why I guess it's in the app. It's what I would call a support feature. It's something we don't use very often, but if we need to, it's there to quickly do something. We don't have to remember, how do I data diff? What queries do I need to run? Grab out some queries, change the table names and the key names, and to run them again, it's click clack, there's my report. You know, it's a small overhead, but when you're trying to do a whole lot of things, you're grateful for it.

Yep, hours to minutes, that's what I care about. Excellent. All right, 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.