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'd be called data layers. So 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. 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.

All righty. 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. 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, 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, if you've got an example of where we'd probably get something where we actually error a file out.

So 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 if that customer file turns up and now it's got an extra five columns or some of those data types have changed. So the first thing the parser does, it goes, this doesn't look like we're got what we've got. I'm going to 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 times 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, 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. TXT 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 because we could just land that CSV in and have it plumb straight through. Since we built the file drop capability, it means people that had a bunch of Excel files, which we know is still, you know, one of the largest data factories in an organization, they could just drop that file as a CSV and would load it. So, you know, 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 Jason's kind of got us through. So in true agile form, 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 and then the next thing we do is we have a temporal data layer or area called landing. And this is actually hidden from anybody apart from ourselves. So the data is not persisted in there and it's not actually visible to anyone else. It's really just a small piece of small area we use for some plumbing. So 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're 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, if I go back to my favorite analogy of a restaurant, 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 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 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.

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 point, Shane, because what we automatically do under the covers is every file that comes in or multiple files, 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. So if we unpack one partition or 20 partitions, you know, an hour or two 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 it 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. You know, we've got some backlog stuff where, you know, 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, the layers, later. And that's okay, right? Because it's designed, using the Pacman 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. 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, first of all, 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 for that data because we have a 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 and the History layer. But key thing is the history's 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 dress 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, the consume layers because we have the original data that came in and the way it came in. If you've gotten 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 two 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 the point I was getting to before that I've looked the ability to rebuild from history because we have it all. We can change any rules above it and rebuild all those layers with.

No risk at all, essentially, because we're basically recreating our 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 that it can grab them later. But we're actually only dealing with the current state data for 99% of our queries, because 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.

In the old days of legacy data warehousing, we used to have a staging error 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 known robust patterns but use them in a new way.

Yeah, exactly. That one about archiving is 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 the historical data within set period of time, it effectively just moves off to the side again. It's still instantly queryable or come back up again. But you know, if you haven't touched your archive data, that might be, you know, one year, two year, five years, 10 years, that's effectively all off in, you know, cheaper storage because you're not using it anymore on a daily basis. That's a nice feature that cloud gives us. We don't have to physically worry about my, you know, temp space, data space, user space is filling up on my database because it's not a consideration anymore.

Yep, and one of the many pieces of magic we use to keep our costs down and therefore provide our magical T shirt pricing.

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 makes sense to them, that 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 the supplier and who's an employee. And so to do that, 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 three 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 purchase 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 in anger the first time because effectively there's a lot of 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 it, 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.

We'Re leveraging patterns that have been around for a while. So the concept, detail and event model, it leverages heavily off a patent 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 a consultant, we had to build it a new technology because the customer always had a slightly different flavor of, of why 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 in SQL or they're using a database that doesn't allow you to use store processes. So you have to buy an ETL tool that does all the heavy lifting. 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 patterns, hardened them and made them as a service. So, you know, probably a lot of. Well, it's not improbably a lot of your. Your life last year, Nigel, was re. Plumbing and hardening those patterns to make them as magical as they are now.

Yeah, that's. 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 needed 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, you know, fit probably a good 95% of what was needed to be done for each of those customers. And there were more and there were diverse customers, different data types, you know, different inputs, different outputs. But, 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 data vault centric modeling, and it's well known out there, is that you end up with too many tables. 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 going to go put that back together and then I've got to go and hook that up to my event and then go, that event is a product or where the hell's a product concept? And then go grab that and where the hell's the detail about the product? And you know, I don't write SQL and I don't know if I could write SQL that did all that together. For us, we want to make that simple. What we did was we added one more layer on top, which is our last layer, which we call the consume layer. 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 automatically. 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 our truly magical agile data experience, the consumers getting close. Because 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, you know, it's. And it's a 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 and 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 a detail about it. And 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. They say, hey, this financial system has got this idea of a customer and here's the id, 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 customers from my financial system is a bit of a pain in the ass because I 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 change to that data. It'll just automatically give me a single table of all my customers data from Salesforce and from my financial system. But if those identifiers are different or if I want to de juke 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 factory. 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? 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 a MPP database, 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, 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 of events and we never persist it? Or do we persist the data in there because there's some benefit in doing that 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 of the BI tools. Visualization tools. So some visualization tools love big denormalized tables. Some of the older tools still like star schemas and the idea of facts and dims. Some of the interesting tools still like relational data models. 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. Computer doesn't have to be tables. Could be views, could be a mixture, could be a mixture, depending on, you know, what that particular rule and volumes and columns. You know, 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. It 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.

It's the joy of the patterns.

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 we enabled you to call that data via an API. And the API was effectively calling the consumer layer the consumer area. So 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.

I suppose.

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 encode and not physically storing data that we land into history. And then every moving part is virtualized as a series of views that's 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 them, them, what their value is, why we use them, and therefore 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 if you're doing a data project and the people you're working with have no clarity around the layers, they say, just put it in the lake and you'll be good.

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 I firmly believe, I always believe that 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 re plumb under the covers, and your diners out in your restaurant are oblivious to it, but their data still comes out on their plate. And recently we. We re plumbed the whole event layer for a customer they had no idea because the consume layer delivered exactly as they expected. We re plumbed 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, I think that's us on layers.

And that Data Magician was another agile data podcast from Nigel and Shane. If you want to learn more about how you can apply agile ways of working to your data head over to Agile Data Data IO.