Delta Lake Optimization: Z-Ordering, MERGE Tuning & VACUUM

0:01hi and welcome back imanu we did the first session on the spark optimization and now immanu is back to work us through the Delta Lake optimization hey Yousef thanks a lot again for in inviting me for this uh second video in the series of optimization videos um so yeah I'm going to share my

0:25screen just give me one sec so as youf has said like we have already done one video that was dedicated to spark optimization and where we walked through this optimization guide and different sections uh regarding spark and today we are going to focus on Delta L optimization so let's uh Dive Right into so like as you can see in the guide we

0:47have different sections so let's start with uh Delta Lake uh so we start with a quick introduction of Delta Lake why it's like like why databases recommends to use uh Delta L as a format given the different you know features like asset trans transactions uh time travel all those things which are which can missing from different like older formats like park or orc um and then uh we have a

1:12section on how you can uh basically optimize the file layout basically Delta is just behind the scene it's parking uh files and on top of that we kind of add transaction logs to give you that assd transaction guarantee so we still need to make sure that our file layout is optim to have a you know very uh stable uh query performance over

1:34time so for that like we are explaining different techniques to bin pack uh smaller file files into bigger files using optimize command or using you know Auto optimize where uh you can just set up some knobs and uh you know uh data BR and Spark will automatically kind of compact your smaller file ubal files uh you know on on the go uh we also talk

1:56about Z ordering which is like a way of indexing your your data so that uh if you use uh particular columns uh many times or let's say more often inqueries uh these queries can run faster because your data will be physically sorted uh using those those columns using Z ordering and again like we explain uh which kind of columns you should be

2:18using for Z ordering like it should be more like high cardinality columns like a customer ID or order ID uh to use for Z ordering we have a section of partitioning obviously data Bri recommends like not to Partition which are smaller than 1 terabyte uh because in that case you can take advantage of uh you know ingestion time clustering again and we have a few other uh new technologies coming up like liquid

2:41clustering which probably we'll cover in the next version of this book uh but yeah if you have to partition again we are giving some guidelines like you should be choosing columns which are low cardinality columns like you know date or year rather than choosing uh columns like customer ID otherwise you will end up like with very small partitions with very small files and that's that's a problem for sure and if if you have to also kind of tweak the file size uh you can use this parameter and uh once you

3:04have set this parameter uh optimize or autoptimize uh all of these uh basically uh B competion Technologies or techniques will uh respect the file that you have the size that you have set for for your table I think yeah uh youf any any questions or no I just wanted to add something that now I comment in um for

3:29the uh uh there is there is a Delta configuration you can add to specify because before you could only specify the number of columns starting from the left to the right that you want it to be uh optimized by default it's 32 now you can do you can add a data configuration and you can just spec without having to

3:51reorder The Columns you can just specify the columns that you want where you want to optimize to have to take place and now it's much much easier I think it's available starting from the dbr 14 14 I think so yes I forgot but it's it's it's one it's on one of specific DVRs I will make sure to add

4:14the link the description of the recording yeah so I think like just just to add some more colors on that what youf is saying like by default Delta collects statistics for first 32 columns uh and when you zorder with one of these columns like your data skipping becomes really effective because you have stats as well as your files are sorted uh by

4:36this column right uh but in many cases we don't need to have these 32 columns on which we need to collect stats because collecting stats is costly so in that case either like initially it was like not possible to choose particular columns now there is a new configuration where you can just specify the columns on which you want to collect statistics basically this data will collect stats for you automatically but you can choose

4:58those columns now right yeah awesome uh let's move on to Dela uh data caching so here we talk about uh Delta caching so Delta uh comes with this nice feature uh like the machines which have SSD drives on that uh these drives can be used to cach your

5:20Delta tables automatically like you don't have to do anything as long as you have you are using the VMS uh which are Delta accelerated or which have these SSD drives uh Delta cash automatically kind of uh caches your your your table so that whenever you query the same table again it's going to be much faster I've done some comparison here with spark cach because this question comes a lot like whether to use Delta cach

5:42whether to use spark cach and how to kind of pick and choose one of these two so again this section gives you some details about like when I should be going for spark cach like for example if you have a data frame which you are writing multiple times in multiple SS so this is where you can take advantage of spark cash otherwise in our experience I think Delta cach is much better in terms of performance so yeah I think we definitely recommend uh going for Delta

6:05cach over spark cash apart from this uh just one small uh Corner case Okay uh there's a sectional Delta merge yeah this is very very important section yes so merge is basically an upsert operation where you can insert and update and even delete uh on a Delta table right uh this operation can be

6:28costly because behind the scene it performs joints uh there will be at least one joint like let's say I have a subset of data I'm trying to merge into a Target data table so I need to join that subset with the target table to figure out uh like uh what are the files uh which will be impacted because they have common you know keys for example uh

6:50so that's a Join one and if I find some matches then I have to do a second join uh that will kind of reconciliate uh do the reconciliation between the data frame that I'm trying to merge with the target table right so obviously joint operations are costly and over time your table volume will grow and this this operation will be uh slower and slower now there there are some certain ways to

7:13kind of uh overcome uh this challenge first one would be file sizes okay so like by default when you run optimize it goes for 1 Gigabyte file size which works pretty well in many cases but for merges we don't recommend it because uh even if one of the row matches inside one parket file that means you might

7:35have to like you you will end up writing the whole file again because there's one row that is changing right so that's a problem uh so to overcome that we recommend to tune the file size to much smaller value like for example 64 MB 32 MB so that you have a lot less rewrite of data uh when there's a match during merge operation uh similarly uh we need

7:58to like we need to narrow down the search space search space right and how we can do that using data skipping partition pruning so in the merge condition you can have like you have this on Clause where you choose key uh like table one key one table two key2 to do the join in this uh condition specified filters like if you are uh you have partition table specify the partitions uh which are available in the

8:22subset of data that you're trying to merge that will give you a nice partition tuning on the target table similarly if your table is z ordered on some columns and uh pick the distinct values of these columns coming from this batch data frame that you're trying to merge and provide that distinct values as filters that that will give you a nice file pruning as well right uh similarly as I said merge is a join

8:45operation so we talked about in the previous video video about spark optimization we touched upon broadcast join so broadcast as much as possible increase the threshold uh put the broadcast keyword uh explicitly right before your batch data frame that is being merged to your to your you know your target table right and lastly like we have this low Shuffle merge which is a nice feature available on Databricks

9:09uh which is I think already uh turned on from 10.4 so yeah use this this feature as well because this tries to kind of like minimize the uh the shuffle happening behind the scene and obviously will give you more uh performance out of your M operations yeah and we can we can add we can add to actually but I think that it's it's part they are part of the new new release is

9:31the vector Del Vector yes absolutely yes and Photon as well so for those who don't know Photon photon is our I mean it's no longer new one but it's now it's part of the data R called uh it's the vectorized engine that has been Rewritten uh which make the workloads uh let's say run much faster and with the list is also let's say way way faster

9:57due to the new mechanic M because I don't know if you know but parket is immutable and whenever you modify so it's a new file that is which will be ritten same thing when you delete so now this operation is not done immediately but we T we mark them as deleted and the compaction or the rear right will be done later which tend to speed the

10:20process specifically if you have the merge and this merge you have updates and deletes absolutely yes and this is going to be in the refresh coming up in couple of months for sure okay and probably last section to quickly go through is uh purging data purging or data cleaning so this is where we talk about vacuum I think uh people who are using datab brakes they definitely know this command so vacuum

10:43is a way to uh Delete uh older parket files which are not part of your uh you know uh let's say defined uh like ver data version defined within the threshold uh so when you run this command these Parky files will be automatically deleted and it will be basically clear up a lot of storage for you so probably like one like couple of things to take like uh kind of uh pay

11:05attention when you are doing a vacums is basically the thresholds uh of retention by default it's 7 days and one thing that you have to to know is that you have a data retention like this file retention as well as Delta log retention okay because you have par files and you have uh Delta log like transaction logs so these are two things different things and by default their retention is

11:27different like uh for files we have seven days for logs we have 30 days uh in our experience it's better to align these values to the same value because in otherwise you would have like log uh going up to 30 days and data up to 7 days and that's a problem because uh like in theory you can time travel up to 30 days but you don't have the files for that so yeah make sure

11:49that you are probably putting these value to the same same same these two configuration to the same value for example 15 days or 30 days whatever you choose but yeah just just be uh kind of attentive of this this parameter other than that it's very simple command just uh like again it's a it's a it's a maintenance job you have to keep on running on on on daily basis or weekly basis uh so that you don't accumulate too much uh stale Parky

12:13files and I think that's it youf I don't think I wanted to add more on Delta uh Delta stuff no everything seems to be I mean that was very very clear and again they will find more details on the uh on the guide you wrote with pant thank you and see you the next session thank you