Unity Catalog explained: catalogs, schemas, tables and volumes

Every table in this course so far had a short name, such as orders. On Databricks, every table, view, volume and function also has a full name with 3 parts, and Unity Catalog, Databricks' catalog, decides who may use it. This module lays out the shop's data in a catalog, grants access, masks personal data and follows where data goes.

The 3-part name

Unity Catalog names everything as catalog.schema.object. A catalog is the top unit, often 1 for each environment or business area. A schema groups related objects inside a catalog. The objects are tables, views, volumes for files, functions and models. Each of these levels can be granted to people on its own, which part 2 uses.

Spark on your computer has a catalog too, called spark_catalog, with the same parts. The scripts in this module use delta_session.py from module 4 for their session. Save this as catalog.py:

"""Make the shop's schemas and 1 table, then find the table by its 3-part name."""
from delta_session import delta_spark

spark = delta_spark()
catalog = spark.catalog.currentCatalog()
print("Catalog:", catalog, "| Schema:", spark.catalog.currentDatabase())

for schema in ["bronze", "silver", "gold"]:
    spark.sql(f"CREATE SCHEMA IF NOT EXISTS {catalog}.{schema}")

spark.sql(f"""
    CREATE TABLE IF NOT EXISTS {catalog}.silver.customers (
        customer_id STRING, name STRING, email STRING, country STRING
    ) USING DELTA
""")
spark.sql(f"""
    INSERT OVERWRITE {catalog}.silver.customers VALUES
        ('C01', 'Ana', 'ana.silva@example.com', 'ES'),
        ('C02', 'Ben', 'ben.byrne@example.org', 'IE'),
        ('C03', 'Chen', 'chen.li@example.net', 'DE')
""")

spark.sql(f"SHOW SCHEMAS IN {catalog}").show()
for table in spark.catalog.listTables(f"{catalog}.silver"):
    print(table.catalog, table.namespace, table.name, table.tableType)
spark.sql(f"SELECT customer_id, name, country FROM {catalog}.silver.customers ORDER BY customer_id").show()

Run it with python catalog.py:

Catalog: spark_catalog | Schema: default
+---------+
|namespace|
+---------+
|   bronze|
|  default|
|     gold|
|   silver|
+---------+

spark_catalog ['silver'] customers MANAGED
+-----------+----+-------+
|customer_id|name|country|
+-----------+----+-------+
|        C01| Ana|     ES|
|        C02| Ben|     IE|
|        C03|Chen|     DE|
+-----------+----+-------+

The script never writes a catalog's name. It asks Spark for the current one, so the same code runs on your computer, where the answer is spark_catalog, and on Databricks, where it is your workspace's default catalog. A short name such as silver.customers is completed the same way, with the current catalog, and customers alone with the current schema too.

The difference is what stands behind the name. Locally, the catalog is a list of names in the metastore_db folder, and anyone who can open the folder can read every file. In Unity Catalog, a query goes through the name, and Unity Catalog checks the privileges of whoever runs it before the files are read.

Managed or external

The table above is managed: you gave it a name, and the catalog chose where its files live. In Unity Catalog, a managed table leaves both its files and its access to Unity Catalog, in the storage of its catalog or schema. It is the default and the type Databricks recommends, and the type whose file sizes and upkeep Databricks can handle for you, as module 4 showed.

An external table keeps its files at a location you name, such as a folder another system also writes. Unity Catalog governs who can use it, and nothing more. Use it when the files must stay where they are.

Files that are not tables, such as the CSV files the shop receives, go in a volume, the same split for files: a managed volume lives in the catalog's storage, an external one points at a folder you already have. Module 2 put the shop's raw files in one, at /Volumes/<catalog>/<schema>/raw.

Laying out catalogs and schemas

Databricks describes the catalog as the main unit for separating data, and suggests, for example, 1 catalog for production and 1 for development. Give them the same schemas inside, and the same code runs against either by changing only the catalog. A catalog can also be bound to the workspaces allowed to use it, so a production catalog is not reachable from a workspace for experiments. Data that needs tighter control, such as customers' personal details, can have its own catalog too.

Inside a catalog, this course uses 1 schema for each layer of the pipeline: bronze for data as it arrived, silver for cleaned tables and gold for what analysts and dashboards read. Module 7 builds those layers; this module decides who sees each.

On Databricks

Every workspace on Unity Catalog has a default catalog, used when a query leaves the catalog out. In some workspaces it is the workspace catalog, which usually has the workspace's name and is open to all of the workspace's users. To see where you are, run:

SELECT current_catalog(), current_schema()

To run catalog.py in a notebook, leave out the import and the delta_spark() line: the notebook's spark is already connected to Unity Catalog. In Catalog in the sidebar, the new schemas and the customers table appear under your catalog, with their columns, their history and, from part 4, their lineage.