Data Engineering Patterns for the AgileData Event Tile, AgileData Engineering Pattern #5 - Episode #71

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 in our data engineering pattern series, and today we're going to talk about event modeling. I think what we'll do is I'll start off talking a little bit about the concept of event modeling, and then from there we can deep dive into the engineering practices and patterns that you use to make the thing happen.

We've talked before about. We hold a three layered architecture. Data comes into history, we then move it into a designed layer where we mesh it all up, make it fit for purpose, and then we have a consumed layer where everybody comes in and uses that data. Within that designed layer, we have three major object types. We have a concept which really is a thing we see it holds effectively a key for a thing that we know exists. Customers, employees, suppliers, orders, payments. We hold another tile type, object type, which is detail, which holds the attributes about those things. So customer name, customer date of birth, supplier type, employee, address. And so those details are bound next to the concepts. And then the third type we have is an event, which basically says we saw a business process or a transaction happen. And within those event tiles we have two types that we deploy. The first one is our standard event. This is where we see a business process happen. A customer orders a product, the customer pays for an order, somebody ships the product or the order to the customer. Customer returns a product to an order. And a little while ago, we introduced a second pattern that we use conceptually called an activity event, and we can talk about that next time. But for now, let's deep dive into this idea that we've actually got a business process or a business event, and we model it as an event.

Yeah. So effectively what we do to make this magic happen is an event tile is basically just a collection of keys that something happened at a point in time. And these are all the business keys that are associated with it. So as you said at this date, this customer id, this order id, this product id, we effectively create that row in our event table of customer orders product, and then the next step is we automatically hydrate that event into a consumed tile. So we effectively take that event and we join it to the details that are associated with it at that point in time. So the consumer is quite wide because effectively it's got all the customer attributes at that point in time, all the order attributes in a nice wide table and that's all done at each point in time. So then when something else happens with that order or it's changed, or its status change, we effectively insert another event record which generates another consume record out the other side to reflect the change to some aspect of that order. And it also picks up all the same product attributes and customer attributes at that point in time. So it's quite a simplistic thing because the tile is really quite boring. It's just a whole lot of business case. But then when we hydrate it into a consume, it becomes a really rich wide narrative for these events that are happening.

And so effectively that tile just holds a small number of columns and unique IDs or keys sitting in there. So we found that when we model it, it's typically no more than seven or eight keys in that table.

Yep, seven or eight is getting up there. I haven't seen many past four, but yep, I think seven is pretty much acknowledged as generally as wide as you go for those keys. I haven't seen any business processes that tend to need more than that.

And because we use that format from Lawrence Core's beam of who does what.

Yes.

We end up with event tables that are always more than two keys. So we don't relationally model our event tables as two way links. We're effectively modeling that core business process using that language. So we always have, like you said, three to four to five concepts being part of an event.

Yeah, and it is generally some variant of a who does what who being a customer for example, or a user that does being one, what they've done, they've ordered something, they've created something, and then the third part of the key is usually the what, the thing they've acted upon a product.

And one of the key things for me is because the order or the payment, the thing that typically holds a value like quantity dollar is still a concept and it's part of that event. The events behave like a fact table from dimensional modeling, except we don't store the numeric attributes in that event. We get those attributes when we build the consume because those attributes are held by the order concept. And if the event is customer orders product, then we know that the order quantity order value is sitting there and we rehydrate it. But it's different to a fact from dimensional modeling because we don't hold any attributes at all on that event tile, do we? There's no detail on the event tile if I ever go and query it. It's just a bunch of keys.

Yes, the Detail comes from effectively the concept keys. Concepts are associated with attributes. So yes, we don't store any attributes on that event table because we don't need to because they are sitting in our detail table. So when we hydrate that, we get the applicable detail attributes at the point in time.

The other thing is it's a form of a link table from data vault modeling. But we don't follow the data vault 2.0 standard where there is detail directly connected to that link table event table in our term, it's not a link table because we don't follow the rules. So we take the part of the pattern we liked and we created a variation or our own pattern for it. The other thing is those event tables are insert only. So we only ever insert new records in there. We never do any updates.

That is correct. We check if the unique situation that we're about to put in there has already happened. And if it hasn't, we then insert that new row of keys effectively.

And then how do we deal with effective dating or dating those events?

So what we do is the event table has an effective date. So we say at this point in time or this business effective date, this customer had a relationship with this order and this product. So we've effectively we've created our slice. And then when we join the attributes to that row to hydrate it, we basically pick the attribute that was current at the point in time of that event. So we say what customer record was the one that was applicable because it may have changed since the customer may have gone on and done something else. So they've updated the address, they've updated something else or their status or whatever. So what we do is when we hydrate the that row in the consume, we hydrate it at the point in time the event was created. So it is always accurate for when it occurred.

And then there's also this concept of a driving concept. We call it do you want to talk me through that? And how that works from an engineering point of view.

So we pick the first concept in the event as our driving concept and that's the one we look to. When changes occur, we insert a new event row. So customer orders, product, and the driving concept in that case is likely to be the order. Because the order is created, the order is updated, it's fulfilled, dispatched. So we want all those changes of state of the order. So that's the one that we drive a new record. So when that order is updated, we insert a new record and we pick up the applicable customer and product at the time. If that order is not updated, we don't create a new event row because we're not driving off the customer or the product. So if the customer's updated, it doesn't matter because this is an order event.

And just to clarify that, when we talk about order being updated, what we're talking about is a new order ID turning up. So if a new order number turns up, we will then go and drive an insert of an event that related to that order. If the order quantity changed, we will then go and drive an insert of an event that related to that order change.

For that instance, we would, because the order is the driver. So that order has changed. So we want a new record to basically capture that the order has changed. So when we hydrate that record, you can see that the order quantity has changed on it.

Okay, so we're effectively doing an insert of every change of that event.

Driving off the driving concept.

Yep. And then we don't end date any records. So now we're using a windowing function to see which event was active at a certain point of time and therefore what details relate to it.

Yes, that is exactly right.

So what happens when we go and get data from somewhere like Google Analytics, which is another type of event, and it's not really customer orders product, it might be individual views web page. Do we treat that differently at all?

We've done both versions of this. We went through a phase of treating native source events as events, and then we went through a phase of basically saying an event in that situation can be treated as a concept, because every row from GA4 is effectively a concept because it's a marker that someone did something and it was captured. And then the second part of it is the detail is what that something is. So GA4 is a little bit unique in that it arrives as an event. So we can treat it as a concept or we can model it as event by splitting keys out. They both get the end result. One's just a little bit more technical because you're unbundling the event just to turn it back into an event.

If we think about that. So we take what is an event record and then we unbundle it to its component parts, a bunch of concepts and details. And the idea that there was a relationship or an event happened, and then we rehydrate it back to itself, because then we have a single pattern for how we load events, regardless of what type of data comes in. And that makes us far more efficient in terms of hardening our code and troubleshooting and understanding the stuff.

There is use cases where we would split event that's coming in when we want to report on the individual concepts that are bundled in it. So if there's a requirement to report on all the unique, say for example page titles coming through, that's where splitting it out into a page title or page concept makes a lot of sense because then you effectively have this master tile which has all your titles in it. So for reporting you can go straight to that. You don't have to go through a convoluted pattern just to get a unique title.

One of the examples here is we have customers that hold custom attributes on their Google Tag Manager GA4 data. So when it comes through and we know we're going to have to report on how many of those unique things there are, it's much cheaper for us to break those out to be concepts so we can report on them because they are things we want to manage versus having to query across all the hydrated events to then find that attribute. And on that, if you think about it, we're creating some of these tiles and then we're having to join to the concepts and then join to the details. There's lots of joins and we run on bigquery. So isn't that an anti pattern to join all that data? Doesn't that cost us a fortune?

That is a really good point. Joins do come with an overhead and a cost and a computational cost. We mitigate some of that by making sure the amount of partitions we are presenting to join and the keys that we join on are clustered to effectively bring down that overhead. But yes, every join we do comes with a little bit of overhead. So when we have to do it, we mitigate it by doing the things that keep it efficient.

What's the anti pattern for event tiles, do you reckon? When wouldn't we use this pattern? We did try using season Ds only because effectively I could create a concept of an order and then I could put the customer and the product and the detail for it. Yes, but we found as soon as we got complex changes within the event, like the customer's address changed, then it became a real problem to try and query it and event solved it because it gave us view of everything about that event at the time the event happened. I can't think of an entity pattern when we wouldn't do it. I think if we weren't modeling exactly the way we're modeling, we probably wouldn't use event tiles if we didn't have the simplicity of concepts, details and events as being the only three types of objects that we model in our design layer. We may have done something different.

Yeah, C's and D's definitely have their place because it's really easy to take historized data to wrap it in a concept and the detail and then rehydrate it as a consume. And for some data sets that's absolutely fine because we pass it straight through. And that's all it is because the concept is really simple. It might be simple attributes about a user. So we've got a user ID as the concept and then the user detail is the user's name and login and stuff. So it's a really simple thing and it's self contained. We get a file of updates for users when they last logged in and that's a good example of a cnd. And we just basically rehydrate that as a user tile. We've got our a unique list of users in the concept and that's fine. And we might just run that as it is. And then as you said, we may have another consume, which is something else. And then we would create a rule to put them together at the consume layer. Because sometimes that is just straightforward.

And I think that's a simple case. And then we get the complex cases, which is things like status change, invoice was entered, invoice was updated, invoice was approved, invoice was paid. And so that used to always cause me massive problems trying to decide what the grain of the event was, what was the trigger to say that a new event turned up or a change to that event happened. Which probably leads into this idea of activity events that we added as another way of doing the event modeling for a whole bunch of reasons, but I think we'll keep that one for another day. So 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.