Unity Catalog Admin & Security Cheatsheet (SQL)

1) Core & Exploration Commands 🗺️

These are the commands you'll use constantly for navigation and discovery.

Set Current Context

-- Set the active catalog and schema for the current session
USE CATALOG my_catalog;
USE SCHEMA my_schema;

List Objects

-- Discover what exists
SHOW CATALOGS;
SHOW SCHEMAS IN my_catalog;
SHOW TABLES IN my_schema;
SHOW VOLUMES IN my_schema;
SHOW FUNCTIONS IN my_schema;

Describe Objects & History

-- Get detailed metadata for a table
DESCRIBE TABLE EXTENDED my_schema.my_table;

-- View Delta Lake transaction history (time travel, versioning)
DESCRIBE HISTORY my_schema.my_table;

-- See object ownership and properties
DESCRIBE CATALOG EXTENDED my_catalog;
DESCRIBE SCHEMA EXTENDED my_schema;

2) Object Management & Modification 🏗️

Creating, altering, and dropping the core building blocks of Unity Catalog.

Creating Objects

-- Create a new catalog and schema structure
CREATE CATALOG IF NOT EXISTS dev_catalog;
CREATE SCHEMA IF NOT EXISTS dev_catalog.bronze;

-- Best practice: Add comments during creation or after
COMMENT ON CATALOG dev_catalog IS 'Catalog for development purposes';
COMMENT ON SCHEMA dev_catalog.bronze IS 'Schema for raw, unprocessed data';

-- Managed Table (UC controls data and metadata)
CREATE TABLE my_catalog.my_schema.managed_table (id INT, event_date DATE);

-- External Table (UC controls metadata, data lives in your cloud storage)
CREATE TABLE my_catalog.my_schema.external_table (id INT, event_date DATE)
LOCATION 's3://my-bucket/path/to/data';

Modifying & Renaming Objects

-- Rename a table
ALTER TABLE my_catalog.my_schema.old_name RENAME TO my_catalog.my_schema.new_name;

-- Add, drop, or alter a column
ALTER TABLE my_catalog.my_schema.my_table ADD COLUMN new_col STRING;
ALTER TABLE my_catalog.my_schema.my_table DROP COLUMN old_col;
ALTER TABLE my_catalog.my_schema.my_table ALTER COLUMN existing_col SET NOT NULL;

Dropping Objects (RESTRICT vs. CASCADE)

-- Default behavior is RESTRICT: fails if the object is not empty
DROP SCHEMA my_catalog.my_schema RESTRICT; -- Fails if schema contains tables
DROP CATALOG my_catalog RESTRICT; -- Fails if catalog contains schemas

-- Use CASCADE to forcefully drop an object AND all objects within it
DROP SCHEMA my_catalog.my_schema CASCADE; -- Deletes the schema and all its tables/views
DROP CATALOG my_catalog CASCADE; -- Deletes the catalog and all its schemas/tables

-- Dropping other objects
DROP TABLE my_catalog.my_schema.my_table;
DROP VOLUME my_catalog.my_schema.my_volume;

Recover a Dropped Table (Time Travel)

-- Dropped managed tables can be recovered within a 7-day retention period
-- Step 1: Find the version right before the drop
DESCRIBE HISTORY my_catalog.my_schema.my_dropped_table;

-- Step 2: Restore the table to that version
UNDROP TABLE my_catalog.my_schema.my_dropped_table;
-- For older versions: RESTORE TABLE my_catalog.my_schema.my_table TO VERSION AS OF pre_drop_version;

3) Permissions & Ownership 🔐

The most common grant patterns for data engineers, analysts, and service principals.

Engineer Permissions (Full Control on a Schema)

-- Engineers can manage a specific schema
GRANT USAGE ON CATALOG my_catalog TO `data-engineers`;
GRANT ALL PRIVILEGES ON SCHEMA my_catalog.my_schema TO `data-engineers`;
ALTER SCHEMA my_catalog.my_schema OWNER TO `data-engineers`;

Analyst Permissions (Read-Only)

-- Analysts get read-only access to a schema (e.g., gold layer)
GRANT USAGE ON CATALOG my_catalog TO `data-analysts`;
GRANT USAGE ON SCHEMA my_catalog.gold TO `data-analysts`;
GRANT SELECT ON SCHEMA my_catalog.gold TO `data-analysts`;

Check Permissions

-- Debug access issues by checking grants on an object
SHOW GRANTS ON TABLE my_catalog.my_schema.my_table;
SHOW GRANTS `user@company.com` ON SCHEMA my_catalog.my_schema;

4) External Locations & Volumes 📦

Connecting Unity Catalog to your cloud storage for ETL and file management.

Create Storage Credential & External Location

-- Step 1: Create a credential to access cloud storage (Metastore Admin)
CREATE STORAGE CREDENTIAL s3_access_credential
  WITH (AWS_IAM_ROLE = 'arn:aws:iam::12345:role/uc-role')
  COMMENT 'Credential to access our S3 data lake';

-- Step 2: Create a location using the credential
CREATE EXTERNAL LOCATION s3_bronze_location
  URL 's3://my-bucket/bronze/'
  WITH (STORAGE CREDENTIAL s3_access_credential);

Manage Non-Tabular Data with Volumes

-- Create a volume for storing files like models, images, or configs
CREATE VOLUME IF NOT EXISTS my_catalog.my_schema.ml_artifacts;

-- Files are accessed via their path: /Volumes/my_catalog/my_schema/ml_artifacts/model.pkl
-- In PySpark: spark.read.format("binaryFile").load("/Volumes/my_catalog/my_schema/ml_artifacts/*")

Syncing Partitions for External Tables

-- For non-Delta external tables, discover partitions added directly to cloud storage
MSCK REPAIR TABLE my_catalog.my_schema.external_partitioned_table;

-- List partitions of a table
SHOW PARTITIONS my_catalog.my_schema.external_partitioned_table;

5) Data Governance (Lineage, Tags, Masking) 🏛️

Leveraging built-in UC features for tracking and securing data.

Explore Data Lineage

-- Find tables upstream of a given table (how was it built?)
SELECT * FROM system.information_schema.table_lineage
WHERE sink_table_catalog = 'my_catalog' AND sink_table_name = 'agg_metrics';

-- Find tables downstream of a given table (what does this table impact?)
SELECT * FROM system.information_schema.table_lineage
WHERE source_table_name = 'raw_events';

Tag Objects for Classification

-- Apply tags to a table or column for governance
ALTER TABLE my_catalog.silver.customers SET TAGS ('PII' = 'true', 'data_owner' = 'finance_team');
ALTER TABLE my_catalog.silver.customers ALTER COLUMN ssn SET TAGS ('classification' = 'sensitive');

-- Find all tables tagged as PII
SELECT catalog_name, schema_name, table_name
FROM system.information_schema.table_tags
WHERE tag_name = 'PII' AND tag_value = 'true';

Simple Column Masking (Secure View Pattern)

-- A common pattern for PII masking without complex UDFs
CREATE OR REPLACE VIEW my_catalog.gold.vw_customers_redacted AS
SELECT
  customer_id,
  CASE
    WHEN is_member('pii-readers-group') THEN email
    ELSE 'REDACTED'
  END AS email,
  country
FROM my_catalog.silver.customers;

-- Then grant access to the view, not the underlying table
GRANT SELECT ON VIEW my_catalog.gold.vw_customers_redacted TO `data-analysts`;