The Hook data modeling pattern with Andrew Foad - Episode #63

Foreign.

Welcome to the Agile Data Podcast where we talk about the merging of agile product and data patterns in a simply magical way. My first book, An Agile Data Guide to Information Product Canvas, is out and published on Amazon.com if you have a problem capturing data requirements, where you hear your stakeholders say, hey, that's not what I wanted, and you hear your data team reply with hey, but that's what you asked for, then you probably have a problem capturing those requirements. The Information Product Canvas is a quick and simple way to capture those data and information requirements in a repeatable way so stakeholders love them and data teams can build from them. If you want to find a way to solve that problem, hop over to Amazon.com and search for Shane Gibson Canvas book or head over to agiledataguides.com and click on the Green Book link to buy a copy. Now I hope you have a simply magical read. Welcome to the Agile Data Podcast. I'm Shane Gibson.

And I'm Andrew Fode.

Hey Andrew, thanks for coming on the show today. We're going to go and have a conversation about hooks. I'm going to suck out of your brain what the hell it is and how we can use it. But before we do that, why don't you give us a bit of background about yourself?

All right, thank you, Shane. Thanks for having me on. Right, where to start? Maybe really quite early because I guess my data journey started very early on in my career. I worked in Manchester in the UK and I had a very enlightened team leader who introduced this concept of data modeling. Nobody was doing anything like this at the time, in the early 90s and it's formed the foundation of my entire career. So I've used that in designing databases and that kind of stuff. I moved to Australia in 2008 and took a job as an Oracle developer. I've done a lot of Oracle development, database design, that kind of stuff and got made redundant from that job. First time that had ever happened in my career. Ended up taking a job as a business analyst, which was the worst possible thing I could have done because I'm really not a very good business analyst. And then I got sick, I got really sick and I was diagnosed with stage three Hodgkin's lymphoma and and I end up losing that job because of it. I went through chemo for about six months and I was out of work. So I got to the point where I was really bored scratching around for a job and got a offered a role for a small pharmaceutical company who were doing Reports on data and they were doing stuff with FoxPro and that kind of stuff and it was, it was a really good job. But they did. It didn't pay very well, but I had a great time and I actually built a solution for them that involved building an ETL tool. This is before I even knew about data and analytics type stuff. So that couldn't go on, that wasn't sustainable. I couldn't afford to stay there and ended up applying for a role with a government role and they were asking for a data modeler. So I thought I'm a data modeler, I can do that. And it turned out that they wanted a dimensional modeler. I. And I'd never done this before, never done anything like this before. So this is the kind of how it fell into place. So I applied for the job, didn't have the credentials or the experience to do it and got the role anyway. Got the book, read the book, Kimball and Margie Ross book and learned it. And that was my journey into data analytics. Off the back of that, it was all Kimball type stuff until I, I learned about this thing called Data Vault and it was like, oh, that's interesting. That's an interesting thing. Another type of modeling. So I tried to find projects in Brisbane, that's where I live now. And there weren't anything, there was nothing around about that time, 2013, 14, really wasn't anything going on in that space at all. So eventually I managed to get a role with a consultancy. This is five years later, it's probably about 2018, 19. And that's when I started getting into Data Vault projects. And off the back of that and seeing the problems that there were with Data Vault, that's where Hook came from. It's my attempt to solve the problems that I had encountered with Data Vault and that's where I developed the ideas for Hook to have a simpler approach for data warehousing.

Yeah, it's funny that Brisbane for some reason was the ground zero for Data Vault in Australasia. It's weird. Yeah. It seems to have the earliest Data Vault projects in this side of the world that I can find.

That's interesting. I thought it would have been Sydney, but that's thing. Okay.

Yeah, there's a couple, I think a couple of key people, I think Knowles, Ebison and a few others. Yeah, I think they, Yeah, I think they adopted it early in some Brisbane companies and then it just went from there. So it's funny when you look at things and where they stick and start and get traction okay. Kimble to Data Vault, to Hook.

Yes.

What is Hook?

Hook is my attempt to solve problems that I encountered with Data Vault. So one of the key projects I worked on, and I might have to drop some names here because I do that, I worked on a project while I was working with Deloitte, one of their clients, and John Giles was on that project. I thought, this is great. This is one of my idols, absolute idols. He'd written literally one of the books on the subject, the Nimble Elephant. And I thought, I'm going to learn something here, really, I'm going to learn something. And the project, and I think John would probably agree to this, was an absolute disaster. It was awful. And then we had all sorts of issues. Now I'll go into all the details of why. It was a bit of a nightmare. But one of the problems is that Lloyd were put in there as a body to deliver a project. They wanted to get to the end. Yeah. But they were thrown this bone that they had to deliver it in Data Vault. They wanted a data vault solution. Most of the guys in there weren't really that well trained, but they did it all the same. So they big consultants, they never say no to anything. They never turn. They'll never turn down work. So, yeah, I got involved in this, got working with John, and it was difficult. It was really difficult because we had three modelers trying to do modeling work. We had a team of engineers who were desperate for us to go and build stuff. And we were the bottleneck. We were always the bottleneck. We had difficulties because we were working on different parts of the model and how to coordinate our efforts. So somebody's working on a dental model. Somebody else is working on a physio model, and they're using different terminology. So it's, are we talking about patients or clients or what's the right language? And because of that, we were just not getting stuff done. We were hitting bottlenecks. We had to have agreements to try and agree what terms are trying to bring models together. It just didn't work. So it got to a point where I thought, there has to be a better way. It's usually that question. And some certain questions were like, why do we have to do the modeling up front? Why do we have to do this effort up front to do the modeling? So we've got a structure that we can target. Is there a way that we could perhaps not have to do that? So that was one of the main thoughts. And that came from a discussion that I'd had with one of the lead engineers. I said to him, it was a bit of a tongue in cheek question. I said to him, I said, look, would it be acceptable if I gave you a satellite table that wasn't connected to anything? No hub, no link? And he said in all honesty, said, yes, give me that. Because if you give me that, I can go and build the reports off the back of that. He didn't care about the vaulty stuff. It was a hindrance, it was in the way. He wanted to get to the right hand side as quickly as he possibly could. So that got me thinking, okay, so if we maybe did that, presented the data in its raw format, at least the engineers could actually start to build something and then could we layer the model over the top after the fact? So that was the thinking behind Hook, and that's where it started to evolve from there.

So what is it though? So it's a different modeling technique. So Hook is a bunch of modeling patterns, Correct?

I don't like to say modeling because I don't think it really is modeling. For me, what modeling is when you take some data and you change its structure. That to me is what modeling is with Hook. You don't do that. Whatever comes in is what comes out the other end. All you're doing is you're adding some additional information to it. So basically you're aligning those objects to business concepts. So business concepts that you would have for building hubs in data vault, it's the same concept, but you're not changing the underlying data. You're basically tagging those assets align and formalizing the business case.

Okay, so if we say though, that modeling is providing context to the data and we may do that physically, we may do that logically with metadata. There's different techniques on how we apply context. And we may change the data structure or we may not. We may change the data values or we may not. There's a whole lot of techniques and patterns we can use to the way we apply a model. So let's say that's what we define for modeling for this conversation. And then I go back and I go with a dimensional model. I know I have a dimension object and I have a fact object, so they have physical objects. I can also say from a concept, from a context point of view, a dim is a core business concept, customer, product, employee, and it holds the attributes about that core business entity or core business concept next to it. It's actually embedded physically in the same table, but it holds the key and it holds the attributes. And then a Fact table is the other object. And that basically holds kind of event concepts, order, payment, those kind of things. And again, it's holding all the attributes closely bound to it. I then go to vault and I say, we're breaking the physical model out a little bit. We say instead of a dim, which holds the key and the attributes, we split it out into an ensemble table to hold the key table to hold the attribute. And then our link table, our relationship table, is effectively a weird form of effect. So if I can describe those in those terms, use that same pattern to.

Describe Hook, we only have really one object type and it's called a frame. I call it a frame. So basically, you take your raw or your landed table and you wrap it, you frame it. It's a nice way to think about it. So the data isn't transformed, it's just a wrapper that you put around it. The additional things that you add to that are formalized business keys, which align to those business concepts. So you're adding formal business keys, and it's the way that you build those business keys that makes Hook what it is so that you, you get one table in, you'll get one table out. You don't have to break out the model at all. All you're doing is adding to it. So I guess the beauty of that is you could implement that as say, a view, so you could create those hooks as views, or you could have a physical table. You just add a column, calculate it, drop a column if you want to get rid of it. So it's really agile in that respect. You don't have to refactor the model at any point. You can add to it, or you can drop things. If you want to get rid of certain keys or you want to refactor the keys or realign to different concepts, you're not changing the overall structure. The granularity stays the same. We're not doing anything complicated to it at all. We're just organizing it. The analogy I like is it's like a library. You take a book that's your data set and you're going to put it on a shelf because you want to go find it afterwards. So you put it in. Here's a book, it's on history, you put it on a section in the library, which is history. Same kind of thing. If a data asset has information about customer, it's got a customer key on there. You align it to customer, but you can also align it to order or whatever other concepts you have you've defined, and that's it. It's not complicated. It's like data vault, I guess, but you've just collapsed all those business keys into the satellite. That's really all it is.

Okay. Which eventually is a dumb, right?

No, because all the contextual information is there as well. Obviously with a Kimball model, you are having to restructure the data. You're putting all your f facts and your measures into fact tables or your contextual information goes into the dimension. You're juggling and moving things around. With Hook, you don't do that. It's one for one. You don't have to think about modeling. All you've got to think about is where are the business keys? Identify those, formally define them, and that's your model. And basically you've got a big bus matrix then concepts and assets or frames. That's it.

So let's do some simple stuff. Coming out of Shopify, I've got customer table and I've got orders table.

Yeah.

So we're going to grab the customer table from Shopify and the order table, we're going to bring that through effectively in any layer that we have. So let's assume we have a layered architecture. Maybe we don't, but let's assume we do. Each layer is effectively going to still have those tables. So when I'm at the consume layer, the layer that the last Mile tool hits to query, I'm going to see a customer table and I'm going to see an orders table. So that's one of the core patterns that you're talking about?

Not quite. We need to talk about the scope of what Hook actually is. So Hook is about the data warehouse component. So it's not about the ingestion. We were assuming that we've got patterns to ingest the data. It's that data warehouse bit, which is. It's the Inman criteria, the subject oriented integrated time variant. And then immutable, it's that bit. After that you've got a consumption bit. That's when you have to do some modeling. But the idea is because you've organized things in the Hook structure, what we found is that you don't need to do too much modeling. If you give that information to a business person, they've got the business keys identified in those tables. They can figure out how to join things up themselves quite easily. So in the example you gave, you got customers and you got orders. So on customer, you'll have a customer business key, I assume. So you formalize that key and you have a HK Hook customer field in that table. So you straight away look at that and go, I know that's got a business key for a customer on the orders table. You'll have one for the order. You'll also have one for the customer. You'll have two business keys formally defined on that structure. So straight away, because I've got HK customer on both those tables, I know I can join them together. So you can just give that. I won't say self serve, because I think that's a bit of a broad term, but you can give that to an end user and they can figure it out. Yeah. So if they can do a lot of that legwork themselves, then when you want to do some specific modeling for specific use cases, because there's some kind of more complex logic that you want to implement, it's much lighter, you don't need to do too much. And we use the term gem as just enough modeling. And the way you look at it, it's like a pyramid. So at the bottom of the pyramid you've got, we've ingested some data which probably meets most use cases. You could probably give that to an end user because they're probably quite familiar with the source systems that they've ingested data from. Got source system experts, they could probably work it out. Then you add the hooks, and that gives another layer of interoperability between those data assets. And then if we're still struggling to meet specific use cases, we do the little tip on the pyramid to actually do those very specific use cases for the user. So it's modeling light because you've organized, you don't need to do too much modeling. That's the idea. But those model assets in the tip of the pyramid, really, you don't need to do too much with them. It's a small subset of assets compared to the big bulk of the data you've ingested.

Okay, all right. So again, just anchoring. So if we anchored typical, probably traditional now three tier data architecture. So three layers, let's use the terms raw, designed and consume. What you're saying is a raw is persistent staging or whatever you want to call it, it comes in, we ingest the data, it's stored whichever way that needs to happen for that. The designed layer, what we used to call the edw, that is where hook comes into its own. And then the consume layer, we do some other modeling techniques to make the data consumable and reportable. Okay, and so in that design layer, when you talk about hotkeys in my customer table, are you saying that I'm going to append a column called hk and that's going to hold the key, or am I going to tag the column that has the key already?

Well, you need the value so you can actually join on it. So yes, you've got to put a. I want to say a physical, because it could be a view. But yes, you've got another column, you've got an additional column in there, but it's named a certain way and it's calculated a certain way. So the. The name of it. So you've got HK customer needs to be one of your core business concepts. So you'll have a list of core business concepts. You don't need an enterprise data model. You need an agreed list of what those core business concepts are. And that's the same for any of these kind of techniques, even with Data Vault. But the thing is, that list is probably quite short. It's not as big as people think. It's not hundreds and hundreds. My belief is that number needs to be probably around about 100. It really is as low as that. And that probably covers most organizations, I think, even complex organizations. That's probably enough business concepts. And there are techniques within Hook that enables you to do things like subtype using a structure we call a key set. So this is a basically a qualifier that you put at the start of the business key to go. This is what this key actually represents.

So let me play it back to you. What you're saying is, rather than what lots of people do, which is create the data model and then reverse engineer the data model into the business glossary, you define the business glossary first and then you apply that glossary effectively against the data to give us the model. Is that what you said?

All we're saying is you can't create a hook or a formalized business key unless you've got a definition for that concept in your glossary. Yes, that's right. It doesn't stop you loading the data. You can still load that data, but we can apply that afterwards. But yes, that's one of the hard rules in Hook is that it has to be in the glossary and you really should have a definition for it as well.

Yeah, we should, but often we don't. Often we give it a name and then we define it after the argument.

Yeah, that's right.

And so therefore, by having a separate field in terms of a view or a physical field, that's going to allow us to deal with things like concatenated business keys and all those other complexities. But we wouldn't surrogate it, would it? So that you wouldn't typically have a surrogate as a H key. It's typically going to be a business key.

No, you wouldn't have a surrogate. What we found is it's nice to have something that's readable so you can put it up on a screen and actually see it. But yeah, you could. You could, for example, build that string and then turn it into a binary. Or hash it. You could hash it. Not a huge fan of hashes, I have to say, but generally business keys are shorter than a hash anyway. So you can. You just got the full thing and readable. Exactly. And that's the thing. It is readable. So that's what you find. If somebody's just queried the table and they're looking at it, you can actually see, oh, I know exactly what that key is for. And because it's got a prefix, which is a key set, and you can also look that up, it tells you basically where that key came from. You can define it that way.

Okay, explain it to me.

So the key set. So if you can imagine, you've got a customer, 1234. So on its own, doesn't mean bag of beans, really. If you have two systems that have the customer IDs in them, two separate systems, you've got customer 1, 2, 3, 4 on one and 1, 2, 3, 4on the other. They're not necessarily the same customer because those keys were generated on separate systems. So what you can do is you can apply a key set. It's basically a predefined sequence of characters which tells you where that key came from. So you can go, this came from system A and this came from system B.

So.

So it's a bit like the BKCC that you have on Data Vault, but it's not quite the same. You can. It's a bit more than that. It's not just distinguishing between systems. It's given us something that we can use to basically give a bit more context around that business key.

So the keyset prefixes.

Yep.

Held in a reference table. Is that what you typically.

We would need that to store that as metadata. So each business concept would have a set of key sets. Yeah. So, for example, one of the tables on the system I've just worked on, there's a customer table and there's two keys on it. There's a customer ID and then there's a customer code or customer number. So one is a surrogate key, the other one is an actual thing. That gets printed on the customer's bill. They're both legitimate business keys, so why not put them both in? So to distinguish between them, you put a different key set at the front to say what it is. Even though they identify to the same business concept, they are different ones. So you can look at one and go, oh no, that's the ID and that one is the number. You can distinguish between them just by looking at them. So they'll have a different prefix. Yep.

But they still both go in the same HK column.

No, you have two. You'd have HK customer underscore_ID and HK customer underscore_ number or code or whatever it happens to be.

And then when we look at the order table, we're going to bring through both those customer hkey keys into the order table.

Only the one that we've got in the orders table, only the one. Because, yeah, we're not going to do any lookups when we ingest data, because obviously that's going to slow your ingestion down, it's going to limit your scalability. When you bring the data in, you just bring the data tables in one for one. So you're kicking that can down the road a little bit in that once you want to start joining stuff back up together, you need to know which one to use. But that's why you use the qualifier at the end to, to give it a name to inform the user that I need to use the id. But at the end of the day, if they try and join on the wrong one, they won't get any results because the key sets are different. You'll get an empty result and they're easier bugs to find when you get no data than when you're getting some data incorrectly.

With Kimbal modeling, dimensional modeling, we get templates for SCD type 2, so well proven templates that allow us to deploy that object which we should all be using, rather than just recreating it from scratch in a YAML file and DBT and thinking that's a good idea. And then within Data Vault, one of the benefits is there's really only six bits of code. Create Hub creates that, create link, loadhub loads that load link and then all the exceptions for hook. I'm assuming that the meta model, this model that holds these key sets and these keys, the kind of, I don't know what you call it, scaffolding or the skeleton or the configuration, that's the bit that you can automate. I'm assuming that's where it's templated, it's reusable, It's a small set of hardened code that gets used time and time again. Is that right?

Yeah. So I built a little tool that does this. It's called, I call it Hook Studio because it sounds real fancy, but it's basically a little. NET application, that desktop application, which at some point I really would like it to be a proper kind of web based tool.

Vibe coding. Now, come on. AI lovable. Vibe coding. Get in there. You can have it in production by tonight.

No, there's people who can do that kind of stuff. I'm not the guy, but yeah, happy to talk to people about that, that's absolutely fine. But the meta model itself is pretty straightforward. There's only a few tables, probably about 15 tables in the whole meta model. You've got Hook, you've got keysets, you've got concepts and frames, that's the main ones. And you've got a few little frilly bits around the outside where you can put codes and conditions and that kind of stuff. But other than that, it's a very simple model. So you just tag stuff and then there's a little template and engine in there which says, how do you want to spit this out? You can generate SQL scripts. What we've done at the last place I worked was we created YAML files which go into a framework which does all this stuff. So you just go, here's a definition of these frames. It goes away and just builds it and no effort required. It's just a case of change metadata, generate the YAML file and it just handles it. So if you want to drop columns or drop cooks or change, rename them or whatever. Yeah, it does that, kind of. So those are the kind of tools and it's pretty easy to build. I've spoken to a few people about possibility of creating these tools. Most of them are data vault, died in the wall type folks. And they're like, nah, not interested in talking to you. But I'm sure there's somebody out there who will take a punt on this.

Let's just go back to those tombs again to make sure I've got them. So keyset was one.

Yep.

What were the other ones?

So there are four concepts. You've got business keys and keysets. You've got frames and then you've got hooks. And the hooks are what align the frames to the business concepts. That's essentially how it all stacks up. I've got pictures, but obviously on an.

Audio podcast, so let me play it back to you. I have a list of business concepts. So I have a customer, I have a product, I have an employee, I have a supplier. I have basically a bunch of key sets that allow me to put Prefixes against the IDs for those things to hold multi IDs if I need to, but also to determine the source system that's giving us that business key. So that's a reference table that holds a set of unique keys or key types. Effectively the hook is effectively the hk.

It's the formalized business key. Yes, that's right.

Okay, so that's the fields I'm appending to the table that holds the keys that I'm going to use. Yep. And then a frame is what?

Then the frame is just the structure that holds all that stuff together. So it's the view or it's the copy of the data, but it has the hooks included in it. I think it's easier to view it when it's a view. You go, you select star from the underlying table and then you've. You form the business keys or the hooks in there as well. And that's the frame.

Okay. So within Data Vault we would talk about a hub and set being an ensemble. Within anchor we would talk about ensemble. So it's the frame is effectively a group of those objects that when put together give us.

It's a simple wrapper, it's a one to one with a source table, you put a wrapper around it, but the additional fields that you put within that wrapper are the hooks which point to the business concepts.

So effectively it's a boundary, it's an ensemble that says if you've got all these things together, then you can do the work you need to do.

Yes.

And in your case it is the source table and the H keys and the relationship to the keyset and the concept. Okay, that makes sense. That's into the ensemble together gives us the thing that actually works.

Yeah.

Okay. It's going to be a whole lot of things that this isn't good at. What are they?

Oh, well, say that I was very, I've been very clear about saying what the scope of it is because like Data Vault tries to do everything. It's got solutions to try and do everything. And I think that's why it's hard to learn. So my, my perspective, it comes in that data warehouse bit. That's what it's good at and that's where it lives. So getting data into the warehouse is an engineering task and it's a very skilled and difficult thing to do because all our source systems are different. Trying to put a single pattern over top of all those systems I think is impossible. So why are we trying to do that? That sounds like a lot of effort when you just need to get a really skilled data engineer to go, here's a API, here's a CDC type solution. That's something that is a very specialized skill. And those guys let the engineers engineer. That's what I'm saying. So it's really focusing on that bit in the middle. It's that warehouse bit. So once. So it's basically the organization structure, which then makes it easier downstream to build the other assets that we need, the modeled assets, which. So the pattern I like to term. I've just come up with, it's not elt, it's elo, extract, load and organize. You're not restructuring, there's no T involved. And then somebody came back and says, surely there must be a T in there somewhere. You have to do some restructuring data. And I said, yes, but that's after the fact. But it's a lot lighter. So it's ELO and then a small T, not a capital T because it's a small amount of modeling.

So the heavy transformation work has been moved to the layer after the design layer, the consume layer. But what you're saying is the hook modeling capability, the way you lightly model in that middle layer reduces the amount of transformation in the consume layer.

Exactly, exactly. And that's what we found. Yeah.

And also would increase the agility, will increase the ability for you to adopt change at lower cost and faster time. I haven't thought about the use cases, the horrible ones that we know breaks every modeling technique. To ask you the really hard questions.

This is what we found though. One of the first projects that we use this on was I parachuted it into the project and they'd already built the bulk of the platform, so they were ingesting data. It was this whole databricks thing. And then, yeah, it was going into a raw layer and then at the raw layer they were building fax and DIMM straight off that. It was horrible. It really was horrible. So I said, oh, you might want to try this hook thing, because I think that's going to make that down the track a lot easier. So we started doing that basically laid over and they were doing everything virtually. They wanted to do everything with views, which was okay. And it worked. We were able to build the frames. We. They weren't called frames then. I changed the name. They used to be called Bags. So if you ever hear that term, it was a bag. I never liked it. So frame seems a much better word. And we started building that out and there was no real impact on the architecture. We could just layer over the top. It's just a nice layer over the top. And our dimensional modeling was easier, much easier. But what we found is that when those assets, those facts and dimms were getting pulled into the BI tool, the BI modelers would just take that information and they would just join the things back up together. So if you had say a project table, we'd produce a project dimension, a project fact, it goes straight into it and they just join it back up together again. So the question was, why are we bothering modeling? Why are we splitting these things up? So we then started just passing stuff straight through to them and they were absolutely fine with it. It's absolutely fine. But there were edge cases, there were things that they just couldn't do in the BI tool. They were difficult things. I'll give you an example. They were trying to do for projects. They were trying to do a burn down chart. Now the problem with a burn down chart, they were just basically seeing which tasks have been completed with their project. Now the problem is if you want to do a burn down chart, you've got to count the same tasks over and over on a different timeline. So the logic within Power BI was just too difficult. So the Power BI developer said, can't do this. Can you create me an asset that does that? I went, yeah, fine. So we basically took what we had out of the HOOK database and we just built another asset which did that kind of multiple aggregations, cumulative over time and just delivered that. And it had the same hooks in there. Everything was the same. It's just that we built that very specific asset for that specific use case. Pass that over, job done. So we only model by exception. We don't model absolutely everything. If we can take it out of, if you want to call it medallion, I know it's a horrible thing, but if you want to just take things straight out of silver into Power bi, absolutely fine. If you can't do that easily, we create a gold asset which does the specific logic that we need for that particular use case.

Okay, so when you've got SAP and that horrible unusable data model from a BI analytics point of view, then you're going to have quite a heavy consume layer because there's heavy lifting that has to happen. Whereas if you've got a simple system where everything's been typed nicely in that system, there is a customer table, there is an order table, there is a product table. There's no complexity. Then what you're saying is just add the HOOK attributes. So give it context to give it a view of business and then let them hit that and you don't need to do any heavy lifting. Apologies to whoever coined this because I can't remember who it was, but if you do actually listen to the podcast, let me know and I'll add it into the show. Notes. Classification was data according to source data causing your business data according to consumption in terms of a layered architecture. So what you're saying is the raw layer, the first layer is data according to source.

Yeah.

The design layer actually is physically structured as data according to source, but logically or conceptually structured as data according to the business organized.

Yes, organized around those terms. Yes, Yep.

So we're putting business context against the raw physical structure.

Yes.

Okay. And then data according to consumption is if you can use the middle layer, if you can use the hook layer, then use it. But if not, apply some heavy lifting in that last consumption layer to make it fit for purpose for reporting.

Absolutely right.

Okay, interesting idea. And so then I can see that our consumption layer could be dimensional. If the tool prefers facts and dims, it could be one big table because we could hydrate multiple of those as physical views. We could blow them away, we could rebuild them, we don't care. It could be activity schema because we could effectively, if we wanted to do the windowing functions, get the value out of that physical modeling technique for reporting, then we could do that. We could hydrate multiple physical models or virtualized of those models in that consumption layer. Yeah, interesting. So for purpose models.

Sorry. One thing we did look at was the USS the Unified Star schema. We looked at using that and we ran a few experiments and it was actually quite good. It works quite well with hook. All you've got to do is you've got to on each of those assets you need to create a unique identifier surrogate. So you do that and then you're just basically joining things up together using that Puppini bridge in the middle. Yeah, we tried that. That seemed to be okay. But yeah, there's different ways. It doesn't have to be that end layer doesn't have to be a dimensional or whatever. It can be a flat wide table. It could be, yeah, another kind of hookish looking table. It's fine.

Let's go through some of the other things that the middle layer tends to have to Deal with historized data?

Yes.

Typically we would end date records or we would put a is current flag on, or we would use the A date and then use a windowing function to be able to see which record is valid at that point in time. How are we going to deal with that within the design layer? Using hook.

That isn't a hook thing, but that's absolutely something that we've done. The place I've just left is we did that, the bronze we loaded into and we put the hooks on there. We actually put them in the bronze layer. So we had an organizational structure on the bronze layer. When we then moved it over into silver, that's when we applied that row effective from row effective to row is current, row is deleted. We added those fields on as well using those same techniques using the windowing functions. But it's all patent, it's all the same. I guess the only difficulty that we found is depending on the source is we what time field do you use to represent your time dimension? And we can have all sorts of arguments about whether it's a business thing, a warehouse thing or what you've been there.

But the key thing is it doesn't matter which physical modeling technique we're working with, we always have to deal with the temporal nature of the data and how do we know a change was a change and how do we sequence the changes so they don't get out of sequence when the source systems do bad shit to our data? Yeah, yeah.

And what'd you do with late arriving data and that kind of stuff? It's like same problems, doesn't matter. No technique in the world is going to solve that. No way. So yeah, that's the kind of pattern we use. So that was our silver layer. So anything in silver, it has those standard columns on, you can see them. So you have that time dimensionality. And to be honest with you, a lot of my users still didn't get it. It was like, you've got history, you can travel in time. Because it was so used to running their reports on a particular date because they were using current data. So when we moved over to the warehouse and suddenly we're giving them history, you can go back to a point in time and run the query as it was at that point in time and don't always get it.

Yeah, actually what we do is we actually provide two consume layers. So let's take that order table. We will provide a current state view of the order table and we will provide a historized view of the order TABLE and the reason is data people. We know that historized data has value. We know be able to say what was the order value that 31st of March compared to now? Because we know it may have changed. We know that and we know that's hard if we don't bake that in as a core pattern in the beginning to hold that history. We know trying to retrofit it is a really large piece of work for us. So we want to bake it in early because we know it has value. But 8, 80% of the time, air quotes on the 80%. The end users, the information consumers don't give a shit. They're like, just tell me right now I have an edge case where I need to know what it was a month ago, but nine times out of 10, I just need to know what it is now. So why do we give them this complex consume layer that they then have to filter? Why don't we just give them two? If you want to know what it is, hit this table. It's smaller, it's faster, and it's easier. And if you want to go back and figure out what it was, then go hit this bigger one and do some more work. Because you're asking a more complex question, and that's my view now, is that we shouldn't try to do one thing to bore the ocean.

It's just stupid. Yeah, no, absolutely. That's the approach we took. It was just one asset. So you have that row as current flag. You just filter on that. Gives you the type 1 behavior. It's the same kind of thing, but it's just an educational case. They're just telling these guys how to use it. And I think it's been simple for them because once you've got those few rules and you've got the hook keys in there, they can work out most of the cases themselves. It's just like they can see what they can join to what we don't have to think about. A data model you don't have to.

Think about and then master data. So I've got a bunch of customer records coming from system one and a bunch of records coming from system two, and they don't have a shared key. So now I've got to do a whole lot of heaving lifting to infer that 1, 2, 3 is the same as ABC. I'm assuming that all happens in the consume layer because that's the heavy lifting. But is there anything in hook that makes it easier?

Nothing, Rick, other than the fact that the way that you formalize those keys. At the end of the day, you've got to create an asset which does the mapping between one key to another key, and that's just another asset. So you can think of that like, I always think of it like it's just another source system. We fed information into it. It's done its magic. It's trying to figure out some things based on what information it has. And it spits out a mapping. You just ingest that. It's got the two keys in there. You just define hooks over the top of those keys in exactly the same way. You can then use that as a bridge, if you like. Like same as link and data vault. Same kind of concept. But in the day, it's just data. It's the same thing. It's just a set of business keys that map one to another.

Okay. And then when we're inferring data, so let's say we're going to create a table with a bunch of attributes, feature flags for an analytical model, say, customer segmentation. So I want to go and infer some values. I want to create some columns that go. Number of orders over the last month was less than 10. Again, all that type of work's happening in the consume layer, not in the design hook layer.

The kind of logical architecture I always had was that, yeah, you've got the data warehouse layer, and then you either go into a consume layer or you have some business logic. You have something that does some crunching, that spits some data out, and then you can re ingest it. So those kind of at the end of the pipeline that gets fed back in. Again, it's just another source. It's just that it sources information from the data warehouse, but it's treated the same as any other source. That. That's the way I view it. Yeah.

And then I tend to use a language now of fact measures and metrics, and the way I describe it is fact is a number that came out of our source system. It's a number. It's immutable. I can go into the source system and I can see a screen that said the order value was $100. And so that is a fact. So the problem with these terms is it goes against Kimball. That's why people look weird at me. But if we think about it from a business point of view, I have a fact. It is in the source system. It is immutable. That number exists. I don't need to do anything with it. That number is there. So order, quantity, order value, and then I have the concept of a measure. So a measure is a simple calculation, it is a sum, it is a count, it is A divided by B, A minus C. It's those simple formulas that we use. And then metrics are where we bring in complex business logic. So A divided by C divided by B minus F. So you know, take the total revenue and minus the total cost and then divide it by the number of periods to give me the average value over time. Right. So I can see the facts are going to come from source into the first layer, let's call it raw again. Then I'm going to inherit those facts directly into designed and then I can consume them, the measures and the metrics. Because we're inferring or calculating things, they're going to be in that consume layer. So that's where we're going to push all that heavy lifting again. But, but what you're saying is that sometimes you actually have a four layered architecture. So you'll go raw then you'll go designed where you're applying the hook logic to give it business context. And then you're going to go into another layer where you start augmenting it or inferring some data that's useful and then you'll hydrate it into consume so that it can be used by whatever tool in whichever way they want. And that will be quite volatile and just. And disposal.

It's interesting because we've had long debates about what's a measure, what's a metric. And so the definitions you just gave there, to me, they're almost the same. Say the difference between is complexity, but at the end of the day a measure is something that's calculated from something else. So is a metric, whereas a factor.

I know, but the problem is I wrote a course to teach some people and I had to teach them what things were things. And so I did a hell of a lot of research to go, somebody's done this, somebody has a language for these things that is so simple that I can just pick it up and I could teach somebody. That was my theory. And, and this is pre chat GPT. So actually I'll probably need to do it again. But I couldn't find it. I went through all the training material I'd done over my many years as a person learning. I went through all the books that I had, I went through everything and I was like, okay, I can't find a simple description so I invented one. Now I can argue that a measure is the same as a metering, but actually I can Give you a clear delineation between them. And that that fact is a number that turns up from the source system that we don't touch. That's a fact. A measure is a very simple calculation. So it's a sum, it's a count, it's a minus, it's a plus, it's a divided by. And a metric is everything else that is really complex. So for me, that worked.

Okay, that's fine. That's fine.

Yeah.

It doesn't matter what language you use, as long as people understand it. And we're consistent about how we use that language. And I think that's often the problem that we have in our business, and especially with concepts, even in that you can talk about the same thing and two people could be. Have totally different pictures in their heads.

I had Remco on because with his book about ELM and sombological modeling, and the example he used was he was working in a hospital environment and he was running a workshop and somebody wanted to count beds. And he's, oh, that's easy. We don't even have to worry about defining that because the bed's one of those physical things that people lie on. And somebody said, oh, not for us. And he goes, what do you mean? And they said, bed is actually the room where the patient is. And it may or may not have a physical bed because we move them in and out, but that's what we count as a beard. And somebody else says, holy shit, no way. A beard is a thing with four wheels that we wheel in with the patient on, and it's made of metal. And so again, even in that environment, they had a slightly different interpretation of beard.

I had that exact same conversation with New South Wales Health, and we were doing a data modeling session, and it was exactly the same thing. They said a bed for them was a as a location. I said, should it be called that? Because a bed's got like a barcode on. You can scan it, it moves. Whereas. So what is another name for it? And they were like, oh, it's a bed. And is there another? And eventually they arrived at bay. It's a different concept and it's just getting everybody to use that same language. And that is generally problematic. Have you learned anything from a guy called Ronald Ross? Have you read his book at all?

The Business Language, the Business Rules.

Very good book. And he talks about concepts, the fact that they don't exist on paper, they exist in people's heads. And two people will see the same thing with the same amount of information and perceive them totally differently because of their experiences. So how do you align people with a concept to think of the same thing? I've got an example. And again, this won't be very visual. It is visual that I've done a training course where I said, do you know what a truncated dotach icosahedron is? Truncated icosahedron. Do you know what that is?

No.

No. So if I was to give you a picture of one, you'd probably have a pretty good idea. So geometric shape, it's a kind of spherical shape. So I'll give you a picture of that. You go, okay, I get a vague feeling what it is. If I gave you a definition for it, it's a big long thing saying, oh, it's got this many faces and edges and all this kind of stuff. But if I gave you a picture of a soccer ball, you'd go, I know what that is now, right? Immediately by giving examples of things is a really good way of aligning people's thoughts in terms of how they perceive a particular business concept. Yeah, it's really important. Language is important, but obviously concepts and conceptual understanding is really important.

That's why I'm a great fan of Lawrence Corners beam methodology, because we effectively start by using beam. When we use beam, we start by capturing the data values as examples and then we go and get a conceptual term for those values because now we have something we can point at and say, what is that? And then can I find it? Like, can I see it? That's customer. Okay, if I went to look for one, what would they look like? With data people, we love to argue semantics is semantics. So what is a semantic layer? What is a data product? We can't even agree amongst ourselves and yet we expect the stakeholders to agree conceptual terms that are quite hard to understand at a drop of a hat.

The idea is just to be really light on the modeling because that to me is problematic. I think it can be a bottleneck and a lot of endeavors in data warehousing. So the idea is to try and take that out the critical path, apply it after the case, rather than have to do that up front and build a model that your source data doesn't align to. So you've then got to go and change it anyway.

Okay. Anything else you want to cover off about Hook before we close it out?

No, not at all. I will say that I've been speaking to Peter Hanson and he's asked me to do a talk in Brisbane next week. And then he made mention of maybe going across to Auckland And Wellington next month.

Oh, that'd be good.

That might be happening. If I am in town, I will say hello.

Yeah, definitely. I live an hour out of town. If you're coming to Wellington, give me notice. But yeah, I think the date engineering meetup in Wellington is two days from now. So we're at the 5th of May. So it would have happened by the time this goes out, which means that it's probably going to be first week of June, potentially.

He was saying towards the end, actually he was okay, but I don't know yet. He hasn't given me those details.

Okay. If you can get the details before I publish this, I will shove it in and then people can come along and see a presentation about Hook and ask you the hard, gnarly questions that I forgot to ask.

Deflect. And I will deflect some more.

Excellent. So if people want to find out more about Hook and more about you, where do they go?

It sounds good. I've got a lot of articles and arguments I've had with people, especially data vault people online. So go and have a look there. I do have a substack. It's a little bit out of date. I do need to refresh it and I've got the guts of a book as well. A short introduction, which I'm hoping to get out freely in electronic format in the next few weeks. So, yeah, good luck with yours. I'll see it on the shelf there.

Yeah, yeah. Actually, for some reason, 2025 seems to be the year of data books, so there are a large number of very good books, including mine coming out. So it's good. It's good that actually it's the year of knowledge sharing. Yeah. Let me know when your one's out and I'll let everybody know it's there and they can go have a look at it. That'll be pretty cool. Yep. Excellent. All right. Hey, thanks for coming on and going through it, because I've now got a much better understanding than I had an hour ago, which is always a good thing.

Always good. Yes. It's all about. Yeah. Sharing is caring.

Sharing is caring. Thank you very much.

I think that's what.

It's not my term, but it's one that I use regularly. And I got no idea where I got it from, but that's okay. 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.