Removes an external table definition from the session and catalog, optionally deleting the underlying data files from storage.
DROP EXTERNAL TABLE [IF EXISTS] <table> [WITH FILES]## Overview DROP EXTERNAL TABLE removes an external table from three layers in sequence: 1. **DataFusion session** - The table is deregistered from the in-memory query engine. Any cached query plans referencing this table are invalidated. This step is best-effort; if the table was not registered in the current session, the operation continues. 2. **Catalog (SQLite/Control Plane)** - The table's metadata record is deleted from the persistent catalog. Associated column metadata is also removed. A table-dropped event is published so other compute nodes can sync. 3. **Physical files (WITH FILES only)** - When WITH FILES is specified, the engine resolves the table's storage location and deletes all data files at that path. For local paths, files and empty parent directories are removed. For cloud paths (S3, Azure Blob, GCS), the object store client issues delete requests for all objects under the prefix. ## Default Behavior (Without WITH FILES) By default, DROP EXTERNAL TABLE only removes the metadata registration. The underlying data files remain intact at their original location. This is the safe default because external tables reference data that may be managed by external systems or shared across multiple tables. ## WITH FILES Behavior When WITH FILES is specified, the engine performs the following: 1. Resolves the table's storage path from the session registry or, as a fallback, from the catalog. 2. Deletes all files at the resolved path. 3. Removes empty parent directories (local filesystem only). If the storage path cannot be resolved (for example, because the table was not registered in the current session and has no catalog entry), the metadata is still removed but a warning is logged indicating that files may remain on disk. ## Path Resolution Order The engine resolves the table's physical location in the following order: 1. In-memory table_paths registry (populated at CREATE EXTERNAL TABLE time) 2. Catalog lookup via CatalogRouter (persistent metadata) This two-level lookup ensures that WITH FILES works both for tables created in the current session and for tables that were only persisted to catalog. ## Cloud Storage Cleanup For cloud-hosted external tables, WITH FILES uses the object store client (configured from the session's permission token) to list and delete all objects under the table's prefix. This works for S3, Azure Blob Storage, Azure Data Lake Storage Gen2, and Google Cloud Storage. The delete operation is prefix-scoped. Only objects under the exact table location prefix are removed.
| Name | Type | Description |
|---|---|---|
table | Specifies the name of the external table to drop. Accepts unqualified names (e.g., events), two-part names (schema.events), or fully qualified three-part names (zone.schema.events). | |
if_exists | Don't error if table doesn't exist. | |
with_files | Also delete physical data files. |
-- Drop an external table (metadata only, files remain on disk)
DROP EXTERNAL TABLE staging.temp_csv;-- Drop an external table, suppressing errors if it does not exist
DROP EXTERNAL TABLE IF EXISTS bronze.raw.old_events;-- Drop an external table and delete the underlying data files
DROP EXTERNAL TABLE IF EXISTS bronze.raw.expired_logs WITH FILES;-- Drop a fully qualified external table with file cleanup
DROP EXTERNAL TABLE analytics.json_demos.listings_captured WITH FILES;