Module 6: Unity Catalog & Data Governance

• Securable object: anything you can grant privileges on
• Metastore-level objects: storage credentials, external locations, connections, shares
• UC works across workspaces: one place for permissions, lineage and audit
• Enabled automatically for workspaces created after November 8, 2023

%sql
SHOW CATALOGS

display(spark.sql("SELECT current_metastore() AS metastore"))

%sql
SHOW SCHEMAS

%sql
SHOW TABLES IN dbx_course

display(spark.sql(f"""
    SELECT table_name, table_type, data_source_format
    FROM {catalog}.information_schema.tables
    WHERE table_schema = 'dbx_course'
"""))

• information_schema exists in every catalog: tables, columns, schemata, volumes, privileges
• Create a schema for experiments:

%sql
CREATE SCHEMA IF NOT EXISTS sandbox COMMENT 'Throwaway experiments'

• Managed storage can be set per metastore, catalog or schema. Objects use the closest one
• Managed tables are the default and recommended type
• External tables and volumes: UC governs access, you manage the files and their lifecycle
• Pick external only when other tools must write the same files, or the data must stay where it is

• Storage credential: the cloud identity (IAM role, managed identity, service account) UC uses
• External location: a path plus the credential that opens it
• Privileges: CREATE EXTERNAL TABLE, READ FILES, WRITE FILES on the location
• Paths of external locations can't overlap in confusing ways: plan the layout up front

%sql
CREATE VOLUME IF NOT EXISTS sandbox.files

path = f"/Volumes/{catalog}/sandbox/files"
with open(f"{path}/note.txt", "w") as f:
    f.write("hello volume\n")

print(open(f"{path}/note.txt").read())
display(dbutils.fs.ls(path))

• Path: /Volumes/<catalog>/<schema>/<volume>/.... Works with Spark, dbutils.fs, os and open
• Privileges: READ VOLUME, WRITE VOLUME
• Managed volume: UC owns the storage. External volume: points at a location you registered
• Use volumes for files (CSV, JSON, images, wheels, checkpoints). Use tables for tabular data

%sql
DESCRIBE VOLUME sandbox.files

• Grant to groups, not individuals. People join and leave groups
• Run production as a service principal, so jobs don't depend on one person's account
• Identities are managed at the account level and assigned to workspaces
• Built-in groups: account users (everyone), admins (workspace admins)

%sql
SHOW GRANTS ON SCHEMA dbx_course

%sql
GRANT USE SCHEMA, SELECT ON SCHEMA dbx_course TO `account users`

%sql
REVOKE SELECT ON SCHEMA dbx_course FROM `account users`

• Every object has an owner with all privileges. Transfer: ALTER TABLE t OWNER TO ...
• REVOKE succeeds even if the privilege wasn't granted

Column mask (hide customer names from everyone except a group):

%sql
CREATE OR REPLACE TABLE customers_masked AS SELECT * FROM customers

%sql
CREATE OR REPLACE FUNCTION mask_name(name STRING)
RETURNS STRING
RETURN CASE WHEN is_account_group_member('pii_readers') THEN name ELSE '***' END

%sql
ALTER TABLE customers_masked ALTER COLUMN name SET MASK mask_name

%sql
SELECT customer_id, name, country FROM customers_masked LIMIT 3

• You're not in pii_readers, so name shows ***

Row filter (only show Dutch customers to everyone not in a group):

%sql
CREATE OR REPLACE FUNCTION nl_only(country STRING)
RETURNS BOOLEAN
RETURN country = 'NL' OR is_account_group_member('all_countries')

%sql
ALTER TABLE customers_masked SET ROW FILTER nl_only ON (country)

Remove both:

%sql
ALTER TABLE customers_masked DROP ROW FILTER

%sql
ALTER TABLE customers_masked ALTER COLUMN name DROP MASK

• UC records lineage from queries run on UC-enabled compute: tables, views, columns, notebooks, jobs, pipelines
• See it in Catalog Explorer: open a table, then the Lineage tab
• Try: create a derived table and look at its lineage

spark.sql("""
  CREATE OR REPLACE TABLE revenue_by_category AS
  SELECT p.category, SUM(o.quantity * p.unit_price) AS revenue
  FROM orders o JOIN products p USING (product_id)
  GROUP BY p.category
""")

• Lineage can take a few minutes to appear
• Uses: impact analysis before a change, debugging a bad number, compliance questions
• Queryable through system tables and the lineage API where enabled

%sql
COMMENT ON TABLE orders IS 'One row per order. Orders can include duplicates in the raw layer.'

%sql
ALTER TABLE customers SET TAGS ('domain' = 'sales', 'contains_pii' = 'true')

%sql
ALTER TABLE customers ALTER COLUMN name SET TAGS ('pii' = 'name')

display(spark.sql(f"""
    SELECT * FROM {catalog}.information_schema.table_tags
    WHERE schema_name = 'dbx_course'
"""))

• Find assets with the search bar and Catalog Explorer filters (owner, tag, type)
• Comments (table and column) feed search and AI-generated descriptions
• Tags drive policies: classify once, apply masks and access rules by tag
• Automatic data classification can detect sensitive data types and tag them. Availability depends on your workspace

• System tables live in the system catalog: system.access.audit, lineage tables, billing usage, query history
• A metastore admin enables each schema of system tables
• Typical questions: who read the customer table, who changed a grant, which jobs touch this data
• Retention and availability vary by system table: check the docs

SELECT event_time, user_identity.email, action_name
FROM system.access.audit
WHERE event_date >= current_date() - 7
ORDER BY event_time DESC
LIMIT 20

• Open protocol: recipients may be on Databricks (Databricks-to-Databricks) or not (pandas, Spark, Power BI, ...)
• Provider side: CREATE SHARE, ALTER SHARE ... ADD TABLE, CREATE RECIPIENT, GRANT SELECT ON SHARE
• Recipient side: mount the share as a catalog
• Control access like any UC object. Audit who reads what

• Sources include PostgreSQL, MySQL, SQL Server, Snowflake, BigQuery and others
• Queries run in place: no ingestion, no copy
• Fine for exploration and small lookups. For heavy or repeated use, ingest into Delta (Module 7)
• Governance (grants, lineage, audit) applies to foreign catalogs too

• One metastore per cloud region is the usual design. All workspaces in the region attach to it
• Same users, groups and grants everywhere. Bind a catalog to the workspaces that may use it
• Common layouts: catalog per environment (dev, prod), or per domain
• Share across regions or clouds with Delta Sharing