Mastering Delta Lake: Liquid Clustering, Time Travel & Vacuuming

0:05Hey, hello. Hello everybody and welcome to another episode. Um, today we're joined by Ysef Rainey, uh, solutions architect at Databricks and, uh, all together just an awesome guy. Um, today we're going to be talking about Delta Lake, uh, tips, tricks, and best practices. um and a lot of just um you know wins from you know the day in the life of um so I'm gonna kick it over to Yousef if you want to say hi to

0:27everybody and just you know give a good warm welcome. >> Hi uh I'm Yousef. I'm based in Paris and I'm a big big fan of Delta Lake. I've been using it for I think more than four years. So I've been noticing all the revolution inside Delta for quite some time. >> Awesome. Thanks for that dude. Um, so yeah, well let's um let's kick it off.

0:50Uh, we're gonna go through some slides today and then we'll take some questions from the audience. Um, all right, perfect. So Yousef, I don't know if you want to uh take control. Um, >> yeah, >> we'll go through. >> Awesome. So yeah, so for the for the whole entire session today, we're essentially talking about kind of it's like a almost like a b back to basics.

1:12So, we're talking about tips and tricks, but a lot of this is kind of based on what are the things that people would need to do or want to do, you know, within the first couple months of working with Delta Lake. And like one of the big things that people usually need to first understand is like how does Delta Lake actually laid out? And so, um, on this slide right now, we just have, you know, essentially kind of table layout 101. Um, so this is just a

1:34typical looking partitioned Delta Lake table. We've got partitions by event date. Uh these are from way back in the day from 2019. Um and all of our actual data files for our delta tables are just parquet. Then we have our transition our transaction log not our transition log our transaction log which is actually keeping track of uh what happens every

1:58time there's like a commit to the table. And so this is what gives us like our acid guarantees. This is what allows us to have multiple readers, multiple writers on the same exact table. It's essentially kind of coordinating things for us. Within the log, we have all of our metadata. We've got uh information about the table itself, what schema it has, what partitions it has, what other kind of table properties

2:22it has as well. And then we have essentially checkpoints. So, as we're writing transactions, periodically we're creating snapshots so that it's easier for us to be able to go back to a certain point in time andor to load the most current version of an individual table. So, you put it all together, you've got a delta table. Um, and now on to the next one.

2:45>> Yeah. And and just to go back to what Scott was mentioning, my my my advice would be go and open the uh the JSON files and see what's what you have inside and then also open the checkpoints which is a Parker file just to see how it looks like. It will give you an idea how delta tracks every single modification you're doing on your table like by modifying adding you're

3:09going to see the structure some some interesting information. So I think this is the basics but make sure to have a look at it. >> Wonderful. And I guess for for this one I wanted to touch down on the Delta Lake clones because I'm those are very awesome features. Um the first one is the shallow clone. I think it's an underestimated feature. And this one is

3:32super super important because um it it allows you to clone a table or just cloning just the metadata without having to copy the entire table. So let's suppose you have I don't know one terabyte table. You want to do some modifications on it but you don't want to copy the entire data. So what you can do you can just shallow clone it and then you can do any modification and you

3:55are sure that you won't be modifying the uh the system the source table. So what will happen like if you modify I don't know do updates where value equal to I don't know to Paris what will happen the shallow clone table uh only the modification will be added to the new table the existing one will remain the

4:17same and this is this is an awesome one because you can use it for short-term experiment without having to pay uh large fees for copying the data or uh moving moving the data and you can also You see in the shallow clone table like the new table that has been created only the modification which means if you update for example one row the only the

4:40unique row that will exist in this shallow clone table will be the row that you have modified and the existing one will be the same but just keep in mind that if you modify the source table the shall clone table will remain the same because when you do shall clone it's on the latest version for examp which means if you modify or update the source table, it's going to be version 8, which means on your side, it's going

5:04to be the same uh the same version. Just keep in mind that if you vacuum the table and remove, for example, get rid of the version you're talking, then the shallow clone table will no longer exist or be will be corrupted, >> which is a really good I think a good kind of segue into thinking more about like how long you want to retain, you know, a table for in general. Um, I

5:27think it's also a good, you know, your call out for like having this as like short-term experiments. It's also good if you're playing around with like new table properties or you want to test, you know, different ways of like, you know, organizing uh the table's metadata or, you know, it's like maybe I'm thinking about different, you know, I care a little bit more about, you know, some specific table properties or I want to try, you know, something new. It's

5:50always kind of nice to have like that almost like that playground to go test in. it's not affecting the actual table that could be you know producing you know it could be responsible for production data for example. So yeah shallow clones are awesome. Um but the next one I think is also really cool too. Yeah the next one are deep clones and deep clones are a bit different from from from shallow clones. So over here

6:13we gonna copy the metadata and the data as well. So this one is pretty straightforward. You can specify the version you're going to you're going to deep clone but the good thing is going to create a new uh new delta log which is good and when should I use for example deep clone let's see for example let's suppose you want to do some data archiving for example you're doing a lot

6:36of modifications on a specific table but the regulation specifies that you need to keep a snapshot every uh every month so what you can do instead of having to keep this copy like those all those different versions can just for example deep clone the latest version on I don't know at the end of each month or if you want to for example clone the table like copy the table to put it somewhere else

6:58in another catalog or schema you can do uh you can do clones and they are super super uh efficient and I think it's really a great way to leverage uh those two features shallow clone and delta lake clones and of course the APIs in the screenshot like see can use this with SQL and uh and python as well.

7:20>> Yeah. I think the other thing too that's interesting like outside of like data archiving and like um like just kind of data uh migration itself um for people who have to do anything with like legal hold uh legal hold is also another nice thing for deep cloning because you can take a snapshot of exactly what the table looks like at that point in time. go move it over into, you know, your long-term storage for like the seven years for legal hold and then continue to move on with your life. Um, removing

7:43data from that table as if you didn't actually have to, you know, keep track of anything else for legal hold. Um, we also do have a question from Alex. Um, this is coming back for like the shallow clone table. And so the question is like for a shallow clone table, the queries on the original data set, but it shows the affected rows in the shallow clone table. And it is it is correct. So like the it's essentially kind of like a pass through. Um, so you have your original

8:08table. So let's call it table A. Table A has all of its parquet for that specific version that you created the shallow clone from. The shallow clone will still be reading all those files unless they disappear. And that's like the whole kind of problem where because it's kind of meta data only changes, going back and reflecting off of what the table was, if that changes, then you you're kind of you're kind of stuck. Um, and so

8:31yeah, it is supposed to be used for short-term experiments as well. Yeah. And something I want to mention for like for the clones you can clone like you can deep clone the same table multiple times. For example, you're going to clone the latest version on for example the 1 of April then the same table in the second and 1 of June and so on. And then you can for example do describe history. You're going to see all the different versions you've you've you've

8:55deep cloned for for example for legal reason or for as as Scott mentioned. So it's can be clones can be a very very good options. >> Oh for sure. Yeah. Given that you do have that transaction log. All right. Mo yeah like moving on. Take it take it back over. >> Yeah. And I think this one also uh log retention duration. I think it's also a

9:17very very important feature because uh people tend to confuse log retention and another table property that we will be talking about it in in in a few minutes. So log retention by default it's fixed to an interval of 13 days 30 days which means like every single modification you're going to be doing on this delta table you're going to keep the logs

9:39there like for example that you updated the table you deleted the row you inserted a new columns you will be able to track uh those modification to track uh the uh logs for 30 days which means if you want to track for example to go back in time for 60 days you need to make sure to modify this uh this log retention duration and put it for

10:01example if you want to keep the logs for I don't know two months then you need to put here intervals 60 days this is very very important and it's the only way to help you perform this time uh time travel queries so this is but that's not the only requirement we still have another requirement that we're going to be uh checking a little bit late later

10:24>> I think it's a good kind of buried lead right there um for that. So yeah, do you uh do you want to do you want to move on? Well, we can circle back to this afterwards. So, >> and also this one, this deleted file retention duration is very very important. Why? Because we're going to front to back and this is something also you need to keep in mind. You need to run the vacuum because vacuum because uh

10:47the system does not run the vacuum on our behalf. So all the old snapshots you you created across time I don't know for two years or three years are not dropped. So you need to make sure that you run this vacuum to get to get rid of all those files. But to do so what you can do you can specify this deleted file retention. So here you're mentioning

11:10that you need to the minimal duration to keep those uh those old snapshot will be seven days. It doesn't mean that the data that was created one week ago will be dropped. No, no, I'm talking just about the snapshots and what you can by default it's set to interval one week but you can for example put two weeks and whenever you're going to run the vacuum automatically it's going to keep just

11:33the last two weeks of snapshot. So this is something uh this is something you need to keep in mind. This is in the case you don't specify the option when running the vacuum but since people does not do this before more often. So make sure to specify this uh inter this interval and make it u suitable for uh for your project and

11:56keep in mind that the vacuum is very important because at the end if you're you're you're storing your data on a data lake which is in an ADLS or an S3 bucket or GCS you're going to end up paying some money for nothing. So that's why I think you should leverage this uh the list file retention. >> I think yeah I think with that also kind

12:19of going back to like the log retention it it's an interesting thing if you want to go back to a specific point in time as well and you have a deleted file interval of one week and you have a log retention period of 30 days and you want to go back for 30 days and you've deleted files there's certain files that you're not going to be able to get back to. And a lot of times like this happens because you don't have deletion vectors

12:41on you're removing specific files from older paret and then that's every time you have an operation that is changing or mutating a specific point in time. It's going to create another parquet file and essentially like you know it's going to orphan the older paret file with the new you know it's no longer be linked to the new current like snapshot like what does the current table look

13:03like? It no longer references that file. And so in a lot of cases if people don't clean up like the older files you'll have like this entire you know this entire life of this table that no one knows about anymore. Um and so you know in order to you know balance what you have in like your metadata to balance what you have in the transaction log and then to balance what you have in terms of kind of like orphan files that are no

13:26longer connected the table like you have to play with both of these together. So it's another thing that can also be tested with deep cloning with shallow cloning. Um just testing the different like properties if you want to be able to go back and say I want to try different settings for time travel. Um because in that case you can continuously just kind of create new tables to you know to test with in a safe way which is fun.

13:51>> And for the next one uh it's more about the optimization side. So over here we have what we call the uh data skipping num index calls. This feature is let's say very very important because that's how delta will capture uh statistics that will help the enzyme when running the uh the query to

14:14improve the data skipping and by default it's fixed to the first 32 columns but you can for example if you have you don't need to for example suppose you have I don't know table with the 50 columns but the most important one are like I don't know the first three ones so you can just for example modify and put data skipping num index calls equal

14:36to three. So to make sure that you don't you don't like run some computes just to capture statistics for some column that you won't uh leverage or benefit from. But this feature is very very important. It's part of the when you run the optimize and you got to see that you have a bunch of other uh properties that are linked to this one in order to

14:58improve the efficiency of uh your queries. Yeah, I think I'm glad you brought up the whole entire the notion of like if you only need three, use three. Like you can also turn off like data skipping if you're just doing like appendon tables and you don't need to do anything special with it. You know, if you're just using like a snap, you know, a snapshot mechanism saying this is the table as is, we're never going to do

15:20anything with it. It's kind of like here's a sealed data set. Um but I think it is really important to be able to think more about like you know there is a cost associated with like 32 columns worth of statistics especially if that is you know that is being calculated on every single micro batch as well if you're doing things in stream and or every single kind of gigantic batch if you're doing you know kind of large

15:42batch style operations and so if you're never going to use it like there is that compute cost to that there's also storage cost to be able to you know keep track of the statistics over time which is important to know. Oh, so um this just actually came up. It's like what's the cost of collecting statistics on many on too many columns? Um it's just time, right? It's time optimize. So optimize will take more time because you

16:05need to capture like I don't know the mean, the max and so many other metrics. So more larger are the like bigger or no longer are the columns, longer the optimize uh will take. And if you're paying for the for the compute, then it's going to cost a little bit more. >> Yep. And I think one last thing but like before we move on from this one um the first 32 columns uh if you have a nested

16:29table will essentially do a depth first uh traversal and so you'll be follow going down versus like across uh within like the table itself which is something that's sort of a gotcha um depending on how people are creating their tables. So if people are, you know, enveloping specific data across a table with a, you know, kind of deep subnested columns, um, you might be surprised at what, uh,

16:51actual columns you're indexing versus what you think you want to be indexing as well, which is just an important thing to keep in mind. >> And I think we have another question. Um, does it affect uh, quering as well? Y >> of course I I mean if you won't be using those columns so it doesn't doesn't make

17:14any sense to run the optimize on those columns because you won't be benefiting from those statistics. But of course, if you if you if you know that you're gonna be I don't know running queries on the I don't know the last column on your table which is I don't know the column number 13 30 then yeah you it can be a good option but we have other optimizations technique that we will um show you in

17:36few that can help you reduce this number of columns that you will be leveraging. >> Yeah but I think I think as well for that like um so for the question of like does it affect querying it definitely affects querying as well. So there are certain cases where um if the entire query can be just pushed down to the statistics themselves, you don't have to open files before actually having all

18:00files that can be returned for an individual query. So you think about like there's like partition uh push down like predicate push down uh anything that can be pushed down to the columns like to the actual statistics themselves um is beneficial for speeding up your queries. And so for very large queries um if you don't have the stats columns then that will be a lot slower of an

18:22operation because you have to scan a lot more data. And so it it is very much something that will help um you know speed up those queries as well. Um, and yeah, yeah, the nested one are also are when it's nested, it also counts as an additional additional columns and you need to pay attention to

18:44to this. >> Yep, 100%. >> Awesome. >> And and the next one, yeah, I wanted to talk about this ordering uh like updating the column order. It's important because you can link it to the previous one. So, let's suppose you have uh like all the interesting columns that you're going to be using.

19:06are I don't know located in the um the u last columns of the specific table. So what you can do you can try to alter if you want of course alter the uh the order of those columns to make sure to push them at the beginning of the table. So you can for example mention hey capture the static six six only for the first eight columns and but that's not the only thing I I've seen some

19:29customers and users who change the order of columns just for the sake of readability because they put the date at the beginning because this is how they they query this table on their head like I want to for example filter by by country for example France and then by uh by city then by date so they order the columns make

19:51it helps them read them. But of course, you can also change the order of column to make sure that you put those the one that are interesting at the beginning. So when you mention the uh the num index call, you specify two or three, four or depending of course on uh the number of columns that are important to your uh queries. >> Yeah. And this is also I think uh for

20:13people who haven't used delta before and are coming just from like standard parquet. This is an operation that was like un unavailable like on just paret itself because you had like ordinal offsets for columns. And so if you were querying by specific table like columns and they reordered then your older queries would fail because they would no longer be in the same spot which is also not a thing that park allows you to do.

20:36And so in this case like with delta you have an overlay over that which is just saying like this human readable column name is associated with this index and then over time like this is where like column mapping comes into play and I think there's a we have another slide on column mapping as well. So kind of just that's where this comes in.

21:00And and the next one here this one is also linked to what we have mentioned. It's the data skipping stats calls. So this this functionality will uh overtake the uh the old one about the number of columns that you will mention to capture statistics. This one can help you get for example if don't want to order reorder the or the columns on your table

21:24what you can do you can just specify the columns where you want to capture statistics. So you will specify for example I want to capture column one, column three, column 10. So you're going to target those columns and to make sure that you are not paying additional cost to optimize non non-important tables and you don't want to change the order of tables because maybe it's used in another tool that can break in case you

21:47change the order of columns. So this one can simplify your life. It's a delta data skipping stats calls and you can mention the columns and it will help you uh optimize the query performance in a very simple and efficient way and it's mainly used for uh wide tables because if it's small ones you can use the um the number of column but this one can be

22:10very very interesting. Yeah. And I think the other interesting thing too here is that so given that like the given that the columns also support like nested column like dot notation, if you want to be able to specifically like index on sub columns of a nest, you can do that as well. Um it really just depends on the style that you want to you know move forward with. Um and then just make sure

22:33that you don't break that when you if you're changing the schema. Um, and so, you know, there's specific kind of, you know, in this case, like if those columns go away, it's just going to stop keeping statistics on it. Um, but just something that's kind of worth noting as well. Um, you think about like the data skipping stat columns being essentially like, you know, required, you know, required columns, which is awesome. Um,

22:56okay. Uh, let's see. We're gonna also, uh, we'll take a break for just a sec. Uh, we're gonna answer a couple different questions as well. Um, all right. So let's see. So yeah, the slides we're gonna slides will be available at the end. Um we can post them um after this is uh complete as well just in the comments. Um let's see.

23:20Okay. So does updating column order require rewriting the entire table in Delta? And this is this is just a mapping. >> Yeah, just the metadata behind the scenes. You're not rewriting the data, just updating the logs. >> Yep. Yeah. So, it's just Yeah, metadata only is a great way to reduce like the overhead of a lot. Um, awesome. Good

23:45question. Thank you. Um, awesome. >> I think we have another question. uh could you elaborate a little bit more on move such columns to pos to positions beyond the index specified by this property? So if we go back to the previous slide.

24:07So what you can do you can uh for example let's suppose you have uh I don't know your table is uh 50 columns. You have 50 columns in this specific table. If you run the optimize by default it's going to capture the statistics for the first 32 columns. But what you can do you can move the important columns to the uh to the

24:29left to the left and then you can just for example if you move five columns and those are the important one you can just say okay capture statistics for the first five columns and that's how you can re like reorder them and you can use for example as I mentioned alter table alter column and put this one first then this one second but of course it's going to take some time because you need to do

24:51it for every single column or you and replace multiple columns at the same time. So that's what that's what we uh we mentioned by reordering the uh the columns. >> Yep. And I think I think the other interesting thing too there would be so if if you've already captured like colmer statistics and you want to go back again in the future like they do

25:13have like optimize full as an option. So if there is if there's been large changes made to the column mapping and you want to now go grab stats for the hill table, you can just go and um basically reoptimize. There's also the analyze uh functionality as well. Um and I know we don't have a slide for that as well, but you take a look at just Delta Lake analyze uh chat GBT will show you

25:35exactly what to do. >> Yeah, the analyze I've tested before. It's super interesting because you can try to you can uh run um describe extended or describe details on a specific column. You will see that you don't have any statistics but once you run the analyze you're going to see for example for a specific date the mean the max the number of uh the number of uh types and stuff and this analyze will

25:59help the spark execution plan to be more efficient. So the skipping will help like avoid having to read all those files and the analyze help the data skipping the spark execution plan. So that's why the analyze can also uh very uh very important to use. And the next one I think this this

26:21feature is very very important. It was introduced I think one year ago. Um deletion vectors and basically it's introducing the merge on the merge on read uh feature. So basically as you know a park is immutable which means whenever for example you I don't know you modify anything it's going to create another file and if you are doing like a

26:45lot of I don't know merge like insert and update insert and update you're going to end up spending a lot of time just rewriting maybe the same file multiple multiple times. So deletion vectors works with a let's say smarter way because it introduce a bit map marking the files that need to be deleted. It doesn't do it immediately

27:09but it waits a little bit later when it's required which means rather than operating like I don't know recreating the files four five times it can only be done once because it's marked and don't need to re to re uh uh rewrite those files. And this feature is very very important in case you're doing as I mentioned a lot of frequent delete, update and merge because you're gonna

27:32significantly reduce the uh the uh the uh execution of those specific uh those specific workloads. So you need to make sure to enable uh deletion uh vectors. Yeah, this also this also saves a lot of uh so aside from like aside from like the other benefits as well like if if

27:54you're costconscious as well. Um you could test both of this um but without deletion vectors the number like that whole number of files like there's just a lot more that needs to be vacuumed and cleaned up after the fact as well because you're going to be orphaning a lot of individual files especially if like there's no like you know no zordering or any other kind of clustering involved uh with the table

28:16itself. a lot of like the point deletes end up being extremely expensive like if you're you know if I'm if I'm like removing like two or three columns per operation um it can significantly kind of like increase like the cost of you know just operating a table in general um hold on so all right there is also so there's another question um which is just u wasn't really a question it's

28:38more just like optimize does not trigger analyze and that's correct um so like the analyze functionality itself will go back and capture the statistics as part of writing u like and whenever we're doing like an add files um or any kind of operation like delete files anything else like that within each individual transaction the end of the

29:00transaction is where the statistics are being uh captured before commit occurs and so like optimize is what we want to run when we're going to like remove uh kind of orphan data or you know older like snapshots of the table over time. Um and then analyze is only if we have missed running any kind of analysis. So for example like if we had no you know

29:22if we weren't capturing any statistics and then we wanted to go and turn on statistic captures like we have to go back and you know either get statistics from the entire table. So go back to the earliest point in time and then go analyze our table andor say okay it's fine like from this point forward I want to start capturing stats. I can change my table property and then start capturing the stats from that point in

29:44time onward anytime a new batch is actually you know appended to the table u or any other operation is you know applied to the table itself. Um so there's a couple different ways of doing that. So uh thanks for uh thanks for that call out as well. Um and let's see we have another one um from uh let's see so we have let's say

30:06we have a streaming job which continuously keeps adding the data to the delta table as a pendon mode so if the optimize is happening automatically then which compute will it be using if it's a job cluster then it's going to be in the job cluster like for that optimized if it's I mean I'm guessing this is like the Databricks auto optimize uh functionality for this one >> um and that's going to like if autoop optimize is running, it's

30:29going to be doing compaction like every 10 uh jobs if it's running in streaming mode. Um so you'll see like a you'll see like an individual like uh job that runs. Um so I'm thinking about this from like a workflows perspective. Um there'll be essentially just a new job that's inserted into that flow that will be going to run like that compaction as well. Um it really depends on if you

30:51have like the right optimizations on or just kind of autooptimize like autoco compat on as well. Yeah, I think default one is 256 megabyte per file if I'm not wrong. >> Yeah. And so that could that can that can be modified uh with different settings as well um for that. But uh yeah and so the second part of the question too is like so if it is running on the job cluster then does it mean it

31:14will also increase the overall latency of the streaming job as it'll be running optimize after each micro back batch execution and and it will like it will it will reduce your ability to go like as fast as possible but on the you know kind of the you're you're paying it forward I guess is the best way to say it. So for anybody who's like downstream of that table, if they were to go back

31:38and have to either read, you know, suffer under like the small file problem or anything else like that, like depending on like how quickly the streaming the streaming job is actually appending new batches, like it's better to be able to, you know, solve the small file problem once and then have everybody downstream of your streaming job um actually, you know, benefit from that as well. Um so while it does

32:01increase the overall latency it's only at one point uh within the lineage >> and I think we have an interesting question about does the vacuum also remove the uh delta logs. So basically no the vacuum will only remove the uh the park files. It does not remove the uh the logs. So the logs are deleted

32:24automatically and and asynchronous I always struggle with this word asynchronous synchronously by the delta lake uh by by the delta lake engine and keep in mind that the logs you specify how long going to retain them. Keep in mind that as Scott mentioned at the beginning whenever you write 10 commits it creates a checkpoint

32:48and then the those files are deleted because think about every time you run a command you run a modification it's a new JSON JSON file and what if you run 10,000 files it's going to be a long number of number of JSON so it's done automatically and after each 10 it create checkpoint and then it delete the all JSON files automatically but the park are

33:11vac are not dropped. You need to run the vacuum. >> Awesome. I think we're I think we're good. Uh we can let's yeah we're good with questions for right now. So we can we can move forward. >> And yeah and the next one I think also um underestimated feature is the change

33:34data feed. So it's really a simple way to run the uh CDC on delta and it's just keep in mind that you enable this feature only and when only it's needed. Why? Because it will increase the size of the table because all the data it retains behind the scene. But then it's a very cool feature

33:57because um if you use if you don't enable this feature you're going to see the different version for each table. But you cannot find like what did they change in the meantime. But with this one you can just run the command select uh and you specify the version for example starting version one and version three and you're going to see like two different rows like the first row for

34:19example you marine v1 and then you're going to see you hes v2. So you know that okay what did they change is the last name and it it simplifies and you know that this is v2. So and you can also see the type of operation whether it's an update or insert or delete. So you can filter by specific version you can filter by specific operation to

34:42propagate those modifications to another table and it's really straightforward but just keep in mind do do not enable it everywhere but make sure to enable it only when it's uh it's needed. >> Yep 100%. Um, I think with a lot of these things, right, it's, you know, you opt in, you have the ability to opt into these specific like features, um, you

35:05know, when when they're necessary. Um, but for anybody who's had to do like CDC any other way, like the change data feed makes that a lot easier, you know, transactionally. Um, given that, you know, the upstream table only has to enable it for the downstream to be able to consume any of those changes. Um and so for a lot of like those change feeds, uh there's a lot like other there's a lot of other ways of kind of tracking

35:27changes that become a lot more problematic. And so given this is built directly into like the protocol and it's also built directly into like you know the Spark ecosystem for example like I can go and read the change feed and that makes it a lot easier um than having to just go read the table and then you know hope that somebody has you know enabled you know some sort of you know homespun

35:49tracking themselves. Like anytime something is at the protocol level it's a lot easier for everybody because it's standard and so um it's very nice in general. Um, okay. >> So, we have a question about liquid clustering. I think we're going to keep it till the end since we have a topic about it. >> Yep.

36:11>> Um, yeah. And this one column mapping I think when it was released I think I don't know two years ago maybe it was a it's a very interesting feature. Why? because it's avoid it helps you avoid uh like uh rewriting the tables and it can give you a simple

36:33example in the past for example if you want to rename a column or drop in a column you had to uh rewrite all the table and what if the table is I don't know one terabyte two terabyte big so it's going to cost you a lot of resources to do so but once you enable column mapping you can do this and those modification will be propagated

36:55at the metadata level which make them very easy to be modified and this feature is also very important because it's also I think super important uh to enable another table properties. I think uh I think uh deletion vectors uh require column mapping if I'm if I'm not wrong but it's it's uh it's a very

37:17important feature and that's not the only thing you can also use column mapping again it's not uh recommended but you can have um space or special characters in the name of the columns but again keep in like be cautious because if you have space in the middle of I don't know two name I don't know auto feature you know that you have a space but what if this space is at the

37:40end of the column. So be cautious when you're using special characters. Spark does not allow them. But with this column mapping, you'll be you will have this uh this opportunity. >> Yeah. And that was also uh for this one it was protocol version five where this came out. Um and so this was I think this was like delta 23 around then. Uh so it's been been around

38:03for a while and um I know we covered it earlier like in the talk as well but um back like like back in the day like with just you know native parquet it was a lot harder to do these things um just because you didn't have you know you didn't have a map over you know where the columns were. So there was no kind of interface between like the offset of the column like you know zero through n

38:26and the actual like human name that you would want to apply to that as well. Um, so it just made our lives a lot easier. Um, awesome. >> And this one was also I think super super uh interesting which is the allow columns default values. So basically over here you can create a delta table

38:49and you can specify for each column a default value which means whenever for example someone is doing some insert and is not I don't know filling the specific column or those columns then you're good to go. You don't need to fill them and you can also think about it in a way for example let's suppose you have a table and every time someone is doing an insert it

39:12should capture for example the current date. So what you can do you can just specify current date as default value for this specific column and then whenever someone is doing inserts it's automatically capture this time date this current date or time stamp and it also avoid for example having to provide um I don't know to have uh those empty empty columns that can maybe cause

39:34issues for some uh systems depending of course whether you're reading data from spark or any other any other enzyme. So for example, you can specify the age, I don't know, the median or uh default value for some car maker. So just for the sake of having this uh clean uh data on on your tables and I really advise

39:57you to use it uh because it can also help you clean your your your data sets. >> Okay. And this one I think this generated column think it was super super cool and I used it first with the uh when I was partitioning my tables. So basically what I what I what I did I remember I

40:20have uh a birth date and it was a time stamp. So what I did I computed three additional columns that was month of birth and year of birth and those columns were computed from birth date and then I was using those two columns to partition my my tables and you can you can have two options

40:42with those generated columns you have the generated always which means you're going to always generate this or what you can do you can generate by default only if this column is not filled with data. So this one can be uh can be can be very very interesting which and you can make sure to compute columns to without

41:05having to add uh like additional things behind the scenes. So I use it a lot and I think we have two features. It's generated columns and the next one are identity columns. But keep in mind that this feature is very efficient and you can also use it for to partition uh your uh your tables.

41:27I I think I think the other thing that's really interesting too um from like the last two slides is that like in a lot of cases there's a lot of things that the protocol itself can do like writing into a delta table can do automatically for you that removes a lot of like a lot of like the work that occurs in a lot of like uh you know ETL pipelines like it's like I am getting specific values I want

41:49to expand on those values to create like these subset columns um if you think about that being like a typical you know, day in the life of a data engineer. Um, if you can automate that, then there's no need to ever be like, oh, you know, what's, you know, what's our data quality metrics for this? How often are we missing, you know, month of birth, year of birth, or anything else like that? It's like, well, if you have

42:11that time stamp, who cares? You can take that time stamp and you can go and you can, you know, automatically always have those values you need. And so, it really reduces a lot of that level of effort um for, you know, utilizing Delta in general. Exactly. And the next one tightly linked to the first one identity columns. And this one I find it pretty interesting

42:34because you can generate unique values. You can think about as I showed in the example I create like let's say primary keys. You can specify the like the range for example start with zero and increment by one by two or whatever you think you're thinking about. Just keep in mind that when you're using identity columns to create unique keys, it cannot

42:58uh be paralyzed, it's only a single node because for this specific one, it needs to keep the track of the values. But trust me, I use it with 30 30,000 values and it was created the unique values. But it can be a little bit slow because it cannot be paralyzed. That's because you need to make sure that those keys are uh are unique. But you can use it as

43:22a primary key and you're sure that the you you have generated unique values. Well, I think this is this is something that's like really interesting too because I think uh for a long time um this wasn't a feature and this was like a very people asked about this a lot right where it's like how do I how do I treat Delta Lake or how do I treat my Delta Lake table like I would a

43:45traditional kind of transactional database and in this case you now have that ability and you know the nice thing too is that like if you think about you know being able to call specifically you know what are my primary keys or what are these identity keys like surrogate keys etc being able to call those out specifically within like the metadata also helps people like attribute that to something that's you know will always be

44:07here it's always generated and it's something I can always refer to and so it's like a kind of stronger key right it's like I will always have this it's always required it's generated for me and so then in the future you can you know you can join easily off of those you know those rows themselves um and then the nice thing too going back to like the stats columns is that being able to keep track of like our minmax

44:31values across like our primary keys gives us the ability to basically skip those offsets as well. So that really helps with retrieval of specific kind of points uh where we have these identity columns as well. >> And last but last least we have uh liquid clustering. So it's also I think

44:53um it's a modern way to optimize your uh your data layout and I find it interesting because it can replace the traditional partitioning uh in case you're not sure of which column we're going to be using maybe for example now you partitioned by by date but you want to add later maybe country so you need you would need to rewrite the underlying

45:15data while with liquid clustering you can just alter table and add U and alter table cluster by and add two three four columns and I think also liquid clustering is very important because when you run the optimize the optimize going to be running will be only on the uh incremental data

45:38and this is super super efficient. You won't need to spend several time like waiting for the cluster to compute the optimize specifically on super super uh large table. You can um of course you can create like uh those um liquid liquid tables easily. You can alter an existing table and create

46:01liquid crusher. But keep in mind if it's partitioned table then you would need to use setas to create a new one. If your table is not partitioned, you don't need to rewrite the uh the underlying data and it's very efficient and the order of the clustering keys does not matter. But keep in mind that you won't be using five columns as a clustering

46:23keys. Keep it keep in mind that just like using partitioning plus the ordering you won't be partitioning by I don't know five columns. You always going to be using maybe three columns and the last one can be zorder. So those are the columns can that can be good uh when it comes to using uh to choosing which one can be a good option for the uh for the clustering.

46:47>> Yep. And then we also had um we have like two other sessions that we had done um before as well if people want to deep dive further into liquid clustering. uh we had like the beyond partitioning then we had the hybrid clustering session also um where he went into a lot more detail on like like the kind of the how's wise the do's the don'ts uh with using uh liquid clustering itself um so

47:09with that said we also have a ton more uh questions that are coming through um and so we yeah this is a perfect slide for that too um all right so let's go back down let's see where do we want to pop into so Hold on a sec. >> Okay. So the first one is very

47:31interesting. It's the injection time clustering. So for for for the injection time clustering uh basically what happened is that whenever you're like writing some specific data it this data is written on a specific time. So basically it's like that this

47:53table is clustered or optimized by default because it's in it's ingested in a specific time for example uh I don't know at 2 p.m. 3 p.m. 4 p.m. 5 p.m. It's like being let's say partitioned and spark in the past because of the some some uh because of some properties it didn't

48:15respect the insert the order of the insertion but now they make sure to keep the same order as as much as possible including I think when you have the merge operation as well but that's how it works so since the data is ingested at the specific time so it's like it's ordered and it can it's like having the natural al data skipping because if you specify okay filter the data from I

48:37don't know where the the hour is equal to four so you know that you don't need to scan all the table just go to the specific index so that's that's what we call this talk about this injection time clustering >> all right then there's also um yeah so we already answered the data skipping stats leverage and the spark uh joins

49:00and delta table merges uh we didn't talk about it as like a spark join in general. Um but anytime that you have the index column um because you can easily um prune what files need to be read, it does help with like those joins themselves. Um the same thing with the merges um as long as you have that data in the actual like in the delta table

49:22stats. Um that speeds up that whole entire process. Um so think of it as it's kind of it's a lighter weight like it's a lighter weight index. Um, but it still will help in the same way um that you would, you know, typically have an index column that you're joining off of to speed up a join with like a traditional database as well. Um, all right. Um,

49:45let's see. Do we want to um there's the so will the identity columns and generated columns uh features where exactly can we find the functionality being applied in the table like um so for this one like for Rama like um if you if you just if you go and you create a brand new table um and you

50:08specify a column as generated always as and then you're you know you could you could apply pretty much any kind of condition you want in there. Um once you have the generated always as anytime you add any row of data um you could do this all like within like a notebook you could do it in like you know Jupyter notebook doesn't really matter where you're doing this just add a row of data

50:31um and you're only filling in the columns that you need to fill in uh everything else will magically just show up. Um so for example like a lot of times if you're reading data from CFKA um you have the time stamp um from the CFKA broker from when new data was written. Um if you're using that to create like a partition key like with the old like you know hive style partitioning um you can do generated always as a date to project that

50:54timestamp into a date value as well and then that column will always show up with your table as as well. Um essentially utilizing another known column that will always exist as well. Um so you don't have to explicitly do anything. U you can ignore it and pretend that that column doesn't even exist and it will always just show up for you.

51:19All right, let's see. Do we have Yeah, >> another one is there any way to prevent optimizers from breaking due to some concurrency errors? So basically, if your table is partitioned, you can make sure like to optimize only some specific partitions. And if you are using a liquid clustering you have the um uh the role level

51:42concurrency which is was introduced with liquid clustering which reduce also the possibility to face those uh concurrency uh errors. >> Yep. But I mean like in certain cases with that too like um we'd covered a little bit of this like uh when we were talking about like the hybrid clustering before there's there's like there's use cases like there's kind of edge and corner cases for a lot of things too. So

52:06um if the process is running and it's optimizing like the way that delta works is it's multi-verion concurrency control. So you'll be creating essentially you'll be doing an optimized flow on a specific version of the table. Um and if new add- like additional changes have been made in the next version then that next version uh being applied to the table won't actually take

52:29uh like that won't be applied into that current optimization flow but it will be the next time the optimize uh process is run um because it's incrementally being applied. And so like it's not going to stop new changes from occurring uh while the current optimized uh flow is going. um if that makes any sense.

52:52So it's yeah there's no like distributed lock for example. Let's see here.

53:19I don't know what some of the other questions are. Um let's see. So there's um there's a longer one. Um we have we've got a couple minutes left. Um so it's like I have a complex job pipeline in Databricks where tasks are dependent on the upstream tasks. I mean a task only runs after its preceding task completes

53:41successfully. Say we have workflow one task called task A. We also have workflow two and task B and the workflow depends on the successful completion of task A and workflow one. John is how can workflow 2 know or auto um you're talking about like I mean this is kind of like this is like a transitioning from like kind of like an airflow mindset to like workflow mindsets. Like

54:03if you wanted to have like if you want if you had workflow one which had like task A in it and then you have workflow two with task B and task A from workflow one is the dependent task in task B. Um the question I I would I would you know propose is can task B exist within that workflow because like if I have a workflow and that's like workflow A and

54:26task one is doing a lot of like preparation for that task B then I can also keep task B uh in memory within the same exeutor uh within that workflow. And so then the read is essentially free because I'd have two tasks being processed within like that workflow as long as like we don't run into memory constraints for anything else within that workflow. So you might actually

54:48speed up the workflow by having task B just be dependent on task A finishing within like a workflow without having to create any sort of like inter you know workflow dependencies uh which always add more complexity uh to the jobs themselves. Um but yeah, there are other like there's like you get different triggers like in the

55:10workflows as well. So if you're waiting for a specific file to exist, um there's different ways of triggering different jobs to run. Um but unless like there's a need for the additional complexity, um it'd always be easier just to, you know, tightly couple that back into that workflow. That's my two cents. I think we have a question but I I

55:32believe you might have answered it in the during the last session. In which scenario should we use auto liquid clustering and hybrid clustering to get optimal runs? Um and does hybrid clustering also the list vectors auto enabled or we should enable it enable it manually? Yeah. So this one the interesting thing

55:54so the hybrid clustering uh was more of like uh it was a it was a name it was a name and more for the thought experiment of like how we connect essentially hive style partitioning which is a type of clustering like which can lead to a type of clustering like zorder clustering or kind of traditional bin packing optimize um versus liquid clustering which is

56:16more efficient than say like a zorder like index on a table. And so for like the hybrid workflows like there's that's up essentially up to the user, right? So we talked about it more as like this kind of like endto-end lineage where we had specific tables that had essentially like um a close of books time period. So if we're waiting for changes for a certain amount of time and we want to be

56:39able to have like right isolation within specific partitions, then that makes a lot more sense for classic like partitioning like the hive style partitioning because the optimizations we can make are very specific to specific partitions. When we're ready to essentially have a table that's longived that can benefit from like liquid clustering, then at that point in time um that new table

57:01becomes essentially liquid clustered. So we're essentially just changing the you know the time to live for these different tables. Um and we have that uh up on YouTube as well just the hybrid clustering for that which goes into a lot more uh detail as well. Um but you would only add deletion vectors if you need them uh for those use cases as well. So in the case where you're overwriting a lot, you can have the

57:24deletion vectors uh on like the classic classically partitioned data. But if you're essentially moving into append only for your liquid clustered table, you might not actually need deletion vectors if you're not actually deleting anything. And so that's like just another thing to keep in mind. Like if you're just essentially deleting, you know, data that's older than say 120 days or 90 days, whatever, um then deletion vectors aren't going to help as

57:46much because you're not modifying individual paret files which is causing a lot of trouble. You're essentially just like truncating a table to a specific point in time. Um and so for a lot of it, it's just contextual um what what solution to to pick and go with. And I have I think we can answer one more question and we can close. I think

58:09it's related to uh liquid clustering again is traditional clustering more effective for robot workloads since liquid has a dynamic approach or or like since liquid uh and then we have dynamic is liquid also good option for streaming since it has a dynamic approach?

58:33I think I mean for for this one. Do you want me to answer it? Yousef, do you want me to I can do you want me to pick this one up? >> Uh yes. Yes, please. >> Okay, cool. No, I I was like all right. I'm like I I could do this. Um so I I think with this like it like when we talk about like kind of like a dynamic approach for anything else, right? It's like do we need to make a decision up

58:55front? Um is there a you know is there a reason for us to think things through beforehand? In a lot of cases like with old the old style which is not not bad necessarily but like with high style partitioning you had to really think through like what's the layout of my table going to look like? What does it look like now? What does it look like in six months? What does it look like in a year? Um because you know if it's a gigantic table any kind of changes that are made that need to relay out the

59:18entire table were costly. So with liquid clustering you don't have to cluster until like your you know your database table you know for delta gets to say you know 20 gigabytes 100 gigabytes then you can start thinking about like what are like the use cases in which we'd want to cluster differently so that like our queries are faster um and that's

59:40essentially just that's something that you can then incrementally uh apply to the table um which can help and that's where like the optimize full comes in. So if you haven't made a decision then you want to go and change like the uh cluster by columns and then you want to go back and reorganize like the like files for those clusters then that can be done like one one large run. Um you

60:02know caveat being if the table's gigantic that job might take a long time to run but that's it. I guess it's it's you know it's dynamic in that you have a choice that you can make later. Yeah. I guess that's it. >> Yeah,

60:24>> it was it was awesome to be uh here with you. Uh for those who posted questions on LinkedIn, I'll make sure to share with you the documentation and the useful links and make sure of course to download this awesome book written by Denny Scott Tristan and also Present uh Delta Lake definitive guide. You can download your free copy. Just scan the

60:46QR code or just write look it look it down in Google.