Difference between Managed Table with a custom location and External Table in Databricks

Requirements:
Managed Table with Custom Location:

CREATE  DATABASE  my_schema_mnged_with_loc
LOCATION  "abfss://[email protected]/data";

CREATE  TABLE  my_schema_mnged_with_loc.my_managed_table (
id  INT,
name  STRING
)
USING  DELTA;

External Table:

CREATE  DATABASE  my_schema_ext;
CREATE  TABLE  my_schema_ext.my_ext_table (
id  INT,
name  STRING
)
USING  DELTA
LOCATION  "abfss://[email protected]/Ext_data";

So the difference between Managed Table with custom location and External table :

When creating a managed table on top of a schema located in a custom location, the metadata of the table is stored in Azure Blob Storage and Databricks. In contrast, for an external table , the metadata is stored within Databricks.

If we try to alter the location of an external table in Databricks, within Databricks the location gets altered successfully. But table metadata is still present in the previous location and in new_location is still acting like External Table.

SQL:

ALTER TABLE my_schema_mnged_with_loc.my_managed_table SET LOCATION "abfss://[email protected]/new_loc"

Before Altering the location:

When you drop the table after altering its location in Databricks, the table itself is removed from the new location. However, the metadata remains in the old location.

After Altering the location and dropping the table:

After dropping the table you can find that table got dropped from Databricks.

Although somewhat unclear, you are talking I presume about Unity Catalog.

In Unity Catalog a Location clause can be defined at Catalog, Schema and Table level, the hierarchy as known there.

if you define Location at Table level, then that is an External Table. Otherwise, it uses the (default) Location that is explicitly defined or inherited from higher level and is a Managed Table.

With External Tables, if you drop it then you will have to delete underlying files yourself.