Unity Catalog Managed Tables: AI-Driven Optimization

0:00here again the problem we hear from customers is that you have to decide what tables to run on we are looking to build um as you said a return on investment dashboard for customers um thinking about next calendar year we have U made a lot of improvements and Innovations you know in the past few years but customers are still stuck on the earliest version of Delta there is

0:22no upgrade to the tables there's no adopting all these new features hey folks today we have a special guest we have of course Quinton but we have also Cindy she is a product manager at datab bra hi Cindy and can you please introduce yourself and tell us more

0:46about your role at data breaks yeah hey folks this is Cindy I'm a product manager here at data breaks on the Delta team um working on features such as predict authorization which we'll hear more about today and also liquid clustering very short and amazing like and like before we start like can you

1:09tell us a bit more about like what you're technically doing are like are you like meeting with customers getting feedback and then bandl to the data Engineers like or software Engineers what's technically your role yeah definitely um I would say uh all of the above you just mentioned uh we talk to the customers get their feedback understand what are the pinpoints and

1:31what we can build um to give them a better product experience at data breaks um we then you know bring back this requirements we think about what is the right product decisions to make Um this can be you know think about trade-offs between performance and cost and things like that we also prioritize what are the different requirements and decide what is the you know we're always wanting to build um the most beneficial

1:54features for the most customers um if we can and then obviously we work with the engineers we bring this requirements and we go back and forth in like designing um the product because as you know all products are technical um so we work with Engineers to go back and forth on that to make sure overall we can deliver the best product for our customers and then after the product is ready we will

2:17go through a preview process with the customers here again we can get more feedback to iterate on the product and eventually we will take the product to GA um here where you can see all the adoption and things like that we celebrate that for sure yeah that's cool yeah lot of lot of things so I think today we will uh discuss about about all the features we have behind the manage table so maybe we

2:40can start here like what's a manage table um like why should I care about manage table if I use the break and also what's the difference between external and manage tables because I think this I this questions always came up with with customers yeah yeah definitely um so manable is definitely our prefer table types um where let me share my screen

3:04right now uh yeah so here is a very quick summary of manage table and external tables as you can see um by the way thank you to UC open source now both tables are open and interruptable that is our goal with think being having an open inter interruptable storage format is very critical for open lake house and then

3:29they all exist currently in the open Dela format we are also looking to support Iceberg and they live in the storage that you own like customers own and control um and then you can see that because they're open to robal they are both accessible by external clients so the only difference between the two is that for manage tables our data intelligence platform would also

3:51optimize the data for you for Peak Performance and E of views so long story short this is our most performant and easy to use table types which you hear more about today and I'll tell you more why you should use manage tables so like if if I just create a table I don't do anything and create table in UC see that's going to be manag

4:13table and if I add uh using the location and a specify like a custom location then that's going to be external table correct correct uh manage tables is the default table type okay so super easy so everybody should be kind of using manage table by defaults yes yeah and what and you mention like there is something called predictive optimization can you tell us more like

4:37which features or what are like the functionalities that this feature is bringing definitely um I will give you a high level overview of predictive optimization so let me go to my slides again here we go

5:00so rization which is actually currently G8 at our data and I Summit earlier this year it really trying to intelligently determine what is the best schedule of optimizations to run to run for our customers manage tables so we have um an AI model that takes into account of you know customer query patterns and combine

5:22them with factors such as data layout what are the table properties you've set you know what is the performance characteristics in your queries to determine what is the most impactful optimizations to run and we're really trying to learn and adjust to your data usage patterns so the intelligence engines would also you know learn about your usage pattern over time

5:45to make sure your data is always stored in the most efficient layout um which we believe translates to cost savings and performance gains without any need at all for your manual interventions so how do we do that what we do is actually we go in and run the authorizations for you once we determine what is the most impactful commands to run and this is done by spinning up serus infrastructure

6:09it will also prioritize the table to run based on the return on investment so it's really trying to understand what tables would benefit the most from these operations again based on your usage patterns and balance it out with the cost of the operations and again by taking advantage of the data Independence which we have and our manage tables pred optimization

6:32is really what we can make your table super performant so as of today we did GA status um we support a few optimizations we have compaction this improves Corey performance by optimizing for your file sizes we also have liquid clustering so this incrementally clusters new data on your liquid cluster tables and we also have a vacuum which

6:54improve cost efficiency by deleting any unneeded files so I'll here and take any question yeah so so just to understand there is this mechanism for some computers that will run behind the scene to and to understand the query pattern and the table that needs to be optimized and then or like uh vacuum then then the

7:16system will donate on your behalf correct correct just and and just to be sure like the customer will be built only for the duration of the execution of the this operation like which is optimized for example example or the vacuum not once when the table are being scanned for example yeah so as of today our intelligence is free of charge and

7:39we as you said we only charge for the compute cost for running optimize and vacuum here the compute cost is actually comparable to the cost even if you run these commands by yourself yeah and and I think it's pretty cool because you can find those uh all the tables that were uh let's say that benefits from predictive

8:01optimization in system tables and you can track the cost um also like uh which operation did did run and like what number of files that were optimized and like number of files that were dropped if you run the vacuum definitely and any cost consideration like can I track how much uh is spending

8:23maybe during this optimization or in adding limits uh so there there's still limits on how many optimizations uh we can do uh but we actually do have a as as I said earlier returnal investment models so for example we've seen customers where they have 30,000 tables that they need optimizations on we actually queue it in a way such sh we optimize the most

8:47impactful table first um before the you know the long tail of the 30,000 tables okay and it's it's on by default as soon as you create a manage table then you will have the optimization happening in the backend that's a great question so as of today uh we do have to go on to the account setting and feature enablement to set uh predicted

9:10optimization to be at the Deep enabled option on the account setting but what we're really excited about is that in the next calendar year which is 2025 we're looking to enable predi optimization by default for all accounts so definitely stay tuned for that yeah that's great and can I so can I turn it on and off on specific dat let's say I have like a super specific workload and

9:32I I know what I'm doing I just want to do you know the liquid clustering myself can I still do that yes say that's definitely possible even though we recommend you know RI onization to do all this table mnet for you yeah and one question sis you mentioned that uh predict optimization runs only optimized which means it's let's

9:54say well tailored for liquid clustering because you no longer need to do the ordering is it right or is there any things other things to consider yeah so on the I guess table layout decisions we definitely recommend liquid clustering as you know we are heavily investing in that and we

10:18recommend customers to use Liquid clustering over Z ordering for a few benef for example you get less right amplifications with liquid clustering so for pre organization specifically we do support the optimized on liquid tables and as of now we don't support zorder on the zorder tables yeah that that makes sense and is

10:41there any other like benefits of using manage tables like automatic statistics or any other things that may be interesting for our customers yes definitely um happy to talk more about that yeah uh great question so even though predictive optimization has already G8 we're actually still

11:04constantly innovating and adding more and more functionalities for our clusters so one of that is auto stats so what I guess what is auto stat so the challenge that we see today is that customers have to run analyze to collect query statistics here again the problem we hear from customers is that you have to decide what tables to run on

11:27how frequently to do so and analyze is actually one of our least run commands because it's just so manually intense to keep up so here comes you know predi optimization will actually automatically collect query optimiz quy optimizing statistics during supported right operations and actually collecting the stats during the right times is more

11:50cost efficient and the time it takes to write the stats during the injection is actually 20% faster than running on analyze as a Standalone operation and last but not least we will also actually intelligently update the statistics to use the optimized quer Plans by running analyze in the background so here again we are using the intelligence to decide

12:13what is the best Cadence to run analy and we only do so when we realize the statistics are still and they requires a refresh so you know the feature in a nutshell your queries are just going to go faster with predict mization and you don't have to think about any of this analyze St collection at all the I have a question like if you

12:37there is a table property where you can specify either the columns that you want to uh have in the stats collection or like the first eight or nine columns when you do this does it also work well with predict optimization or predictive optimization will always run the capture statistics for the first 32 despite the

12:59properties you're going to put in the table yeah that's a great question so a clarifying point is that we actually have two types of Statistics so one is query statistics and the which traditionally customers have to run analyze to collect the other is Dela statistics which as of today we collect it um automatically on the first 32 columns so if a customer has Auto sets

13:23enabled they will get two things first um the core statistics are collected automatically again the predict optimization is intelligently deciding what to collect you know what are the queries that what are the columns being quered and therefore what stats are important across the entire cable and secondly on the data set side it actually becomes intelligent and it can collect the

13:48number is still 32 but we are no longer restricted to the first 32 columns Auto stats will be able to pick up the most important 32 columns for us to collect Delta stats across the entire table that's amazing because in the past it was a nightmare like you need to select the columns that were interesting or like numbers or maybe reorder them to make sure take all the first eight

14:11columns yeah that's and so correct me if I'm wrong but like the so the Delta stats it's being used for example if you do I don't know counts on the table then we can use Delta stats to give the answer like instantly we don't have to scan anything well we don't have to scan everything sorry and the query optimization that's the one you run with analyze that's for

14:34the query Optimizer so if you do joins for example they would know how to shuffle everything based on the I don't know correct correct yeah it's going to help the execution plan improve the aqe faster queries with auto statistics that's that's nice so so far the tables

14:59are automatically updated automatically optimized vacuumed and capturing statistics I think we're going to lose our job I think and and are there any other things like because you mentioned that also with the with the liquid clustering we're going to have like since we're

15:21going to capture the statistics you it can help you decide which column to Cluster I think this something also part of the road map yes yes definitely so another upcoming feature that we're super excited about is that we are building automatic liquid clustering key selections for our manage table customers so by simply running

15:44create table cluster by Auto uh predict authorization will come in and choose the clust columns on your behalf so the challenge of liquid clustering here with here today is that clusters still have to decide which clustering columns to use and as you said earlier you might also not have the statistics collected if it's not the first 32 columns but really what we're doing with pred

16:07authorizations to fully automate the entire process because we believe that we can do a really good job at choosing this clust columns based on your usage pattern so how does this work we will first collect query scan statistics so this includes your query predicates any joint filters you use in your queries to determine the tables that are most

16:29beneficial from adding or involving liquid clustering Keys Next up we will also model the workload to understand what are some um you know most eligible clustering columns we choose these candidates by looking at your quy workload and try to maximize the data skipping we can provide by setting these cion columns we then estimate the potential cost savings you will get for

16:52each column assuming that if that is being set as a clustering key so our savings calculations are based on you know the scan ques in the past 30 days and again here we're trying to optimize um in reducing the amount of data you have to read whenever you C your cable um to maximize your cost savings and last but not least once we decided the

17:14top you know clustering key candidate that can provide the maximum data skipping heuristics will actually also calculate if the benefits of applying a new set of cion keys would outweigh the cost of changing the keys itself because we know that CL the clust string operation itself could be very expensive and once we identify you know having this new set of cling colums will

17:37actually have a lot more benefit than the old set of uh than old CL columns or the existing data layout would actually then go ahead and change the collection columns so the key Point here is that it's really trying to make a positive return on investment decision for our customers because cling could be expensive so one very simple example here we've seen from customer workloads

18:00is that customers sometimes insert by a Tim stamp so the table naturally has an insertion order and however they also cor on time stand so that's is actually a good example where even though setting cion conss can give you a slightly more benefit by sliding it selecting time stamp it actually doesn't make too much of a difference between your insertion order compared to the CL columns so here

18:24we'll make intelligent decision to actually keep it as an partition table so again um the one key thing to take away here um the capability in the nutshell is that we will decide the most optimal data layout for you let it be liquid let it be non-liquid um but such that the predictive cost savings we have um from your data skiing will outweigh

18:46the cost of any operations we run and just to just to clarify so folks we with liquid clustering you no longer need to partition your tables but you can cluster them it's like logical partitioning which can help you to easily change the columns rather than having to rewrite all the underlying

19:08table so that's that's the purose of using cluster and with this predictive optimization you have this awesome feature of cluster by Auto and it will be I guess available for uh all cluster like allpurpose clusters and the SQL warehouses as well right so um to run the comment create

19:31table cluster by Auto support this in all sorts of runtime environments in but that the dbr version has to be 15.3 allward yeah yeah that's perfect any questions Quinton no I mean I love it but I've seen so many issues we know with I I

19:53remember once I've seen someone like making a mistake and they picked the wrong column to partition and they were partition by time stamp which was creating like one file per second um and it was like insanely file issues yeah so like that like it's going to remove all these issues and you don't have to you don't even have to know about it you just know turn on P R2 and boom you you're done

20:15you know don't have to think about your clustering don't have to think about your partitioning your files your FES stuff like that so H it's going to be great yeah we all love it and any other features you want to show us I mean so far we have like listed three and I'm still trying to keep

20:38up yeah definitely I think one thing I want to touch on is the automatic feature enablement I think you alluded to it earlier today so to clarify this is something outside of pric optimization but again something we want to build for us see manage tables so so the challenge we see across customers

21:02is that we have made a lot of improvements and Innovations you know in the past few years but customers are still stuck on the earliest version of Delta there is no upgrade to the tables there's no adopting of these new features so some the examples here as you can see on the slide is for example we have Ro level concurrency where we automatically resolve concurrent

21:24transactions we have column mapping which makes it super easy to change column names or drop any columns without actually having to go through a physical delete and we have Del vectors this is a one of a customer favors for those who have adopted it but we also see a lot of customers who are still not on delion vectors yet but this is really powerful because

21:47it's our storage optimization features which allows us to track any deleted rows in the metadata level without actually have to go in and physically deleting those rows so what that means that they actually can significantly speed up operations like merge update and delete with no performance changes on the r side of things yeah I remember I remember this

22:12this this was a big pain because you had to uh enable this like you have two options like to like create a cluster and add properties make sure that every table created with this cluster must will have this this those features or if they they are are enabled later you need to do it at every for every table which is a bit uh

22:35overwhelming now it can automatically be enabled without any manual tuning correct correct definitely so that is exactly the problem you know we see across like all like houses all table formats people are stuck on the earliest version and it's so difficult to pick up the latest features um but however now with automatic feature

22:57enablement on the UC manage tables all your tables would would just have to work out of the box with no work required from the customer side so all just means that we will automatically determine if your table is ready to get all these new features without you know without you having to do any work at all we will

23:19also like automatically detect if there's any reader comp compatibility issues from your reader thatat are like reading a tables and we will only UPG the features if you don't if you don't see any read compatibility issues yeah that that's I was about to ask you know how we're going to deal with the kind of uh older version of the reader and and you know if it's going to

23:41break everything but like it's been taken care of so you scan you like you you would scan everybody who's reading the table and if you see like an old version of the of the reader like not knowing about delion vctor then you don't turn it on is that how it's working yeah yes so taking delion vectors as an example I believe it's compatible from a

24:0412.2 reader version all works but if we detect that there is readers reading this table from an older version than 12.2 then we will know that we shouldn't be applying delis vectors however if we know that all the readers reading the table is 12.2 and Beyond then we know that this table is ready to be upgraded to have delusion vectors enabled yeah that's great it's going to be super

24:27smooth you know upgrade and and new feature enablement yeah y definitely yeah that's what about the variant data type AC variant dat data types is it is it coming to yes variant for everybody maybe we have a few coming soon features that we're also looking to automatically enable so one being

24:51variant which improves our performance in Pro processing unstructured data um it's a new data type that we recommend customers to use on unstructured data and once you use Varan we actually have a lot of core optimizations on the quy engine side to be able to quy the sort of nested structures a lot faster we

25:13also have automatic type widening so this widens data types automatically when there is a data type change without any data rewrites we hear a lot from like DT customers where the pipelines would break because out of a sudden your in become comes a long data type value but here with automatic type Bing that's are take all taken care care of and the goal here is we never have to break a

25:36pipeline again because of data type changes last last but not least our alltime favorite liquid clustering this is something that we want to enable enable by default as well with a combination of what we mentioned earlier the intelligence from predictor optimization so we're bringing all the Delta Delta 4.0 features to the to the

26:00manage tables automatically definitely definitely and what about the vacuum because I remember like many customers used to go to external tables because whenever they they used to drop a table in manage it was automatically

26:23the data was automatically deleted as well as the table compared to the external tables we only lose the metadata and the data is still in the location and then later I think we brought a feature called androp but what if you wanted to drop the table immediately yeah yeah that's a that's a great point you are bringing all the

26:46customer ping points to us and we are building we're building everything as you as you see the Ping points so really to give us a complete data life cycle management you know we have C per performance at scale with the default feature enablement have ai power optimizations to predict optimization then as you ask what happens if you you

27:08know files are deleted deleted because you're dropping a table so now we you see manage tables whenever a customer dropped their table data BRS will actually automatically delete the files for you and this is actually still within your control because as you said there is a seven days undrop period but we are also making that conf your wet so if 7 days is too long feel free to

27:30change it to 3 days and the exciting thing is there's the delete now capability here if you choose to delete now will actually immediately delete the cable from the UC side of things on your behalf and the benefit here is that customers will get storage cost savings because if you're certain that you don't

27:52need this table anymore there's no reason to keep it in the cloud storage I I think I've heard a rumor that we're going to have a dashboard uh that will be automatically added to where we can track like all the dbus you've saved and the storage you've saved and also like everything related to the cost you've saved since you started using predictive

28:15optimization and manage tabls is it a is it a real rumor yes yes it's a rumor for now but I want it to be a real rumber so um yeah St stay tuned we are looking to build um as you said a return on investment dashboard for customers um thinking about next calendar year um so as you said we were able to show you how many

28:38how much data with vacuum um what are the associated cost savings from these vacuum runs you know how how much were we able to speed up your quaries because of optimized Auto stats um liquid clustering things like that um to really show the benefit of predi optimization for our customers yeah and I think something important to

29:00mention that manag tables are also now accessible through other engine thanks to UC see open source can be duck TB or spark OS spark or any tool I think this is something important you need to keep in mind so if you're doing some migrations like or moving from a ten to another make sure to consider moving to

29:23manage tables and if you are using external tables not a big deal keep using the external one and all the new tables we're going to create make sure to move them to manage tables I think that was supposed to be your sentence Cindy all good all good just need to get a message across yeah and do you have any other words before we

29:45close no um as youf said move to manage tables that's what I you know one thing you take away is use manage table today that's the most powerful and easy to use table type editor break all right now that's amazing perfect go go manage table easy yeah yeah thank you cind thank you

30:10fol thank you for having me here bye