Unity Catalog & Data Governance

-- Create a catalog
CREATE CATALOG IF NOT EXISTS production;

-- Create schemas within the catalog
CREATE SCHEMA IF NOT EXISTS production.sales;
CREATE SCHEMA IF NOT EXISTS production.analytics;
CREATE SCHEMA IF NOT EXISTS production.ml_features;

-- Create a managed table
CREATE TABLE production.sales.orders (
    order_id BIGINT,
    customer_id BIGINT,
    product STRING,
    amount DECIMAL(12,2),
    order_date DATE
) USING DELTA
COMMENT 'Production order data from e-commerce platform';

-- Create a view
CREATE VIEW production.analytics.daily_revenue AS
SELECT order_date, SUM(amount) AS revenue
FROM production.sales.orders
GROUP BY order_date;

-- Set default catalog and schema
USE CATALOG production;
USE SCHEMA sales;

-- Now you can reference just by table name
SELECT * FROM orders LIMIT 10;

-- Grant catalog-level permissions
GRANT USE CATALOG ON CATALOG production TO `[email protected]`;
GRANT CREATE SCHEMA ON CATALOG production TO `[email protected]`;

-- Grant schema-level permissions
GRANT USE SCHEMA ON SCHEMA production.sales TO `[email protected]`;
GRANT SELECT ON SCHEMA production.sales TO `[email protected]`;

-- Grant table-level permissions
GRANT SELECT ON TABLE production.sales.orders TO `[email protected]`;
GRANT MODIFY ON TABLE production.sales.orders TO `etl-service-principal`;
GRANT ALL PRIVILEGES ON TABLE production.sales.orders TO `[email protected]`;

-- Revoke permissions
REVOKE SELECT ON TABLE production.sales.orders FROM `[email protected]`;

-- View grants on a table
SHOW GRANTS ON TABLE production.sales.orders;

-- View grants for a specific principal
SHOW GRANTS TO `[email protected]`;

-- Row-level security with row filters
ALTER TABLE production.sales.orders
SET ROW FILTER region_filter ON (region);

-- Column masking for sensitive data
ALTER TABLE production.sales.customers
ALTER COLUMN email SET MASK mask_email;

from databricks.sdk import WorkspaceClient

w = WorkspaceClient()

# Get lineage for a specific table
lineage = w.data_lineage.get_table_lineage(
    table_name="production.analytics.daily_revenue"
)

# Show upstream dependencies
print("Upstream tables:")
for dep in lineage.upstream_tables:
    print(f"  - {dep.catalog_name}.{dep.schema_name}.{dep.name}")

# Show downstream consumers
print("Downstream tables:")
for dep in lineage.downstream_tables:
    print(f"  - {dep.catalog_name}.{dep.schema_name}.{dep.name}")

# Column-level lineage
col_lineage = w.data_lineage.get_column_lineage(
    table_name="production.analytics.daily_revenue",
    column_name="revenue"
)
for col in col_lineage.upstream_columns:
    print(f"  revenue derived from: {col.table_name}.{col.name}")

-- Create a storage credential (links to cloud IAM)
CREATE STORAGE CREDENTIAL my_adls_credential
WITH MANAGED IDENTITY '<managed-identity-id>';

-- Create an external location
CREATE EXTERNAL LOCATION raw_data_lake
URL 'abfss://[email protected]/'
WITH (STORAGE CREDENTIAL my_adls_credential)
COMMENT 'Raw data lake in ADLS Gen2';

-- Grant access to the external location
GRANT READ FILES, WRITE FILES ON EXTERNAL LOCATION raw_data_lake
TO `[email protected]`;

-- Create an external table pointing to existing data
CREATE TABLE production.sales.legacy_orders
USING DELTA
LOCATION 'abfss://[email protected]/orders/';

-- List external locations
SHOW EXTERNAL LOCATIONS;

-- Create a share (a named collection of tables)
CREATE SHARE customer_analytics;

-- Add tables to the share
ALTER SHARE customer_analytics
ADD TABLE production.analytics.daily_revenue;

ALTER SHARE customer_analytics
ADD TABLE production.sales.orders
PARTITION (order_date >= '2024-01-01');  -- Share only recent data

-- Create a recipient (external organization)
CREATE RECIPIENT partner_company
USING ID 'partner-sharing-id';

-- Grant the share to the recipient
GRANT SELECT ON SHARE customer_analytics TO RECIPIENT partner_company;

-- View share details
SHOW ALL IN SHARE customer_analytics;

-- As a recipient: create a catalog from a share
CREATE CATALOG partner_data
USING SHARE provider_org.customer_analytics;