Skip to main content

Best Practices for Moving to Unity Catalog Managed Tables

Summary

  • Managed tables as default: Unity Catalog managed tables are the new standard, supporting read/write with automatic performance optimization and storage cost reduction without manual tuning.
  • Automatic optimization: Databricks manages table location and applies automatic statistics, Liquid Clustering, and predictive optimization—reducing operational overhead compared to external tables.
  • Future-proof architecture: Automatic feature upgrades mean managed tables evolve with the platform, ensuring organizations benefit from performance improvements without manual migrations.

Best Practices for Moving to Unity Catalog Managed Tables

Watch: Best Practices for Moving to Unity Catalog Managed Tables
Talk by Elizabeth Bowman, Product Manager, UC Managed Tables , Databricks ; Youssef Mrini, Solutions Architect, Databricks
Are you ready to unlock the full power of Unity Catalog managed tables? this video delivers actionable insights for transitioning to UC managed tables. Learn why managed tables are the default for performance and ease of use, and how automatic feature upgrades future-proof your architecture. Whether you manage thousands of tables or want to streamline operations, you’ll gain the tools and strategies to thrive in the era of intelligent data management. Join us and discover how easy it is to move to UC managed tables!
Here’s more to explore:
Production ready data pipelines for analytics and AI: https://www.databricks.com/solutions/data-engineering
The Big Book of Data Engineering: https://www.databricks.com/resources/ebook/big-book-data-engineering-2nd-edition
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

FAQs

What are the three types of tables in Unity Catalog?

Foreign tables (read-only from EMR, Snowflake), external tables (stored in S3/ADLS/GCS but requiring manual optimization), and managed tables (new standard with automatic optimization).

Why are managed tables better than external tables?

Managed tables support automatic performance optimization (statistics, Liquid Clustering), reduce storage costs, and require no manual tuning—Databricks handles location and optimization automatically.

What automatic features do managed tables include?

Automatic statistics, Liquid Clustering for optimal data organization, predictive optimization, and automatic feature upgrades ensure performance and cost efficiency without manual intervention.

How should organizations plan their migration to managed tables?

Teams should assess their current table inventory, plan phased migrations by workload, and leverage included tools for conversion from foreign or external tables to managed.

Full transcript

0:00 Introduction

Uh thank you for being all here. We going to talk about a very interesting subject manage tables. Uh I am a solution architect uh based in Paris and I've been working for the company for the past four years. And Hi everyone. I'm the product manager for Unity catalog manage tables. Before we start, we have a question for you like how many people already used manage tables? Just raise your hand. Okay, thank you. It was a cool session.
Bye. So today you will learn about the future of data management and the latest best practices and we'll dive into like how you see manage tables are the future of the platform and we're going to also talk about a magic command. Just remember this magic command and for today's agenda we will talk first about UC manage tables then how to convert from a foreign catalog or for like UC external tables to manage one convert from external from foreign table and of course how you're going to plan your journey because this is super important to make sure that you have a successful migration one.

0:44 Agenda

So let's start with UC manage tables but before that we will be talking about the types of tables in Unity catalog. The first one is are foreign. So those foreign catalog are the cataloges you're going to be bringing from other tools. It can be for example EMR, it can be snowflake or so on. So just keep in mind those type those kind of tables are read only and of course you can you can do any optimization. The second types are external tables.

1:34 Types of tables

So those one where are stored in your ADLS GCS or S3 buckets. Of course you can read and write from datab bricks but also other external engines but you're going to require to do like optimization. you will still require to do it manually which sometimes can be cumbersome. And of course we have manage tables and those are the new standard. Keep in mind US manage tables are the new standard. Why? First of all they support read and write.
This is very very important. You will benefit from automatic performance optimization which can also help you reduce the storage cost. So which means you're going to benefit from everything. Oops. And like what's the difference between external tables and manage ones? So just be like me. Whenever you define your tables, be lazy. Don't define the location. It still means that your table will be stored on your data storage S3, ADLS, GCS.
But data bricks will manage the location like where this table is going to be stored. So basically that's the main difference between external tables and manage one. But in practice how does it work? So the first one as I did mention external tables can bypass access controls. Why? Because like a data engineer can have access to the data storage and they can read and write from external engines and they can bypass like the rules like if this person is not allowed to maybe view this column or this row or the entire table.
This can be pretty dangerous. So you need to make sure that your tables are governed at every single moment with uh with the manage tables like you don't need to handle the location which means whoever want to query those tables will go through unity catalog which means the security is enforced and of course you will benefit from some of the top optimization features. Why? Because since everything goes through Unity catalog, we have enough metadata to automatically optimize those tables and make it very performant and by the same time reduce uh your your data bricks cost and keep in mind that with also external tables you will require manual optimization and I'm pretty sure I was a data engineer before I forgot to run the optimize and vacuum so many times and I end up paying a lot of money just for the storage of those tables.
And this is something also very important. Unity catalog is the only and unique catalog that will allow you to read and write from third party clients. Basically, it was just it will be a private preview, but you can in the past or not in the past like just few weeks ago, you could only read from external engines. Now we'll be able to read and write and make sure to attend com tomorrow's conference to learn more about uh this integration but we're going to have much more clients to announce and you're all going to ask me a question okay I have external tables but what are the benefits I can get from manage tables well let's hear first from uh from the director of engineering at Zepto they were able to reduce the storage cost by $1 million just by running the or using the uh predictive optimization.

5:32 Benefits of Managed Tables

And we are only talking about one feature. We're talking about vacuum. But keep in mind that when you use manage tables, you're going to have other awesome features that we're going to learn for like going to benefit from. And let's walk you through it. The first part are automatic like the feat features are automatically enabled for you. And we have bunch of features. We have deletion vectors and I'm pretty sure deletion you've heard about it but basically it's it's a feature that will allow you like speed up your process because as you know park is immutable which means whenever you're going to do an update it will create a new file and so on.
Deletion vectors reduce this because it just mark this row that is that it needs to be deleted and it will be done later on which can of course like make your your performance very very fast. Of course, you have other features like rowle concurrency and column mapping. And for those who don't know what's column mapping, it's basically feature that you will need in case you want to rename a column or for example if you want to have special characters, I don't recommend you to do it in the name of your columns as well. and of course row level concurrency and later we will will automatically enable type widening which allow you to move from like uh I don't know char to another type to string without having to rewrite in in the entire table which is very important and of course we have variant data type for those who handles semistructured or unstructured data of course on the top of it you're going to also benefit from a feature called automatic statistics and basically what happens like data bricks or like engine will capture the statistics which will improve the uh the adaptive query execution plan which means you will speed up the process.

7:24 Automatic Statistics

It's it's it it down to like whether we need to run the analyze manually or not. And this is something cool with manage tables. It can capture the statistics for more than the first 32 columns which means no longer need to go and try to figure out which columns you need to optimize or how many ones. This can be cumbersome. Just let the system do the work for you the data management part and focus what will bring the air for your company.
And of course we have something like that was announced a year ago liquid clustering. So this is a new way rather than partitioning your table and you know that when you partition table and you want to switch to a new partition you would need to rewrite the entire table for predict for liquid clustering it's like say a logical partitioning and you can switch the partition like the the keys easily for example your table is clustered by year and now like next you want to you want to you want to cluster by year and month it will be very easy to be done and also the optimize will much faster because it will be applied only on incremental data which means you benefit from like better performance and you still reduce your cost and then we announced something very interesting called cluster by auto.

8:11 Liquid Clustering

So what if you become more lazy? Why? Because sometimes you need to think about what are the best column to use to cluster my tables and you all and you all know that some and we keep evolving. Maybe today I'm gonna query my data by uh maybe by aggregate filtered by year or by mount but tomorrow I will switch to country or city which means my clustering keys need to evolve and basically what when you enable this cluster by auto it will automatically define which is the best column to uh to cluster your to cluster your table and because we capture this the previous statistics and we understand your queries thanks to Unity catalog we're able to choose the best the best column And that's why I already spoiled.
And also something that a lot of customers tend to forgot is running the vacuum. As you all know, when we are for example using a delta table, we keep the different type of the different versions. Like if you do an update, we're going to have a snapshot for the previous one and so on. And after after like uh maybe three weeks or four weeks, we're gonna have some old snapshot. And we need to get rid of those snapshot because they're no longer tracked.
And how to do so? We need to run the vacuum to get rid of all those files. And we know that we tend to forget to run this. But with predictive optimization, the vacuum will run automatically and you will be able to track all those operations within the system tables where we can see how many times I vacuum my tables, the number the number of data that was saved and how much did it take me and also to track how much did it cost me.
And we have also something that was like preventing customers from moving to manage tables which are the drop. So basically if you use an external tables if you drop this table you will just drop in the registration of this table but the data will still reside in the S3 ADLS or GCS bucket GCS while with manage tables when you drop the table in the past you would have lost the entire table. Now what we did introduce is the androp feature where basically if by mistake you drop a table you can still androp it within a specific threshold and of course this is this can help a lot of people and something that is not like widely known by using manage tables you're going to improve the query performance why because the transaction log are cached even when you're like writing the data which means you no longer need to go to the S3 bucket to get those transaction logs and then get them populate the table and query the data which means just by using manage tables you're going to increase the performance and of course this is something also very important as you all know whenever you use delta table we're asking you to optimize this data why because optimizing will avoid having to to play with small files you're going to compact those file automatically which means you're going to avoid having this small files problem which can increase the uh or decrease the uh the performance.

11:50 Performance

So just to recap when you use manage tables you're going to benefit with predictive optimization where you're going to benefit from like having the latest feature enabled by default. You're gonna have the vacuum, the analyze, and the optimize run for on like for you or on your behalf. And you will be able to track all of those operations in the system table and of course extra features like liquid by like cluster by auto.
But you're all going to ask me a question. Hey Yousef, you're talking about moving to manage tables. That's good. But when I'm going to try to migrate my tables, I will face some problems. So the first one is how can I reduce the reader and writer downtime. We understand this is this is a big big problem. How can I prevent like preserve the table history? For example, I I am using the like the change data feed and I want to track the different versions and how can I propagate this data.
I will lose the entire data. This which is pretty bad. How can I like safely switch over by like avoiding table uh corruption and how can I prevent the like by keeping the same name catalog name schema name and table name. We all know that if you do this then you need to rewrite the entire notebooks or like script you did right and you may forget one which can do some some mess. And how can I handle rollbacks if something like goes wrong?
I can maybe go on deep clone but I'm gonna lose some stuff. So what's the best solution? Well, I guess I don't have the solution. I will jump. All right. So now we're going to talk about an exciting command set manage that makes this whole process much easier for you and solve some of the problems that we talked about earlier. How's this going back? Sorry. So I know a lot of you guys do use UC manage tables already. For those of you I'll be sharing some new exciting features on our road map coming towards the end of this.

14:22 UC External Tables

But for those who don't use UC manage tables and who are on UC external tables, I'm going to tell you how you can convert from UC external tables to UC manage tables much more easily. So what's the status quo? If I wanted to convert from a UC external table to a UC manage table, I would have to go and do a deep clone. I'd have to then do a bunch of renaming and then drop that UC external table. So, really not ideal. You know, I want something easier.
We have this new exciting command that helps you easily convert to UC manage tables. It's alter table set managed and you would run this in DBR 17. 0 which is coming out shortly. So again, super easy. one command, it takes care of everything for you. And what are the benefits and the things that it really helps solve? It'll help minimize reader and writer downtime. You're not doing this whole renaming, dropping kind of game.
It'll handle concurrent writes during conversion. So, if you actually uh write to the external location, but then wait, this table is in the middle of being converted will handle that for you to avoid table corruption. And it also will retain table history which is something that's really important that otherwise if you were doing a deep clone approach you would have to lose that. In addition, it maintains all the table configurations, the table name, the table settings, the table permissions as well as any materialized views that you have on these tables.
So all you have to do is just run that command and all of these things here are taken care of for you. And so some of you guys might be thinking, well, I don't know, you know what? something goes wrong, like I'm kind of worried. I I I don't want to try this yet. I'm not sure. But don't worry, we do have a safety net for you. This is a two-way door within 14 days of moving to a UC manage table. You can run this other command called unset manage to just undo it.
Uh so just as a safety net for anyone um who wants that. Now that I talked about how to move from UC external tables to UC manage tables very easily with one simple command, I'm going to talk about how to move from foreign to manage tables. So anyone who has foreign tables in Unity catalog, we also have a way to move that to UC manage tables pretty easily. So we have this command that's going to be coming soon soon. It's the same thing, but you would run alter table on your foreign table set manage.
So that's coming very soon. And until then we have something in private preview which is alter table set external. And so you can move from a foreign to external table. And then you can use that command that I talked about earlier about going from external to managed. And so what are the benefits here in terms of moving from foreign to managed? You retain the same table name space. So you don't have to change that or worry about that.
You keep the same table permissions similar to what I said before about going from external to managed. Um, we also have dry run checks to make sure that table is actually going to be compatible before you start moving it over. And as I mentioned earlier, it's one simple command. And so some of you, I don't know how many of you that are in the audience, is there anyone who's not using Unity catalog at all or who has other foreign cataloges?
Okay, we've got a couple people. Um, so for those of you, you know, there might be some customers that ask us, you know, hey, I have a different catalog. uh moving over to Unity catalog seems like it would require some heavy lifting. So for those of you again you don't even have any foreign tables, you don't even have any external tables. You know what are you going to do? We have something called lakehouse federation.
So you can search up lakehouse federation. It's in our public documents. You can use that in in combination with these simple commands to eventually move to unity catalog manage tables. And so what is lake house federation? There's really three key steps. The first one is connecting to the data source. So you have your other catalog, you have your Unity catalog. We connect to your data source. We crawl through and discover all of your assets.
And then in the third step, we have foreign tables then created for you in Unity catalog. And um you can start governing them with fine grace grain access controls from day one. And this is particularly popular for customers moving from let's say an HMS meta store all the way to Unity catalog. So what's the process that's actually going on here? Um what's really beneficial is this helps you with an incremental migration.
So you're not just, you know, leaving your other catalog and coming straight to unique catalog. It's this process. And so here what happens is that uh your Unity catalog is synced with your uh HMS uh external catalog and then immediately you can see those foreign tables within Unity catalog and then you can create workloads on top of Unity catalog and then eventually if you should so decide to then you can go to Unity catalog all the way but we have this process for you to help you along the way.
So now for those customers who are not even on Unity catalog, they've used catalog federation, they've created their foreign tables and then they can use that foreign to external and then external to manage commands to move all the way to UC manage tables and get some of those benefits like was talking about earlier. 20 times faster query speeds, 50% reductions in cost. Remember about Zepto saving a million dollars and that's just not a million dollars, that's a million dollars per year.
And that's just one particular feature of UC manage tables. So, there's really a lot of benefits you can get here. All right. So, I know uh everyone must be tired. You know, you want to go to lunch. So, I want everyone to stand up and just take a stretch break. So, stand up. This is not a stretch break. Stand up. Stretch. All right. And then sit back down. So, everyone's paying attention. Thank you. Great. So, now we're going to go through the last part of this presentation.

20:20 Planning Your Journey

This is about planning your journey in terms of moving to UC manage tables. I'm going to share some practical tips, some best practices, some things that you will really want to know if you're going to go ahead and use these commands. So, first for the journey from external to managed, here are some recommended steps to actually get you started in preparing to use this command. So, let's say you're, you know, you're not using this command yet.

20:36 Getting Started

You're just thinking about it here sitting in the audience. You know, what are some things I might want to keep in mind and get started with? The first one is checking for thirdparty tools accessing your tables. And I'll show you a tool that will help you do that. The second one is updating any readers and writers from path-based code to name based codes wherever you can. That can be helpful. We'll have some solutions for just, you know, uh cases where you maybe can't change your path-based co code in the future.
Uh but for now, this is really the best practice. So would certainly recommend it. And then lastly, please upgrade any readers and writers to the latest DBR version as much as possible. Uh again, if you can't, we have options for you, but this will certainly make your life better. Let's talk about the first one, checking for third party tools accessing your tables. So, we have a new um dashboard and tool that is created by these two folks up here.
You can raise your hand. Thank you for working on this tool. Um and this is going to be released in the next couple weeks that will allow you to see a list of okay, what are all my tables? Who's reading and writing from those tables? And then what are the table sizes? And this is particularly important as you're moving to using managed tables. You may want to start with the tables that are read and written to only by data bricks and then expand it from there.
As well as you can see kind of the table sizes here and help decide of which ones you want to move first. And that's going to be available in our public docs. Um and I'll link to that at the end of this uh presentation. Secondly is updating your pathbased code to name based code. we have a a UCX tool which will help you identify when you're actually using path-based code so that you can change that and then eventually this tool will also be enhanced to help you actually change your pathbased code to be name based.
Um so again we certainly recommend using namebased code. So that's catalog do um it's using the catalog's name, the schema name and the table name when you're actually trying to read and write from that table. Um, so again, best practice and important for when you're converting. Lastly, please upgrade all of your readers and writer DBR versions. And why is this important? I mean, you could just not do that at all. Um, but here's how it's going to help you.
If you're using DBR 16. 1 plus, that gives you no reader downtime. So, when you use a conversion command, you have zero reader downtime if you're using namebased access. So, this is really a great benefit. And so, that's why I'd recommend upgrading. and you do retain table history with DBR 15. 4 in terms of the readers and writers that are accessing that UC manage table. Um you're going to have minimal reader downtime.
So again, you don't have to do all of that rename shenanigans with all the deep clone. But again, it's not no reader downtime and you'd also retain table history with DBR less than 15. 4. You're going to have minimal reader downtime. Uh but you can time travel only by version but not by time stamp. And so do keep this in mind when you're using the conversion command that if your readers and writers are accessing the table from DVR 16.
1 you're going to really have the best benefits that we have to offer. So planning your journey right I have all of my tables let's say are UC external in this case and I want to move them to UC managed. So start with those three steps. Checking for thirdparty access. Um update your readers and writers to use name based code. Um and update update the DBR version for your readers and writers if you should so choose. Next you can test this out.
Choose a couple of tables to run this set manage command on. I would recommend something that's you know not too small. So maybe above let's say 50 megabytes but maybe not too big maybe below one terabyte. And ideally, it's a some tables that you do actually use. So it's not just some sort of table that you never touch, something that you would maybe have like five plus scans on, have some recent writes going on. Um that will be helpful so that when you move these test tables, uh it's something that predictive optimization is then going to actually run on.
So you can check out how that works and get familiar. And then as a next step, you can check with that um tool that these guys are working on to see which of those tables are um only read to and written to from data bricks. Um those ones are good to then move next because you won't have any problems with those. You can then just run the set manage command and then slowly but surely move everything else. Um you'll want to check that for the ones that do have third party access. double check that the reads and the rights from that thirdparty tool are supported.
Um so that will take a little bit of time to check those out but that's why I recommend with step one and then slowly move on this transition path. So this is the process I would recommend um following as you're moving to UC managed tables. What about foreign to manage? Are there any you know best tips practices for doing that? Um yes there are. The first one that you may want to keep in mind is let's say that you're in uh HMS or Hive Metas store and you have HMS manage tables.
Uh those ones you would want to convert to HMS external before starting on your journey. If you don't, we'll it'll error out for you, but just wanted to let you know um about that to make sure to follow that step. In addition, let's say that you're creating your foreign catalog. So within Unity Catalog Lakehouse Federation that I was talking about earlier, please be careful when you name your foreign catalog. Don't call it, you know, my cool foreign catalog because it's going to stay like that forever.
So u please pay attention to that when you're going through this process. And just as another kind of FYI, you you'd run um this command on 16. 4. So that's the set external command and then the set manage command on 17. 0. So, plan your journey. You start out with Lakehouse Federation if you weren't even on Unity catalog. Uh, if you already have foreign tables, don't worry about that. Then from your foreign tables, you would run the set external, the alter table set external command to move from foreign to external.
That's in private preview today. And then you do the alter table set manage to move from external to manage table. So that would be your process going fully from foreign or not even foreign before that all the way to UC manage tables to get all the benefits storage cost savings faster query speeds. So anyone who's interested please um take a picture of this QR codes. We also have the links underneath. The one on the left is about going from foreign tables.
So that's the set external command to help you go from foreign to external. and then the one on the right which is external to managed. If you're coming from foreign tables, you'll have to scan both these codes. If you're coming from external tables, scan the one on the right. Um so sign sign up for these. You can get early access to these um commands. So what's coming next with UC manage tables? I know a lot of you guys said, you know, I'm actually already using UC manage tables.
Maybe you have some that are still external. They need to move over, but uh for those of you who are on UC manage tables, I did want to mention some great new capabilities that we have coming next on our road map. One is a configurable undrop period. And so let's say when you drop a table, it's automatically deleted after 7 days. So not just the table metadata, but in the cloud storage as well. So that's helpful. But now what if I want to specify that period?

27:43 Upcoming Features

You know, I don't want to wait for seven days for it to delete. I want to delete it right away or you know I want to actually keep it around for longer. I want to keep it around for 30 days. So that period will now be um configurable. That's on our road map. This one we talked about earlier but the foreign to manage command. Right now it's foreign to external and then you run external to manage but eventually in uh public preview we'll be supporting the foreign to manage without having to do that external hop in between.
And then we also have some other um features coming to help you reorganize your schemas and cataloges if you wanted to change um the schema and catalog locations. We'll have that available for you coming soon. And then also improved table level observability. This is something a lot of customers have asked for. So they want to see of all my tables, what's the table size? You know, what are the table properties? What are the like custom tags for each of my tables?
And some of this information can be really useful because you can find your large unused tables that you want to delete. They're just hanging around. They're they're, you know, it's all these additional costs for you. So you can um find those and delete those. Uh so lots of uh improved observability there. And then auto time to live as well. So this is another feature. Uh it will automatically delete expired data for you.
So let's say you have data within a specific column and you want to delete that after a certain number of days. Predictive optimization will eventually take care of this for you. So that's another feature a lot of our customers have requested and is coming soon. More with predictive optimization uh automatic compression, automatic archival. And by the way, if there's anything you don't see on this list, please talk to me at the end and say, "Hey, there's this feature I'd like with UC manage tables and I want you to build it."
Let me know. Um and the last one is manage disaster recovery. So this is the ability to replicate your data within 15 minutes no matter the size. So thank you everyone for coming to this session. Here are some other sessions that may be interesting to you. We deep dive into predictive optimization. That one is right after this talk. Um we have more information about interoperability. So that's reading and writing to your tables.
Um, also about open table formats if you're interested in iceberg and what we support for that. And lastly, some other QR codes I wanted to put up here. The nextgen lakehouse. Um, this is uh Yousef's YouTube channel, so you can just uh follow that for some tips. As well as our new public docs. So, as of this morning, we have new public documents available for managed tables. If you um can scan this, we also have a sub section within the manage table documents page that is called convert from external to manage and that goes through a lot of the details of the things we talked about today.
Um so thanks so much uh for coming today. I hope you heard about the benefits of UC manage tables. 20 times faster query speeds, 50% reduction costs, all sorts of great features and all that's automatically done for you. So, let's get started on your journey. Thanks so much,

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.