-- List catalogs
SHOW CATALOGS;
SHOW CATALOGS LIKE 'prod*';

-- Describe catalog
DESCRIBE CATALOG EXTENDED <catalog>;

-- List schemas
SHOW SCHEMAS IN <catalog>;
SHOW SCHEMAS IN <catalog> LIKE 'sales*';

-- Describe schema
DESCRIBE SCHEMA EXTENDED <catalog>.<schema>;

-- Current context
SELECT current_catalog(), current_schema();

-- Set context
USE CATALOG <catalog>;
USE SCHEMA <schema>;

-- List tables
SHOW TABLES IN <catalog>.<schema>;
SHOW TABLES IN <catalog>.<schema> LIKE 'customer*';

-- Column definitions
DESCRIBE TABLE <catalog>.<schema>.<table>;

-- Full metadata (owner, location, properties)
DESCRIBE TABLE EXTENDED <catalog>.<schema>.<table>;

-- Delta details (size, partitions, files)
DESCRIBE DETAIL <catalog>.<schema>.<table>;

-- Table history (30-day retention)
DESCRIBE HISTORY <catalog>.<schema>.<table>;

-- Table properties and DDL
SHOW TBLPROPERTIES <catalog>.<schema>.<table>;
SHOW CREATE TABLE <catalog>.<schema>.<table>;

-- List views
SHOW VIEWS IN <catalog>.<schema>;

-- Row count
SELECT COUNT(*) FROM <catalog>.<schema>.<table>;

-- Sample data
SELECT * FROM <catalog>.<schema>.<table> LIMIT 10;

-- Random sample (1%)
SELECT * FROM <catalog>.<schema>.<table> TABLESAMPLE (1 PERCENT);

-- Compute statistics
ANALYZE TABLE <catalog>.<schema>.<table> COMPUTE STATISTICS FOR ALL COLUMNS;

-- List volumes
SHOW VOLUMES IN <catalog>.<schema>;

-- Describe volume
DESCRIBE VOLUME <catalog>.<schema>.<volume>;

-- List functions
SHOW USER FUNCTIONS IN <catalog>.<schema>;

-- Describe function
DESCRIBE FUNCTION EXTENDED <catalog>.<schema>.<function>;

# List files in volume
databricks fs ls /Volumes/<catalog>/<schema>/<volume>/ -o json

# Volume details
databricks volumes read <catalog>.<schema>.<volume> -o json

CATALOG (USE CATALOG, CREATE SCHEMA)
  └── SCHEMA (USE SCHEMA, CREATE TABLE/FUNCTION/VOLUME)
        ├── TABLE (SELECT, MODIFY, ALL PRIVILEGES)
        ├── VIEW (SELECT)
        ├── VOLUME (READ VOLUME, WRITE VOLUME)
        └── FUNCTION (EXECUTE)

-- On objects
SHOW GRANTS ON CATALOG <catalog>;
SHOW GRANTS ON SCHEMA <catalog>.<schema>;
SHOW GRANTS ON TABLE <catalog>.<schema>.<table>;
SHOW GRANTS ON VOLUME <catalog>.<schema>.<volume>;

-- To principal
SHOW GRANTS TO `user@example.com`;
SHOW GRANTS TO `group_name`;

-- Catalog access
GRANT USE CATALOG ON CATALOG <catalog> TO `user@example.com`;
GRANT CREATE SCHEMA ON CATALOG <catalog> TO `user@example.com`;

-- Schema access
GRANT USE SCHEMA ON SCHEMA <catalog>.<schema> TO `user@example.com`;
GRANT CREATE TABLE ON SCHEMA <catalog>.<schema> TO `user@example.com`;

-- Table access
GRANT SELECT ON TABLE <catalog>.<schema>.<table> TO `user@example.com`;
GRANT MODIFY ON TABLE <catalog>.<schema>.<table> TO `user@example.com`;
GRANT ALL PRIVILEGES ON TABLE <catalog>.<schema>.<table> TO `data_team`;

-- Revoke
REVOKE SELECT ON TABLE <catalog>.<schema>.<table> FROM `user@example.com`;

-- Transfer ownership
ALTER TABLE <catalog>.<schema>.<table> SET OWNER TO `new_owner@example.com`;
ALTER SCHEMA <catalog>.<schema> SET OWNER TO `new_owner@example.com`;
ALTER CATALOG <catalog> SET OWNER TO `new_owner@example.com`;

-- Query specific version
SELECT * FROM <catalog>.<schema>.<table> VERSION AS OF 5;

-- Query at timestamp
SELECT * FROM <catalog>.<schema>.<table> TIMESTAMP AS OF '2024-01-15 10:00:00';

-- Compare versions
SELECT * FROM <catalog>.<schema>.<table> VERSION AS OF 5
EXCEPT
SELECT * FROM <catalog>.<schema>.<table> VERSION AS OF 4;

-- Optimize (compact small files)
OPTIMIZE <catalog>.<schema>.<table>;

-- Optimize with Z-ordering
OPTIMIZE <catalog>.<schema>.<table> ZORDER BY (col1, col2);

-- Vacuum old files (default 7 days)
VACUUM <catalog>.<schema>.<table>;
VACUUM <catalog>.<schema>.<table> RETAIN 168 HOURS;

-- Tables metadata
SELECT table_name, table_type, created
FROM <catalog>.information_schema.tables
WHERE table_schema = '<schema>';

-- Column details
SELECT column_name, data_type, is_nullable
FROM <catalog>.information_schema.columns
WHERE table_schema = '<schema>' AND table_name = '<table>'
ORDER BY ordinal_position;

-- Find tables by column name
SELECT table_catalog, table_schema, table_name, column_name
FROM <catalog>.information_schema.columns
WHERE column_name LIKE '%customer%';

-- Join across catalogs
SELECT a.*, b.*
FROM catalog1.schema1.table1 a
JOIN catalog2.schema2.table2 b ON a.id = b.id;

-- Create view spanning catalogs
CREATE VIEW my_catalog.my_schema.combined AS
SELECT * FROM catalog1.schema.table
UNION ALL
SELECT * FROM catalog2.schema.table;

-- Upstream tables (sources feeding into this table)
SELECT source_table_full_name, target_table_full_name, event_time
FROM system.access.table_lineage
WHERE target_table_full_name = '<catalog>.<schema>.<table>'
ORDER BY event_time DESC;

-- Downstream tables (tables consuming this table)
SELECT source_table_full_name, target_table_full_name, event_time
FROM system.access.table_lineage
WHERE source_table_full_name = '<catalog>.<schema>.<table>'
ORDER BY event_time DESC;

-- Column-level lineage (source columns for a target column)
SELECT source_table_full_name, source_column_name, target_column_name
FROM system.access.column_lineage
WHERE target_table_full_name = '<catalog>.<schema>.<table>'
  AND target_column_name = '<column_name>';

# Table lineage (upstream and downstream)
databricks api get /api/2.1/unity-catalog/lineage/table-lineage \
  --json '{"table_name": "<catalog>.<schema>.<table>", "include_entity_lineage": true}'

# Column lineage
databricks api get /api/2.1/unity-catalog/lineage/column-lineage \
  --json '{"table_name": "<catalog>.<schema>.<table>", "column_name": "<column>"}'

-- Instead of ALL PRIVILEGES, grant specific permissions
GRANT SELECT ON TABLE <catalog>.<schema>.<table> TO `analytics_readers`;
GRANT SELECT, MODIFY ON TABLE <catalog>.<schema>.<table> TO `data_engineers`;

-- Audit permissions on a table
SHOW GRANTS ON TABLE <catalog>.<schema>.<table>;

-- Audit what a specific principal can access
SHOW GRANTS TO `user@example.com`;