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

A governed catalog keeps raw personal data in 1 schema, publishes what analysts need as views in another, and grants readers only the second. setup_catalog.py builds that for the shop: customers and orders in silver, a spend view and a masked contacts view in gold, and a check that fails if any table or view in gold shows an email in full. On Databricks, 3 grants open gold to the analysts, and a column mask protects the emails in silver itself.

- Page: https://tecyfy.com/data-engineering/course/unity-catalog/build-it
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 5: Unity Catalog and governance, Build it: the shop's catalog
- Parts of this module: Catalogs, schemas and the 3-part name (https://tecyfy.com/data-engineering/course/unity-catalog); Privileges and grants (https://tecyfy.com/data-engineering/course/unity-catalog/privileges); Masking personal data (https://tecyfy.com/data-engineering/course/unity-catalog/personal-data); Lineage and audit logs (https://tecyfy.com/data-engineering/course/unity-catalog/lineage-and-audit); Build it: the shop's catalog (https://tecyfy.com/data-engineering/course/unity-catalog/build-it)

At the end of this part you can:

- Lay out a catalog's schemas so that personal data stays in 1 of them
- Publish views that show analysts what they need and mask the rest
- Check a schema for raw personal data, and grant it to readers on Databricks

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](/data-engineering/course/delta-lake-and-iceberg).

## 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`:

```python
"""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`:

```text
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](/data-engineering/course/unity-catalog) 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](/data-engineering/course/unity-catalog/personal-data) 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`:

```sql
-- 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.

- Interview: [Databricks interview questions for Data Engineer roles](https://tecyfy.com/interview-questions-and-answers/data-engineer/databricks). Databricks questions for Data Engineer roles, with answers, from easy to hard.

## Check yourself

1. On Databricks, the mask function checks is_account_group_member('pii_readers'), and you run setup_catalog.py's check as yourself. You are in pii_readers. What happens?

   A. The check passes, because the view masks the emails for everyone
   B. The check is skipped on Databricks
   C. The check fails on gold.customer_contacts, because the mask shows you the emails in full, as it should for a member
   D. The check fails on gold.customer_spend

### Answers

1. C. A mask runs with the identity of whoever runs the query, so the check sees what its runner may see. Run it as a user or service principal outside pii_readers, the identity whose view of gold the check is meant to test, or it reports the emails a member is allowed to see.

## Questions

### How should I organise schemas in a Databricks catalog?

A common layout is 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, inside 1 catalog for each environment. Personal data stays in silver, and gold shows it only through views that mask it, so granting readers gold alone is safe.

### How do I check that a table does not expose personal data?

Read each text column and count values that match the pattern of the data, such as an email address with its name part intact, then fail the job if any appear. setup_catalog.py does this for every table and view in gold. Run it as a user without access to personal data, since masks show different values to different users.

## Sources

- [What are catalogs in Databricks?](https://docs.databricks.com/aws/en/catalogs/)
- [Manage privileges in Unity Catalog](https://docs.databricks.com/aws/en/data-governance/unity-catalog/manage-privileges/)
- [Manually apply row filters and column masks](https://docs.databricks.com/aws/en/data-governance/unity-catalog/filters-and-masks/manually-apply)
- [Column mask clause](https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-column-mask)
- [CREATE FUNCTION (SQL)](https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-create-sql-function.html)