Lab 07: Unity Catalog & Governance SQL

Before we write code, let's understand our Goal: We have hundreds of tables and thousands of employees. We need a strict security system to ensure the Interns cannot accidentally delete the CEO's financial data.

The Tool: Unity Catalog (UC) is Databricks' centralized governance solution. It manages permissions, audits, and data lineage across every workspace in your company.

1. The Unity Catalog Hierarchy

Real-World Analogy Mapping: Imagine Unity Catalog is a massive Corporate Office Building.

Metastore (The Building): The top-level container for your company's data. You usually only have one per region.
Catalog (The Floor): A logical grouping of data. E.g., The "Finance Floor" or the "Marketing Floor".
Schema / Database (The Room): A specific room on the floor. E.g., The "Q1_Reports Room".
Table / Volume (The Filing Cabinet): The actual container holding the data files.

To find a file, you must specify the exact address: catalog.schema.table (e.g., finance_floor.q1_reports.revenue_table).

2. Core Architecture Setup (Metastore to Tables)

Anatomy Breakdown: Here is how an administrator physically builds the Corporate Office using standard SQL.

sql

-- 1. Create the Catalog (Building the Floor)
CREATE CATALOG IF NOT EXISTS enterprise_data;
USE CATALOG enterprise_data;

-- 2. Create the Schema (Building the Room)
CREATE SCHEMA IF NOT EXISTS finance_dept;
USE SCHEMA finance_dept;

-- 3. Create a Managed Table (Putting a filing cabinet in the room)
CREATE TABLE q1_revenue (id INT, amount DOUBLE);

3. External Locations and Volumes

What Existed Previously: If your company already had 50 Terabytes of images stored in an AWS S3 bucket, Databricks couldn't govern it easily without copying it all into Databricks storage.

How Present Technology Solves It: You can create an External Location. This tells Unity Catalog to act as a security guard for a bucket you already own on AWS/Azure, without moving the data.

sql

-- Map your existing S3 bucket to Unity Catalog using a secure IAM role
CREATE EXTERNAL LOCATION s3_finance_bucket 
  URL 's3://company-finance-bucket/' 
  WITH (STORAGE CREDENTIAL aws_finance_role);

-- Create an External Table pointing to that exact location
CREATE TABLE external_revenue (id INT, amount DOUBLE)
  LOCATION 's3://company-finance-bucket/revenue/';

Volumes: If you need to store raw, non-tabular files (like PDFs, MP4 videos, or JSON logs), you create a Volume instead of a Table.

sql

CREATE VOLUME raw_invoices;
-- You can now access files securely via path: /Volumes/enterprise_data/finance_dept/raw_invoices/

4. Role-Based Access Control (Grants)

The Smart Way: You do not assign permissions to individual people. You assign them to a group (e.g., finance-analysts-group), and then add people to that group via your company's Single Sign-On (Okta/Azure AD).

sql

-- Give the group a security badge to enter the Catalog floor
GRANT USE CATALOG ON CATALOG enterprise_data TO `finance-analysts-group`;

-- Give the group a key to open the Schema room and READ the tables inside
GRANT USE SCHEMA, SELECT ON SCHEMA finance_dept TO `finance-analysts-group`;

-- Immediately revoke access if needed
REVOKE SELECT ON TABLE q1_revenue FROM `interns-group`;

-- Check who has access to a specific table
SHOW GRANTS ON TABLE q1_revenue;

← Previous: Lab 06: Lakeflow & DLT (Bronze to Gold) | Next: Lab 08: Orchestration, CI/CD & DABs →**