Hands-Off Table Maintenance: Databricks Predictive Optimization Setup

0:00hello my name is Yousef and today I'm happy to introduce data breaks predictive optimizations this feature removes the need to manually manage maintenance operations for Delta tables on database once this feature is enabled data Bri identifies automatically the tables that would benefit from maintainance operations and run them for the user and

0:23of course this operation is only run when it's necessary and the purpose is to remove the burden associated with tracking and troubleshooting the performance and of course to avoid running unnecessary op maintenance operations so far it's managed two operations we have the vacuuming and the purpose of the vacuuming is to reduce storage cost by deleting data that no

0:46longer referenced by Delta tables and then the optimize and the purpose is to improve query by optimizing file sizes by compacting them and of course capturing uh statistics and in order to enable predictive optimizations you need to have some specific permissions so you need to be first of all account admin and then for each catalog and schema you

1:09need to be either the catalog owner or the schema owner and how would I enable this predictive optimization so first let's open the uh databas platform go to catalog click over here and then enable predictive optimizations and when it's

1:31once it's done then you can go and enable uh this predictive optimizations for a specific catalog or for a specific schema so over here what I did I use this SQL query alter catalog and my catalog is football UK and then I will enable predictive optimization and to make sure that it's enabled you can use

1:55the described catalog extended and you will see here predict Ive optimization is enabled and of course there is something interesting over here the predictive optimizations work with inheritance which means if I enable it at the catalog level it will be automatically activated for the schema and the table level so what you can do

2:18for example you can enable it at the catalog level to enable for all the schemas and then for example you can just disable it for one in case you have 10 schemas and only one where you don't want to enable this predictive optimization and how you can this you can just you know alter schema midseason for my case and disable predictive optimization and I can show you with describe schema extended the predictive

2:40optimization is disabled and I have another schema in this catalog called All Seasons I will run describe schema xn and then you will see predictive optimization is enabled and it shows that it has been inherited from catalog football UK and there is something I need to mention of course once I did tell you that uh you this predictive

3:03optimizations run two operations the optimize and the vacuuming and vacuum is very very you need to pay attention to this operation the retention uh window for the vacuum command is determined by the Delta deleted file retention duration which is a Delta table property which is set to seven days by default that's why

3:27in case you want to keep those files longer to make sure to modify or update uh those properties at a table level and you can just run this alter uh table midseason games clean and set table properties which is Delta table Delta Delta the deleted file retention duration equal to 20 days and now you're

3:51going to be sure that those uh snapshot will still be there for at least 20 days and that's not every everything of course I did mention that uh database will run those operations and you can track every single operation that did happen and how you can do this you just need to make sure to go to a catalog

4:13called uh in system tables and there is uh there is a catalog called system tables and then a schema called storage and then you can run a query and as you can see over here I did run a query and in order to capture all the acumed uh tables of course just list the first five ones so we can see the workspace

4:38the start time the uh the end time the name of the metast store the catalog name the schema the table name the operation whether it was successful or failure and the cost and you can see that this this vacuuming cost 0.2 DBS and then you can also have additional statistics like number of Fes deleted and amount of data deleted which means you can for example at the end of the

5:01year um uh sum all this data to see how many uh how many storage did you uh let's say uh save by running this by having this operations run for you and same thing will happen for the optimize so I did run the operation types equal to compaction you will have the same

5:23information like the start time the end time the metas name the exact same information the only that will change would be of course the operation metrics and over here again you will see the number of uh output files and the number of compacted files and other statistics like the size of this file and of course the uh usage uh

5:47quantity required to optimize uh this specific table and in case you don't have those those tables you can always run this uh this specific uh uh notebook that will go and enable system tables for you just make sure to be an account uh admin it will automatically get the metastore uh the

6:10metastore ID it will get a token for you and then it will run uh several uh API SC to enable the different schemas so you have storage you have building access operational data compute Marketplace lineage and and storage as I did uh mention thank you for watching this video