3.3 Managed Tables, External Tables, & Unity Catalog Volumes

3.3 Managed Tables, External Tables, & Unity Catalog Volumes

DP-750 Exam Focus: Differentiate the lifecycle, storage management, and DROP behavior of Managed Tables versus External Tables. Understand how Unity Catalog Volumes provide fine-grained governance over non-tabular data, compare Managed versus External Volumes, and master the /Volumes/<catalog>/<schema>/<volume>/... POSIX file path syntax.

---

Tabular vs. Non-Tabular Storage Governance in Lakehouse

A modern enterprise lakehouse must govern two primary classes of digital assets:

1. Tabular Structured Data: Curated tables and analytical views querying Delta Lake, Parquet, or Iceberg formats.
2. Non-Tabular / Semi-Structured Files: Raw JSON landing drops, CSV extracts, log archives, image/audio files for computer vision, geospatial rasters, and machine learning model artifacts.

Unity Catalog unifies governance across both classes through two distinct securable constructs: Tables (Managed and External) and Volumes (Managed and External).

---

Managed Tables: Lifecycle, Storage, and Drop Behavior

A Managed Table is the default and recommended table type in Unity Catalog. When you create a managed table, Unity Catalog manages both the table metadata and the underlying physical data files in ADLS Gen2.

                               +---------------------------------------------+
                               |                Managed Table                |
                               |    CREATE TABLE prod.silver.customer_orders |    |
                               |    (order_id INT, amount DOUBLE);           |
                               +---------------------------------------------+
                                                     |
                                                     v
                               +---------------------------------------------+
                               |     Storage Location Inherited from:        |
                               |     1. Schema Managed Location (if set)     |
                               |     2. Catalog Managed Location (if set)    |
                               |     3. Metastore Root Location (fallback)   |
                               +---------------------------------------------+
                                                     |
                                                     | DROP TABLE customer_orders;
                                                     v
                               +---------------------------------------------+
                               |              Deletion Result:               |
                               |  [X] Metadata deleted from Unity Catalog    |
                               |  [X] Data files deleted from ADLS Gen2      |
                               +---------------------------------------------+

Managed Table Characteristics

• Default Creation: Any CREATE TABLE statement without an explicit LOCATION clause creates a Managed Table.
• Storage Location Inheritance: Data files are stored in a managed directory structured hierarchically: <managed-location>/__unitystorage/schemas/<schema-id>/tables/<table-id>.
• Storage Location Resolution Priority:
  1. Schema's MANAGED LOCATION (if explicitly set during schema creation).
  2. Catalog's MANAGED LOCATION (if explicitly set during catalog creation).
  3. Metastore's root storage location (if neither schema nor catalog specifies a location).
• DROP Semantics (Critical Exam Topic): When a user executes DROP TABLE <managed_table>;, Unity Catalog removes the table metadata from the catalog AND permanently deletes the underlying physical Delta/Parquet data files from ADLS Gen2 (subject to Delta retention windows: tombstoned files stay in storage until VACUUM runs, and delta.deletedFileRetentionDuration defaults to 7 days, not 30).

-- Creating a Managed Table in Unity Catalog
CREATE TABLE prod_catalog.silver.sensor_readings (
  sensor_id STRING NOT NULL,
  reading_timestamp TIMESTAMP NOT NULL,
  temperature DOUBLE,
  pressure DOUBLE
)
USING DELTA
PARTITIONED BY (DATE(reading_timestamp))
COMMENT 'Managed Delta table with lifecycle governed by UC';

---

External Tables: Decoupled Metadata and Storage

An External Table is a table whose metadata is registered in Unity Catalog, but whose underlying data files reside in an external cloud storage directory specified by an explicit LOCATION clause.

                               +---------------------------------------------+
                               |               External Table                |
                               |    CREATE TABLE prod.silver.legacy_logs     |
                               |    LOCATION 'abfss://raw@adls/logs/';       |
                               +---------------------------------------------+
                                                     |
                                                     | DROP TABLE legacy_logs;
                                                     v
                               +---------------------------------------------+
                               |              Deletion Result:               |
                               |  [X] Metadata deleted from Unity Catalog    |
                               |  [ ] Data files KEPT INTACT in ADLS Gen2    |
                               +---------------------------------------------+

External Table Characteristics

• Explicit Location: Created by providing a LOCATION 'abfss://...' clause pointing to an existing directory in an authorized External Location.
• Required Privilege: The creator must hold the CREATE EXTERNAL TABLE privilege on the corresponding Unity Catalog External Location object.
• DROP Semantics (Critical Exam Topic): When an external table is dropped via DROP TABLE <external_table>;, Unity Catalog deletes ONLY the metadata registration. The underlying data files, Parquet directories, and transaction logs in ADLS Gen2 remain completely intact on cloud storage.

-- Creating an External Table in Unity Catalog
CREATE TABLE prod_catalog.bronze.partner_invoices (
  invoice_id STRING,
  vendor_code STRING,
  invoice_total DECIMAL(10,2),
  invoice_date DATE
)
USING DELTA
LOCATION 'abfss://raw-data@adlsprod.dfs.core.windows.net/landing/invoices/';

---

Comprehensive Comparison: Managed vs. External Tables

---

Unity Catalog Volumes: File-Level Governance

A Volume is a Unity Catalog securable object representing a logical volume of non-tabular, unstructured, or semi-structured storage in cloud object storage.

                                       +-----------------------------------+
                                       |              Schema               |
                                       |       (prod_catalog.bronze)       |
                                       +-----------------------------------+
                                                         |
                                 +-----------------------+-----------------------+
                                 |                                               |
                                 v                                               v
               +-----------------------------------+           +-----------------------------------+
               |          Managed Volume           |           |          External Volume          |
               |      `landing_json_volume`        |           |        `partner_sftp_volume`      |
               +-----------------------------------+           +-----------------------------------+
               | Storage: Inherits schema root     |           | Storage: Explicit External Path   |
               | Lifecycle: Files deleted on DROP  |           | Lifecycle: Files preserved on DROP|
               +-----------------------------------+           +-----------------------------------+

Why Volumes Replace DBFS Mounts (/mnt)

• Legacy Risk: In legacy Databricks, accessing raw files required configuring DBFS mounts (dbfs:/mnt/...) or storing AWS/Azure credentials on clusters. DBFS mounts lacked fine-grained authorization—if a cluster could access /mnt/data, every user on that cluster had full access.
• Modern Governance: Volumes provide full ANSI SQL privilege controls (READ VOLUME, WRITE VOLUME) on files, audit access via system tables, and eliminate shared storage credentials on compute nodes.

Managed Volumes vs. External Volumes

---

Working with Volumes: SQL and POSIX File Access

Unity Catalog makes files stored in Volumes accessible via standard SQL commands and a POSIX-compliant filesystem path mapped directly into the operating system filesystem on all compute nodes.

The Standard Volume Path Syntax

POSIX Volume Path: /Volumes/<catalog>/<schema>/<volume_name>/<subpath>/<filename>

1. File Management via SQL

-- Create a Managed Volume
CREATE VOLUME prod_catalog.bronze.unstructured_landing
COMMENT 'Managed volume for incoming PDF and JSON invoices';

-- Create an External Volume
CREATE EXTERNAL VOLUME prod_catalog.bronze.partner_dropzone
LOCATION 'abfss://partner-drops@adlsprod.dfs.core.windows.net/incoming/'
COMMENT 'External volume pointing to vendor ingestion container';

-- List files in a Volume
LIST '/Volumes/prod_catalog/bronze/unstructured_landing';

2. Reading Files in Python / PySpark

Because /Volumes is mounted as a local POSIX path via Databricks FUSE, standard Python file utilities, open-source libraries, and Spark APIs interact with files natively without needing special cloud storage drivers:

# Standard Python file I/O
volume_file_path = "/Volumes/prod_catalog/bronze/unstructured_landing/invoices/inv_2026_08.json"

with open(volume_file_path, "r") as f:
    raw_json_data = f.read()
    print(f"Loaded {len(raw_json_data)} bytes")

# Reading semi-structured JSON directly using PySpark DataFrame API
df = spark.read.format("json").load("/Volumes/prod_catalog/bronze/unstructured_landing/invoices/")
display(df)

# Reading binary/image files for Computer Vision
import cv2
image = cv2.imread("/Volumes/prod_catalog/bronze/unstructured_landing/images/sample.jpg")

3. Granting Privileges on Volumes

-- Grant read-only access to data scientists
GRANT READ VOLUME ON VOLUME prod_catalog.bronze.unstructured_landing TO `data_scientists`;

-- Grant full write access to data engineering ETL pipelines
GRANT READ VOLUME, WRITE VOLUME ON VOLUME prod_catalog.bronze.unstructured_landing TO `data_engineers`;

---

Medallion Architecture Storage Strategy: Tables & Volumes

In a production Databricks Lakehouse architecture:

1. Bronze Layer: Non-tabular raw files (JSON, XML, CSV, media) arrive in Unity Catalog Volumes (/Volumes/prod/bronze/landing_zone). Auto Loader (cloudFiles) streams data from the Volume into Managed Bronze Delta Tables.
2. Silver Layer: Structured transformations, deduplication, and quality validations write exclusively into Managed Silver Delta Tables (prod.silver.*).
3. Gold Layer: Business aggregates, dimensional models, and curated reporting marts reside in Managed Gold Delta Tables and Unity Catalog Views (prod.gold.*).