A data analyst is attempting to drop a table my_table. The analyst wants to delete all table metadata and data. They run the following command: DROP TABLE IF EXISTS my_table; While the object no longer appears when they run SHOW TABLES, the data files still exist. Which of the following describes why the data files still exist and the metadata files were deleted?

The behavior described—where metadata is deleted but the underlying data files persist—is the defining characteristic of dropping an external table in Databricks. When a DROP TABLE command is executed on an external table, Databricks removes the table's definition from the metastore but intentionally leaves the data files in their specified external location untouched. This prevents accidental data loss, as the data might be used by other processes or tables. In contrast, dropping a managed table would delete both the metadata and the data files.

A. The table's data was larger than 10 GB: The size of the data is irrelevant to the behavior of the DROP TABLE command concerning metadata and data file deletion.
B. The table did not have a location: All tables have a location. The distinction is whether Databricks manages it (managed table) or if it's a user-specified path (external table).
D. The table's data was smaller than 10 GB: Similar to option A, the data size does not affect how DROP TABLE operates on managed versus external tables.
E. The table was managed: If the table were managed, the DROP TABLE command would have deleted both the metadata and the underlying data files.

1. Databricks SQL Language Reference

DROP TABLE: "If the table is an EXTERNAL table

only the metadata for the table is removed from the metastore. The data files that comprise the table are not deleted from the file system."

Source: Databricks Documentation > SQL reference > Data definition language > DROP TABLE.

2. Databricks Documentation

"What are tables on Databricks?": "When you drop an external table

you do not delete the underlying data. The table definition is removed from the metastore

but the directory containing the data remains."

Source: Databricks Documentation > Data management > What are tables on Databricks? > External tables.

3. Databricks Documentation

"What are tables on Databricks?": "When you drop a managed table

you also delete the underlying data." This reference clarifies the alternative behavior for managed tables

confirming why option E is incorrect.

Source: Databricks Documentation > Data management > What are tables on Databricks? > Managed tables.