Optimize Lakehouse Cost and Performance With Intelligent Storage and Liquid Clustering

Summary

Liquid Clustering provides flexible multi-column co-localization for Delta tables that eliminates the scattered-row and small-file problems caused by date-based partitioning, scaling to petabytes of player-tracking and similar high-volume data.
Automatic Liquid Clustering is rolling out as the default for Unity Catalog managed tables, combined with Predictive Optimization to proactively recommend and apply table health actions without manual intervention.
Auto-TTL enables automated data lifecycle management, and co-clustered joins with layout-aware optimizations further accelerate queries for mixed workloads involving agents, analysts, and data engineers simultaneously.

Optimize Lakehouse Cost and Performance With Intelligent Storage and Liquid Clustering

Optimizing cost and performance in the lakehouse starts with how your data is stored and organized. In this video, we’ll show how Intelligent Storage and Liquid Clustering work together to automatically optimize data layout for your workloads. You’ll get a quick refresher on Liquid Clustering, then dive into what’s new, including Automatic Liquid Clustering on Unity Catalog managed tables, adaptive layouts that handle evolving query patterns, and metadata-driven optimizations that reduce unnecessary data scans. More importantly, we’ll cover best practices for when and how to rely on automation versus manual tuning. Learn how to choose the right table types, avoid common pitfalls, and take advantage of co-clustered joins and layout-aware optimizations to accelerate queries at scale. Whether you’re managing terabytes or petabytes, walk away with practical guidance to reduce costs and improve performance without constant intervention.
Talk By: Jeffrey Gong, Product Manager, Databricks ; Rahul Mahadev, Staff Software Engineer, Databricks ;

Chapters

FAQs

What is Liquid Clustering in Delta Lake?

Liquid Clustering is a flexible data layout mechanism for Delta tables that co-localizes rows based on one or more columns, eliminating the scattered-row and small-file problems that arise with date-based partitioning. It adapts to evolving query patterns without requiring manual repartitioning or schema changes.

What is Automatic Liquid Clustering?

Automatic Liquid Clustering applies Liquid Clustering by default on Unity Catalog managed tables, removing the need for teams to manually choose clustering keys or schedule OPTIMIZE jobs. Combined with Predictive Optimization, the platform proactively manages table health without constant manual intervention.

How does Predictive Optimization work on Databricks?

Predictive Optimization continuously monitors table usage patterns and proactively recommends and applies layout improvements before performance degrades, operating as a continuous feedback loop inside Unity Catalog. It works alongside Automatic Liquid Clustering to keep managed tables fast and cost-efficient at scale without requiring user-scheduled maintenance jobs.

How can I reduce storage costs with Delta table lifecycle management?

Auto-TTL provides automated data lifecycle management that removes stale data based on configurable retention policies, reducing unnecessary storage costs over time. Combined with co-clustered joins and layout-aware optimizations, teams can manage petabyte-scale tables without constant manual tuning or cleanup scripts.

Full transcript

[00:07] Hey everyone. Uh today we're talking about the most important topic uh one that every single person can learn from.
[00:20] layout of choice here at Data Bricks liquid clustering. Uh to introduce myself, I'm Jeffrey. I'm a product manager on the lakehouse storage team. I'm joined by Rahul. He's our staff software engineer and our incredible
[00:32] tech lead uh for data layouts here at data bricks. Uh a little bit about Rahul if you'll indulge me. Um Rahul has been here since 2019. Uh he was part of the founding team of Delta Lake. Uh and him and the team worked tirelessly uh day
[00:44] and night to make the big data experience feel seamless and he's overseen the development of liquid clustering from the ground up. Uh today liquid manages exabytes of your data. Uh so please go find this man and shake his
[00:56] hand after the talk. Uh, and my job is pretty easy. I just get to talk about all of our amazing features that we build here. So, before we get into it, every I think they say every great talk opens with a wall of legal text. Uh, so
[01:08] you guys know the gist by now. Uh, anything I say about the future is a forward-looking statement, yada yada yada. Lastly, super important, fill out the survey after the talk. You can do it in
[01:20] the app under my surveys. We would love to hear from you. Uh, did you like the talk? Did you not? What can we do better for next year? What should we keep on doing?
[01:32] Awesome. Uh, now that we have the housekeeping out of the way, why should you care about this talk? Well, the lakehouse is expanding faster than ever. Not only do you have more data, more pipelines, you also have more players
[01:46] entering the arena, agents, business users querying through natural language along with your data engineers and analysts. Uh despite this growth, two goals have always remained the same. How
[01:58] do I keep my queries fast? Uh and keep my costs down. Uh the good news is I'm going to show you today uh Rahul and I are going to show you today how to rely on the intelligence and automation of
[02:10] Unity catalog manage tables to scale your lakehouse. So if we do our jobs right, you'll leave this talk learning three things. how to lay out or physically model your tables, how to maintain your tables, and how to
[02:24] manage the life cycle of your tables. However, we actually need your help first, if you'll indulge me. Uh, as everyone knows, or hopefully knows, the World Cup is happening right now. Uh,
[02:36] and here at Data Bricks, uh, we actually have a fantasy league and it gets pretty competitive. Uh, everyone wants to draft the team that wins them, the fantasy world cup. To do that, we need to find undervalued players that our co-workers
[02:50] are overlooking in order to win. And because it's data bricks, uh we have a bit of a secret weapon. Uh and and spoiler alert, it's obviously data. Uh specifically, we have access to one
[03:03] pabyte of player data. Uh this data tracks where each player was on the pitch, what match they were playing in versus what team, when that match was played. Uh, and that data gets tracked
[03:15] 25 times per second going back six years. Don't ask us how we got our hands on this data set. Uh, remember it's a forward-looking statement. So, let's see how this works. I unfortunately some of the colors might
[03:27] be missing, but uh uh here we have four players on the pitch. One of them being Yoan Wissa, who uh scored a crazy header yesterday against Portugal to to tie the game, if anyone saw that. Uh you can see
[03:39] that we track the exact millisecond of the clock time during this game. Uh we track their X and Y position on the pitch. It's I think it's very hard to see the pitch but uh and other derived features like their speed. So now let's
[03:51] take the next frame. Uh we can see that whist's going in for the header. Uh and we see that all the players have moved slightly. So we track their updated positions in the table as new rows. Uh you can see in the yellow. It's pretty
[04:03] simple. So there is a problem though. This table was given to us. We inherited it. Uh and it's currently partitioned by match date. Yes, Gasp. Uh let's zoom in to uh
[04:17] one day in this table to see what's going on. Uh here we have one day uh one partition and we can see that it has three files, but we also see that because we're only partitioned by date, the same players data is scattered
[04:30] across all three files. Let's uh see a real world example of how this affects our fantasy league chances. So right now we're trying to evaluate whether or not we should draft uh
[04:43] Captain America. Uh this query specifically is trying to figure out if he fades later into the game. Uh so we do this by tracking the number of late sprints uh and filtering by the player which in this case is Chris Pissic uh
[04:55] for matches played after August 1st of last year for clock times in the last quarter of the game. Uh and this issue is the issue is that this query is pretty slow and we'll see why.
[05:11] So if we zoom in to each day that pollic played, we see that his rows are scattered across every single file in those date partitions. Uh and the frames and the clock times are also all jumbled up. Uh so you might be wondering, you
[05:23] know, why can't we add player and clock time as partition columns then? It's a valid question. Uh the answer is that would seriously over partition our table and it would result in billions and billions of tiny tiny files. Uh, and as
[05:36] a result, you can see this scouting query is quite slow. That's not the only scouting query that's slow. Uh, here's another query that we want to run. Uh, we're trying to track the fit and form profile for every
[05:48] player since January 1st of last year. We do this by counting the matches they've played in, the number of late sprints, and their average offball threat. Uh, let's see why this query also suffers.
[06:00] So, some days turn out just have less data than others. So in these partitions the files end up being uh way smaller than surrounding days and the issue is we pay a fixed price to open and read each of these files. Uh this seriously
[06:13] slows down our second scouting query. So to solve the problems that I described so far uh data bricks has an innovative data layout called liquid clustering. First it's fast. Uh in fact, write
[06:26] performance uh could actually be faster than partitioning and zordering. And it has read performance out of the box that outperforms even the most well-tuned partitioned and zordered table. It's incremental. Uh what that means is it
[06:38] only clusters data that needs to be clustered. Uh it leaves old old clustered data in place. Uh and it leads to lower right amplification and faster clustering times.
[06:52] It's also self-tuning and skew resistant. What that means is like we saw before with the data skew uh you can get slower queries from a partition that just doesn't happen to have a ton of data. Uh in contrast with liquid clustering you don't have to worry about
[07:04] the cardality of the column that you pick for clustering. Liquid will always try to produce a consistent and good file size. And last but not least it's got I mean a way cooler name than partitioning. Uh I
[07:16] mean come on. Obviously kidding. It's flexible. uh you can change the clustering columns at any time based on your needs uh without rewriting the table like you would have to do with partitioning. So I'm going to hand it
[07:28] over to Rahul for a bit to do an actual deep dive into the uh how liquid clustering works and how it's going to help us win our uh fantasy league. >> All right, thank you Jeff for the introduction. Uh so I'm I'm going to do
[07:41] a quick technical deep dive of liquid clustering. But before we go into the deep dive, let's try to answer the question, what makes an ideal data layout? There's actually two properties that we care
[07:53] about the most. The first one is file sizes, and we also care about data range. Let's see how file sizes impact query performance. So at data bricks, we actually did a lot of benchmarks and
[08:06] research with file sizes. And what we found with small files basically anything less than a megabyte is that you need to do more cloud requests because for each file you need to uh like have a separate request and even
[08:19] for like query performance there's a fixed cost with opening every file. So even at a query level you need to spend more more compute to read the same amount of data. And last but not the least, uh your table formats, whether
[08:33] it's iceberg or delta, need to deal with the vast amount of files and this can lead to metadata bloat when you have small files. On the other end of the spectrum, when
[08:45] you have extremely large files, uh let's say greater than 2 GB, you have the opposite problem, uh which is now your queries are harder to parallelize. And even at the right level, there's a
[08:57] single task writing out this big file. So, it's going to be slower and you no longer are able to do selective reads because there's no way to split this large file anymore.
[09:09] So, what we recommend as the ideal zone for file sizes is actually uh somewhere between 16 megabytes to 1 GB. And this can vary depending on your table size and workloads uh and whatnot, but this
[09:21] is the range that we recommend. uh and with this you actually get metadata that's constrained. You no longer have table format struggling to maintain the data and uh with with
[09:34] respect to selective performance. It's also like it balances between selective performance and parallelism uh at this file size. Now let's look at data ranges.
[09:46] Uh to actually understand data ranges, I have a small query in front of me. So I'm basically uh requesting all rows for a given date. In this case, give me all rows for Jan 3rd. Uh and this is the
[10:00] first data layout that I have. If you can see here, I have five files and all five of them sort of overlap with each other. What I mean by that is look at
[10:12] file one. uh it has data from Jan 1st to Jan 3rd and file two has data from Jan 2nd to Jan 5th. So this query now
[10:24] actually has to read all five files because basically the the data for Jan 3rd could be in any of these files or in fact all of those files. We don't know that. So we have to read all of them
[10:37] and you can extrapolate this at scale at a pabyte scale that that can be very expensive. you might have to read all the files in the table in the worst case. On the other hand, uh what we call as a
[10:50] non-over overlapping range is is this where you have five files. All of them just contain this data for a single date. So file one just contains Jan first, file two just contains Jan second
[11:02] and so on. Now the same query is super optimized and it has to just read a single file now. So it's going to be much faster. So to quickly summarize what we just
[11:14] learned, uh what we care about the most is optimized file sizes between 16 megs and 1 gig and uh you want the cor correlated data to be located together
[11:27] which results in non-over overlapping data ranges and most importantly the cost of maintaining this layout has to be low. uh that means incremental table maintenance and lower right
[11:39] amplification with table maintenance operations. Now let's actually look at liquid clustering and see if these properties hold true.
[11:51] So going back to the table which Jeff introduced uh a quick way is just to uh create the liquid table is just to create table cluster by and here we're using uh three three columns
[12:04] and uh basically I'm choosing match date player and clock millisecond. Uh and we'll see how how the data is going to look. So for the sake of simplicity I just picked two of the columns because there's no way for me to represent three
[12:16] dimensions. But if this was the table before it was a liquid table, you can see uh there's a lot of small files and there's a bunch like skew as well because uh there are some dates and some
[12:28] players that don't have enough data to be its own partition. So there was uh like some skew and some small files. Now with liquid we actually balance the
[12:40] file sizes and clustering. So we actually know what the target file size is beforehand. So we can figure out how many files need to be in the table. So that's the most important question which is given the table size and the target
[12:54] file size which we already know we can work backwards and see how many files this table needs to have. So in this case uh we know that and we can now go ahead and combine the closest files to
[13:07] form larger files that are equal to this target file size we just defined. So, and by closest I mean like if you have data from uh from June 11th and June 12th, they're going to be closer than
[13:19] June 11th and June 13th. So this is basically going to look something like this where uh as you can see for the top left box that I have player A, player B for June 11th and
[13:32] June 12th are going to be combined. And uh we see that similarly across across the layout and once uh we write them we end up with files that are all equal to our target
[13:45] file size that we set out with. So with this operation, we've sort of balanced file sizes and the data range. So this is a simple example with just like a single insert. So you may ask, but what
[13:57] happens when you have more challenging workloads, right? Uh so yeah, actually our player table also is going to be a table that's ingested to daily because with every World Cup game, you have new
[14:10] data coming in. Uh so let's say there's new data coming in for the 13th of June. Um so what happens is when when the new data comes in the most important
[14:22] question we ask is what part of the table needs to be rewritten and that's that's the the answer we have is data that's already well clustered. So in this case we're able to see that
[14:35] the data for June 11th and 12th is well clustered and we don't need to touch that data. So we don't have to rewrite anything there. And then going forward we can look at uh
[14:48] the June 13 there is some some uh data we can reccluster but not all. So for the top right box we actually see there's no benefit in rewriting that file because the data there is not going
[15:01] to change even if you rewrite rewrite rewrite it. But on the bottom bottom box we can see that there are small files that we can actually combine together to produce a larger larger file. And that's
[15:14] exactly what we do. So in this exercise, we only rewrote necessary data. But you could then argue that uh hey this was a rather simple ingestion
[15:26] because the data was already in order. What happens for backfills? Well, for backfills uh are also common it right because even for ours there are
[15:38] like some games that report their data late. So it might arrive four or 5 days later and sometimes uh like all the data could arrive at once four or five days later due to upstream issues. Right? So
[15:50] in this case let's say you have a big backfill that's writing to a a large part of the table and uh that's where we we introduce clustering on right. So here what we do is when you're writing
[16:03] the data itself we can go look at the table and see what it looks like and then try to map what the ingestion the the data and injection looks like and try to make it align with uh with the
[16:15] data already in the table. So uh what we do is at a row level we decide which file that where each row in the input has to go to. So I'll show you a quick example.
[16:28] So let's say uh for in in the data we have uh a few rows that match June 11th. So here you see like there's enough data for player B and player C. Uh and then
[16:42] on its own it's not enough for each player to be its own file. But we can combine them to produce like a target file size. Uh for let's say the 12th we already had enough data to to just write it as is.
[16:58] And similarly for the 13th we had like a lot of small data so we end up combining them and writing them out into a single file. So yeah this is what the result will look like. So we basically at the time
[17:11] of write itself we produced clustering and this leads to low write amplification because the next time your table maintenance operation like optimize runs it doesn't have to do much
[17:24] and even for your read queries they they get like good uh read performance right right out of the box. So all all the stuff that I explained has been in liquid for already for the
[17:37] last two years. So you you may ask what what can we do better and that's the question we asked as well and we actually looked at partitioning. There were like some cases where partitioning was doing really well. So some of you
[17:51] might be aware of of these. So what you get with partitioning is strong boundaries. So you get a good logical separation. So for this table if you look at date each date with the
[18:05] partition table is going to be like logically separate. So you get that really good separation. And what that leads to is let's say you want to delete data that's older than when the World Cup started. This is a very quick operation for a partition
[18:18] table. So you just have to look at the metadata, figure out what the dates are and what the partitions for the dates are and just delete it. And similarly, let's say you want to figure out who the fastest player was during the World Cup
[18:30] week one. Uh this is a very fast operation with the partition table as well because you just have to look at the metadata and the stats that we collected uh and don't have to actually even read any of uh any of the files
[18:44] and you also get an option with maintenance you can uh selectively choose which part of the table to maintain with uh APIs like optimize where.
[18:56] So what what we did with liquid was we took the core concept here where partitioning does really well for certain cases uh in this case like low cardality columns like date and what we
[19:08] do with starting with DB7.3 and above is we intelligently detect what these columns are and we first go and cluster completely on these columns similar to what partitioning would do and within
[19:21] each of these uh dates for this example we would go and cluster on the remaining columns. So essentially we get sort of best of both worlds for partitioning and liquid.
[19:34] So let's take an example. Let's take data that's let's say not clustered at all initially. Uh we first completely cluster by date. So as you can see here uh you have all
[19:46] the June 11ths together, all the June 12ths. There's no overlap between the dates at all. And now within each state we can go and cluster by the player.
[20:01] So you can see here within each state we have like neatly uh clustered by player and we've also maintained the file sizes here. We've not broken up the files uh into like very small files and you can
[20:13] see there are some cases where that each player has maybe a lot of data. uh you can imagine a player who ran a lot maybe they produce a lot of sensor data so we
[20:25] we can have multiple files for that player and uh if you have other clustering columns you can like split the multiple files based on those dimensions if needed and on the other hand uh let's say player A and player B
[20:38] were substitutes for for the game and there's not enough data to fill up a single file so we can actually combine the data here and not produce small files
[20:54] So what this means in reality is uh basically what we ran like a bunch of uh standard benchmarks with this optimization and we see 31% lower table maintenance time. So your optimize commands are 31%
[21:06] faster. Uh and even the right amplification associated with optimize is 26% lower and even queries improved by 22% with this. And also that uh we see like right side improvements for up
[21:20] to 40%. And like I said this has been around uh automatically enabled since TBR 17.3. Uh and we are extending the coverage greatly as we speak. And uh even right
[21:32] now we have more than 15,000 tables in the fleet using this feature. Uh and that that's around 40 pabytes under management already benefiting from this.
[21:44] Now I'll hand it back to Jeff to see how how all of these helps helps us win our fantasy league.
[21:56] >> Thanks Rahul. Yeah, let's take a look at our original scouting queries to see, you know, did we did we get faster? So let's go back to our original two. The first one was we were trying to figure out if uh Pissic faded late into the game by filtering on uh matches that he
[22:10] played after August 1st of last year, specifically in the final quarter of the game. If you remember from our original partition table, we had to read every single file uh in the dates that Pisic played uh because the data wasn't sorted
[22:23] by player or clock time. Now, our liquid cluster table, oh, it's hard to see, but our liquid cluster table is now clustered by date, player, and clock time. Uh, this means that we get great sorting on all three columns,
[22:36] and then within each day that Pistic played, we only have to read the single file that's relevant for us. Uh, this significantly speeds up our first scouting query. For our second scouting query, uh, we were trying to figure out the fitness
[22:49] and form profile for players after January 1st of last year. And if you remember, we had to read uh a lot of small files on our partition table because some date partitions just had less data. Maybe less matches were
[23:02] played. Uh we were suffering from a small file problem and we're paying a fixed cost to read every single one of those small files. Well, now because of our uh liquid cluster table, we work backwards from
[23:14] the correct target file size and we always get consistently good file sizes. Uh now that same query only has to read one file uh rather than many from before. And so the good news is these aren't
[23:26] even the only queries that are super speedy on liquid tables. For example, what if we wanted to find the maximum speed for a player uh for certain dates and matches or uh and the good news is
[23:38] well an optimization we have already implemented in liquid clustering allows this query to be largely metadata only. And so what does that mean? Uh it means we actually don't even have to go to the disk and open files to answer this
[23:50] query. We've benchmarked this on representative data warehousing benchmarks and found this to be about 20 times faster. Uh let's see how this works. So as you can see from before our liquid
[24:03] table neatly separates dates and players for most of the files where it makes sense. Uh and for these files you can actually calculate the partial aggregate of maximum speed uh by just reading the metadata. Uh there's no need to
[24:15] physically open the file at all. And then for any files that aren't neatly separated, like Rahul mentioned, maybe there was a sub uh which we do because we're trying to maintain a target file size, we can actually just read that file the normal way and compute the
[24:28] maximum speed. And combining these two approaches together, we get our final maximum speed. Uh and this is way faster than having to read all of the files every time. And so now I'm sure many of you are
[24:40] asking, well great, you know, not all queries involve just one table. What if I what if I wanted to join? Uh well, we also have uh along with our tracking table, we have an events table. Uh this stores what a player was doing at a
[24:53] certain frame. For example, were they shooting a goal? Were they trying to pass the ball? Were they going for a steal? Uh and it's also clustered by the exact same columns now as our tracking table. Date, player, and clock time. And
[25:05] we want to use this events table to figure out what a player was doing uh when they shot the ball by joining it with our tracking table. And because the two tables are relatively large, this falls out of
[25:18] eligibility for a broadcast join. Uh so previously we would have had to full shuffle the data which is super expensive. Um it's a super expensive process, really really slow. Uh we're excited to introduce co-cluster joins.
[25:30] This is a new feature in private preview. So do reach out to your account teams to try it out. Uh this significantly speeds up these kinds of joins specifically when the two tables have the same clustering columns. Uh the way this works is we map the two tables
[25:42] data layouts on top of each other. Uh this allows us to do a partial shuffle instead of a full shuffle. Uh and in our benchmarks, we actually found this to be 51% faster and shuffle 87% less data.
[25:55] So I'm sure everyone is now wondering, you know, liquid clustering sounds awesome. I'm going to go implement it across my entire data state. How but like how should I convert my partition tables to liquid? You know, maybe I tried out liquid two years ago. wasn't
[26:08] impressed. I went with partitioning, but now it seems a lot better. Uh, but I also want to do so without breaking my pipelines and jobs. Like, yeah, I'm not going to, you know, work over a weekend to do this migration.
[26:20] So, I'm excited to introduce a new generally available feature, uh, liquid conversion. Uh, fun fact, this was actually my intern project last summer. Uh, maybe I even had a customer call with one of you while building this feature. Um, liquid conversion easily
[26:34] converts a partition table to liquid with uh minimal downtime and rewrites. It's as simple as a oneline SQL command. In this case, alter table replace partition by with cluster by. And then you just specify what clustering columns
[26:46] you want. But also don't just listen to us. Uh here's what Marson uh a senior platform engineer at Bolt had to say about liquid conversion. Uh he said we ran the conversion to liquid from partitioning alongside live ingestion with zero
[27:00] downtime. Uh with this capability, liquid conversion provided us exactly the performance and reliability we needed at platform scale. Uh and if you're somehow still skeptical about liquid clustering, Rahul and I uh
[27:12] recently actually published a blog that debunks the eight most common uh data layout myths that keep uh people tied to partitioning in our experience. Uh I would encourage everyone to scan the QR code, read the blog if you have time.
[27:24] It's a great read. It also has three success stories uh of people converting to liquid clustering and seeing pretty massive benefits. So I know it's been a while, but going back to the very first question on our
[27:36] agenda, how should I lay out my table? And the answer is very simple. It's just use liquid clustering. Can you imagine how hard it was before when everyone had to manage their own files? We all do it. We do it for you now. So So the World
[27:49] Cup is now over. Uh we won our data bricks fantasy league. all of our co-workers uh their scouting queries are probably still running. Uh we drafted all the undervalued players before they even knew what hit them. Uh however, two
[28:01] things happened now that the World Cup is over. Uh we were fine maintaining this table by hand uh for an event as important as the World Cup, but now that it's over, nobody's really got time for all that.
[28:14] So the solution to this is predictive optimization or as we call it PO. Uh this continually and automatically optimizes your tables in the background. Essentially every single read and write
[28:26] on a managed table uh that data flows through Unity catalog. So we can see your layout. We can see the statistics on your table. We can see their query patterns. And we've trained an AI model to weigh benefit against cost and then automatically run the highest value
[28:39] optimizations on your table uh that on your tables that matter the most. Um and this is without you having to figure out the right schedule to run optimize or vacuum. Uh and as your data and queries evolve, PO will keep learning and adapt
[28:52] to your uh query pattern changes over time to make sure your tables stay optimized. So concretely, what PO does under the hood is it will uh understand when to run three different maintenance
[29:04] commands. The first being optimize. This keeps your data well clustered uh and it makes sure it does it incrementally. The second thing it'll do is vacuum. This deletes unreferenced data so you're not paying for unnecessary storage costs. And the last thing it'll do is analyze
[29:17] which keeps your statistics fresh. This makes sure uh this makes sure your joins are fast and also data skipping is effective. And and the second thing that happens is uh the way in which people query this
[29:30] table uh actually now changes. People are way more interested in teams and competitions over specific players. Now people want to figure out you know how's the Prem doing? How's the certain FA Cup doing?
[29:45] The solution to this is to rely on a feature called automatic liquid clustering. Uh this is a feature that's been generally available since uh last year. And the way that it works is very similar to the loop that we showed with predict optimization. We analyze the telemetry of your workload through Unity
[29:58] catalog. Uh we have a model that evaluates different clustering configurations and actually verifies which one has the highest pruning benefit on your table. Uh and then we apply those clustering columns as needed. So in our example, uh if we had
[30:10] clustered by auto, uh our table that was originally clustered by date, player, and clock time would now be clustered going forward by competition and team as people are more interested in those queries.
[30:22] And we're especially excited to announce that uh automatic liquid clustering is now rolling out by default on newly created managed tables. Uh before you had to specify like a cluster by auto clause. Uh, and we we've actually spent
[30:34] the past year validating the benefits of auto liquid on all of our customers tables. And we found it's actually pretty much just a no-brainer uh to turn this on by default. It's just saving everyone money and reducing TCO. Uh, especially if you have a lot of people,
[30:47] I'm sure everyone has experienced this, that never really bother to manually specify a data layout, whether it be partitioning or clustering. We also recognize that uh with great power comes great responsibility or in
[30:59] our case with great automation comes great need for observability. Uh we've heard your feedback that PO and auto liquid uh sometimes tend to be black boxes. Uh it's hard to tell when it runs a certain operation what effect it had
[31:12] or uh if it intelligently skipped an operation why it did so. Uh that observability and the reasons for skipping are actually going to be shown in the UI. uh and in the output of
[31:24] describe extended and at the bottom you'll see two visualizations that we're building. On the left this is an actual table's query patterns. Uh so we know it's pretty much impossible today to understand how exactly your downstream
[31:36] users are querying your table. Uh this view will show the dominant filters that people use uh and provides kind of a window into understanding how autoquid is making its decisions. And on on the right uh we show a visualization of your
[31:48] file size health per table. Uh this is also a real table that we converted to liquid clustering from partitioning in April and you can see that actually before it was almost 100% made up of small files um and was an seriously over
[32:02] partition table uh and then after we converted it the number of small files in table went down to zero. We also recognized that it may be easier said than done to rely on autoliquid uh if you are kind of used to manually picking your clustering columns by hand. Uh
[32:16] that's why we're also building a dry run capability for Autoli liquid. Uh this will basically just tell our model to at that instant evaluate the different clustering configurations for your current workload on this table and then spit out the optimal set of clustering
[32:28] keys. Uh and that'll be kind of ranked by pruning rate. So you'll actually see the evidence. Uh and it'll also tell you whether or not it matches your current data layout. Uh and we're also building proactive recommendations. So we can
[32:41] actually figure out in the background when you have a sub-optimal data layout and proactively surface to you that hey we actually found a a better clustering configuration that can save you plus x% pruning. And lastly a feature that's in beta
[32:53] right now is lakehouse indexing. Uh this helps speed up your super selective queries uh kind of like your point or needle in the haststack queries.
[33:05] So for the second question how should I maintain my table? The answer is use PO uh and auto liquid. Let us do the heavy lifting. And then lastly, we have a bunch of old data lying around. Uh you know, this
[33:18] table goes back six years, but who really cares how PISC was doing 5 years ago that probably has zero bearing on his performance now? So, we're excited to introduce another feature that is now generally available.
[33:31] This is called auto TTL or auto time to live. What it does is it provides automatic row deletion uh for your managed tables. Uh and it's as simple as using a oneline SQL command to define this expiration policy. In our case, we
[33:43] could maybe delete rows in our tracking table a year and a half after the match date. Let's see how this works. Under the hood, what it's doing is PO actually is automatically scheduling deletes,
[33:55] purges, and vacuums to first soft delete your rows and then hard delete uh off disk. uh because we've and this and the actually the best thing about this is because we've defined it on a clustering column uh match date we get metadata
[34:09] only deletes because our table is already separated by date uh we don't even have to go look at the file to uh mark that file as deleted um if that doesn't quite make sense go back to the metadata only slides it's very smart
[34:23] uh and also what's coming next in this in this space well we recognize that sometimes there are scenarios where you want to keep data lying around uh but you can't delete it uh and you also don't query it very often. Uh you may
[34:35] want to save storage cost on it. We're excited to announce we are building an autoarchchival feature uh to complement our life our current life cycle policies and we're also going to integrate with the broader ecosystem of govern tags uh to allow you to specify life cycle
[34:48] policies based on tags. Uh this will enable you to apply these kind of storage life cycle policies and govern them across your data state um scalably. So for the last question, how should I
[35:00] manage the life cycle of this table? The answer is use autotl and be on the lookout for uh new life cycle policies that we're coming out with. And and one last thing I'll leave you
[35:13] with. Uh you might be sitting in the crowd and thinking, well maybe the other data engineers in the room here have no idea how to choose good partition columns, but at least I always choose the perfect clustering or perfect partition columns every single time. Uh
[35:26] well, I'd encourage you to check again uh because we actually took the time to run the numbers on our fleet. Uh we found that actually 75% of partition tables suffer from over partitioning or small file problems. Pretty crazy.
[35:39] And if you won't listen to me, you can at least listen to Smokey Bear when he says only you can prevent partitioning. So awesome. Just to recap all the lessons we learned today, um from top to bottom, make sure to rely on autolid uh
[35:52] or just wait for it to roll out by default. Identify your partition tables or wait for our proactive recommendations and convert those tables from liquid to partition or sorry from partition to liquid with minimal downtime and rewrites. Uh use predictive
[36:06] optimization to continually maintain your tables. Uh here at data bricks we think of PO kind of like brushing your teeth. Uh it's really not that expensive to do, but it's very expensive if you don't. Uh and lastly, try out autotl. Uh
[36:20] this will easily handle your old data that you don't need to keep around anymore. Uh the thesis here really is that you know if Unity Catalog manages your tables, it makes table maintenance way way easier. Uh it makes your life easier and it helps you scale your
[36:32] lakehouse beyond 2026. Awesome. Yeah. And thanks everyone. Please remember to uh complete the surveys and then come to the stage, ask us any questions. I'll leave this

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.