Stop Manual Tuning: Predictive Optimization Explained

0:04I remember I released the first video on this topic on November 2023 and I think it's the right moment to have a new video about it. But first, what's predictive optimization? Well, PO functions as the platform intelligence layer that comes on the top of your lakehouse which going to continuously optimize your data layout, reduce

0:26storage footprint and maintain precise file statistics required for efficient querying on Unity catalog manage tables which means it's supported for delta tables and Apache iceberg tables. And based on the observed usage pattern, predictive optimization will automatically determines when and how to run commands like optimize which going

0:48to compact small files and improve data localology. The vacuum which selects and reference file to control uh storage cost. The automatic liquid clustering which selects automatically or the optimum column or columns for a table. and then analyze which maintains accurate statistics for query planning and data skipping. Those commands are

1:11very important and po will remove the burden from so many developers. And trust me, this is a gamecher. In in addition, if you want to be able to track your uh all the operations that the system did on your behalf, you have a new system table. Let me show you. You have a new system table called in the storage called predictive optimization

1:35operations history. If you query this table, you will see all the operations that did uh run on using this this feature. Gonna find the catalog name, the schema name, the table name, the type of operation, compaction, astering ice, vacuum, and also something very important, the estimated DBU for this

1:57kind for this operation, which means everything is fully transparent to you. Databricks is working towards improving the uh observability. That's why they did release a feature called governance hub where we gonna have many interesting dashboards embedded in the platform to track the usage. For example, over here we can see the optimizations over time.

2:20You can see the summary of all the operation that did run on for example 3,000 operations on the vacuum. The cost was almost 100 USD and were able to vacuum almost 115 terabyte which with of course the estimated safe annually and the same thing we're going to have it for the compaction the clustering and so on. So make sure if

2:43you're interested by the governance hub to reach out to your database representative. And that's not everything. We have a new feature that's going to be unveiled very soon called the uh auto uh TTL. So this new feature, it's an optimization capability that's going to completely automate row deletion. So using this feature, you're going to be able to set up a time to

3:06live directly on a UC manage table. Let's suppose you're going to create a new table. So you will use the same commands create table and then you will need to add this command over here which is delete row number of days after and specify the name of the column. This is for a new table. For an existing table

3:28of course going to run the alter table the name of your table and then add delete rows number of days after a specific specify the column. And of course, if you want to get rid of this feature because no longer need it, you just need to alter table and then drop row deletion. So basically the syntax would be the alter table delete rows number of days after the name of the

3:52column. And this is going to be super super efficient for you in case you want to get rid of some specific rows after a specific time. And to keep and something very important to keep on the top of your mind, predictive optimization is enabled by default for all the workspaces in case it's disabled, you can enable it at the account level or you can also what to do like enable it

4:15at the catalog or the schema level. And of course when you enable something, you can also disable it either at the table level, at the catalog level or the database or schema level. And this is the commands alter catalog. and then disable or enable predictive optimization and so on. Thank you so much and see you soon. In lake

4:38>> rise data seas lake flow streams the flow iceberg and delta lake tables the glow unity catalog governs lake base and strong jinny text SQL MOSA AND CHA PROLONG you quitting co-host light the fire dream scale higher and higher next drop