Build a governed catalog on Databricks: schemas, masked views and grants

The module so far made the shop's schemas, granted access, masked the emails and followed lineage, 1 piece at a time. This part is the shop's catalog as 1 script you can run again at any time, plus the statements that finish it on Databricks. It needs only delta_session.py from module 4.

Build it: the shop's catalog

The plan is a rule more than code: personal data lives in silver, analysts read gold, and nothing in gold shows an email in full. The script builds the layout and then tests the rule, so a view added later that breaks it stops the run. Save this as setup_catalog.py:

"""Lay out the shop in its catalog: tables in silver, views for analysts in gold, emails masked.

Every statement replaces or skips what is already there, so it can run again at any time.
Names have 2 parts, schema.object, and resolve in the current catalog.
"""
from delta_session import delta_spark

spark = delta_spark("catalog")
EMAIL = r"^[^@*\s]+@[^@\s]+$"  # an address with its name part intact


def make_schemas():
    for schema, comment in [
        ("bronze", "Files and rows as they arrived"),
        ("silver", "Cleaned tables, 1 row per key; holds personal data"),
        ("gold", "Views for analysts and dashboards; no raw personal data"),
    ]:
        spark.sql(f"CREATE SCHEMA IF NOT EXISTS {schema} COMMENT '{comment}'")


def make_tables():
    spark.sql("""
        CREATE TABLE IF NOT EXISTS silver.customers (
            customer_id STRING, name STRING, email STRING, country STRING
        ) USING DELTA
    """)
    spark.sql("""
        INSERT OVERWRITE 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("""
        CREATE TABLE IF NOT EXISTS silver.orders (
            order_id BIGINT, customer_id STRING, ordered_at TIMESTAMP, amount DECIMAL(10,2)
        ) USING DELTA
    """)
    spark.sql("""
        INSERT OVERWRITE silver.orders VALUES
            (1001, 'C01', TIMESTAMP'2026-10-01 09:15:00', 25.50),
            (1002, 'C02', TIMESTAMP'2026-10-01 10:02:00', 12.00),
            (1003, 'C03', TIMESTAMP'2026-10-02 11:30:00', 60.00),
            (1004, 'C01', TIMESTAMP'2026-10-02 12:45:00', 40.00),
            (1005, 'C02', TIMESTAMP'2026-10-02 16:20:00', 18.25)
    """)


def make_views():
    # On Databricks the body is: RETURN is_account_group_member('pii_readers')
    spark.sql("CREATE OR REPLACE FUNCTION silver.can_see_pii() RETURNS BOOLEAN RETURN false")
    spark.sql("""
        CREATE OR REPLACE FUNCTION silver.mask_email(email STRING) RETURNS STRING
        RETURN CASE WHEN silver.can_see_pii() THEN email
                    ELSE regexp_replace(email, '^[^@]+', '***') END
    """)
    spark.sql("""
        CREATE OR REPLACE VIEW gold.customer_spend AS
        SELECT c.customer_id, c.country, count(o.order_id) AS orders, sum(o.amount) AS spent
        FROM silver.customers AS c
        LEFT JOIN silver.orders AS o ON o.customer_id = c.customer_id
        GROUP BY c.customer_id, c.country
    """)
    spark.sql("""
        CREATE OR REPLACE VIEW gold.customer_contacts AS
        SELECT customer_id, name, silver.mask_email(email) AS email
        FROM silver.customers
    """)


def check_no_raw_emails(schema):
    """Stop if any table or view in the schema shows an email address in full."""
    for table in spark.catalog.listTables(schema):
        df = spark.table(f"{schema}.{table.name}")
        text = [field.name for field in df.schema.fields if field.dataType.simpleString() == "string"]
        for column in text:
            found = df.filter(df[column].rlike(EMAIL)).count()
            if found:
                raise RuntimeError(f"{schema}.{table.name}.{column} shows {found} email addresses in full")
        print(f"{schema}.{table.name}: no raw emails in {len(text)} text columns")


make_schemas()
make_tables()
make_views()
check_no_raw_emails("gold")
spark.sql("SELECT * FROM gold.customer_contacts ORDER BY customer_id").show(truncate=False)
spark.sql("SELECT * FROM gold.customer_spend ORDER BY customer_id").show()

Run it with python setup_catalog.py:

gold.customer_contacts: no raw emails in 3 text columns
gold.customer_spend: no raw emails in 2 text columns
+-----------+----+---------------+
|customer_id|name|email          |
+-----------+----+---------------+
|C01        |Ana |***@example.com|
|C02        |Ben |***@example.org|
|C03        |Chen|***@example.net|
+-----------+----+---------------+

+-----------+-------+------+-----+
|customer_id|country|orders|spent|
+-----------+-------+------+-----+
|        C01|     ES|     2|65.50|
|        C02|     IE|     2|30.25|
|        C03|     DE|     1|60.00|
+-----------+-------+------+-----+

Run it again and it prints the same. Schemas are created only if missing, the tables' rows are replaced with INSERT OVERWRITE, and functions and views are replaced, so a rerun never adds a second copy of anything.

The names have 2 parts, such as silver.customers. Spark's own catalog accepts 3-part names for tables, as part 1 showed, but not for functions, so the script names everything within the current catalog. On Databricks, that is your workspace's catalog, or whichever one you choose with USE CATALOG.

What the check is for

check_no_raw_emails reads every text column of every table and view in gold and fails on any value that looks like an email with its name part intact. It does not trust that the views were written correctly; it looks at what a reader would get. That is the rule of part 3 turned into a test, one that can run after every change to gold.

On Databricks

In a notebook, leave out the import and the delta_spark line, and replace the body of can_see_pii with RETURN is_account_group_member('pii_readers'). Then finish the catalog in SQL, with your catalog's name in place of shop:

-- Analysts read gold, and nothing else
GRANT USE CATALOG ON CATALOG shop TO `analysts`;
GRANT USE SCHEMA ON SCHEMA shop.gold TO `analysts`;
GRANT SELECT ON SCHEMA shop.gold TO `analysts`;

-- The emails stay masked wherever silver.customers is read
ALTER TABLE shop.silver.customers ALTER COLUMN email SET MASK shop.silver.mask_email;
ALTER TABLE shop.silver.customers ALTER COLUMN email SET TAGS ('pii' = 'email');

On Databricks the check sees what its runner sees, because the mask asks who is running the query. Run it as a user or service principal outside pii_readers, or it will rightly fail on the emails a member may see.

Then try 2 changes on your own. Add a view gold.mailing_list that selects name, email FROM silver.customers and run the script: it stops with gold.mailing_list.email shows 3 email addresses in full. Then make can_see_pii return true, as it would for a member of pii_readers, and the check fails on gold.customer_contacts instead.

Next, module 6 gets data into the catalog as it arrives: files with Auto Loader, apps and databases with Lakeflow Connect.

InterviewDatabricks interview questions for Data Engineer rolesDatabricks questions for Data Engineer roles, with answers, from easy to hard.