Shane Gibson and Nigel Vining explain how to automatically choose the right load pattern for your data. The system profiles 90 days of history and column names to sort data into two types: immutable event streams like GA4, or mutable records that need upsert logic. Automated trust rules catch when data doesn't match its expected pattern.
Chapters
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. Today another data engineering pattern. This time we're going to 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, 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 are orders, order is created and 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 eventual 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 panel 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 upset 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 because 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 because this is a really cheap and fast pattern. So that's effectively what we're doing with this pattern.
So we're looking to say, is the data we've 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 where we use it a lot. Somebody viewed a web page and then 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 where you 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 ghosts of data past. Why don't we do that fancy one where we don't end data and then just leave the insert dates and then go through and do 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 end 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 rules, say in this case a concept rule to load a concept tile. 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 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 a thousand, 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? 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 given 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 that. And 99% of the time it's going to go, cool, it's event data. I'm going to load like this. You say, great, it's changed data. I'm going to like this. The 1% of the time that we get our guess wrong, it'll still load. But effectively we will generally find that the second load throws a few errors and warnings because we're 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're 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 in 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 effectiveness 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 got to 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 follow on. Like an example would be event data, but very small a number of events, say, you know, five or ten events a day. Whereas typically events, you know, in the magnitude of thousands, tens of thousands, hundreds of thousands. You wouldn't expect to see four or five 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 right for a day. And then we would, on day two would start to get key errors because it would start to duplicate the data.
I like the way it effectively gives us a hint that 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 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 three 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 is 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 Sam.
