In Hive, what happens to the data when you DROP a managed table versus an external table?

## The two table types Every Hive table carries a `Table Type` in the metastore, either `MANAGED_TABLE` or `EXTERNAL_TABLE`. The distinction is not about where the bytes physically sit — both are directories of files on HDFS or object storage — it is about **who owns the data's lifecycle**. ```sql -- managed: Hive owns the files CREATE TABLE sales (id BIGINT, amount DECIMAL(12,2)) PARTITIONED BY (dt STRING) STORED AS ORC; -- external: someone else owns the files CREATE EXTERNAL TABLE sales_raw (id BIGINT, amount STRING) PARTITIONED BY (dt STRING) STORED AS TEXTFILE LOCATION '/data/landing/sales'; ``` ## What DROP does - `DROP TABLE sales;` — the metastore entry disappears **and** the warehouse directory for the table is deleted. The data is gone (subject to the HDFS trash, if enabled, which buys you a grace period and is the only reason some of these accidents are recoverable). - `DROP TABLE sales_raw;` — the metastore entry disappears; `/data/landing/sales` is untouched. Re-issuing the `CREATE EXTERNAL TABLE` statement with the same location and schema brings the table straight back, and `MSCK REPAIR TABLE` re-registers the partitions. That asymmetry is the entire question, and it is asked constantly because the failure mode is memorable: someone drops a table to "recreate it cleanly" and destroys a landing zone that three other pipelines were reading. ## Choosing between them Ask who writes the files. **Use external when:** an ingestion tool, another engine, or an upstream team writes the directory; the data lives in a shared bucket or a location outside the warehouse; multiple tables project different schemas over the same files; the location is a raw landing zone you want to keep even if the table definition churns. **Use managed when:** the table is derived output produced solely by Hive, and you want dropping it to actually reclaim the space. Managed tables also give you `TRUNCATE TABLE` and — in Hive 3 — ACID semantics. A practical platform convention: raw and landing layers are external; curated tables built by the warehouse itself are managed. ## What changed in Hive 3 Hive 3 sharpened the split rather than blurring it. Managed tables became **transactional (ACID) by default**, stored as ORC under a managed warehouse path, and only managed tables support row-level `UPDATE`/`DELETE` and `MERGE`. External tables are the non-transactional variety and live under a separate external warehouse path. `hive.strict.managed.tables` enforces that a managed table really is transactional. Practically this means the choice is no longer only "does DROP delete my files" — it also decides whether row-level mutation and compaction are available, and whether non-Hive engines can safely read the files directly (they usually can for external tables, and need ACID-aware readers for managed ones). There is also a table property that deliberately blurs the line: `TBLPROPERTIES ('external.table.purge'='true')` on an external table asks Hive to delete the data on drop after all. Use it knowingly; it removes exactly the safety net people choose external for. ## Common misconceptions "External means the data is outside HDFS." No — an external table can point at a path inside the same cluster, even inside the warehouse directory. External is a lifecycle flag, not a storage location. "External tables are slower." Nothing about reads differs; the same files, the same formats, the same partition pruning. Performance comes from format, partitioning and file sizes. "You cannot partition an external table." You can. The difference is that partitions are often registered by an external process, so `ALTER TABLE ADD PARTITION` or `MSCK REPAIR TABLE` becomes part of the ingest workflow. "TRUNCATE works on either." `TRUNCATE TABLE` targets managed tables; on an external table it is rejected unless the purge property makes Hive the owner. ## How to check before you act ```sql DESCRIBE FORMATTED sales_raw; -- look for: Table Type: EXTERNAL_TABLE -- and: Location: hdfs://.../data/landing/sales ``` Making that a habit before any `DROP` is the operational answer to the question, and stating it in an interview signals you have been burned once and learned. If you only need a fresh schema, `ALTER TABLE` or dropping and recreating an *external* definition is the safe path; dropping a managed table to "reset" it is not.