Data Lakehouse: An Introduction

0:00 welcome back to my channel I'm Brian kathkey and in this video we're going to be talking about the data lake house and this will be an introduction to give you a conceptual background to what that is before I jump in please consider supporting me on patreon you'll get direct access to me special content and periodic q and A's among the benefits now one of the differences on my channel I try to maintain is introducing everything conceptually and making sure you have a firm foundation under which to understand the technical details that are discussed further on and in that same kind of mentality I'll be talking about the data lake house so we'll be talking about data lake house to data swamp revisiting the traditional data warehouse what's so hard about that and introducing the data lake house if

0:46 you're going out Wayback machine maybe 12 years ago a lot of hype around Hadoop and the idea that you could do all kinds of massive data processing using Hadoop mapreduce and as part of that the Hadoop distributed file system this sounded great people said let's use this stuff so we got people out there and they threw stuff out into the data Lake which is really just a storage system just like a file folder right throw some files out there did querying then they told their friends and they threw files out there and more people and so on and so on and eventually you had lots and lots of data sitting out on these file folders with no data governance no really thought about how it should be used and this was called Freedom right hey it was like we don't need no education we don't need no rules problem

1:32 eventually they started asking questions like what is this data it was put out a year ago and the guy left what happened what is this is it current is it accurate where did it come from why are there so many bogus values in the data how do I get the information I need and has someone already gotten this data I need together so I can just use it so this became a data swap instead of a data Lake in other words it became pretty useless pretty quickly people started to you know enter the trough of Despair This Promise wasn't all it was cracked up to be but In fairness sometimes people just jump too quickly and they forget that the data warehouse is in the SQL Server World also had to evolve and you can't just throw away the core requirements it's not things don't work like that there's no magic but

2:17 people forget that and Everybody sung the Praises of the end of the old relational databases and all the requirements of doing a data warehouse so let's talk about that for a minute let's talk about relational databases and the kinds of features that they offer to do a data warehouse well we know that they support these structured query language and that they're built upon set theory and EF Cod came all about all these roles and they came up with this idea of a relational database system and structured query language is a very rich and robust language that has been extended over the years and the idea behind a relational database is that you have these objects to store data called tables each table is supposed to store a discrete set of information maybe about sales or customers or products and only about that information so it relates all to

3:04 that subject you may have a relationship between given tables sales for instance as a customer somebody bought the product so there's a relationship between these relational databases are also good at supporting transactions right what are transactions Brian this is how you maintain the data it starts by inserting data then you needed to go back and do updates and maybe eventually you realize like the customer isn't going to be a customer anymore you delete them so insert update and delete are the kinds of things we do against our tables to maintain the data and we do that in something called transactions transactions on a relational database support acid which means atomicity consistency isolation and durability you don't need to worry about all the details what that means but the idea behind it is sort of an All or Nothing

3:49 Concept in which we apply all the related sets of Maintenance tests together or we do them not at all so if we consider something like the customer in sales we have something called referential Integrity here we want in other words we don't want to allow someone to insert a sales row unless there's a related customer which is represented on the sales Row the customer key that means that we have to first insert the customer then we can insert the sales row and if we can't do both we should roll everything back and that's the idea so there could be more tables involved we could have many tables involved and we would wrap the whole thing up in what's called a transaction do this insert do this insert do this update whatever it is we need to do and we put it together as a

4:36 package called the transaction and at the end of it we say commit it now if the commit has a problem suddenly it runs out of space or something goes on it can't do it then it's supposed to roll back or the program it can test it and say oh something went wrong I need to roll back the transaction meaning undo everything as if I never touched it now you can imagine you've done a lot of changes it's important that you get rid of everything you did and return it to the state where it was before you even started that gives it a sort of consistency you may be missing data for that transaction but at least you're not Half Baked some of it got through some of it didn't and you don't even know what got through and what didn't so it's better to have this consistency and that's supported in these transactions All or Nothing it can do this or it doesn't do this

5:23 another thing that extends the sort of Integrity of the data are constraints there's a whole bunch of different types of constraints you can see in the database picture there we talked about referential integrity and that means you can define a rule in the database that says you cannot insert a sales row unless the customer key is found on the customer table in other words if I'm inserting a sales for customer a then customer a must exist on the customer table there's a lot of other types of constraints domain constraints and force that data types are what they should be integers and decimals you can't put the wrong type of data in a column we have key constraints what's a key constraint well things like uniqueness if you're

6:08 going to have a key for a table which is the unique identifier for a given customer it has to be unique so that's a unique key constraint entity Integrity constraints mean that a key a primary key for instance for a customer cannot be null you cannot have null primary keys we've already talked about referential integrity constraints and then we talk about column value constraints also known as check constraints so what are those Brian but the idea is that you can say sales cannot be for instance less than zero in fact probably should always be greater than zero a date on a sale must be filled in you can't have a null value so null check constraints are one you could say values are a certain thing there's all these types of things that are meant to kind of keep a sane set of values in the tables supporting this whole idea of

6:55 transactions are transaction logs these are external files that are maintained when the transactions are occurring so as you do inserts there's this little transcription taking place in the database to say oh I see you're adding a customer I see you're adding a sale I see you're doing this and making these changes and it's keeping track of all these changes so that when you say oh wait something went wrong it can use the transaction log to reverse the data back to the way it was before you started when you say commit it locks it down down and says okay that's the way the data is and the transaction log record that that's the current state of data this was committed so transaction logs are really important for recoverability and they also allow us to know what happened to the data typically in relational databases now this is a self-contained world right the

7:40 environment of relational databases that you can only go through the database server whether it's Oracle or SQL Server software to do anything these are not external files like in a data Lake these are all self-contained in the database and it must manage them periodically to make sure we don't lose our data if something goes wrong and the server crashes or something dbas will take database backups and typically this is like nightly we'll do a nightly database backup and then during the day all the transactions happening will be logged in the transaction log so lo and behold something happens in the system crashes and we lost all the data well you would go and grab the backup last backup and it might be from last night 8 pm and then you take the transaction logs of everything that happened since then to the latest transaction and you apply those transactions actions for it to restore the backup and start applying

8:25 the transactions until you get to the most current state you can get to which hopefully is pretty good and you haven't lost much data these are all parts of resilience and recoverability that SQL Server databases have been doing for a long time and relational databases also include a lot of security because again you have to go through this sort of veneer wrapper around it to get at the data so it's going to say who are you and do you have a password and what permissions do you have in the database so they have a lot of security around them and they typically also support triggers additionally to triggers that can be stored procedures and functions which I'm not really going to get too much into because it's not critical to a database to have that but it is a nice feature you can store essentially programs in the database written in the SQL language but triggers get back to sort of this data maintenance kind of thing for instance way back in the old

9:12 days triggers would be used to enforce referential Integrity somebody would try to insert a row into the sales table and you would have a trigger and a trigger is something you write a piece of code that should execute when an action is performed on a table so you try to insert into sales you say well if there's an insert on the sales table before you do that check that customer key coming in is it on the customer table yes okay let it go through no don't let this transaction happen don't let them do the insert and there's on updates and on inserts and after and all these different types of controls are on the trigger I'm not a huge fan of triggers I haven't used them in a long time but there are use cases where they make sense they can also be used sometimes to do automatic logging you insert into the sales table and you could write to a log saying somebody inserted a row and before we get into

9:57 the data lake house and what it is I need to talk about the two primary types of workloads that relational databases support on the left we talk about the transaction or online transactional process in oltp database workload and on the right we're going to talk about the data warehouse which is probably what we're going to focus on right but we've got these two potential types of workloads and both of these coexist and have coexisted in the relational database world for decades but the bread and butter of relational databases really has been on the left the transactional processing systems these are databases that store your mission critical data in other words you've got your applications running in this during the database typically in a relational database this could be your sales system Financial record systems like accounting

10:44 and general ledger and accounts payable banking transactions all kinds of things that are absolutely essential for the daily operations of your business data warehouse purpose is reporting and it's decision making and planning that's what it's kind of involved around what should we be doing what products should we sell how can we do a better job transactional systems are focusing primarily on the data maintenance and doing it really well so that's why you saw that thing about transactions earlier insert update and deletes key they have to be fast they have to be efficient and they have to avoid a lot of locking because many different people could be trying to do these kinds of operations in different places all over the system and nowadays it could be a website that's distributed all over the world people are trying to do updates and inserts so it's an important thing but remember these are

11:30 sort of small sets of data it's inserting a sales row and a customer row and things like that not hundreds of thousands of rows from a given single person data warehouse it's not about maintaining the data typically there's some sort of a batch window maybe once a night ETL kicks off and it does all the loading and merging and crushing up the data to where it needs to be what's really important is when people run queries do power bi do reporting they get quick responses in the querying which is involved in sorting and aggregating not looking at individual rows so again a very different Focus here in transactional systems the thing that is really crucial because these are like if this is down your business is dying and you have to solve this problem quickly so it's crucial it needs to be

12:15 reliable it needs to be secure it needs to be resilient and very fault tolerant you'll see lots of architectures where something fails over the database system fails and it triggers a failover to a whole nother database system and that may even have another failover so if that goes down it goes to a third it's a lot of things going on it has to be recoverable we saw the whole backup recovery thing when we can restore the database if you lose it you're in deep trouble whereas on the data warehouse we're doing queries we're doing lots of aggregation also the originating source of data is the transactional systems which typically the data from there is going to be pulled out and put into our data warehouses so the transactional systems are really our system of record they're the ones that are going to ultimately say what's right and what

13:02 isn't so they have to be accurate the data warehouse has to make sure they pull all that into the data warehouse and get it right also means that data warehouses have a lot of integration they pull data from many different sources and consolidate it so it can be used for reporting and again as I mentioned aggregation of large data sets and finally transactional oltp's databases use a modeling technique called entity relationship modeling and they apply the laws of normalization remember that in your interview so these ERM models allow you to eliminate data redundancy which is the enemy of transactional databases however the data warehouse says no way you want redundancy we're going to use dimensional modeling we don't care if we

13:48 have 50 copies of the same customer all we care about is that the queries run fast so a very different orientation now why do I care about this Brian why is this so important because when I talk about bowering Concepts and features that are in relational databases I want you to remember that the data lake house is focused on emulating the data warehouse functionality in fact the data lake house name comes from taking data Lake data warehouse and merging those words together to get data lake house very clever all this is good Brian but what's the big deal why is it so hard to just add those kinds of features to a data Lake seems pretty easy right hmm think about it for a minute and this is important because as you run into trying to build your own data lake houses and

14:34 things you might say hmm why is this so challenging and how can I get around it I'll tell you why because in the old relational database world you got a single box everything's local very simple you try to insert a row and you say is that a unique row it can very quickly check against the table and say yep that's unique doesn't exist yet or whatever that's unique row this key doesn't exist referential Integrity not a problem I go to insert a sale row takes the customer key says is that on the customer table it's all very local very close no network traffic really it's all right there very efficient all on one machine simple architecture now when you look at a scaled out data platform whether it's databricks a snowflake or synapse it's very different world you've got many different nodes running many different machines they're actually separate they can be even be separated by some Network barriers this

15:21 is a lot of overhead and this could be like 10 000 nodes in a cluster and bear in mind these are actually files right it's not like the relational database World in which everything's wrappered in a single monolithic interface in a service that wraps it all up these are actually separate storage files in the data lake house it based it on parquet which it enhanced and called Delta format but they're just flat files so this adds a lot of complexity because you say okay is this a unique key value it's going to have to look at all the cluster nodes to find that out if you said is this customer key on the customer table it's going to have to do a shuffle to co-locate the data so we can find out if that customer key is there or not these are very expensive operations and this non-efficient use of a scaled out platform but we need that

16:08 functionality so we're kind of in a quandary and as mentioned that the database is external files so even though we're trying to emulate this sort of relational database functionality it has some key differences and that's a big one it's just a bunch of files really somebody could just take a file and delete it and that's part of your database so let's step back and let's say how well has the data Lakehouse implemented that old relational database data warehouse functionality like where are they at based on our previous diagram well spark has had at least the query level select type of support for SQL since 1.0 it's been a good had that for a while and it can do that on top of flat files in fact the whole data Lake thing I talked about earlier I should have mentioned basically that was schema on read you throw a file out there Define an SQL schema over it which we

16:55 call Hive and then you can use SQL to query and all that stuff and that's been around a long time bear in mind that data lake house wasn't worried about maintaining data it was just querying data that was dumped out there now we're talking about maintaining it and the good starting point is we have the SQL language to start with transactions have been implemented that's huge this is massive and this is when people started talking about Delta Lake what is Delta Lake beginning at Delta Lake was the addition of transactional support on a data Lake all right it's based on parquet new format called Delta but what does Delta really do it adds transaction logs we're going to talk about in a minute we get acid support right so we're getting that kind of robustness we can do a complete commit or rollback something that didn't exist only a few years ago now exists and we have transaction logging so a Delta file is

17:43 really just a parquet file but it's got transaction logging and we'll see more of that in future videos constraints first time I'm not using green I'm using an orange because although databricks has done a great job of implementing many types of constraints on the lake house it's still evolving there's still parts that aren't completely there I used a primary key generated the other day using something called the identity column them work great but then I found I had to kind of do a hack to get what the identity column value was so I could use it as a foreign key in another table which is a very common thing so I found it wasn't completely there in terms of its implementation but I was able to get it to work so it did what I needed so just be aware it's evolving and don't expect it to be just like a good old Oracle or SQL Server yeah the

18:30 referential Integrity constraints I'm really excited about because I honestly didn't think I'd see this in a scaled out platform but databricks says they got it there I have not tried it yet and it's in public preview which means it's pretty far along so take a look at that if you get a chance I'm going to be interested in looking at that that's a challenging thing to do on a scaled out platform now security honestly you're in a cloud platform with databricks you've got to deal with that cloud platform's way of securing things and bear in mind this is not like a relational database that it's all wrapped around in the service these are just files that are sitting out on blob or Azure data Lake storage or AWS store storage or Google storage so security there's some there's some Grant revoke type stuff you can do within the data Lake there's some grants revoke kind of things you can do within the data lake house but really you need

19:16 to really look at the cloud Architects you're working on and secure everything along those lines so I wouldn't worry much about that and as far as triggers go I don't think they're implemented I haven't really researched it yet last I checked they weren't but I'm okay without triggers too because they typically have caused more problems than they solved so I typically avoid them but I did want to mention them knowing databricks I wouldn't be surprised if they do Implement them for those people that really wanted database backups again this gets back to you kind of had to do database backups when you're in this monolithic specialized proprietary service called SQL Server Oracle why Brian it's still storing files under the covers but it doesn't tell you where they are and it doesn't let you manage them or copy them in fact if you tried to do anything to them you'd probably corrupt your entire database so you need to leave them alone and let the system

20:02 manage them because that's the case you need to use the special extraction program called the database backup to write a database backup file and then you have to use the database backup restore command to bring it back that's not the case with something like a data lake house these are flat files and you can do in slice and dice whatever you want and it's up to you to really manage them and be careful with what you do I'm sure that some functionality around governance for those is is definitely needed but the database backup idea probably is less relevant here High availability and all those things and recoverability definitely are and you're going to need to think about that and definitely for these files that you're putting out on your lake house figure out how you can make sure you have good recoverability copies of them maybe you

20:48 copy them to a few places in storage or move them to Archive storage so that if something happens and you lose them you can get them back typically cloud storage does a couple of copies maybe maybe more depending on the type of storage but I I think you should make sure you have the kind of reliability to make sure you don't lose data what is schema evolution is first in the old data warehousing world I've seen many times where they've gone along and somebody just throws a new column into a data set or a table that you're pulling and you aren't expecting it happens all the time now in the traditional world everything breaks and you're kind of left around panicking trying to solve the problem because nobody told you they were adding new tables or columns or making changes or dropping columns but databricks added a really interesting feature called schema Evolution and

21:33 schema Evolution when new columns come in or changes occur you can make a decision in your code in architecture to say how do you want to handle that and if you choose to you can allow your schema to evolve like adding new columns so that's a very cool feature and I'm sure you have to be careful how you use it but good to have because in this fast changing world with data lakes and all kinds of things being dumped into your your data storage you probably can't afford to just stop everything and do a project to figure out how to handle a new column I want to add a few things to what I've said I took a very specific Viewpoint it is based on my own background if I look at the history of things I come from that SQL relational background and most people that are moving to the data Lake do as well and database is aware of this however it's important to understand that the data

22:18 Lake ain't your old-fashioned Legacy data warehouse there's a lot of functionality in there that wasn't in the old world data warehouse for instance if we go to the right and look at data lake house we get now because of this new data lake house functionality metadata and governance so that's what the data lake house brings to us but we also are getting all kinds of support for different types of file structures the Big Data world right not just structured data that we used to but pictures and images and sound and video and on and on we need to be able to process that it's not an option anymore we need to be able to analyze videos and say what's happening in the video or transcribe it we need to look at sound we need to be able to handle all kinds of data so this is something you just can't do in the relational database world and we want to be able to support

23:03 machine learning and AI AI right this is a big part of where things are going again something that isn't traditionally supported in a data warehouse world now I also want to call your attention the fact that this is databricks view of their evolution right going from your traditional data warehouse to the data Lake and then realizing they lacked the governance and features needed to be a proper data warehouse with all that transactional support so we get to the data lake house so this is theirs there's a lot of other things they talk about and if you look at this link at the bottom here it says more that's a link that will take you to a databricks Blog from about two years ago where they talk about data lake house and what it does and all these features and it doesn't really take it from the sort of viewpoint I'm taking it they're just looking at more from an overall functionality that was lacking that they're trying to build into the lake

23:49 house so there's more in that than I've discussed here but I hope this gives you a sort of smattering of what's involved and what the lake house is about and by the way a link to these slides is available in the video description so we started out by talking about the data Lake to data Swap and the idea that why wow we can handle big data and everybody jumped on board and got so excited that they thought that the magic of this technology meant we didn't have to do any work anymore just throw it out there and analyze and that disillusionment was identified pretty quickly then we got to looking at as databricks did what the traditional data warehouse did and how do we get some of that functionality back that we need in the data Lake world and then we talked about the architectural differences between a relational database and a data Lake

24:34 which make applying some of these things particularly challenging but somehow they did most of it and they're doing more and so we talked about introducing the data lake house and all the things that have been implemented towards giving us that kind of data warehouse functionality that's it I want to thank you for watching please like share subscribe put comments in and questions until next time I'm Paul and thrower all in this together thank you