Unity Catalog Complete Reference: Every Securable Object, Metastore, Catalogs, Schemas, Tables, Views, Volumes, Functions, Models, Storage Credentials, External Locations, Connections, Delta Sharing, Privileges, and Lineage

Table of Contents

• The Complete Unity Catalog Hierarchy
• Metastore — The Top-Level Container
• Catalogs — Organizing by Environment or Business Unit
• Schemas — Organizing by Domain or Layer
• Tables — Managed vs External
• Views — Virtual Tables
• Volumes — Unstructured File Storage
• Functions — Reusable Logic
• Models — Registered ML Models
• Storage Credentials and External Locations
• Connections — Federated Query Access
• Shares and Recipients — Delta Sharing
• Privileges and Access Control
• Lineage — Tracking Data Flow
• Legacy Hive Metastore vs Unity Catalog
• Designing a Production Unity Catalog Structure
• Common Mistakes
• Interview Questions
• Wrapping Up

Our Unity Catalog Deep Dive covered metastore setup, three-level namespace, governance, and lineage. Our Managed vs External Tables post covered table types and external locations. This post is the comprehensive reference that maps every securable object in Unity Catalog — tables, views, volumes, functions, models, services, storage credentials, external locations, connections, and shares — into one complete picture with SQL examples, privilege details, and production design patterns.

Analogy — A city government. Unity Catalog is the city government that manages all property (data). The metastore is the city itself. Catalogs are neighborhoods (Development District, Production District, Staging District). Schemas are streets within each neighborhood (Orders Street, Customers Street). Within each street, you have buildings of different types: tables (warehouses for structured goods), views (windows into warehouses — you see the goods but do not own them), volumes (storage units for unstructured items — files, images, documents), functions (workshops that take inputs and produce outputs), and models (AI labs that make predictions). The city also manages storage credentials (master keys to external storage facilities), external locations (registered addresses outside the city), connections (diplomatic channels to foreign databases), and shares (trade agreements for sharing goods with other cities). Every building has a deed (owner) and access permissions (who can enter, read, write).

The Complete Unity Catalog Hierarchy

Unity Catalog Object Hierarchy:

  METASTORE (one per region -- top-level container)
    |
    ├── Catalogs (environment/business unit separation)
    |     |
    |     ├── Schemas (domain/layer separation)
    |     |     |
    |     |     ├── Tables (structured data -- managed or external)
    |     |     ├── Views (virtual tables from SQL queries)
    |     |     ├── Volumes (unstructured files -- managed or external)
    |     |     ├── Functions (UDFs -- SQL or Python)
    |     |     └── Models (registered ML models)
    |     |
    |     └── (more schemas...)
    |
    ├── Storage Credentials (authentication to cloud storage)
    ├── External Locations (registered cloud storage paths)
    ├── Connections (foreign database connections for federated queries)
    ├── Shares (Delta Sharing outbound data)
    └── Recipients (Delta Sharing consumers)

  Three-level namespace for data assets:
    catalog.schema.object
    Example: prod_catalog.orders.customers
             dev_catalog.bronze.raw_events

Securable object types at a glance:

  WITHIN schemas (three-level namespace):
    Tables      -- Structured data (Delta format, managed or external)
    Views       -- SQL query results (standard, materialized, or dynamic)
    Volumes     -- Unstructured files (CSV, JSON, images, PDFs)
    Functions   -- Reusable SQL/Python logic (scalar or table-valued)
    Models      -- ML models (MLflow-registered, versioned)

  DIRECTLY under metastore (account-level):
    Storage Credentials  -- IAM roles or service principals for cloud storage
    External Locations   -- Registered paths in S3/ADLS/GCS
    Connections          -- Foreign database connections (MySQL, PostgreSQL, etc.)
    Shares               -- Delta Sharing outbound definitions
    Recipients           -- Delta Sharing consumer identities

Metastore — The Top-Level Container

The metastore is the top-level container for all metadata in Unity Catalog. Every workspace in a region is linked to one metastore. The metastore stores all object definitions, permissions, lineage, and audit logs.

Key facts about metastores:

  - One metastore per region (e.g., one for us-east-1, one for eu-west-1)
  - Multiple workspaces can share the same metastore
  - Created automatically for workspaces after November 2023
  - Contains the three-level namespace and all securable objects
  - Managed by the Account Admin or Metastore Admin role
  - Stores ONLY metadata (not the actual data -- data lives in cloud storage)

  Metastore admin responsibilities:
    - Create catalogs
    - Assign catalog ownership
    - Manage storage credentials and external locations
    - Configure Delta Sharing
    - Monitor lineage and audit logs

-- View current metastore
SELECT CURRENT_METASTORE();

-- List all catalogs in the metastore
SHOW CATALOGS;

Catalogs — Organizing by Environment or Business Unit

Catalogs are the first level of the three-level namespace. They sit directly under the metastore and are used to separate data by environment (dev/staging/prod) or by business unit (marketing/finance/engineering).

Analogy — Apartment buildings in a neighborhood. Each catalog is a separate apartment building. The dev building is for experimentation (tenants can renovate freely). The prod building is locked down (strict rules, no unauthorized changes). The staging building is for dress rehearsals before moving to prod. Each building has the same floor plan (schemas), but different tenants (data) and different access rules.

-- Create catalogs for environment separation
CREATE CATALOG dev_catalog
  COMMENT 'Development -- sandbox for experimentation';

CREATE CATALOG staging_catalog
  COMMENT 'Staging -- pre-production validation';

CREATE CATALOG prod_catalog
  MANAGED LOCATION 'abfss://unity-catalog@storageaccount.dfs.core.windows.net/prod/'
  COMMENT 'Production -- governed, audited, access-controlled';

-- Create catalogs for business unit separation
CREATE CATALOG marketing_catalog
  COMMENT 'Marketing team data assets';

CREATE CATALOG finance_catalog
  COMMENT 'Finance team data assets -- PII restrictions apply';

-- Switch to a catalog
USE CATALOG prod_catalog;

-- List all catalogs
SHOW CATALOGS;

-- Describe a catalog
DESCRIBE CATALOG prod_catalog;

-- Transfer ownership
ALTER CATALOG prod_catalog OWNER TO `data-platform-team@company.com`;

Catalog Design Patterns

Pattern 1: Environment-based (most common for data engineering)
  dev_catalog      -- engineers experiment freely
  staging_catalog  -- integration testing, pre-production
  prod_catalog     -- production data, strict access

Pattern 2: Business unit-based (large organizations)
  marketing_catalog  -- marketing team owns their data
  finance_catalog    -- finance team owns their data
  shared_catalog     -- cross-team shared datasets

Pattern 3: Hybrid (recommended for enterprise)
  dev_catalog        -- all teams share dev
  prod_marketing     -- production marketing data
  prod_finance       -- production finance data
  prod_shared        -- production shared datasets

Schemas — Organizing by Domain or Layer

Schemas (also called databases) are the second level of the namespace. Within a catalog, schemas group related objects by domain (orders, customers, products) or by data layer (bronze, silver, gold).

-- Create schemas for medallion architecture
USE CATALOG prod_catalog;

CREATE SCHEMA bronze
  COMMENT 'Raw ingested data -- no transformations applied';

CREATE SCHEMA silver
  COMMENT 'Cleaned, validated, deduplicated data';

CREATE SCHEMA gold
  COMMENT 'Business-ready aggregations and dimensions';

CREATE SCHEMA metadata
  COMMENT 'Pipeline configs, audit logs, data quality results';

-- Create domain-specific schemas
CREATE SCHEMA orders
  MANAGED LOCATION 'abfss://unity-catalog@storageaccount.dfs.core.windows.net/prod/orders/'
  COMMENT 'Order management domain';

-- List schemas
SHOW SCHEMAS IN prod_catalog;

-- Switch to a schema
USE SCHEMA bronze;

-- Now tables can be referenced with short names:
-- SELECT * FROM raw_orders;
-- Instead of: SELECT * FROM prod_catalog.bronze.raw_orders;

Tables — Managed vs External

Tables are the primary securable object for structured data. Every table in Unity Catalog is a Delta table stored in cloud storage. Tables can be managed (Unity Catalog controls the storage lifecycle) or external (you control the storage location).

Managed Tables

-- Managed table: Unity Catalog decides where to store the data
CREATE TABLE prod_catalog.silver.customers (
  customer_id BIGINT,
  customer_name STRING,
  email STRING,
  region STRING,
  created_at TIMESTAMP
)
COMMENT 'Cleaned customer records';

-- When you DROP a managed table, the data files are deleted too
DROP TABLE prod_catalog.silver.customers;
-- Data is gone (can be recovered via UNDROP within retention period)

External Tables

-- External table: you specify the storage location
CREATE TABLE prod_catalog.bronze.raw_orders (
  order_id BIGINT,
  customer_id BIGINT,
  amount DECIMAL(10,2),
  order_date DATE
)
LOCATION 'abfss://raw-data@storageaccount.dfs.core.windows.net/orders/'
COMMENT 'Raw orders from source system -- external location';

-- When you DROP an external table, only the metadata is removed
-- The data files in the external location remain untouched
DROP TABLE prod_catalog.bronze.raw_orders;
-- Metadata gone, but files still exist in storage

Managed vs External Decision

Use MANAGED tables when:
  - Unity Catalog should handle storage lifecycle (create, delete, optimize)
  - You want the simplest setup (no external location management)
  - Tables are created by your Databricks pipelines
  - You want automatic VACUUM and optimization

Use EXTERNAL tables when:
  - Data is produced by systems outside Databricks (Spark on EMR, Snowflake, Kafka)
  - You need data to persist even if the table metadata is dropped
  - Multiple systems need to read the same storage location
  - Regulatory requirements dictate specific storage locations
  - You want full control over file retention and cleanup

Views — Virtual Tables

Views are saved SQL queries that appear as virtual tables. They do not store data — they execute the underlying query each time they are accessed.

-- Standard view
CREATE VIEW prod_catalog.gold.active_customers AS
  SELECT customer_id, customer_name, email, region
  FROM prod_catalog.silver.customers
  WHERE is_active = TRUE;

-- View with complex logic
CREATE VIEW prod_catalog.gold.customer_lifetime_value AS
  SELECT
    c.customer_id,
    c.customer_name,
    c.region,
    COUNT(o.order_id) AS total_orders,
    SUM(o.amount) AS lifetime_revenue,
    AVG(o.amount) AS avg_order_value,
    DATEDIFF(CURRENT_DATE(), MIN(o.order_date)) AS days_since_first_order
  FROM prod_catalog.silver.customers c
  LEFT JOIN prod_catalog.silver.orders o ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, c.customer_name, c.region;

-- Dynamic view (row-level security)
CREATE VIEW prod_catalog.gold.orders_by_region AS
  SELECT * FROM prod_catalog.silver.orders
  WHERE region = CASE
    WHEN IS_MEMBER('ontario_team') THEN 'Ontario'
    WHEN IS_MEMBER('quebec_team') THEN 'Quebec'
    WHEN IS_MEMBER('admin_team') THEN region  -- Admins see all
    ELSE NULL  -- No access
  END;

View types in Unity Catalog:

  Standard View    -- SQL query executed on every access
  Dynamic View     -- Includes row/column-level security logic
  Materialized View -- Precomputed results, auto-refreshed (like a cached table)

Volumes — Unstructured File Storage

Volumes are Unity Catalog’s way of governing unstructured files (CSV, JSON, images, PDFs, ML model artifacts). They provide the same three-level namespace, permissions, and lineage that tables have — but for files instead of rows and columns.

Analogy — A governed file cabinet. Before volumes, files in DBFS were like papers stuffed into an ungoverned filing cabinet — anyone could read, write, or delete anything. Volumes are labeled, locked filing drawers (governed by Unity Catalog) — each drawer has an owner, access permissions, and an audit trail of who opened it.

-- Managed volume: Unity Catalog manages the storage
CREATE VOLUME prod_catalog.bronze.raw_files
  COMMENT 'Raw CSV and JSON files from vendors';

-- External volume: you specify the storage location
CREATE EXTERNAL VOLUME prod_catalog.bronze.vendor_uploads
  LOCATION 'abfss://raw-data@storageaccount.dfs.core.windows.net/vendor-uploads/'
  COMMENT 'Vendor file upload location';

-- List files in a volume
LIST '/Volumes/prod_catalog/bronze/raw_files/';

-- Upload files to a volume (via dbutils or UI)
-- dbutils.fs.cp('file:/tmp/orders.csv', '/Volumes/prod_catalog/bronze/raw_files/orders.csv')

-- Read files from a volume
df = spark.read.csv('/Volumes/prod_catalog/bronze/raw_files/orders.csv', header=True)

-- Write files to a volume
df.write.parquet('/Volumes/prod_catalog/silver/processed_files/orders/')

-- Drop a managed volume (deletes files)
DROP VOLUME prod_catalog.bronze.raw_files;

-- Drop an external volume (files remain in storage)
DROP VOLUME prod_catalog.bronze.vendor_uploads;

Volumes vs DBFS

DBFS (legacy):
  - No governance (anyone with cluster access can read/write)
  - No lineage tracking
  - Paths like: dbfs:/mnt/raw-data/orders.csv
  - Being deprecated in favor of Volumes

Volumes (Unity Catalog):
  - Full governance (permissions, audit, lineage)
  - Three-level namespace: /Volumes/catalog/schema/volume/path
  - Managed or external storage
  - Recommended for all new projects

Migration path:
  dbfs:/mnt/raw-data/orders.csv
  -> /Volumes/prod_catalog/bronze/raw_files/orders.csv

Functions — Reusable Logic

Functions (UDFs) registered in Unity Catalog are governed, versioned, and shareable. They follow the three-level namespace: catalog.schema.function_name.

-- SQL scalar function
CREATE FUNCTION prod_catalog.silver.clean_email(email STRING)
  RETURNS STRING
  COMMENT 'Standardize email: lowercase, trim whitespace'
  RETURN LOWER(TRIM(email));

-- Use the function
SELECT clean_email('  Naveen@DriveDataScience.COM  ');
-- Returns: naveen@drivedatascience.com

-- SQL table-valued function
CREATE FUNCTION prod_catalog.gold.get_top_customers(min_revenue DECIMAL(10,2))
  RETURNS TABLE (customer_id BIGINT, customer_name STRING, total_revenue DECIMAL(10,2))
  COMMENT 'Returns customers with revenue above threshold'
  RETURN
    SELECT customer_id, customer_name, SUM(amount) AS total_revenue
    FROM prod_catalog.silver.orders o
    JOIN prod_catalog.silver.customers c USING (customer_id)
    GROUP BY customer_id, customer_name
    HAVING SUM(amount) >= min_revenue
    ORDER BY total_revenue DESC;

-- Use the table function
SELECT * FROM get_top_customers(10000.00);

-- Python UDF (registered in Unity Catalog)
CREATE FUNCTION prod_catalog.silver.extract_domain(url STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  COMMENT 'Extract domain from URL'
  AS $$
    from urllib.parse import urlparse
    if url is None:
      return None
    parsed = urlparse(url)
    return parsed.netloc
  $$;

-- List functions
SHOW FUNCTIONS IN prod_catalog.silver;

-- Describe a function
DESCRIBE FUNCTION prod_catalog.silver.clean_email;

Why register functions in Unity Catalog:

  Before (unregistered UDFs):
    - Defined in a notebook, available only in that session
    - Lost when the cluster restarts
    - No access control, no versioning, no lineage

  After (Unity Catalog functions):
    - Persistent -- survives cluster restarts and notebook sessions
    - Governed -- permissions control who can execute
    - Discoverable -- appears in Catalog Explorer, searchable
    - Shared -- any notebook or SQL query can reference it
    - Lineage tracked -- Unity Catalog knows which tables it reads

Models — Registered ML Models

Unity Catalog serves as a model registry for ML models trained with MLflow. Models follow the same three-level namespace and governance as tables.

import mlflow

# Set the registry URI to Unity Catalog
mlflow.set_registry_uri("databricks-uc")

# Register a model
model_name = "prod_catalog.ml_models.churn_predictor"

with mlflow.start_run():
    # Train your model...
    mlflow.sklearn.log_model(
        sk_model=trained_model,
        artifact_path="model",
        registered_model_name=model_name
    )

-- List registered models
SHOW MODELS IN prod_catalog.ml_models;

-- Describe a model
DESCRIBE MODEL prod_catalog.ml_models.churn_predictor;

-- Grant access to a model
GRANT EXECUTE ON MODEL prod_catalog.ml_models.churn_predictor TO `data-science-team`;

-- Use a model for inference (SQL)
SELECT
  customer_id,
  prod_catalog.ml_models.churn_predictor(features) AS churn_probability
FROM prod_catalog.gold.customer_features;

Storage Credentials and External Locations

These two objects work together to provide secure access to cloud storage from Unity Catalog.

Analogy — A master key and a registered address. A storage credential is a master key (IAM role or service principal) that can open doors in a cloud storage building. An external location is a registered address (a specific path in S3/ADLS/GCS) that says “this key works on this address.” You create the key once, register it with specific addresses, and then tables and volumes at those addresses use the key automatically.

-- Step 1: Create a storage credential (account-level, done by admin)
CREATE STORAGE CREDENTIAL az_storage_cred
  WITH AZURE_MANAGED_IDENTITY (
    ACCESS_CONNECTOR_ID = '/subscriptions/.../accessConnectors/unity-connector'
  )
  COMMENT 'Azure managed identity for production storage';

-- Step 2: Create an external location using the credential
CREATE EXTERNAL LOCATION prod_raw_location
  URL 'abfss://raw-data@storageaccount.dfs.core.windows.net/'
  WITH (STORAGE CREDENTIAL az_storage_cred)
  COMMENT 'Production raw data in ADLS Gen2';

-- Step 3: Create an external table at that location
CREATE TABLE prod_catalog.bronze.raw_orders
  LOCATION 'abfss://raw-data@storageaccount.dfs.core.windows.net/orders/';
-- Unity Catalog automatically uses the matching external location and credential

-- List storage credentials
SHOW STORAGE CREDENTIALS;

-- List external locations
SHOW EXTERNAL LOCATIONS;

-- Grant access to an external location
GRANT CREATE EXTERNAL TABLE ON EXTERNAL LOCATION prod_raw_location
  TO `data-engineering-team`;

GRANT CREATE EXTERNAL VOLUME ON EXTERNAL LOCATION prod_raw_location
  TO `data-engineering-team`;

Connections — Federated Query Access

Connections enable Lakehouse Federation — querying external databases (MySQL, PostgreSQL, SQL Server, Snowflake, BigQuery, Redshift) directly from Databricks without copying data.

-- Create a connection to an external PostgreSQL database
CREATE CONNECTION postgres_crm
  TYPE POSTGRESQL
  OPTIONS (
    host 'crm-db.company.com',
    port '5432',
    user 'readonly_user',
    password SECRET ('secret-scope', 'postgres-password')
  )
  COMMENT 'Read-only connection to CRM PostgreSQL database';

-- Create a foreign catalog from the connection
CREATE FOREIGN CATALOG crm_catalog
  USING CONNECTION postgres_crm
  OPTIONS (database 'crm_production');

-- Query the external database directly (federated query)
SELECT customer_id, customer_name, email
FROM crm_catalog.public.customers
WHERE region = 'Ontario';

-- Join external data with local data (no copying)
SELECT
  local.order_id,
  remote.customer_name,
  local.amount
FROM prod_catalog.silver.orders local
JOIN crm_catalog.public.customers remote
  ON local.customer_id = remote.customer_id;

Shares and Recipients — Delta Sharing

Delta Sharing is an open protocol for secure data sharing across organizations, clouds, and platforms. Unity Catalog manages shares (what to share) and recipients (who to share with).

-- Create a share
CREATE SHARE revenue_share
  COMMENT 'Monthly revenue data for partner analytics';

-- Add tables to the share
ALTER SHARE revenue_share ADD TABLE prod_catalog.gold.revenue_monthly;
ALTER SHARE revenue_share ADD TABLE prod_catalog.gold.customer_summary;

-- Create a recipient (the consumer)
CREATE RECIPIENT partner_analytics
  COMMENT 'Partner company analytics team';

-- Grant the share to the recipient
GRANT SELECT ON SHARE revenue_share TO RECIPIENT partner_analytics;

-- The recipient gets an activation link or token
-- They use it with any Delta Sharing client (Spark, pandas, Power BI, Tableau)

-- List shares
SHOW SHARES;

-- List recipients
SHOW RECIPIENTS;

Delta Sharing key points:
  - Open protocol -- consumers do not need Databricks
  - Consumers use pandas, Spark, Power BI, Tableau, or any Delta Sharing client
  - Data is read directly from your storage (zero-copy)
  - Provider controls exactly what tables/views are shared
  - Audit trail tracks all consumer access
  - Shares can include views with row-level security

Privileges and Access Control

Every securable object in Unity Catalog has an owner (full control) and can have privileges granted to users, groups, or service principals. Privileges are inherited: granting USE CATALOG on a catalog grants implicit access to use schemas within it.

-- Grant catalog-level access
GRANT USE CATALOG ON CATALOG prod_catalog TO `data-analyst-team`;

-- Grant schema-level access
GRANT USE SCHEMA ON SCHEMA prod_catalog.gold TO `data-analyst-team`;

-- Grant table-level access
GRANT SELECT ON TABLE prod_catalog.gold.revenue_monthly TO `data-analyst-team`;

-- Grant all tables in a schema (current and future)
GRANT SELECT ON ALL TABLES IN SCHEMA prod_catalog.gold TO `data-analyst-team`;

-- Grant volume access
GRANT READ VOLUME ON VOLUME prod_catalog.bronze.raw_files TO `data-engineering-team`;
GRANT WRITE VOLUME ON VOLUME prod_catalog.bronze.raw_files TO `data-engineering-team`;

-- Grant function access
GRANT EXECUTE ON FUNCTION prod_catalog.silver.clean_email TO `data-analyst-team`;

-- Grant model access
GRANT EXECUTE ON MODEL prod_catalog.ml_models.churn_predictor TO `data-science-team`;

-- Revoke access
REVOKE SELECT ON TABLE prod_catalog.gold.revenue_monthly FROM `intern-team`;

-- Show grants on an object
SHOW GRANTS ON TABLE prod_catalog.gold.revenue_monthly;

-- Show grants for a principal
SHOW GRANTS TO `data-analyst-team`;

Privilege Summary by Object Type

Lineage — Tracking Data Flow

Unity Catalog automatically tracks column-level lineage — which tables, views, notebooks, and jobs read from and write to each object. Lineage requires no configuration — it is captured automatically when queries run.

Lineage in Unity Catalog:

  What is tracked:
    - Table-to-table dependencies (notebook reads table A, writes table B)
    - Column-level lineage (column X in table B comes from column Y in table A)
    - Notebook and job references (which notebooks access which tables)
    - Dashboard dependencies (which dashboards use which tables)

  Where to view lineage:
    - Catalog Explorer -> select a table -> Lineage tab
    - Shows upstream (where data comes from) and downstream (what depends on this)

  Why lineage matters:
    - Impact analysis: "If I change column X, what breaks?"
    - Root cause analysis: "Dashboard shows wrong numbers -- where did the data come from?"
    - Compliance: "Show me every table that contains PII data and every job that touches it"
    - Documentation: auto-generated data flow diagrams

Legacy Hive Metastore vs Unity Catalog

Before Unity Catalog (pre-2022), Databricks used the Hive Metastore (HMS) — a workspace-local metadata store inherited from Apache Hive. Understanding the differences is critical because many organizations are still migrating from HMS to Unity Catalog, and the hive_metastore catalog still exists for backward compatibility.

Analogy — A local phone book vs a centralized directory. The Hive Metastore is like each office having its own phone book — workspace A has its own list of tables, workspace B has a separate list, and there is no way to share entries or enforce consistent rules. Unity Catalog is a centralized corporate directory — one list that all offices share, with consistent access control, and a single admin team managing it.

Hive Metastore (legacy):
  - One metastore per workspace (not shared across workspaces)
  - Two-level namespace: database.table (e.g., default.orders)
  - No fine-grained access control (cluster-level only)
  - No lineage tracking
  - No data sharing (Delta Sharing not available)
  - Tables stored in DBFS or external locations (no governance)
  - No Volumes, Functions, or Models governance
  - Configured via cluster spark.sql.hive settings

Unity Catalog:
  - One metastore per region (shared across workspaces)
  - Three-level namespace: catalog.schema.table
  - Row/column-level access control with privilege inheritance
  - Automatic lineage tracking
  - Delta Sharing built in
  - Tables, Volumes, Functions, Models all governed
  - Configured at the account level (no cluster config needed)

The hive_metastore Catalog

When Unity Catalog is enabled on a workspace that previously used HMS, all existing HMS tables appear under a special catalog called hive_metastore. This provides backward compatibility — old code that references database.table still works because Databricks maps it to hive_metastore.database.table.

-- Old code (HMS style -- still works)
SELECT * FROM default.orders;
-- Internally maps to: hive_metastore.default.orders

-- New code (Unity Catalog style -- recommended)
SELECT * FROM prod_catalog.silver.orders;

-- List tables in the legacy metastore
SHOW TABLES IN hive_metastore.default;

-- Check if a table is in HMS or Unity Catalog
DESCRIBE EXTENDED hive_metastore.default.orders;
-- Look for "Catalog" field -- if it says "hive_metastore", it is legacy

Migrating from Hive Metastore to Unity Catalog

Migration approaches:

  Option 1: CTAS (Create Table As Select) -- copy data
    CREATE TABLE prod_catalog.silver.orders AS
    SELECT * FROM hive_metastore.default.orders;
    -- Creates a new managed table in Unity Catalog
    -- Data is copied to Unity Catalog managed storage
    -- Old HMS table remains (drop it after validation)

  Option 2: SYNC (metadata-only for external tables)
    -- For external tables that point to existing storage:
    -- Register them in Unity Catalog without moving data
    CREATE TABLE prod_catalog.bronze.raw_orders
    LOCATION 'abfss://data@storage.dfs.core.windows.net/orders/'
    AS SELECT * FROM hive_metastore.bronze.raw_orders WHERE 1=0;
    -- Then: INSERT INTO prod_catalog.bronze.raw_orders
    --       SELECT * FROM hive_metastore.bronze.raw_orders;

  Option 3: UCX (Unity Catalog Migration Tool)
    -- Open-source tool from Databricks Labs
    -- Scans all HMS tables, groups, permissions
    -- Generates migration scripts automatically
    -- Handles table upgrades, permission mapping, code migration
    -- Recommended for large-scale migrations (100+ tables)

Migration checklist:
  1. Inventory all HMS tables (SHOW TABLES IN hive_metastore.*)
  2. Identify managed vs external tables
  3. Create Unity Catalog structure (catalogs, schemas)
  4. Migrate tables (CTAS for managed, register for external)
  5. Update all notebooks/jobs to use three-level namespace
  6. Update cluster configs to remove HMS settings
  7. Validate data and permissions
  8. Deprecate hive_metastore references

Designing a Production Unity Catalog Structure

Recommended production structure:

  Metastore (one per region)
    |
    ├── dev_catalog
    |     ├── bronze (raw ingested data)
    |     ├── silver (cleaned, validated)
    |     ├── gold (aggregated, business-ready)
    |     └── sandbox (individual developer experiments)
    |
    ├── staging_catalog
    |     ├── bronze
    |     ├── silver
    |     └── gold
    |
    ├── prod_catalog
    |     ├── bronze
    |     |     ├── Tables: raw_orders, raw_customers, raw_products
    |     |     └── Volumes: raw_files (vendor CSV uploads)
    |     ├── silver
    |     |     ├── Tables: orders_clean, customers_clean
    |     |     └── Functions: clean_email(), parse_address()
    |     ├── gold
    |     |     ├── Tables: revenue_daily, customer_ltv
    |     |     └── Views: active_customers, top_products
    |     ├── ml_models
    |     |     └── Models: churn_predictor, demand_forecaster
    |     └── metadata
    |           └── Tables: pipeline_log, quality_results
    |
    ├── Storage Credentials: az_storage_cred, aws_s3_cred
    ├── External Locations: prod_raw_location, prod_archive_location
    ├── Connections: crm_postgres, erp_sqlserver
    └── Shares: partner_revenue_share, marketing_share

  Access control:
    data-engineering-team  -> USE CATALOG on all, MODIFY on bronze/silver
    data-analyst-team      -> USE CATALOG on prod, SELECT on gold only
    data-science-team      -> SELECT on silver/gold, EXECUTE on models
    etl-service-principal  -> MODIFY on bronze/silver, EXECUTE on functions

Common Mistakes

1. Not using the three-level namespace in code. Writing SELECT * FROM orders depends on the session’s current catalog and schema. If someone changes the context, the query reads from the wrong table. Always use prod_catalog.silver.orders in production code, notebooks, and scheduled jobs.

2. Granting privileges directly to users instead of groups. When a user changes teams, you must manually revoke and re-grant. Grant to groups (data-engineering-team, data-analyst-team), then add/remove users from groups. This is the same principle as Snowflake RBAC.

3. Using DBFS instead of Volumes for file storage. DBFS has no governance, no lineage, and is being deprecated. Volumes provide three-level namespace, permissions, and audit logging. Migrate file paths from dbfs:/mnt/... to /Volumes/catalog/schema/volume/....

4. Creating all tables as external. External tables require managing external locations and storage credentials. If your data is produced entirely within Databricks, use managed tables — simpler, auto-optimized, and fully lifecycle-managed by Unity Catalog. Use external tables only when data is shared with other engines or produced outside Databricks.

5. Not granting USE CATALOG and USE SCHEMA. A user with SELECT on a table but no USE CATALOG or USE SCHEMA cannot access the table. The privilege chain is: USE CATALOG -> USE SCHEMA -> SELECT on table. Missing any link breaks access.

6. Forgetting to set ownership on catalogs and schemas. By default, the creator is the owner. If a single engineer creates everything, they become a bottleneck. Transfer ownership to a team group: ALTER CATALOG prod_catalog OWNER TO data-platform-team.

7. Not leveraging lineage for impact analysis. Before changing a table schema (renaming a column, changing a type), check lineage to see what depends on that table. Catalog Explorer -> select the table -> Lineage tab shows every downstream table, view, notebook, and dashboard that would break.

8. Registering functions only in notebooks instead of Unity Catalog. Notebook-local UDFs disappear when the session ends. Register production functions in Unity Catalog so they persist, are governed, and are callable from any notebook or SQL query.

Interview Questions

Q: What is the Unity Catalog object hierarchy? A: Unity Catalog uses a hierarchical structure. At the top is the metastore (one per region), which contains catalogs. Catalogs contain schemas. Schemas contain data assets: tables, views, volumes, functions, and models. All data assets follow a three-level namespace: catalog.schema.object. Additional objects like storage credentials, external locations, connections, shares, and recipients exist directly under the metastore at the account level.

Q: What is the difference between managed and external tables in Unity Catalog? A: Managed tables have their storage lifecycle controlled by Unity Catalog — dropping a managed table deletes the underlying data files. External tables point to a storage location you manage — dropping an external table removes only the metadata, leaving files intact. Use managed tables when Databricks is the primary engine and you want simplicity. Use external tables when data is shared with other engines, produced outside Databricks, or must persist independently of the table metadata.

Q: What are Volumes and how do they differ from DBFS? A: Volumes are Unity Catalog’s governed storage for unstructured files (CSV, JSON, images, PDFs, ML artifacts). They follow the three-level namespace (catalog.schema.volume), support permissions, and track lineage. DBFS is the legacy file system with no governance — anyone with cluster access can read and write files. Volumes replace DBFS for all new projects, providing the same access control and audit capabilities that tables have.

Q: How do storage credentials and external locations work together? A: A storage credential stores the authentication mechanism (Azure managed identity, AWS IAM role, or GCS service account) for accessing cloud storage. An external location maps a specific cloud storage path to a storage credential. When you create an external table or external volume at a path, Unity Catalog automatically finds the matching external location and uses its credential for access. This eliminates hardcoding credentials in notebooks or cluster configurations.

Q: What is Lakehouse Federation and how do Connections enable it? A: Lakehouse Federation allows querying external databases (MySQL, PostgreSQL, SQL Server, Snowflake, BigQuery) directly from Databricks without copying data. A Connection object stores the external database’s connection details and credentials. A foreign catalog created from a Connection exposes the external database’s schemas and tables in Unity Catalog’s namespace. Queries on foreign catalogs are pushed down to the external database engine, and results are returned to Databricks.

Q: How does Delta Sharing work in Unity Catalog? A: Delta Sharing is an open protocol for sharing data across organizations. The provider creates a Share object and adds specific tables and views. The provider then creates Recipients representing consumers and grants them access to the share. Consumers receive an activation link and can read shared data using any Delta Sharing client — pandas, Spark, Power BI, or Tableau. Data is read directly from the provider’s storage (zero-copy). The provider has full control over what is shared and can revoke access at any time.

Q: How should you design a Unity Catalog structure for a production data platform? A: Use catalogs for environment separation (dev, staging, prod). Within each catalog, use schemas for data layers (bronze, silver, gold, metadata) or business domains (orders, customers). Place raw files in volumes within the bronze schema. Register reusable transformation logic as functions in the silver schema. Register ML models in a dedicated ml_models schema. Use storage credentials and external locations for any data outside Databricks. Grant privileges to groups, not individual users. Set ownership on catalogs and schemas to team groups. And always use the full three-level namespace in production code.

Wrapping Up

Unity Catalog governs every data and AI asset in Databricks through a single hierarchical model. Tables, views, volumes, functions, and models follow the three-level namespace and inherit permissions from their parent catalog and schema. Storage credentials and external locations secure cloud storage access. Connections enable federated queries. Delta Sharing enables cross-organization collaboration. And lineage tracks data flow automatically.

The key insight: Unity Catalog is not just a metastore — it is a complete governance platform that applies the same permission model, ownership, lineage, and audit trail to structured data (tables), unstructured files (volumes), business logic (functions), ML models, external databases (connections), and shared data (shares). Everything in one hierarchy, one permission model, one audit trail.

Related posts: – Unity Catalog Deep Dive – Managed vs External Tables – File Storage (Volumes, DBFS) – Databricks Notebooks – Snowflake Account Setup & RBAC

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.