What's the recommended way of creating tables in Databricks with unity catalog (External/Managed)

[Shivap]

I have databricks with unity catalog enables and created an external ADLS location. when I create the catalog/schema it uses the external location. when I try to create the table it uses the extrenal location but they are managed tables. What's the recommended way of creating tables in Databricks with unity catalog. How I can create an external table with Unity catalog enabled -

CREATE external TABLE test_catalog.test_schema.test(  

name STRING

) LOCATION 'abfss://databricks-storage@saXXX0101.dfs.core.windows.net/'

[Brahmareddy]

Hi Shivap,

How are you doing today?, As per my understanding, in Unity Catalog, if you want to create an external table, you just need to make sure the external location is registered and approved first. Even though you're specifying a path with LOCATION, Unity Catalog treats it as a managed table unless you're pointing to a pre-registered external path. So, first register your external location using CREATE EXTERNAL LOCATION, then grant access to it, and finally create your table using CREATE TABLE ... LOCATION 'your-path'. Unity Catalog will treat it as an external table if the path is outside the managed schema location. You don't need to add the word EXTERNAL—just providing a valid custom location is enough. Let me know if you’d like help setting up the external location step-by-step! 

Regards,

Brahma

[Shivap]

Thanks Brahma, Appreciate the details. So looks like catalog and schema are at the managed location and I cannot use the same location for the table to create it as external. I have created an external location and has all the privileges. when I create the catalog/schema I specify the external location so I was thinking it is in external location but it looks like catalog/schema are managed. when I try use the same location it's overlapping with managed location. Little confused. what should be the standard practice. I used ADLS location so it should treat it as external location while creating the catalog/schema,

[Brahmareddy]

Hi Shivap,

Totally get the confusion😀—it’s a common one! Even if you use an ADLS path when creating a catalog or schema, Unity Catalog still treats it as managed because Databricks controls the folder structure inside that path. So when you create tables under that schema, they’re considered managed tables, even though the storage is in ADLS. If you try to use the same path for an external table, it overlaps with the managed area, which Unity Catalog won’t allow. The best practice is to keep managed tables under the catalog/schema path, and for external tables, use a separate folder in ADLS that’s registered as an external location. That way, Unity Catalog clearly knows which data it manages and which one you’re managing. Let me know if you’d like a quick example of how to structure it!

Regards,

Brahma

[Shivap]

Thanks. It will be helpful if you can provide me an example. I want to create all the three namespace in external location. what I am creating is looks like managed!!