Running DDL against managed and external tables

In Azure Databricks, DDL commands create either managed tables (Databricks controls both metadata and underlying data files) or external tables (Databricks controls only metadata, while data resides at a user-specified location). Understanding CREATE, DROP, and ALTER behavior for each table type—especially what happens to underlying data—is essential for safe schema and storage management.

1 · Learn the must-know

• 1CREATE TABLE without a LOCATION clause creates a managed table whose data is stored in the default managed storage location (DBFS root or, in Unity Catalog, the catalog/schema's managed location).
• 2CREATE TABLE ... LOCATION 'path' creates an external table; Databricks stores only metadata in the metastore while data remains at the specified path.
• 3DROP TABLE on a managed table deletes both the metadata and the underlying data files, whereas DROP TABLE on an external table removes only the metadata and leaves the data files intact.
• 4In Unity Catalog, external tables require a defined external location and storage credential granting access to the target cloud storage path before creation succeeds.
• 5CREATE TABLE AS SELECT (CTAS) and CREATE OR REPLACE TABLE can be used to create/overwrite managed tables from query results, and the default table format is Delta unless another USING clause is specified.
• 6DESCRIBE TABLE EXTENDED (or DESCRIBE DETAIL) reveals whether a table is MANAGED or EXTERNAL along with its storage Location, which is the reliable way to verify table type.

2 · Check your understanding