Databricks: Unity Catalog vs Catalogs vs Workspace vs Metastore

by

🔑 Unity Catalog vs Catalogs vs Workspace vs Metastore

1. Unity Catalog (UC) ✅

  • Think of it as the master governance system.
  • It’s account-level (above all workspaces).
  • Manages:
    • Who can see what (permissions, ACLs).
    • Metadata (table names, schemas, lineage).
    • Secure data sharing across workspaces (Delta Sharing).

👉 Analogy: National Library System – it governs all libraries in a country.

2. Catalogs 📚

  • A container for organizing data assets inside Unity Catalog.
  • A catalog contains Schemas (databases).
  • Within schemas, you have Tables, Views, Volumes, Functions, Models.

👉 Analogy: A library inside the national library system.

3. Schemas (Databases)

  • Sub-containers within Catalogs.
  • Organize Tables and Views.

👉 Analogy: Sections in the library (History, Science, Fiction).

4. Tables & Views

  • Actual data objects stored in schemas.
  • Tables → structured datasets (Delta by default).
  • Views → saved queries on tables.

👉 Analogy: Books on the library shelves.

5. Workspace 🖥️

  • A UI and compute environment where users collaborate (notebooks, jobs, clusters).
  • Workspaces don’t “own” the data; they just connect to Unity Catalog for governed data access.

👉 Analogy: The reading room where you sit, study, and work with books.

6. Metastore 📒

  • The backend metadata database that stores info about catalogs, schemas, tables, permissions.
  • In Unity Catalog:
    • Account-level Metastore is shared across workspaces.
  • In legacy mode:
    • Each workspace had its own Hive Metastore (separate, siloed).

👉 Analogy: The card catalog / index system telling you where each book is and who can borrow it.

✅ Hierarchy

Unity Catalog (Account-level Governance)
   └── Metastore (Metadata storage)
         └── Catalogs (Top-level containers)
               └── Schemas (Databases)
                     └── Tables / Views / Volumes / Models

And Workspaces are where you interact with all of this (via notebooks, jobs, queries).

Key Distinction

  • Unity Catalog = The system of rules + governance layer.
  • Catalog = Logical container for data inside UC.
  • Metastore = Metadata database that keeps track of it all.
  • Workspace = Your working environment (UI + compute) that connects to the above.

Let’s wrap up everything we’ve discussed about Metastore → Catalog → Schema → Table and external locations into a single, step-by-step tutorial that you can follow on Databricks.

📘 Tutorial: Understanding Databricks Data Hierarchy & External Locations

1. The Hierarchy

In Unity Catalog, Databricks enforces this hierarchy:

Metastore 
   └── Catalog 
         └── Schema 
               └── Table / View / Volume / Model
  • Metastore → The root metadata container. Every account gets one Unity Catalog metastore.
  • Catalog → Top-level logical container for schemas and data assets.
  • Schema (Database) → Organizes objects within a catalog.
  • Table → Stores data (managed or external).

2. Managed vs External Tables

  • Managed Table: Databricks manages both metadata + storage. Dropping the table deletes files.
  • External Table: Databricks manages only metadata. The data stays in your cloud storage when dropped.

3. External Location (Key Concept)

  • An External Location is a Unity Catalog object that maps a cloud storage path (S3, ADLS, GCS) + a storage credential.
  • Defined at the Metastore level → not at Catalog or Schema.
  • Used when creating external tables.

Example:

-- Step 1: Create storage credential (cloud-specific)
CREATE STORAGE CREDENTIAL my_cred
WITH AZURE_MANAGED_IDENTITY 'my-managed-identity'
COMMENT 'Credential for ADLS';

-- Step 2: Register external location
CREATE EXTERNAL LOCATION my_ext_loc
URL 'abfss://external-container@mydatalake.dfs.core.windows.net/data/'
WITH (STORAGE CREDENTIAL my_cred)
COMMENT 'External data location';

4. Creating Catalog, Schema, and Tables

-- Create a catalog
CREATE CATALOG sales_catalog;

-- Create a schema (inside catalog)
CREATE SCHEMA sales_catalog.sales_schema;

-- Managed table (Databricks manages storage)
CREATE TABLE sales_catalog.sales_schema.customers_managed (
  id INT, name STRING
);

-- External table (you provide LOCATION)
CREATE TABLE sales_catalog.sales_schema.customers_external
USING DELTA
LOCATION 'abfss://external-container@mydatalake.dfs.core.windows.net/data/customers/';

5. Insert, Query, and Drop Data

-- Insert data (only works on managed tables)
INSERT INTO sales_catalog.sales_schema.customers_managed VALUES (1, 'Alice'), (2, 'Bob');

-- Query data
SELECT * FROM sales_catalog.sales_schema.customers_managed;

-- Drop table
DROP TABLE sales_catalog.sales_schema.customers_managed;

-- For external table → only metadata removed, data files remain
DROP TABLE sales_catalog.sales_schema.customers_external;

6. Quick Rules Recap ✅

  • Metastore → Mandatory, stores metadata & external location definitions.
  • Catalog → Mandatory, logical top-level container.
  • Schema → Mandatory, organizes tables within catalogs.
  • Table → Optional, where the actual data lives.
  • External Location → Defined at Metastore, used at Table level.

🎯 Conclusion

  • You cannot define external locations at catalog or schema level.
  • You must define them at the metastore and use them when creating external tables.
  • Always think:
    • Metastore = registry
    • Catalog = library section
    • Schema = shelf
    • Table = book (data itself)

Catalogs themselves don’t directly “use” external locations, but you can associate them with external storage in Unity Catalog. Let me explain:

Catalog Storage in Databricks using External Location

1. Default Behavior

  • When you create a catalog in Unity Catalog: CREATE CATALOG sales_catalog; → Databricks automatically assigns it a default storage location (in the metastore’s root storage).
    • All managed tables created inside sales_catalog go there by default.

2. Catalog With External Location

  • You can override this by explicitly binding a catalog to an external location: CREATE CATALOG sales_catalog MANAGED LOCATION 'abfss://container@storageacct.dfs.core.windows.net/sales_data/';
    • Here, the catalog’s default managed tables will live in that external location.
    • This is sometimes called a “catalog-level managed location.”

3. Schema Level

  • Similarly, you can also define a managed location for a schema: CREATE SCHEMA sales_catalog.retail MANAGED LOCATION 'abfss://container@storageacct.dfs.core.windows.net/retail_data/';
    • Now tables created in this schema (without explicit LOCATION) go here.

4. Table Level

  • For external tables, you still provide the LOCATION explicitly: CREATE TABLE sales_catalog.retail.customers USING DELTA LOCATION 'abfss://container@storageacct.dfs.core.windows.net/customers/';

✅ So to answer your question

  • Yes, you can create a catalog in Databricks that uses an external location as its default managed storage.
  • But ⚠️ this doesn’t mean all tables are external tables — it just changes the default storage path for managed tables created in that catalog (or schema).
  • External tables still need their own explicit LOCATION or use a registered external location.

Leave a Comment