Entity Relationship Diagrams (ERD) in Unity Catalog
Who this is for:
Architecture / Concept Overview: Entity Relationship Diagrams (ERD) in Unity Catalog
ERDs in Unity Catalog are driven by declarative primary key and foreign key constraints stored as table metadata.
%%{init: {"theme":"base","themeVariables":{"background":"#0B0E14","primaryTextColor":"#E0E6ED","lineColor":"#5D6470","darkMode":true,"primaryColor":"#2E4A4A","secondaryColor":"#374151","secondaryTextColor":"#E0E6ED","tertiaryColor":"#111827","tertiaryTextColor":"#E0E6ED","edgeLabelBackground":"#1f2937"}}}%%
flowchart LR
classDef source fill:#3F4B59,stroke:#9CA3AF,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef ingestion fill:#5A4B36,stroke:#C9A86B,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef processing fill:#535072,stroke:#8E82B4,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef storage fill:#2E4A4A,stroke:#5FAFA8,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef serving fill:#3D5550,stroke:#6BB7AA,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef governance fill:#5A3F52,stroke:#C28BB0,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
TBL[Delta Tables<br/>with PK/FK constraints] --> UC[Unity Catalog<br/>Constraint Metadata]
UC --> CE[Catalog Explorer<br/>ERD Visualisation]
UC --> BI[BI Tools<br/>Auto relationship detection]
UC --> QO[Query Optimizer<br/>Informational optimisation]
TBL:::storage
UC:::governance
CE:::serving
BI:::serving
QO:::processing
*Figure 1 — PK/FK constraints flow from table definitions through Unity Catalog to visualisation, BI tools, and the query optimiser.*
A star schema with explicit constraints produces a clear ERD in Catalog Explorer.
%%{init: {"theme":"base","themeVariables":{"background":"#0B0E14","primaryTextColor":"#E0E6ED","lineColor":"#5D6470","darkMode":true,"primaryColor":"#2E4A4A","secondaryColor":"#374151","secondaryTextColor":"#E0E6ED","tertiaryColor":"#111827","tertiaryTextColor":"#E0E6ED","edgeLabelBackground":"#1f2937"}}}%%
graph TD
classDef source fill:#3F4B59,stroke:#9CA3AF,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef ingestion fill:#5A4B36,stroke:#C9A86B,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef processing fill:#535072,stroke:#8E82B4,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef storage fill:#2E4A4A,stroke:#5FAFA8,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef serving fill:#3D5550,stroke:#6BB7AA,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef governance fill:#5A3F52,stroke:#C28BB0,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
FACT[fact_sales<br/>PK: order_id<br/>FK: date_key → dim_date<br/>FK: customer_key → dim_customer<br/>FK: product_key → dim_product] --- DIM_D[dim_date<br/>PK: date_key]
FACT --- DIM_C[dim_customer<br/>PK: customer_key]
FACT --- DIM_P[dim_product<br/>PK: product_key]
FACT:::processing
DIM_D:::storage
DIM_C:::storage
DIM_P:::storage
*Figure 2 — Star schema ERD with explicit PK/FK constraints enabling automatic relationship detection.*
Key Terms
Prerequisites and Setup
- Unity Catalog enabled with Delta tables
ALTERprivilege on tables to add constraints- Tables with logically unique key columns (PK) and referencing columns (FK)
- A modern browser for viewing ERDs in Catalog Explorer
Step-by-Step Implementation
Configuration Reference
| Constraint Type | Syntax | Enforced at Write | Notes |
|---|---|---|---|
| Primary key | PRIMARY KEY (col) | No (informational) | Declares uniqueness for metadata and BI tools |
| Foreign key | FOREIGN KEY (col) REFERENCES table(col) | No (informational) | Declares relationships for ERD and BI tools |
| NOT NULL | col TYPE NOT NULL | Yes | Enforced at write time |
| CHECK | CHECK (condition) | Yes | Enforced at write time |