Automated Load Patterns based on Source Data Profiles, AgileData Engineering Pattern #4 - Episode...

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, today another data engineering pattern. This time we're gonna talk about automated load patterns based on source data profiling. So, Take me away with what it is and why I care.

This one's quite an important one and it underpins our product in lots of ways. So essentially we have, we classify the two types of data that turn up in our projects as event data. Think GA4 user viewed page or sensor type data, which captured a reading of something, or change data, info about a customer, customer changing status, customer changing address, orders, an order is created, an order is processed. We do a little bit of magic when new data turns up, and we look at the data to determine the shape, if we think it's event data or change data. Event data is generally quite clear-cut because it'll have event timestamps, it'll have an event name, it'll have attributes that basically give us a pretty good clue that it's event data. But what we also do is we look basically back over the last 90 days of that data and we profile it to see what shape of it, how many records are turning up each day based on the timestamps or the business key that we know. So if we have business keys and we see changes of the data on the business key, we're fairly confident that it's change data. So once we know whether the data is event or change data, we lightly tag it. And then what we do is when we go to load it, this is where we determine which load pattern we use. So, you know, we use concepts, details, events. Concepts, we always use an insert pattern, and that's fine whether it's event data or change data, it doesn't matter. All we're doing is inserting a new key when we see it. When we get to detail about the data, this is where we change. So for change data, we run an upsert pattern. So if it's change data we haven't seen before, we insert it. If it's change data we already have, but it's changed, the row hash has changed, then we update the existing row to mark it as effectively ended and we insert a new row. Whereas if it's event data, event data is generally only given to us once 'cause it's just a long stream of something happened, something happened, something happened. So what we use for that is we don't need to go looking back through the whole table to see if we've got it. We use a simple partition replace and we basically just delete the last couple of days of partitions And then we insert those events again. So we're always basically putting the new events in at the end of the table and we don't go looking for them, cuz this is a really cheap and fast pattern. So that's effectively what we're doing with this pattern.

So we are looking to say, has the Dataweg been given immutable? It's an event that happened, we'll never be given that exact same event again. That event never changes.

Yes.

So that's typically GA4s, we use it a lot. Somebody viewed a webpage. And if it is, then we do an insert pattern. We just basically insert those records because we know we don't have to worry about whether they have changed or not, because they're all immutable and new. Otherwise, it's a different pattern where we know the data might change, probably comes from some form of relational schema in the source system. So we know that the customer name might change or the customer address might change or the order quantity may change. And we therefore have to manage those changes and rack and stack them. So that's, therefore we do a, an upset pattern, we effectively end date the previous record and insert the new record.

Correct. Yes.

That end dating is quite an old pattern. It's a bit of a ghost of data past. Why don't we do that fancy one where we don't end date it and then just leave the insert dates and then go through into a whole lot of windowing functions whenever we need to determine what is the latest record?

A lot of that's around cost, we partition by those dates. So we can quickly look at the current data. If we weren't to do that, it means we would be looking across potentially all the rows in the table every time for each business key to find out where that record started and ended. So there's a lot more overhead to do it dynamically. Whereas to do a quick lookup, because it's a partition, and a clustered key, we can do it and then date the record very cheaply.

Cool. So it's a cost decision that we've used to pick that pattern.

That's exactly right. We could definitely do it with a lot less complexity just to load all the records and then at the end do a window, as you said, look for the latest record and mark it as current. But that's more processing and we have to read more data to do that pattern.

Talk me through the way the profiling works. How does it actually determine whether it thinks it's an event or change data?

So the profiling is based on the change rule that the user creates. So the user will create a rule, say in this case, a concept rule to load a concept type. Once we have that rule, we can effectively run that rule, effectively bucket the results that come back to work out how many unique keys are appearing per day. And we also check the column names as well, looking for identifiers that we know that event data typically have. But profiling generally tells us because if we are getting lots of unique keys turning up every day, it's generally indicative of event data because you don't create, say, 200,000 customer IDs every day. You may create, you know, 100 or 1,000, but not a large volume. So if we see 200,000 keys turning up every day, those are probably all unique event keys, and we're pretty safe with that. So we use some thresholds in there based on what we know event data tends to look like.

And why do we do it? Like, why don't you just force me to tell the system what kind of data it is when I start to collect it?

That's a good question. We're effectively removing a little bit of cognition from your load. And also what happens is it means we can onboard data a lot quicker because typically when we prototype a new load for a customer, we just keep giving them a file. We may not know if it's event or change data. We just load that file and effectively we're letting the system do that quick profile for us. So you can happily go and create rules on that. And 99% of the time it's gonna go, cool, it's event data, I'm gonna load it like this. You say, oh, great, it's change data, I'm gonna load it like this. The 1% of the time that we get our guess wrong, it'll still load, but effectively we would generally find that the second load throws a few errors and warnings because we are not loading the data quite right.

And that's where the trust rules come in. Those automated trust rules will give us an alert to say, this has been flagged as event data, but we are starting to see that the customer key is constantly getting change records coming through. That's an anti-pattern for the pattern that you've been selected. Therefore, you probably need to go and change the config and context and rebuild some stuff so that it's safe, trustworthy.

That's exactly what happens. Effectively, the first thing we get is usually unique key points because we're always checking for unique records. If we're not loading the data quite right, it'll start to create duplicates very quickly. The other thing we check for is the effectivity date. Of those records, because if we're getting lots of records turning up with the same effective date and we're trying to update based on that, it's also an indication that we're possibly using the wrong pattern because we don't have an effective date for each business key. So it's more likely to be event data.

And then anti-patterns, where wouldn't you use this or what's the gotchas you gotta watch out for?

The anti-pattern is where we get data turning up that's very different to what we've seen and what we've based our profile on. Follow on. Like an example would be event data, but very small number of events, say, 5 or 10 events a day. Whereas typically events in the magnitude of thousands, tens of thousands, hundreds of thousands, you wouldn't expect to see 4 or 5 events being created each day. So that would trip us up. We would go, oh, that's change data because that volume's so low. And it would load all right for a day. And then we would, on day 2, would start to get key errors because it would start to duplicate the data.

I like the way it effectively gives us a hint, it picks it for us. And then if something goes wrong, it tells us and we can fix it. I like that because often we have customers that just send us their data. So they're not comfortable with us reaching in and collecting it, connecting internally to their systems or their cloud systems. So we give them a secure bucket and they effectively just dump some data in there. And we've never seen it before. So this helps us do that first bit of design around that data. I think with the new AI wave, there'll be some interesting ways we can enhance it. In theory, as we see more and more systems, we could actually use that as a hint to one of the LLMs to actually say, here's all the patterns of data we've seen before. And then we could actually tag certain systems like Google Analytics to say it is event data so that when we get a customer that's only got 3 events happening through Google Analytics on a daily basis, it's going to drop back to say volume's too small. Let's go look at some of the other patterns and use a judge type pattern to decide which one wins and also flags it to say not enough volume to actually determine the profile properly. Fill back to schema detection. You probably want to go and look at this one and just check it or wait to see if you get any alerts to say the data's not so trustworthy. It's all about racking and stacking those patterns together to automate that work. Excellent. 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.