AgileData #20 - Data Layers, the magic of the right data in the right place

Welcome to the Agile Data Podcast, where Shane and Nigel discuss the techniques they use to bring an agile way of working to the data world in a simply magical way. Welcome to the Agile Data Podcast. I'm Shane Gibson, and I'm Nigel Vining, and today we thought we'd have a chat about what we call areas in agiledata.io, or typically in the world of data, they'll be called data layers. In the old days when we did dimensional data warehouses, there were really some standard layers that you had within your data platform that pretty much everybody who knew what they were doing used to build out their data warehouse. Then we went into the land of big data bollocks and we got a whole lot of new layers and a whole lot of new ideas that were not really proven. And what we're seeing now is a move back to more of a managed layer approach within the data platforms with some areas of variation where some of the agility you can get out of that data lake type of behavior has some value. So we thought today what we'd do is have a bit of a chat about the areas or the layers that we use within the platform we've built and why we're doing it that way and some wisdom of words from Nigel about how the hell he engineered and plumbed all that stuff. Sound good?

Sounds good to me.

Alrighty, so for us, the first layer or area that data turns up in is a bucket. We made a decision that regardless of how we acquired the data, if we're pulling it off a software as a service application as an API, or we're hitting an internal database and bringing the data across, and whether we're using change data capture or snapshots and deltas, that landing the data using a same pattern into the same area had value for us. And so for us, we land that data as either CSV or JSON into a bucket, and it's the first time we see it when that data is actually dropped into that bucket.

Yeah, and the benefit of the bucket and having a common space is then we can leverage a common archiving and error handling pattern because basically files will turn up in the bucket, we load them. If they load successfully, we say great and we archive that file and put it aside. If it doesn't, we move that file off to an error bucket where we can retry and reprocess. But it gives us a common place to put files and a common place to archive files right at the start of the process.

And so when you talk about error handling, you know, one of the things we say is load the data as much as we can. We We try not to reject data, so we don't apply any data quality routines at that stage and put those records into an error-handling bucket that an analyst then has to go and do some work before that data turns up. But there are scenarios where the data isn't fit for purpose for us in terms of— I talk about data mutations. So, you know, have you got an example of where we'd probably get something where we actually error a file out?

So where we would commonly error file is where we've been happily receiving a daily customer extract, for example, and it's got 20 columns and the data types are all known. And then one day what happens is that customer file turns up and now it's got an extra 5 columns or some of those data types have changed. So the first thing the parser does, it goes, this doesn't look like we've 'cause what we've got, I'm gonna put it aside and raise it that we need to do something with this file.

And that's actually one of the things we've got on our backlog is to bring some analytics or some algorithms in to automate that process. So when we see certain types of mutation, say mutation of schema, how the hell we can get our algorithms to automatically inherit that mutation and bring it through so that we remove that manual process and it just becomes magical. So those CSVs and those JSON files, right, right, they turn up. You know, why do we pick CSV and JSON?

Because the platform we, under the covers, that we're using, it natively supports CSV and JSON files. And CSV/text files are pretty much the one common format that every database software as a service product uses as a common file format to share to export-import data. And increasingly JSON tends to be the more common one for our customers as well. But CSV text, it's been around forever. It's still the most common pattern that you can move data between platforms relatively easily.

And the cool thing was, you know, because we could just land that CSV in and have it plumbed straight through, since we built the file drop capability, it means people that had a bunch of Excel files, which we know is still one of the largest data factories in an organization, they could just drop that file as a CSV and we'd load it. So, we'll probably look at maybe some Parquet stuff in the future if we find out that there's a bunch of source systems or tools that want to publish their data out in that format. But right now, CSV and JSON's kind of got us through. So, in true agile form, I only do it where it has value and when you need something new, build it. So, data comes in, it gets put into that bucket. Then the next thing we do is we have a temporal data layer or area called landing. This is actually hidden from anybody apart from ourselves. The data is not persisted in there and it's not actually visible to anyone else. It's really just a small piece of a small area we use for some plumbing. Why do we do that? Why do we have this temporal little area that doesn't persist forever and nobody really sees?

I guess the benefit of landing is that we've successfully brought in a file, files from customer. Once they are in tables, then we get a whole lot of benefits around that. It's got a little bit of structure to it. We can run profiling over a table a little bit easier than maybe over a file. We can basically have a first cut of you know, what this data before we move it up through the layers. So it really just gives us the pattern of it. The data is now in a table, so we can use it a little bit easier rather than reading it inside a file.

And so for me, you know, if I go back to my favorite analogy of a restaurant, you know, we're kind of doing a pre-check, right, before we move the goods. We've received them, we want to check that the goods are really what we thought they were before we move them into the storeroom and and hold them forever.

So exactly, we've actually unpacked them effectively. We've taken the raw input into the kitchen and we've unpacked the box, and now we can actually see what we've got because it's now in a format that's a little bit more obvious to it.

And also it allows us to do some really cool shit around data SLAs. So, um, you know, we may receive data and it may come in every 5, 10, 15 minutes, but we may— the customer may have a requirement where the SLA for the data is it's only refreshed every half hour. You know, or they want to deal with it slightly differently. So again, it gives us the ability to do some of those things on the way through.

Actually, that's a good, it's a good point, Shane, because what we automatically do under the covers is every file that comes in, or multiple files, we, although they're going into the same table, they're actually going into separate partitions in that table. So we're effectively logically stacking them up. So even if we don't process them straight away for some reason, basically all those files have been stacked and partitioned partitions in that table. So if we unpack 1 partition or 20 partitions, you know, an hour or 2 hours or 24 hours later, the data is effectively preserved how it's arrived and we have timestamped it. So that's another feature of chucking into a table, appending it to a table, sorry.

Yeah, and so I think, you know, in terms of the data layers we do, it's probably an unusual pattern to structure the data so early. We've got some backlog stuff where if we could figure out a way of doing a more variant data type as we bring it into that landing area, we may make it less structured and then actually move the parser of that data to structure it further up the layers. But that's actually one of the key things, again, as we talked about in the last podcast about refactoring. We know that we may end up refactoring this particular feature further up the layers later, and that's okay, right? Because it's designed using the Pac-Man analogy so that we can just move it, test it, and we're okay. So data comes in, we hold it temporarily, we profile it, we make sure that we understand the structure of it and it's safe for us to move on, and then we move it into history. And history for us is persistent. It is the single source of corporate memory for all data. So every piece of data that comes in goes into history and it stays there forever. We never delete data out of history. So let's have a chat about, you know, first of all, you know, what are we doing to rack and stack that data in history? And then why do we keep it forever?

Oh, why do we keep it forever? That's a good question. So first of all, Write History is our persistent store. So We effectively move the data from landing to history. At that point, we identify the unique business key that holds that data together. This is probably the most important thing we do in the whole layers process, because once we have a unique key for the data, we can uniquely identify it anywhere in the process, and it's always safe. We will never accidentally change the grain of that data. We will never overlook a change change for that data because we've uniquely identified it.

Yeah, and that's again one of the areas that we, we're probably going to refactor in the future where we're going to move that key further up the layer to reduce some effort on the analyst in the history layer. But the key thing is the history is racking and stacking all changes over all time, and that enables us to do a couple of things. So at any time we can say, what did that customer record look like on the 31st of January 2019? What did it look like on the 31st of January 2020? And what's the difference? What had changed? Had the customer's address changed? Had their date of birth changed? Which, you know, address change is probably okay. Date of birth, probably want to start looking at it and saying that the data is mutated. It also means that at any time we can completely rebuild the upstream layers, which we'll talk about in a minute, the event and than the consume layers because we have the original data that came in and the way it came in. And so if you've got a data factory like Salesforce or one of those other tools, as you change those records, it may or may not record that change. It may just update and never actually be able to tell you what it looked like the week before or 2 weeks before. So by bringing that data in and storing all those changes in history, it means we have that immutable corporate memory. And if we need to, we can rebuild everything from scratch. So that gives us a whole lot of safety around that.

Yeah, that was, that was the point I was getting to before that I overlooked. The ability to rebuild from history because we have it all. We can change any rules above it and rebuild all those layers with with no risk at all, essentially, because we're basically recreating layers on top of history because we have all the history. So there's very little risk in doing that. We don't lose any granularity or changes because we have all the data to build from at any point we want.

But if I think about, okay, I've got some large pieces of data coming in, and they're coming in every day, and then I'm racking and stacking and storing them in this history thing, and that table, that data is just going to get bigger and bigger and bigger and bigger, so it'll get really slow, right? So, you know, is there some engineering magic that you've done to stop my queries getting slower and slower every day?

Yep, so under the covers we use the built-in feature of the platform we're using, which is partitioning of the tables, and we effectively keep all the current data, or as the data looks like now in the source system, in the current partition. As those records change, we park them into history partitions. They're still instantly available and queryable, but we've effectively pushed them off to the side. The platform stores them in an optimized way there. It can grab them later, but we're actually only dealing with the current state data for 99% of our queries, 'cause that's what users generally expect. So partitioning helps us reduce cost, improve performance massively, but cleanly store our history off to the side with no real penalty of doing that.

And you know, in the old days of legacy data warehousing, we used to have a staging area or persistent staging area. It's not a new pattern, it's not a new layer. I think back then the problem was our databases couldn't handle holding data for all time, so we used to have to archive it or back it up or delete it or move it off. So one of the benefits of the new cloud serverless platforms is they give us technology capabilities we haven't had before. Which means we can go back to knowing robust patterns but use them in a new way.

Yeah, exactly. That's, that one about archive, it's really a nice segue because effectively a partition table with your historical data partitioned off over time, effectively under the covers that gets moved off to cheaper storage. If you haven't used that historical data within a set period of time, it effectively just moves off to the side. Again, it's still instantly queryable or come back up again. But if you haven't touched your archive data, that might be 1 year, 2 years, 5 years, 10 years, that's effectively all off in cheaper storage because you're not using it anymore on a daily basis. And that's a nice feature that cloud gives us. We don't have to physically worry about, oh, my temp space, data space, user space is filling up on my database. Because it's just not a consideration anymore.

Yep, and one of the, the many pieces of magic we use to keep our costs down and therefore provide our magical t-shirt pricing. So, okay, so we've got the data in history, we've got this corporate memory or data over all time, and then we want to make it usable, right? We want to apply some context, some understanding to that data because the data factories may store the data in tables that make sense to them, that are quick and easy for them to allow you to update that information and search on their screens and find it. But when we bring it into, into the data platform, under the covers it's pretty ugly. You know, it may have that horrible what we call party entity. So it may have one table that holds customers and employees and partners and vendors and, you know, everybody else. And then there's another horrible table you've got to join to, to look up and say, well, you know, who's a customer and who's who's the supplier and who's an employee. And so to do that, we, we have a layer that sits right on top of History, which we call Events, and that's where we model the data. And as I said in the previous podcast, we only model it with 3 types. There's a concept, which is a thing, so a customer, a product, a supplier, an employee, an order. There's detail about those things, so the customer's name, the customer's address, the supplier's store, or warehouse, or the order value, or the order quantity. And the third one is an event where we know that customer purchased product, or customer ordered product, or customer paid for order, where there's a combination of concepts together at a point in time to say that an event happened. So within the event layer, that's where we do the gnarly work to define those core business processes and those core business events in there to structure the data that way so that we can understand it.

Yeah, and under the covers, the events is quite a fun layer, I guess, from a plumbing point of view. It's where our patterns really get used at Anger the first time because effectively there's a lot of, a lot of stuff happening in events. Up until now, landing to history is quite a vanilla data movement because we're just picking up the data and moving it through as it is. We really aren't changing the structure or adding to the structure. Once we go from history to events, that's when we effectively really break all the data down and we split out into its, as Shane said, into the individual events. And it's where our plumbing patterns start to come into play of how we build those event objects, what's common to them around keys, I guess versioning, dates, basically what those tables look like. We use patterns to speed up how quickly we can break down a raw history table and split it out into value-add event parts.

And I think, you know, we're leveraging patterns that have been around for a while. So the concept, detail, and event model leverages heavily off a pattern called Data Vault for a way of data modeling. It leverages off some event modeling and business analysis modeling that has been defined around who does what. So we're reusing those patterns that have been around for a while to effectively be able to model that data in a safe way, but a way that matches the organization's business process and their core business events. We've used this pattern, this event pattern, for a while. We've done it in a number of consulting gigs, but the challenge was every time we did it as consultants, we had to build it in new technology because the customer always had a slightly different flavor of of the way they want to store the data. It might be Parquet on a file system, it might be Redshift or Azure PDW. They might want to run it in Spark or they might want to run it in SQL or they're using a database that doesn't allow you to use stored processes, so you have to buy an ETL tool that does all the heavy lifting. And really what we were doing was rebuilding those patterns time and time again with different technologies just because the customer decided They wanted technology that had not been done by us at least before. So now what we've done is taken those patents, hardened them, and made them as a service. So, you know, probably a lot of— well, it's not probably— a lot of your life last year, Nigel, was re-plumbing and hardening those patents to make them as magical as they are now.

Yeah, that's exactly. And it was quite an exciting year too, actually. Where we effectively condensed our last 10 years' worth of building these platforms and we took all those patterns, we whiteboarded them, we condensed them down to, I guess, a dozen or so, and we basically built them up as something we could use over and over again without having to start from scratch every time. And suddenly the economies of scale, as massive because if you always build an event table the same way, you know, you're not reinventing the wheel every time. If your layers always, as Shane says, Pac-Man between each other, you know, you're not having to reinvent all those integration wheels every time. And that's where just the cost and time is massive to build those layers because they always— the complexity in them is a lot. And we figured, hey, why not just spend a couple of months and do each pattern as something you can use over and over again? And it appears to be working quite well.

I think it has because we haven't had to touch them for a while, right? We've spent most of our time working in the rule type and some of the other areas. We haven't actually had to go back and refactor the core patterns of how we create and populate those concepts, details, and events. So for me, there was minimal technical debt and minimal need to refactor at the moment. So that was pretty cool.

Yeah, agreed. And we've actually, you're right, we've done that across multiple initial stage customers. And the patterns actually fit probably a good 95% of what was needed to be done for each of those customers. And they were diverse customers with different data types, different inputs, different outputs, but the patterns, because they work so well, actually did pretty much everything we need to do with the exception of writing some additional rules, which we're always going to have to do anyway for these specific use cases.

Now, one of the problems of that, that Data Vault-centric modeling, and it's well known out there, is that you end up with too many tables. You know, you're an analyst and you're going in there and it really is horrible trying to put the data back together, right? You gotta understand, I got this concept of customer, and then I've got all this detail about it, and the detail may be physically stored in different tables for certain reasons. And now I'm gonna go put that back together, and then I've gotta go and hook that up to my event, and then go, "Oh, all right, and that event is a product. Oh, where the hell's the product concept?" And then go grab that, and, "Where the hell's the detail about the product?" And, you know, Yeah, I don't write SQL, and I don't know if I could write SQL that did all that together. So for us, we want to make that simple. And so what we did was we added one more layer on top, which is our last layer, which we call the consume layer. And that's where, once we've defined these concepts, events, and details, effectively we understand what they mean, because they've got business terms. And as Nigel said, we have patterns that create them automagically. So what we do is we reverse engineer that. We basically put those events back together into a consume where we can have a table or a view that enables you to, as an analyst, as a user, to very easily and quickly query that data without having to do all those horrible joins.

Exactly, and I think that's, if there's one layer that would be a truly magical agile data experience, the consumers getting close, 'cause as you say, effectively we undo all the complexity that we've done under the covers to make it simple, and we present back to the users a nicely set up effectively business layer with all their business terms, the tables are nice and clean, and it's straightforward experience for them. The under the covers to do that has a little bit of complexity, but it's where the Data Vault pattern actually helps us because we know how all those objects go back together. We can create rules that put them all back together for us relatively easily. As Shane said, he doesn't write SQL and we don't expect him to. He just creates a rule that says I want this, this, and this together, and effectively his consume object turns up. It's at the correct grain, it's correctly joined, it's at the correct point in time, and he can use it in his end user tool. There are no joins, there's no complexity in it. Magic.

And for me, so what that means is I can bring some data from Salesforce that has customer in it. We bring it in, we automatically drop it in a bucket, it automatically goes through landing and into history where it's stored full-time. I'll then go and create a rule to say, hey, that's the identifier for our customers, this is the detail about it. What I get and consume is a single table for customers of all their details. I'll then go back and I'll grab some data from another Data Factory. I might grab maybe something out of the financial system, so where the payments live. And again, that data will come in and it'll come all the way through to history. And then in the event layer, I'll create a rule to say, hey, this financial system has got this idea of a customer and here's the ID, the key, the business identifier for those customers. And I'll define the details about it. And again, I then consume, get a unique view of those customers so I can query them. And then I go, well, cool, but you know, hey, having a table of customers from Salesforce and a table of customers from my financial system is a bit of a pain in the ass because I've got to put them back together again. So I'll create a third concept where I put the two together. If the keys are the same, right, if the identifier for customer 1 is 1 in Salesforce and customer— that customer has the identifier 1 in my financial system, then my rule is really simple, right? I don't need to write any rule logic. I don't need to apply any, any change to that data. It'll just automatically give me a single table with all my customers' data from Salesforce and from my financial system. But if those identifiers are different, or if I want to dedupe them or do some magic, then we have rule types and enable that. But the key being, I then just go into consume and I have one table that has my customers and all my details from all my data factories. So the same thing happens for products, and more importantly, the same thing happens for events. So, you know, if we have an event of customer orders product, when I go into consume, I will see one table that has the customer and all their details, the order and all the details, and the product that was ordered and all the details in one large table, what we call a denormalized table. And the reason for that is I don't want to have to join I just want to put my sexy BI visualization tool at that table and make it look beautiful. So on there again, same problem though, right, Nigel? I mean, that's going to run like crap, isn't it? I mean, we're going to get tables with 300 columns with a billion rows and it's just going to run like a dog.

Well, that's in the old world. That would be the case and we wouldn't get away with those patterns because they would basically die and it would not run. But luckily, because we're leveraging the power of a columnar database, you can have as many columns as you like. It makes no difference at all to the performance because columnar databases love columns, funnily enough. Lots of rows, again, not a problem because under the covers you're now leveraging what's generally called an MPP database, massive, massively parallel processing. So the amount of data makes no difference anymore because under the covers your query is chopped up into smaller pieces, farmed out across a nice big processing array, results are put back together and given to you still within seconds. You don't know that it's gone off and done a round trip, you know, a couple of hundred hours of elapsed time under the covers, it still gives you the results back in a few seconds. We also leverage at this layer, what are we leveraging? We've still got our partitions, partitioning in our event layer. So when we build up to consume, our queries are still optimized because they're still reading out of partitions of data. Clustered keys, that helps us, but that's all under the covers. The users don't see a lot of these technologies. All they know is, wow, that query ran really fast. It's got lots of rows and lots of columns, but it still only took a few seconds, so I'm happy.

And, you know, there's a lot of technology patterns under the COVID and the technology we use that we haven't even touched yet. So, you know, there are technical things like materialized views, which which we haven't even needed to bring into the way we do the data patterns or the technology patterns yet. But we will get to stages where we have customers with data that gives us certain scenarios where we'll need to refactor some of those consume areas to optimize for that. One of the decisions we constantly go back and forth on is, should our consume area be physical or virtual? Do we deploy what we call a view into consume that punches back and just reads the data out of events? And we never persist it, or do we persist the data in there because there's some benefit in doing that? And, you know, eventually where we end up, I think, is we'll have a form of optimization within agiledata.io where depending on the size of the data and the response times and the type of data that's being surfaced and consumed, the way we store it will be different. And that's true as well as of the BI tools, visualization tools. So You know, some visualization tools love big denormalized tables. Some of the older tools still like star schemas and the idea of facts and DIMMs. Some of the interesting tools still like relational data models. And so from our point of view, we really don't care. We can deploy the consume tables or views in any structural format that suits those BI tools because We're effectively taking the data out of the event layer and surfacing it in a way that makes it easy for the analyst and the user and for the tool itself.

Yeah, agreed. And layers and patterns, you're right, give us the flexibility to do any of that stuff essentially on the fly as applicable. Consume doesn't, yeah, You're right, consume doesn't have to be tables. Could be views, could be a mixture, could be a mixture depending on what that particular rule and volumes and columns, it could be a feature based on what tool the end customer wants to use over that layer. And as you say, if it needs a star, it's not a drama because it's just a rule that says spit out consume as a star for your tool. It makes no difference on our part because it's a pattern. If you want a star, it will produce a star. If you want a big wide table, it'll produce that. It's just a pattern under the covers. That's the joy of the patterns.

And, you know, a good example of that is when we had to start delivering the data, the consumable data via an API. Again, all we did was we created a new feature, we created a new Pac-Man that allowed enabled you to call that data via an API. And, you know, the API was effectively calling the consumer, the consume area. So, you know, again, just another pattern on top of a known pattern, and it was all good. Worked like a thing of magic, a thing of beauty.

Yeah, sorry, I forgot APIs. You're right. Yeah, files, APIs, views, tables, you could have any of those. It makes no difference under the covers. Even a chatbot.

So, yeah, I suppose, you know, in the new data world, there are people saying you don't need to actually have layers, that you can magically do that. And potentially, we'll get there where actually our layers are all in code and not physically storing data, that we land into history and then every moving part is virtualized as a series of views. Driven off the rule types. I'm not sure that actually that has as much value compared to the cost of us doing that, but it's something that we can do if we need to. But the key thing for us is we started off with a set of layers that we defined, and then we iterated on them, but we're very clear about what those layers are, what goes in the what their value is, why we use them, and therefore, when we make changes, we know which layer we're talking about. So, when Nigel and I are talking about, you know, the next thing we want to build, pretty much the first thing he always does is validate with me, "Right, so we're talking about doing something in the event layer, right?" And I'm like, "Yeah, that's where I think I am," because that gives some clarity between us in terms of the conversation of, of what we're talking about, given the large number of moving parts we have to deal with the complexity of data that our customers have and make it so simple and so magical. So, you know, if you're doing a data project and the people you're working with have no clarity around the layers, you know, if they say just put it in the lake and you'll be good, yeah, I'm not sure I agree. I'd probably say get a whiteboard out or a piece of paper and maybe just sketch out a couple of simple layers that have some value, have some use, and articulate what you can and can't do within those layers.

Yeah, I think my final point was, um, I firmly believe, I always believe that, uh, layers actually keep you safe. They provide some layer of isolation, going all the way back to Ralph Kimball, in the kitchen analogy, in front of house layers keep you safe. It allows you to replumb under the covers and your diners out in your restaurant are oblivious to it, but the data still comes out on their plate. And recently we, we replumbed the whole event layer for a customer. They had no idea because the consume layer delivered exactly as they expected. We replumbed it under the covers to add some new features, isolated, safe, no impact to the end user because it's in a different layer. Layers keep you safe, that's what I believe.

So yeah, so really we've taken an age-old data pattern in terms of layering the data through your platform and applied it in a new and beautiful way. It's kind of like, I don't know, a new Asian-inspired beef Wellington.

Fusion.

That's actually a good idea. But yeah, we've taken some old ingredients and we've made something that's modern and new and damn tasty. So, right, I think that's us on layers.

And that, Data Magicians, was another Agile Data Podcast from Nigel and Shane.

If you want to learn more about how you can apply apply agile ways of working to your data, head over to agiledata.io.