AgileData #44 - Layered data architecture with Veronika Durgin

Foreign.

Welcome to the Agile Data Podcast, where we talk about the merging of Agile and data ways of working in a simply magical way.

Welcome to the Agile Data Podcast. I'm Shane Gibson.

Hey Shane, I'm Veronica Durgan.

Hey Veronica. We've got a good one today. We've got a deep dive into this idea of layered data architecture. So the reason I asked you to come on the show was you did a great post in the Data Quality select group around the architecture that you've been running. I wanted to go through and let's talk about what it is, what the layers are, what they do, maybe some alternatives you thought about, ones that tend to work and some ones that don't. But before we rip into that, why don't you give us a bit of background about yourself and your journey in this world of data.

Yeah, certainly. So I've actually spent my entire career working with data and I started in 1999 and I think why it's important, maybe slightly funny, is because I started as everybody was freaking out about Y2K and I had no idea what it was or why it was important, but it was interesting to observe people stressing over it. And then nothing happened and the world moved on and it was great. So I fell into data. My undergraduate degree is actually in biology. I was pre med. Never really had enough passion to go into medicine, which is okay. So I landed with a junior DBA role. Loved it, absolutely loved it. Loved data, loved SQL Server. Ended up going to get a master's degree in software engineering because back then there was formal education and data and spent my entire career in data in various different roles. I was a DBA for quite a long time, focusing on performance tuning, optimization, have real deep understanding and appreciation of how well systems can run on databases when they're designed well and have TLC going on there. Tender love and care. And lately I've transitioned out of SQL Server into Snowflake and more of analytics. Still love it. Data is my work, my hobby. I love Snowflake. I've been really enjoying Data Vault lately as well. A lot of passion there.

I was around for Y2K as well and I was gutted that I didn't realize I should have become an SAP consultant and traveled to Europe and made an absolute fortune doing Y2K projects at horrendous rates in those days. In fact, they're still good rates today. I was back in the days when we had Oracle and we had SQL Server and so we had the on prem databases. We had those massive constraints where we couldn't put all the data in, we couldn't run any query we wanted because those things just didn't have enough horsepower. And so typically the DBAs were grumpy because they wanted to protect their baby. They couldn't let their database go down. So were you a grumpy DBA or.

Were you a. I wasn't a grumpy one. I was actually, I would call myself a lazy dba. I always wanted to just automate things or do it just once and never do it again. I said my ultimate goal in life is to automate myself out of my job and then I can sit back. But to your point, I spent quite a few years basically code reviewing, doing code reviews. And then software engineers were a little bit scared when I walked by their area. I would see them just kind of dive under their desks. They didn't want to get yelled at. No, I think I was nice, but I also part of my job. I used to teach and help others just get better at writing better SQL. So I want to say I was nice, but I don't know.

I remember when I was at Oracle, so I worked for the company itself and we had a great consulting team and there's a lot of database usage at the time. And there was a consulting dba, Craig, and he was grumpy, but he was brilliant. But he was grumpy because typically he got parachuted into a customer that was having performance problems with the database. And I remember he had this toolkit, think about it as a diskette in his top pocket, although it wasn't at the time. The first thing you do is you parachute him. He would run all these automated scripts over the database and that would give him insight in terms of what was running. And then what he tended to say was, all the code's crap, rewrite your code, there's nothing wrong with the database. Sometimes he could re index it and repetition it. There's some things he could do, but yeah, he always blamed the code. But that level of automation, that's what, 20, 25 years ago that people were automating that drossi work and saw us in the data world start to do that and then the big data Hadoop bollocks came out and then the modern data stack and we moved away from automation back to handcrafting. What's your view at the moment? Do you reckon we're moving back to the world of automation again or we still in decentralization? Self service, Craft your own COD mess and let's see how it goes, there's.

So many dimensions to it. I think back then we had to be very careful and also creative because like you said, we were limited by hardware, we were heavily limited. We invested in servers and that's all we got for whatever three to five years. So we had to be very careful. We had to do more with what we had. And then cloud happened and then everybody stopped caring, oh, just throw more power at it, throw a bigger box, it's fine. I think we're back to, oh, but wait, it's expensive. And now how much more power can you possibly throw at this vast amount of data that we want to crank through? I think it's part of it. We are forced to be creative when we're desperate. And I think we're getting back to that point where we can't afford to do things manually. We have to be very conscious of how much money we spend and it's not that we want to necessarily spend less, we just don't want to spend more. I think it's a full circle we came back to, okay, now we actually again need to care how we use it and what exactly we use to make sure that we're running optimal things, that we're actually getting value. Because at some point it's a net zero game, right? If you can invest all of this and you're getting nothing back, then why are you doing it?

I definitely agree that theory constraints, once we start getting constrained, constrained by hardware, we're now constrained by dollars for people. So we don't have the teams that we were allowed to out and be effectively lazy in how we did it. Those constraints force us to optimize. And there's various ways we can do that. And one of those ways is to actually have an architecture, a data architecture, to have some constraints about what data can go where, what we can use it for, what we can't. Which we lost with the big data Hadoop area where we said, just dump it all, let anybody write any query and the machine will take care of it, which. Which proved funny enough to not be true. When you think about a layered data architecture. And I want to differentiate between the detailed data modeling for now and the idea of these layers. I think of it as a cake or a bow tie or a series of steps in your head, the data architecture that you described on that slack, do you just want to run through that, talk to me about the layers and what they are and what they aren't?

Yeah, certainly. And throw some other things potentially controversial. I don't know if it's cool anymore or not. I am a supporter and believer in elt. I'm a little bit of a data hoarder. I don't like throwing out data just because I don't think I need it now. So I truly believe you load it first and then you figure out what you need to transform and how and when. I also being a dba, I always love to have plan B. So I'd like to have a copy of pre transformed data because if I make a boo I can always roll back. So with that in mind and I guess it's like partially personality or maybe partially experience living through the past two plus decades. The way I look at data is again following LT approach. Data is generated in source systems, applications, devices, et cetera. It is generated there to support capabilities of those applications devices. It is not meant for analytics. I know there's this whole thing, but software engineers store data to support applications and capabilities and devices. It is not the same as analytics. So as this data lands in whatever data platform you use, pick your own. To me, this data is good to have it there just as it comes unchanged, honestly. Let's talk about ODS Operational Data Store. You're essentially offloading some operational reporting. Again, applications databases that are designed to support applications are not the same as supporting analytics. To me, there's tremendous value into moving this data just as is into some platform that can support that. I consider that to be silver data. Again, it doesn't matter, it's semantics. But to me what it means is that we know where it came from, we know which application came from, but we don't necessarily know all the details about this data, nor do we need to. We don't have to model everything. Not every data is necessary for downstream analytics, but there's again tremendous value to it. Then some of this data gets modeled for your data warehouse and again pick the pattern that you like, enjoy, whatever. Again, I'm a huge fan of Data Vault right now and we can dive into that later. But that's what I consider gold data because we actually know exactly everything about it. We modeled it to reflect business, we cleansed it, we caught all the data quality issues that we had to. There's lineage, there's data dictionary. To me, data warehouse again becomes that kind of like gold data. There is analytics team that supports it and guarantees that the relationships are built between analytics teams and business people that generate application data. And then of course there's data that I call bronze because we're going into this decentralized analytics space. I can't remember who said it and I wish I remembered. Centralized data, decentralized analytics. So these analytics team need ability to experiment, move fast, not necessarily production lies, but do whatever they need to do to discover, to experiment, test. I can consider that data to be bronze. So because they mix and match, maybe they load some files, who knows? So that data is still there, they know what it is, data teams don't. So that's how I think about data sets. Again, more of semantics to me. Logically it makes sense. It's also quality of data and who supports it. Where would you go to ask questions about it?

I really like those two lenses. The quality of the data, how much do we trust it and who supports it. Your use of bronze, silver and gold is very different to the standard in the market for the audience. I just want to clarify that a little bit. We're seeing databricks adopt the bronze, gold, silver terminology game for patterns. And it's typical that the raw data, the data that's been landed from systems of capture that we haven't done much to is typically called bronze. The middle layer, where we have modeled it, we've cleaned it, we've done some stuff to make it for purpose, that's typically called silver. And then the presentation layer, the title, cherry on top, is typically called gold. And then your definition of discovery, which you call bronze, where we've got a bunch of analysts throwing data in and prototyping and discovering stuff that we haven't automated yet, that typically just gets called sam. It's interesting that you're using slightly different terms for different things, but the key thing I always say to people is it doesn't matter which terms you use, just write them down to be very clear. So when you're in your organization and I hear silver, I know that I'm in the raw area, I'm in the history or the persistent staging or whatever term we're going to use for it. That's silver.

Yeah, exactly.

So let's just go back and we'll just go step by step through those layers. So we've got the systems of capture, we've got software engineers doing their hard job, then we've got ELT to bring the data into a lake and then we model it, consume it, and then we've got some kind of discovery area where analysts can go in and do the bit that they need to do. And we're talking about a team design, typically where we have a centralized data team and decentralized analysts, which is very common. So let's look at that software engineer first. There's this whole move to data mesh, this whole move of let's take this domain of data and the things that we do and let's push it back to those engineers. And it's very unclear the patterns that need to be adopted. Sometimes I read we're going to get the engineers to do the job. Sometimes I read we're going to parachute a data engineer into the software engineering team and they're going to be a team of one. Sometimes I read we're going to have data engineering team and they're sitting right next to the software engineering team, but we're still handing off. And if we look at that first one, pushing the work back to the software engineer, that's really what we want. In an environment world, when they create their applications, if they create the data that fits for analytical purposes, we're done. Actually we don't have a job. But that's okay because the consumer, the stakeholder, can now use the application and query the data from an analytical purpose. But the problem is if you're a product owner or a product manager for the application and you've asked them to build a couple of new widgets on a screen for a new flow to onboard some users and the software engineer comes back to you and goes, we've done all the screens, we've done the flows, we've all tested, we're ready to release out to the audience, but we just need a couple more weeks to do the data analytics work. 9 times out of 10 that product manager is going to prioritize, push the product to the customer, get the value, do the data analytics work later. Because they don't really treat that data analytics work as a first class citizen. And if your corporate culture is not to have data as a first class citizen, if you're not going to make that trade off decision where you're going to wait for the value to be delivered, until the analytics value is being delivered, then it's never going to work. What's your view about pushing our data work into the software engineering team?

I actually don't think software engineers should be doing analytics work at all. I lived very long time side by side with software engineers. They're good at developing capabilities. Unless you're company in the business of data like Netflix, your software engineers should not be building analytical solutions. That's not their core capability. We were trying to jam something into them that they're not good at. They're good at writing software, GUI website, whatever that may be. That is not data for analytics. So I'm completely against it. It's like somebody asking me all of a sudden to write software. The software that I would write be absolutely horrendous. You don't want me writing software. I'm not good at it. I'm good at data. And the other side of it is even if you.

Drop data team side by side with software engineering team, where does data integration happen? Analytic needs to happen across all of your data. I have for example 20 applications that have data about a manufacturer cars. I have 20 applications. If I drop 20 data teams into each application, how do I integrate it? I need to tell story about the whole car, not just rubber on the tire. So I'm actually I'm probably we're going to jump all over the place. But I am not exactly bought into data mesh as the right thing to do for every company. I think it is something that a very large company with many data teams has to do. But for most of us I think it's just an excuse to not do data.

I agree. I think there are patterns that fit you as an organization in your context and these patterns that aren't. And as we know, as soon as somebody comes up with a semi new pattern, the whole industry and all the consulting companies jump on it and flick it. I would say though that I believe we could locate our data engineering squads, teams, pods, whatever you want to call them, next to our software engineering breads are in and we know that working closely together as a team, we get value. And yes, I agree that the consolidated reporting, the ability of a single view of customer or a single view of product, that cross domain reporting, which is where we spend most of our time, has never been answered in the data mesh paradigm. It's like a data product on top of a data product on top of a data product. And we've seen how that goes. But I'm also a fan of data vault like you. So for me it's still the best modeling technique for that middle area, for that gold modeled area that I found. I'm hoping there's a better one coming at some stage that solves some of the problems. But for all the modeling techniques out there, it's the one I think does the best job. And I keep thinking if we push the work back into a data team that sits with the software engineering team, Data Vault gives us a bunch of patterns. So for example, if we think about the idea of a core concept, have a concept of a customer, a concept of a product, a concept of an order, a Concept of a payment and data vault, we treat that as a hub. We go and say, look, there's a key, I have a customer id and that's unique. And the first time I've seen it, I've seen it. And then after that it's just a bunch of detail about it and we have a bunch of patterns about how we integrate those. So we have the ability to lightly integrate where we get told all the keys in the organization are the same across all the engineers, which would be lovely, but we know never true. And then we have other ways of upping the patterns that we use when we get that uncertainty. But in theory we could get that data software engineering team to use the same patterns to define hubs, which then allows us to integrate across the mesh teams with minimal effort.

I would make it much simpler as a software engineer team. You don't have to create hubs, just give me all the keys that are required that make it easier for me to tie all the other data together. I think that's it. If we solve two basic things is add all the dependencies that I need to be able to easily tie data across the company. And also if you want to do product analytics, analytics, certain things just this measures KPIs, whatever that may be. That's all. Honestly, I would ask of software engineer that you've lived through NoSQL, right? NoSQL is going to take them all relational going to die. That just shows you that software engineers good at software, not a data. So yes, if we ask them to do basic things and whether data engineering teams are co located or centralized doesn't matter. I think specifically we're talking about software engineers. Here's three things that I needed to do to make downstream life easier. That's it.

It's funny you say NoSQL, we've got a customer on our platform and they're a startup, they're building out their own application and they're using a NoSQL database and it's all good. We've got the data coming in and then one day we had some of our anomaly alerts go off and we look at it and it looks like the product codes that used to be a guidance have all of a sudden become text, just natural texturing. And you sit there going that can't be true. So you go through and you look and you go okay, obviously schema mutation we've missed and we're picking up a different field. And we went and looked and no, the schema is exactly the same. And what we found out was the engineer had actually re keyed their entire database and changed the key structure without telling us. So there are a bunch of immutable patterns that as data people we want our software engineering brewthrin to not break. But if you talk to an organization about data governance, it always seems to be committees and data quality and catalogs, all this big data governance. One of the first principles or patterns we should put in is here's how we define a customer. Define it that way or tell us you've got an exception. Here's how you're going to store certain attributes because we need them. If you're not, let us know because we've got some more work to do. There should just be a small set of immutable principles or policies or rules that you can't break. Do you find that? Do you find that organizations don't set those basics in place and they tend to try and bore the ocean.

That used to be the case. So when I was a dba there was literally five rules about not breaking schema. You can't rename a column if you want. It's backwards compatible changes. So whenever you deploy it has to be backwards compatible which means you can't rename, you can't change data type. It has to be a multi release change. There's a handful of these. Those are must be done rules. And I think what happened it's because I've also, and I'm sure you've heard us, oh, you guys are bottleneck, you're slowing us down. That's how NoSQL happened. But then you probably also heard this, oh, we can only pull data, about 10 customers out of our database before time's out. I'm like, I bet you're running NoSQL. So I think we've relaxed. We've been seen as bottlenecks. Data people are going to be out of jobs because we're just data lakes happen because it was so EAS dump without worrying, I think we've come a full circle. So the data contracts conversations are starting. But to your point, I don't believe in. If I have to read five page document, I'm not, I'm just not going to do it. Give me five hard rules, that's all. And they're really as simple as that.

If we look at the way the software development engineers work, they automate a lot. They automate their testing, they automate their deployments of their services. They use libraries to make themselves faster when they're developing. So I'm always intrigued that we've not come up with a set of patterns where we say, here's a library deploy that call it whenever you're defining the application, it tell you if you're breaking a rule we care about and if it does, don't, because we're going to get the same alert. And that way they know what they can do and what they can't do and they will typically do the right thing, because people typically do the right thing if they know what it is. But at the moment we're after the fact. We don't engage with them, we don't tell them what's important. They don't know that they the break that they're doing, it's costing us two weeks of redevelopment.

And maybe that's where you were saying where data teams sit close to software engineering teams. It's again softer side. It's not tools, it's just people and processes. If you sit together as one team, you collectively want to succeed as a team. So maybe observing the pain that things like this cause will actually encourage you to do the right thing.

And then the challenge, of course, if you decentralize your data teams to sit next to the software engineer teams now what you lose is you lose the rigor across all your data because you now your data teams are effectively isolated. So you've got to figure out how to run a matrix model or guilds and all that kind of stuff. Team design is whenever I'm working with an organization, first thing I say to them is team design. First figure out where your teams are going to sit, how the comms lines are going to work, and then after that we can talk about platform architecture and data architecture, because Conway's law basically says we'll revert to the way the organization flows. So let's talk about the next step then. So we've got got our software engineering friends all sorted. They finally give us good quality data that meets our needs because we give them a set of rules and then we're going to bring the data into the data lake and we've got that whole elt, etl, et ltttt. And I'm like you. So I break a rule of agility when I talk about this because if we're being truly agile, what we say is we only touch data that has value. We only touch data when we need it. And the reason is because as soon as we touch data and we're moving it, there's an incremental cost to the organization of maintaining that. So if there's a field and we don't need it, don't bring it. Because if the software engineer changes that field, we don't care because we're not using it. I don't actually run that way and I'm like you, I effectively will bring all the data in and then effectively land it into the lake and we call it history and I'll land it in raw, I'll touch it as little as possible. And I do that for a couple of reasons. One is it makes me more agile further down the value stream. So what we know is when a stakeholder says to us, oh, I have this bit of information and if we haven't collected it before, that cost of collection is always expensive. It takes us time, it takes us effort. If we're already collecting it, then it's in our domain, it's in our platform, we can now do all the work forward and we're much faster. So I think by bringing it into the lake and then having it sitting there available, it makes us much faster and more agile to deliver to our stakeholders. The second thing is it forces us to automate. If I'm bringing 10 fields in or 10 tables, I'm probably going to do it semi manually. If I'm bringing in 700 tables, I'm going to automate the snot out of that work because I ain't doing it 700 times. And the other thing is we can roll back. We have this trusted immutable lake of information that doesn't change. And that's the key, right? We can't change it. It's, it's the history, it's what we saw. Again, one of the principles of data vault is bring it in and don't touch it. Always be reconcilable back to the system that capture you got it from before. You do bad things to it in your model to your gold layer.

I agree with you, but it's an interesting thing. So between agility only work on what's being asked, which is I think how the traditional dimensional data warehousing was only deliver what's being asked. But then the whole idea of a data product is actually expanding it so it covers more than just that specific use case. And then we've falling now into data vault pattern where it's. You're already looking at this data pull everything related to it. So usually it depends. I hate being that person and say you have to find that balance because you also don't want to spend hours and days looking at the entire source system and pulling data that you actually don't necessarily need right now. And I think that's where experience comes in. We have to keep practicing to find that balance. Because I get pulled even from my teams, we have to move fast, we have to be agile. But I'm like, but that's not an excuse. You can spend four days and work on something instead of three and deliver a lot more value than that specific tiny use case that you've been asked to do.

And it is a balance. It's something you've got to balance. And there's a bunch of patterns there. As always, if you're hitting that table and it's got unstructured blob objects sitting somewhere in there and you don't need them and you haven't built a pattern for that yet, you're probably not going to bring it through. But if you know that system of capture is not tracking history, it's not actually keeping a state of what things were, then you're probably going to bring it through. Because that's the question you always get asked as soon as you've been asked how many why? Is the next question. And that involves the change state. And the key thing is automate it. So where you can automate it so you don't care whether it's one table or 100 tables, you don't care whether it breaks or not because you're going to get alerted. So automate all that drossy work and then volume doesn't matter to you as much 100%.

And the other thing is back to your question when you asked build it yourself versus automate. I talk about this a ton. The company you're working for, you custom building, say ingestion engine. Is there value for a company in that? Because there are a ton of commercially available open source, whatever ingestion engines built already. So you're reinventing the wheel, but what is the value? So just bring something that's again like build versus buy. And as engineers, we'll have to build, but at some point we'll build something that's actually valuable to your current company, to your current business, whether it's your own or not. Not reinvent the wheel.

Yeah. It's weird that in the data domain and data engineers love to build the same thing from scratch every time. Somehow it's in the mentality.

It's like this definition of insanity. If you haven't succeeded the first time, you're probably not going to do it better the second time. Just bring it in, build something else. There's plenty of space to Innovate.

Yeah, there's lots of gaps that we can't buy off the shelf and it's the same as replacing components. Oh, I'm running Ebike now. I'm going to change to Dexter. Why? Ebike's not really working for me. Why not? What's it missing? What's it not doing? Because we tend to dislike to re engineer things because we think the next one's going to be magical. So that elt. One of the challenges we have is the diversity of systems we hit. We got systems we still have on prem systems around there, but we have systems where the only way to get the data is going to the database. There's systems where there's value in hitting the redo logs to bring in via change data capture technology. There's ones that have APIs, these ones that don't. I'm still not seeing a lot of Systems that have APIs that can handle the type of extracts that we want. You've got the big ones like Salesforce where they have the mass of hardware and engineering where we can treat their databases. If it was an API, pretty much. But that's fairly unique. Typically most of the other systems, if they have an API, it's throttled. It's only giving me the current state. Is that what you're saying, that we have this nirvana of pull everything via an API, but the reality is we're going to always end up with a mismatch of technologies and extraction technologies?

Yeah, that's what I'm seeing. Exactly. All over the place. You have your own on prem databases still. So generally that's where you want to do some sort of log CDC thing to not overload your systems. Then there's various SaaS applications. Some of them are more mature, where you actually have a stable API you can call, but again they're throttling. So it's all over the place. Some say, well, download stuff into Excel CSV and load it yourself. What I'm also seeing is that a lot of companies are if we're going to use your tool and we're going to exchange data, this is still our data. You have to make it easy for us to get it back. And I think now it's actually been pushed into contracts. So if you're not making it easy for us to get our data back, I might reconsider purchasing your tool. So I think collectively it's getting better, or at least I wouldn't be optimistic. But you're right, it's all over the place.

That Data sharing is going to be an interesting market, especially because we've got that 0etl bullshit coming up now. And I'm going to call bullshit on the term, because 0ETL is not a thing unless you're not transforming it and you're not loading it. I see absolute the cloud analytics database. Having access to a system to capture without us having to write any code, that's massively valuable. That's taking a whole lot of work off us, which would be great. But it's still extracting, it's still loading, right? Absolutely.

I just can't reverse etl elt, ltl. Okay, let's come up with new letters. We don't have to use the same three letters. Let's be creative here.

Yeah. So my recommendation always is figure out your application domain. Yeah. So what kind of applications you have, Try and do a segmentation or a cohort analysis where you can put them into buckets. Try to find a small set of technology patterns you can use to extract data and then apply them to many of those systems that make sense. And then as soon as you hit a new system that you don't have a patent for, be aware, that's going to hurt. You've got new technology, new patterns, new learnings. It should plumb into all your observability or the same patterns that you've already got so you know that the first time you hit that new one, you've got a problem.

When you're negotiating with a new vendor, ask them, drive that conversation. Don't take it, because they might actually be just as willing, because it's hard for them too, if they have to write some sort of custom extract for you. So have that conversation with the vendor, getting yours and their engineering team together. I've also observed in general that a lot of vendors are willing to work with you to just standardize as well. They're having the same pain as we are on the receiving side of it.

Our stakeholders go out and spend money on a new capability that only delivers half of what we need and then tells us it's the data team's problem to deliver the other half where we had no input. So that's a good point. Right? Try and behave, Work with those stakeholders to maybe say if both applications are suitable, the second one has better integration, will save us a lot of time and money. So we get that data and we bring it in and then we bring it into the lake, which is our first repository of data. And this is where it gets interesting, because I'm a Fan of the data in that silver or that raw, that lake area. Matching the structure of our source system. Yeah, I tend to land it, I tend to version it. So I get change tracking of change records because it has some value. I'll do a little bit of metadata work on it. I'll augment it slightly because it has value for us, but I won't touch the data. It's raw up. It's a immutable version of what that source system look like. Whereas in data vault world, we're meant to vault it at that stage. And I don't. And the reason I don't is it takes a lot of effort. I have to go and do some design and I don't want to front load all that design work. I want to get the data in and then do design over time. Still using the vault methodology for me. What's your view? What does your data lake layer look like? Is it source system specific or is it modeled?

So the role layer, again, to me that's. I don't know, whatever terminology we should come up with. It's like a data lakeish ods. It has that. When you said your version, that's insert only. Right. It's ODS because it's matching your Source Systems. I'm 100% on board with that. You can load all of your data like that. Not all data needs to be modeled. So again, I love data Vault, but we don't need to model everything into data vault. You're right. It's a lot of effort upfront, especially if you're just starting. You can't possibly model everything. But there is a tremendous value in doing, whether it's ad hoc analytics or operational analytics on that raw data lake. You're basically offloading source system onto your data lake, which can handle various analytics.

These things we can do as we start to figure out what we want to build. We can profile it. There's a whole lot of things we can apply a contract to and tells us early if it's breaking. So I'm with you. And if we look at what's happened now with the data lakehouse where we're merging file storage with cloud Compute, compete against the cloud analytics databases, perhaps we should actually rename this architecture to Vault House, where you have a lake in the funnel or vault Lake house or lake Vault house, which always come up with a new name.

Right. Lake. Let's just combine ELT in some other new way that ttle. I don't know, I'm struggling a little bit with is we are generating a lot of data across the world and Data Lake has all of it versioned and then we move it to Data Vault, which is again, all of it versioned. Now we have two copies of the same data. So it's a lot of data one and there's also compliance. Now we have two copies of the same data. So this is where. And I don't have a solution to this, but in my mind this is where I'm struggling a little bit. Perhaps at some point we can virtualize Data Vault. I still really like Data Vault because you organize data by business concept. I think to me that is just massive and huge and your source system agnostic at that point. So as business, you can continue function without worrying that all this data came from SAP or Salesforce or whatever that may be.

I think it's the theory of constraints again. Just in the past we used to have a DBA telling us when our queries were needed to be tuned or an index applied. And then that disappeared with the cloud analytics databases, where they're big enough and ugly enough that we can just throw good volumes of data at them and they just run. And we don't need to care about tuning as much as we used to. We still need to care, no matter what the vendors say. I think we'll get to a stage where we can virtualize, but it's not here yet. And we do that in our platform as we virtualize as much as we can. We got an example where we've got a consumable table that's just hitting 400 million rows. We can't virtualize that easily without pending a fortune on consume costs every time a query is run. So we physicalize that table, but if it's small, we virtualize it because why not? From a pan point of view, you need a switch. Whichever a human or the system sees it's. It's cheaper and easier to virtualize it because we're not moving the data as often or. No, actually there's value due to the constraints to physicalize that data at this point in time. And I think we'll see that come through. We've seen it in data virtualization tools like Denodo where they allow us to write a query and it will pass it back to three different databases and optimize the query as much as it can. I think we'll start seeing that in the data space in our domain where we can start virtualizing our logic. I'm just thinking that through now. I'm not sure that's actually going to make it easier because it's hard enough now as we move the data through our spaghetti lineage graphs because we're changing it to figure out where something came from. If the system's dynamically virtualizing or physicalizing it for us and we've got a performance problem, we've got another lens, don't we? Where we go, is it the code? Is it the logic? Is it the data? Is it the engine that's optimizing it? Is it my query plan?

Did you try and then try to figure out data quality of what fell through the cracks? For all of you engineers out there, when you think of potentially writing another CSV ingestion, focus your energy on actually thinking through how we can virtualize and how we can dynamically figure out what's the right materialization is so challenge out there to the listeners.

But that's a big problem. If we look at how long was spent in the database world to get query optimizers materialized views and optimization and that query plan, all that logic about where to run, where fast, that's a big technical challenge. We'll see how that goes. So we're in the model layer now. So we've gone from the lake, we've moved it into the model layer. We both like data vault as a modeling technique for that for a whole bunch of reasons. But what we've seen with the DBT way is we're seeing them, first of all, abuse the term model by taking it from something where we actually do some design to basically calling it a blob of code, which I'm still incredibly angry about. Not that we have a bunch of code because we just know what was going to happen when you end up with 5,000 bunches of code. And I don't know why the market's surprised now that 5,000 blobs of code have some problems, but we're going to model, but most people don't. Was that not true? Am I not seeing the market? So from what you're seeing, are organizations and data teams actually modeling data or are they just doing blobs of spaghetti code that do create?

I'm talking about dbt. I have a friend of my relationship with it and DBT is just a transformation, just like Informatica. There's no more. You just write code and transform. So I'm with you on that end. And I remember correcting somebody once I got angry, somebody wrote some blog I wrote. Then there's oh, DBT models. I'm like, that's just transformation. Anyway, So I think there are two sides of it. I think older mature companies have formal data models, your logical models and your physical models. And then everybody who's new and I was recently on a podcast, we were trying to figure out the new generation that probably in the past five to seven years they don't need to model because the compute right now can handle spaghetti messy, overly complicated, 15 nested CTEs code. They weren't forced. Again, older companies had to do it and they still have that muscle. They continue doing it. They see value in it though. A lot of them rebuilt their data warehouses maybe 20 times by now, but newer ones haven't gotten into constraint where they have to. And as a matter of fact, I ran into somebody I used to work with recently in his late 20s, and I still don't see value of Data Vault. I don't understand why you would do it, which is fine. Doing dbt, I'm like, that's okay, I'll talk to you in five years. We'll have a completely different conversation when you'll actually run into all the problems that you're generating right now. And then we'll have a conversation again about how Data Vault is actually useful. So I think it depends on the maturity of the organization.

I would say with every pattern there comes a pro and a con, there's reasons that it has, it's useful and these things that it's not so good at. And one of the things about Data Vault is it's incredibly complex when you look at the Data Vault table structures because we get a lot of them. But, and so we don't typically want our consumers to have to query Data Vault. We want to put a layer on above it to take away that extra effort from them and make it easy. But one of the things I'm always intrigued of is when people start to use Data Vault and they're still hand coding the code for Vault and, and, and, and it's not, it's. If you're not automating it, then you're crazy.

Oh my God, please don't. Do you hate yourself? I remember when I first started with Data Vault and there was no tools. I just, I was experimenting and it was like at least 50% of my time was copy pasting, like just all this hashing and concatenating all the columns. I'm like, do you hate yourself? Fine. You don't have Budget. They're open source tools that can make your life easier. So I'm 100% with you on that. But also the other Side is like, I've seen enough failed dimensional models. Data Vault isn't any harder and you can mess it up just like you can mess up everything else.

A failure is not purely around the modeling technique. It's around your team design, your ways of working, the skills you have, the roles you have. There's a whole lot of things that, that cause us to fail or not fail. So one of the tricks though, with Data Vault is even though it's a really, in my head, a really simple conceptual model, because there's only three objects, hub sets and links, and a couple of outliers, somehow we seem to make it complex. Do you have any theory on that? It seems like dimensional modeling seems to be easier to understand at the higher grain at the conceptual level, and Data Vault seems to be harder. And I've never figured out why.

I don't know why either. I honestly don't know. To me, the hardest part, removing the type of modeling, actually understanding the business. What I've personally experienced is engineers reproduce source systems. It's easy for us to write a bunch of SQL queries, look at the data and then map it. Maybe that's how they model dimensional model, where it just reflects the source system. In Data Vault, we have to float above all of that, disconnect ourselves from data, and actually understand how business works. And I don't know if you've experienced that. Some businesses, for me personally at least, I have easier time to relate to, and others I have very hard time relating to. And modeling those ones is just difficult. It is really easier. Just write a bunch of queries and just copy what I already see in front of me. And I think that's where actually Data Vault fails. As you talk to business, you identify so many gaps that your 20 applications and source systems will never tell you.

Or we're just under pressure to do it quickly. And so we give up and we go, all right, I'm just going to write some queries against that data, answer your question. And I know I should be modeling it because I know you're coming back, because that's one of the things we know. We know the first question is only the first question. They're just. They're scoping out what the next question is and they don't know what it is. So when it's like, how many orders have we got? The next question is going to be, where? How much? How many?

We have this popular children's book in the United States. I don't know if you've heard it, how to Give A mouse, a cookie. So you give a mouse a cookie and next thing is ask for milk and then it basically moves into your house. That first question is just that first cookie that there's going to be definitely glass of milk being asked right after that. Going back again to data products, you need to have that general idea of what else is coming so that you build a little bit more so you don't have to rebuild things all the.

Time and also automate as much as possible. So if you are using Data Vault or any other modeling technique that is.

Repeatable, if you're using anything like at this point in any modeling, anything you have to automate.

So what's your view on source specific data vaults? And what I mean by that is so I've got say Shopify data coming in and I've got HubSpot data coming in and they bulb hold customer. But the question I'm getting asked is how many customers have ordered a product and that's only in Shopify. So our natural reaction is to do a source specific data ball. I'm going to go create a hub which is Shopify customer. I'm going to get that data out. It's lightly modeled so it still has some value. But I haven't gone and figured out the core concepts across the whole organization, the integration keys. And then HubSpot will get a question. We may do a source specific HubSpot model and then we'll try and integrate the two and deal with that horrible thing. What's your view on that? Because Data Vault 2.0 says don't, but they also don't say use a data lake. What do you think?

If you mimicking your source systems, why bother Data Vault? Like just stay with your data lake. But for me, like I've been around long enough to know that the first thing I would ask give me definition of a customer. And customer is always like the worst thing actually to ask about definitions. As you go across the company, across departments, across teams, you'll get 50 different definitions and that's okay. But I won't touch anything until I get a plain word definition of what something is. And it's actually, it's quite fascinating. I'm not falling into that trap two point again. Why build Data Vault if you're building something that mimics source, just use your source data at that point and then the other thing you lead into is data Mesh. That's also what boggles me. If your team works on HubSpot data and another team works on Shopify data, they build their own analytics and the CEO is like, how many customers do we have? Then what happens?

And are we going to say to them, don't adopt Shopify because we don't have a single view of customer definition yet? That's what should happen. We should actually say before you implement any system of capture, any operational system, you need to define what a customer is.

Right.

But I'm not sure I've ever seen that.

Probably not. And I don't think it'll ever happen. It's like being a good architect takes a while and it's also a bit of an art to it. There aren't many very good architects. So I think we'll continuously build and then fix that continuous loop of our world.

If you look at the building and construction industry, we'll just go pour some various foundations wherever you want and that's okay. We'll be able to build a house on top of it. Don't worry. We don't. Yeah, it's fine.

It's like street paving, right? Like they repave it every couple of years and you'll be like, oh, by now do you know how to pave a street? That it lasts?

Okay, so we've gone system to capture and the teams around that we've gone into elt it into a data lake or persistent data store which is maps like an ads does the source system tables gives us some changes and that immutable history. We're modeling it using whichever technique we prefer, but we both prefer data vault. To give us that value of that modeled and design data. I would typically then put a consume layer on top a presentation layer. So I'd typically put either a modeling technique or a technology that makes it easier for our consumers to use last mile tools to use that data. So I always have a third tier. What about you? Do you tend to have a presentation?

Yeah, we definitely. Again, I really like presentation layer. Data vault is great, but it is complex. It's not for everybody. But that presentation layer to me has like a few characteristics. And again, kind of like curious, do you use dimensional? I've seen a lean towards one big table, but you can virtualize it so it doesn't really matter. Before I heard the term one big table, I used to call it flatten the hell out of it data buffet, whatever that is. But to me you have to define the grain. If you can't define the grain, that object cannot be presented to users and it has to have a data dictionary. Every field in that object has to be defined in regular plain words. If Whoever is building it can't explain it. If the person requesting it can't explain it, then it shouldn't be going out. So those are just like this handful of rules. And then once that's out, then anybody can understand how to use it, what's in it, how to join it to other things. We also have obviously keys, naming conventions. If there's a key here and the same name, they better be like joinable. Right. Not mean actual different things.

Yeah, so data buffet, that's a great term Again, when we have our vault lakehouse, we should data buffet it. But it's good because it's that whole idea that if we're going to eat something, we typically want a list of ingredients. We want to know what's in that meal. So when we get this data served to us, let's find out what it is. I'm a great fan of One Big Table. Everybody keeps telling me. I know analysts prefer dimensional modeling and I'm going to call bullshit on that. I think they've been taught to use dimensional models because that was what they've been given. So they understand how to link dimensions to facts. But I think if you got somebody that been in your 5 to 7 year generation and asked them what an SED type 2 was and how they made sure they got the current value or the value at a point in time, they can look at you blankly because they haven't been taught that. So I'm not sure that dimensional model is easier to understand. I just think it's been taught well. One of the problems with One Big table and it was called out in social media just lately, is while we're using columnar databases now, which means if there's 2,000 columns and we're only using five, we don't care because we're only paying for the compute for five. There still is a problem when you give an analyst a 2000 column table, it's just, it's too big for them to find the columns they want. So that's a downside. The grain is order line and they want to count customers, they have to understand to do count distinct. Yeah, there's no different understanding how to join a fact in a dim. And typically we serve a table of customers and a table of events separately. So you can always go and see the customer table because why not? And that's one of the things again is that if you're using a modeling technique in the middle, you should automate the creation of your consumption of your presentation layer because you understand the patterns With Vault, we know that if you take a hub and a sat, put it back together, you've effectively got the equivalent of a dim. If you take a link and break it out to one big table, you've got effectively the same as effect with the DIMMs already embedded in it. And we can automate that. We know how the relationship of that model works, so we can write code that automates that consume. And so the same with the technology we use. Some can be virtualized, some can be physicalized, some can be summary, some can be point in time. They're all just patterns that we can use to craft the right solution for our consumers. And that's what we should be doing 100%.

And the other thing about dimensional model, I think a lot of tools require that. And that's part at least like what I've observed popularity in the United States is dimensional model. It's not that it's taught, but to your point, SCD2, like Blink. What are you talking about? You're 100% correct. But I think facts and dimensions is what a lot of visualization tools require, and that's what a lot of analysts are used to seeing.

I think Europe has a stronger bolt population and America has a stronger dimensional, which is really interesting given that Vault came out of America. Kimball did a great job of training and educating, did a brilliant job of helping people use that technology. Vault, not so much. And the second thing is these urban legends. So the one I always get is Power Bi prefers a dimensional model over one big table. Table. And I'm like, really? But if it's doing direct query and it's passing it down, then the database is doing the work. So are you telling me that the DAX engine prefers dimensional, like, where's the bottleneck? You tell me what optimization is and then I'll agree with you. But most people just go, oh, no, it runs faster. And I'm like, compared to what?

No, no, yeah, you're right. What is. There's dimensions and measures and tools. So I've seen Looker as an example. There's dimensions and measures, so it's maps to dimensions and facts easier, but the underlying model doesn't matter. The other downside of one big table is if you're materializing a table, then maintaining it. It's hard when you have 200 columns and some need to be updated, merged. That's where, at least from like operational side, the overhead comes in. But if they're virtual, then it's fine. I think with data, you do need to know what you're doing or do it and learn and then improve.

And so there's trade off decisions with every pattern, pros and cons, things that work and you get value and things are going to bite you in the bum. So let's move on to that last one which is discovery or what you call bronze. So this idea of sandpits, this idea of people playing or people experimenting or people researching and typically that will either be your data scientists that need full on your analytical feature factories and ML models and ChatGPT and then your analysts that are probably more draggy droppy drop some data in or some jupyter notebooks and those kind of things. And typically in the past we always had that as a separate environment. It was always off the side. It was called sandpit. We built a whole lot of policies around what they could store in there, what they couldn't. We put a massive constraint on it so that it was never as fast as the rest of the platform because we couldn't afford it. What are you seeing now? What are you seeing? Your bronze discovery area zone.

It's very similar. Only my experience in the past, whatever five, six years been with Snowflake. So you don't separate environments, you do zero copy clones. It's but it's still very much, it's sandbox, it's isolated to teams. It's either like a team sandbox or personal sandbox. You can't share because there's all that compliant data. Just because you have access to it doesn't mean that somebody else has access to it. And as they are done and they're okay, this is what I want to go to production, then it has to go through the process, then it has to go and land into that gold data. Right Ultimately because otherwise data governance is all gone. Bye bye. I don't want to be a bottleneck. I appreciate the fact that data science teams, advanced analytics teams, they actually do need to experiment in a lot of what they experiment with might not see the light of day but the parts that do need to be productionalized and support the business. They have to go through the process and be documented and have lineage and have to come into the data dictionary just like everything else that hasn't changed, they just live on the same platform now.

I did a project many years ago with MAPR back in the Hadoop world and trying to get my head around this idea of a data lake and how it was different to a persistent staging area apart from the technology and one of the patterns that I loved was this idea of bounding policies and patterns by a community of people. And what I mean by that is say here's a group of people and here's the collaboration they want to do and here's the boundary and here's the gates, here's the policies that actually have to be met before we could go outside that. That same scenario you've got where there's a pattern of this is my space, I can do whatever I want, but I can't share it. This is my team or my community space and I can do some work and share it with my peers for review so they can use and get value out of it. Then I can go to a bigger bubble, which is a community that might be a business unit or domain across the organization. And so again, that's a wider audience. And then from there maybe I go into everybody and then from there into external, outside my organization. If you think about each of those bubbles, when we move data outside the bubble to the next bubble, the blast impact of that data is bigger because more people have access to it. Therefore we should invoke more policies and processes around it because bad things happen, they escalate the more people that get impacted by. So I think that's what you were saying is treated as those bubbles of people in community and how far out? Yeah, but again, when we look at data governance, people don't deal with it that way. Again, go back to the data governance committees and their documents and they don't look at, they don't even do a simple model that says if it's going to more than five people, it should be peer reviewed. If it's going to more than 20 people, it should be automated. Just simple policies around those bubbles, lowering.

The barrier for data ops, the principle CI, cd, PR reviews. And that's where dbt, your modern data stack, the uncool modern data stack comes in handy and just again, deploy actions, merge PR reviews, rotation, like we're lowering the barrier for that. And then other teams, even within their own bubbles, they can follow this pattern. And you already kind of like on your way to productionalize if you wanted to. So I think that's where, when you write about data governance committees, some become just like a ton of bureaucracy.

Yeah, they're after the fact, they're reviewing after the fact rather than setting the policies up front that are valuable. And that point about reuse, data ops and reuse is really interesting. On another podcast I do, we had a couple of people came out of Spotify and yeah, the Spotify model is well known. They hate the fact that it's called the Spotify model, but the Spotify way of working back at that time as well now. And one of the things that was interesting about that way of working was each team was completely decentralized and autonomous. But if there was a capability in the organization that more and more teams used, then that kind of became the de facto standard. Not that people were lazy, but they just said if we're going to use a ticket management system and everybody's using that piece of crap called jira, they just call it jira. They go, why wouldn't we use jira? Because everybody's using, everybody knows how to use it. The supports there, we can move people in and out of teams and they'll understand the tool we're using. So they'll naturally coalesce to using the same tool. So if we're sitting out in that big bubble of production capability, we should be picking up those tools and techniques we have and trying to make them available to people in other bubbles and make it easy for them because they'll just use it because it makes their life easier, like ours. Don't hold it in our little bubble. And that's the same with access to the lake. So you see organizations that say analysts and scientists can't have access to that raw layer, that silver layer in your terms. They can only go to modeled. But all the behavioral data is in that raw, in that history layer. So they have to have access to it. So again, do you see that?

It's funny, I was just, I just came back from Snowflake Summit and I was talking about security and you go for data democratization or data dictatorship, the pattern of least privilege, right? Data safer, forget about anything else. But data democratization is where you want for everybody globally to have access to all your data. Because that's when creativity happens. That's new things for your company happen. But then the other side of what I've seen as well is people have access to all the data, they bypass the process, it's a backdoor. So they then create these ad hoc, who knows what, some random stuff that's ungoverned. So there again we're back to their trade offs. But I truly believe that everybody should have access to data. You want to anonymize compliant data, but in general, especially in the world we're living now with, just everybody needs to get better at data. You will find some unsupported unofficial things popping up where people just, just don't want to follow the process because it's a bottleneck and move off and do something else.

Some people don't want to fill out an expense form to get their money back, but they don't just pay themselves. They don't just go into the corporate bank account and take the money. There's a couple of rules to stop them doing that. So we do need some rules and policies. The one that I always crack up about is we restrict access to the raw data. What we'll see is we'll see spreadsheets, Google sheets turn up with that data because somebody's had access and then they've shared it with somebody else. And now we get these offline data marts and Excel because people are just going to get the job done. So we need to protect the data. We need to protect, but we need to be very clear about that data. What actually do we have to protect? And then let's protect it properly. Let's make everything else accessible, let's make sure it's monitored. If the data does turn up into another database somewhere and it starts becoming a semi production database, that should be observable, we should see it over there and we should say, what's the problem? What's the bottleneck? How can we solve that for you? But it is hard putting a big gate on it and saying nobody can have access. It's so much easier. We feel safe as a common pattern.

I think the other thing, and I don't know if you've seen it clearly, if there's a clear way to show people where data came from. You're looking at a report and that report is there, is like a stamp says gold data. You're like, I am comfortable. I know exactly what it is, I know where it came from, I know the team does versus somebody gave me a G sheet. You're like, huh. So I think collectively it takes a little bit of time, but I think as a company, as long as you can clearly define where data came from, as a company, people start to lean towards trustworthiness. Not some, yeah, maybe it's quicker, but I don't know if it's correct. So it just takes a little bit of time.

I think stakeholders trust people, they don't trust processes. That's just look at it, they go, oh, I know that Jane, listen, always gives me my numbers and numbers are always right. So Jane, yeah? Where's my numbers? All right, excellent. Hey look, I just want to wrap that up. So what we talked about was, we talked about systems of capture the software, applications, engineering teams and Some of the challenges and things we can do around there. Then we talk about elt, not etl, into a first layer, lake, raw history, silver, whatever you want to call it. So immutable version of the data based on our source systems. Then the middle modeled layer, gold designed, whatever you want to call it, trusted, where we're modeling it, we're cleaning it, we're doing a whole lot of. We're inferring values that don't exist. We're inferring values in terms of KPIs, then some form of presentation consumable, make it easy for the people that need to use it to get it in the way that suits them. And then some form of discovery or bronze where the analysts and the scientists can go and do that discovery sampit type of work. And the key thing is to find those layers to add a couple if you want to take one out if you don't think you need it, but just draw a picture, put some boxes, give it names and then focus on the policies. What can go in there, what can't, what will happen in there, what won't, who can access it, who can't. Those are really the core things you got to define for each of those layers. And if you do that, you'll be in a much better place than chaos. Don't over bake it because you're just going to put constraints that people will bypass when they have to. So yeah. Is that kind of how you think about it?

Yeah, exactly. And I think it's define it. Just use words at the end of the day, name it whatever you want, but define it. And I also spy a book. I know this is a podcast audio, but the Project Phoenix. Isn't that a delightful book to read?

I love it. What I loved about it is you're learning lean Manufacturing and DevOps without knowing it. They're just telling you a story and.

It'S oh well, lane and relatable, isn't it? It's almost like I remember that. Yeah, I remember that too.

Yeah, Some good stories in there you go. Oh, I know. Was it Steve, wasn't it? Yeah, I know. Oh no, Brent, Brent, Brent. Yeah, yeah, yeah, I know lots of Brents actually. Maybe I've even been a Brent. No, I haven't been. But yeah, I know lots of Brents.

No, I think I was a Brent at some point or another in my career for sure. Yeah.

Yeah. And the last thing for me is really interesting is that you came from biology. I am always amazed at how many people in the data and data science, particularly space, have come out of biology side of science. It's really interesting that biology space seems to be a space that lots of people have come out of in terms of their training and education.

Probably because a lot of us had that naive dream of going for a medical degree and then realized that it's a long journey and you really have to love it to do it. And we realized that we probably don't love it as much.

Yeah, I suppose data's as messy as human bodies. Like we've just coined the vault, data House and data buffet. You can just rename yourself the Doctor of Data. There you go.

There we go. Yeah.

Excellent. Hey, look, that's been great. I think we've managed to cover each of those layers and I think it match back to what you said in the Slack channel, so that's even better. But hey, thanks for coming on the show. It's been great to go through each of those layers, what they aren't, and back to that key message. Just define what yours is, put some words on it, put some boundaries on it, and then make sure that's what you're executing or you're realizing you're changing it. And change is okay, but it comes with a cost and a consequence. Thanks for coming on the show and I hope everybody has a simply magical day.

Thanks for having me.

Data Magicians was another agile data podcast. If you'd like to learn more on applying an agile way of working to your data and analytics, head over to agiledata IO.