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

  1. Need to store data in Unity Catalog
  2. Is it tabular data? Yes: Create a table. No: Create a volume for the files.
  3. Must other tools use a fixed cloud path? Yes: Make it external with LOCATION. No: Make it managed (default).
  4. 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.orders

Example

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

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic