# Mask personal data in Databricks: column masks, row filters and dynamic views

In Unity Catalog, a column mask is a SQL function attached to a column with ALTER TABLE ... ALTER COLUMN ... SET MASK: every query on the table gets the function's result, so it can return the email to members of a group, checked with is_account_group_member, and a masked value to everyone else. A row filter is a function that returns true or false for each row, attached with SET ROW FILTER. A dynamic view puts the same logic in a view, and protects the data only if readers cannot read the table under it.

- Page: https://tecyfy.com/data-engineering/course/unity-catalog/personal-data
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 5: Unity Catalog and governance, Masking personal data
- 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:

- Write a mask as a SQL function and test it on your computer
- Attach a column mask and a row filter to a table on Databricks
- Choose between a column mask, a row filter and a dynamic view, and name what each leaves open

[Part 1](/data-engineering/course/unity-catalog) put the shop's customers in `silver.customers`, with their emails. Run `catalog.py` from there first if you are starting here. [Part 2](/data-engineering/course/unity-catalog/privileges) decided who reads which schema. That is not enough for a column: the support team needs the emails, and the analysts who read the same customers should not see them.

## A mask is a function

A mask is a SQL function that takes the column's value and returns what the current reader may see. On Databricks it asks whether the reader is in a group, with `is_account_group_member('pii_readers')`. That function exists only on Databricks, so on your computer a small function stands in for it, and you can switch it to see both readers. Save this as `masks.py`:

```python
"""A mask function for emails, used by a view, tried for a reader without and with access."""
from delta_session import delta_spark

spark = delta_spark()


def allow_pii(allowed: bool):
    """Stands in for is_account_group_member('pii_readers'), which only Databricks has."""
    spark.sql(f"CREATE OR REPLACE FUNCTION silver.can_see_pii() RETURNS BOOLEAN RETURN {allowed}")


allow_pii(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 silver.customers_safe AS
    SELECT customer_id, name, silver.mask_email(email) AS email, country
    FROM silver.customers
""")
spark.table("silver.customers_safe").orderBy("customer_id").show(truncate=False)

allow_pii(True)
spark.table("silver.customers_safe").orderBy("customer_id").show(truncate=False)
```

```text
+-----------+----+---------------+-------+
|customer_id|name|email          |country|
+-----------+----+---------------+-------+
|C01        |Ana |***@example.com|ES     |
|C02        |Ben |***@example.org|IE     |
|C03        |Chen|***@example.net|DE     |
+-----------+----+---------------+-------+

+-----------+----+---------------------+-------+
|customer_id|name|email                |country|
+-----------+----+---------------------+-------+
|C01        |Ana |ana.silva@example.com|ES     |
|C02        |Ben |ben.byrne@example.org|IE     |
|C03        |Chen|chen.li@example.net  |DE     |
+-----------+----+---------------------+-------+
```

The same view gives 2 answers, because the function runs again for every query. The mask keeps the domain, which is often enough for support and analysis, and drops the part that identifies the person. A name is personal data too; mask it the same way if readers do not need it.

## On Databricks: a column mask

In Unity Catalog the function lives in a schema like a table, and you attach it to the column itself:

```sql
CREATE OR REPLACE FUNCTION shop.silver.mask_email(email STRING)
RETURNS STRING
RETURN CASE WHEN is_account_group_member('pii_readers') THEN email
            ELSE regexp_replace(email, '^[^@]+', '***') END;

ALTER TABLE shop.silver.customers ALTER COLUMN email SET MASK shop.silver.mask_email;
```

From then on, every query on the table runs the function as each row is read, with the identity of whoever runs the query. A notebook, a dashboard and a job all get the masked value unless their user is in `pii_readers`; there is no way around it through the table. Each column takes 1 mask, and `ALTER COLUMN email DROP MASK` removes it.

To attach a mask you need to own the table, or have MANAGE and SELECT on it, and EXECUTE on the function. It works on SQL warehouses, on compute in standard access mode from Databricks Runtime 12.2 LTS and in dedicated access mode from 15.4 LTS. It has costs worth knowing: a table with a mask or a row filter cannot be read with time travel or cloned, and a mask cannot be put on a view.

## Row filters

A row filter is the same idea for rows: a function that returns true for the rows the reader may see. Support staff in Ireland see Irish customers, the data team sees all:

```sql
CREATE OR REPLACE FUNCTION shop.silver.customers_for_reader(country STRING)
RETURNS BOOLEAN
RETURN is_account_group_member('pii_readers')
    OR (is_account_group_member('support_ie') AND country = 'IE');

ALTER TABLE shop.silver.customers SET ROW FILTER shop.silver.customers_for_reader ON (country);
```

A reader in neither group gets no rows at all. `ALTER TABLE shop.silver.customers DROP ROW FILTER` removes it.

## Or a dynamic view

A **dynamic view** puts the logic in a view instead: `masks.py` above built one, and on Databricks its function would check the group with `is_account_group_member`. Readers get SELECT on the view, not on the table.

That last condition is the whole difference. A view protects only queries on the view; anyone who can read `silver.customers` itself sees every email. Databricks recommends not granting readers the tables a dynamic view reads, which [part 2](/data-engineering/course/unity-catalog/privileges) did by granting only `gold`. Use a view to publish a curated slice, a join or a reshaped table; use a mask when a column must be protected wherever its table is read.

## Tags and policies, for many tables

1 mask on 1 column is fine for the shop. With hundreds of tables, label the columns instead:

```sql
ALTER TABLE shop.silver.customers ALTER COLUMN email SET TAGS ('pii' = 'email');
```

Each catalog lists its tags in `information_schema.column_tags`. Databricks' attribute-based policies go a step further: a policy attached to a catalog or schema masks or filters the columns and tables whose tags match, and Databricks recommends them for consistent rules across many tables. [Part 4](/data-engineering/course/unity-catalog/lineage-and-audit) shows how to find out who read the data and where it went.

## Check yourself

1. The emails are protected by the dynamic view gold.customer_contacts, which masks them for anyone outside pii_readers. By mistake, the analysts are also given SELECT on silver.customers. What do they see if they query that table?

   A. Masked emails, because the view's function applies to the table too
   B. Every email in full, because a dynamic view protects only the view; the table under it has no mask of its own
   C. Nothing, because a table used by a dynamic view cannot be read directly
   D. An error, because SELECT on silver is blocked by the grant on gold

2. Support staff in Ireland should see only Irish customers in silver.customers, while the data team sees every row. Which tool fits?

   A. A row filter, a function of the country column that returns true for the rows the current user may see, attached with ALTER TABLE ... SET ROW FILTER
   B. A column mask on the country column
   C. A separate copy of the table for Ireland, refreshed every night
   D. A tag on the country column

### Answers

1. B. A dynamic view applies its logic to queries on the view only. Anyone who can read the table under it reads the raw column. That is why Databricks recommends not granting readers access to the tables a dynamic view reads, and why a column mask, which is attached to the table itself, is the stronger choice for a column that must never leak.
2. A. A row filter decides, row by row, which rows a query returns, and it can check the user's groups with is_account_group_member. A mask changes values but keeps every row; a copy has to be kept in step and governed on its own; a tag labels a column but does nothing by itself unless a policy uses it.

## Questions

### How do I mask a column in Databricks?

Write a SQL function that takes the column's value and returns what a user may see, for example CASE WHEN is_account_group_member('pii_readers') THEN email ELSE '***' END, then attach it with ALTER TABLE customers ALTER COLUMN email SET MASK mask_email. You need to own the table, or have MANAGE and SELECT on it, plus EXECUTE on the function. ALTER COLUMN email DROP MASK removes it.

### What is the difference between a column mask and a dynamic view?

A column mask is attached to the table, so every query on the table gets the masked value. A dynamic view is a separate view whose SQL does the masking, so it protects only queries on the view, and readers must not have access to the table under it. Use a view to share a curated slice, a join or a reshaped table; use a mask when a column must be protected wherever the table is read.

## Sources

- [Row filters and column masks](https://docs.databricks.com/aws/en/tables/row-and-column-filters)
- [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 a dynamic view](https://docs.databricks.com/aws/en/views/dynamic)
- [Apply tags to Unity Catalog securable objects](https://docs.databricks.com/aws/en/database-objects/tags)
- [CREATE FUNCTION (SQL)](https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-create-sql-function.html)