Managed vs external tables & volumes
Databricks · Unity Catalog & Governance
Theory
Unity Catalog has two kinds of tables. A managed table stores its files in storage that Unity Catalog manages (the metastore, catalog or schema managed location); you just CREATE TABLE without a LOCATION. Unity Catalog controls the files, optimizes them and, on DROP, deletes the data after a recovery period (UNDROP works for 7 days by default). Managed tables are the default and recommended choice. An external table points to a path you give with LOCATION inside an external location; DROP TABLE removes only the metadata and the files stay.
Volumes do the same for files such as CSV, JSON or PDFs. A managed volume lives in Unity Catalog storage; an external volume maps to an existing cloud path. Files are addressed as /Volumes/<catalog>/<schema>/<volume>/<path>, and access is granted with READ VOLUME and WRITE VOLUME. For example, raw CSVs land in the volume main.sales.raw_files and load into main.sales.orders.
Why we use it
Teams need governed places for both tables and raw files. Managed objects remove storage housekeeping and get automatic optimization; external objects let you govern data that other tools already write to a fixed path.
When to use it
Default to managed tables and managed volumes. Use external tables or volumes when other systems must read or write the same path directly, or when data already sits in a bucket you cannot move. Never use DBFS mounts for new work; use volumes.
How it works
- Need to store data in Unity Catalog
- Is it tabular data? Yes: Create a table. No: Create a volume for the files.
- Must other tools use a fixed cloud path? Yes: Make it external with LOCATION. No: Make it managed (default).
- Grant access and query by its three-level name
Syntax
CREATE TABLE main.sales.orders (...) -- managed
CREATE TABLE main.sales.orders_ext (...) LOCATION 's3://bucket/orders' -- external
CREATE VOLUME main.sales.raw_files -- managed volume
CREATE EXTERNAL VOLUME main.sales.landing LOCATION 's3://bucket/landing'
Path: /Volumes/main/sales/raw_files/orders.csv
UNDROP TABLE main.sales.ordersExample
CREATE VOLUME IF NOT EXISTS main.sales.raw_files;
-- Load a CSV from the volume into a managed Delta table
CREATE OR REPLACE TABLE main.sales.orders AS
SELECT * FROM read_files(
'/Volumes/main/sales/raw_files/orders.csv',
format => 'csv', header => true);
-- External table over an existing path
CREATE TABLE main.sales.orders_archive
LOCATION 's3://techtown-archive/orders/';
# Python: list files in the volume
display(dbutils.fs.ls("/Volumes/main/sales/raw_files/"))Key points
- Managed tables are the default and recommended table type
- DROP on a managed table deletes data after a recovery window; UNDROP restores it
- DROP on an external table removes metadata only; files stay at the LOCATION
- Volumes govern files; paths look like /Volumes/catalog/schema/volume/...
- Grant READ VOLUME / WRITE VOLUME for files, SELECT / MODIFY for tables