What you will be able to do
- Explain what a Unity Catalog column mask is and when it is evaluated
- Create, apply, replace and remove a column mask with SQL in the correct order
- Wrap Python logic in a SQL UDF so it can be used as a mask
- Avoid the type-mismatch and ANSI-mode problems that make a mask return the wrong values
Key concept
Column mask — A SQL user-defined function, registered in Unity Catalog and bound to one column. Every time the column is queried, the function receives the stored value and decides whether this user sees the real value or a protected version of it. The stored data never changes. Only what each reader gets back changes.
1.A mask is a function evaluated at query time
Anonymization and pseudonymization in Unity Catalog do not need a second, scrubbed copy of a table. You attach a function to the sensitive column. Unity Catalog runs that function whenever someone reads the column, so the result can differ from one user to the next. Inside the function you can call any built-in runtime function or another UDF. The most common choices are is_account_group_member() and session_user(), which identify who is running the query.
There are two structural rules. Each column can have only one mask. A mask can also take other columns as inputs, so its output can depend on more than the value it is masking.
CREATE FUNCTION ssn_mask(ssn STRING)
RETURN CASE WHEN is_account_group_member('HumanResourceDept') THEN ssn ELSE '***-**-****' END;Checkpoint 1 of 5· Check yourself
A data engineer wants a phone_number column masked one way for analysts and another way for auditors. What is the correct design?
A column can carry only one mask. Different audiences are handled by branching inside that single function.
“Each column can have one mask. Column masks can take other columns as inputs to vary behavior based on multiple attributes.”Source: docs.databricks.com
2.Applying, replacing and removing a mask
You can bind a mask when you create the table, by adding MASK to the column definition, or later on an existing table with ALTER TABLE ... ALTER COLUMN ... SET MASK. The Catalog Explorer equivalent is the Mask edit icon on the table's Overview tab.
Assigning the function needs EXECUTE on it, plus USE SCHEMA and USE CATALOG on its parents. Adding or removing a mask on an existing table needs table ownership, or both MANAGE and SELECT on the table. Readers are also limited by compute: a SQL warehouse, standard access mode on Databricks Runtime 12.2 LTS or above, or dedicated access mode on 15.4 LTS or above. Masks also survive REPLACE TABLE, provided the new table has columns with the same names as the masked ones.
--Create the `users` table and apply the column mask after:
CREATE TABLE users
(name STRING, ssn STRING);
ALTER TABLE users ALTER COLUMN ssn SET MASK ssn_mask;| Goal | Statement |
|---|---|
| Bind at creation | CREATE TABLE users (name STRING, ssn STRING MASK ssn_mask) |
| Bind to an existing column | ALTER TABLE users ALTER COLUMN ssn SET MASK ssn_mask |
| Change the masking logic | CREATE OR REPLACE FUNCTION with the same name |
| Unbind | ALTER TABLE users ALTER COLUMN ssn DROP MASK |
Checkpoint 2 of 5· Put it in order
Put the steps for introducing and later fully retiring a column mask in a safe order
- 1.ALTER TABLE users ALTER COLUMN ssn SET MASK ssn_mask
- 2.ALTER TABLE users ALTER COLUMN ssn DROP MASK
- 3.CREATE FUNCTION ssn_mask with the masking logic
- 4.DROP FUNCTION ssn_mask
The function must exist before it can be bound. When retiring it, unbind the mask before dropping the function. If you drop the function first, the table is left inaccessible.
“You must perform the ALTER TABLE command before dropping the function or the table will be in an inaccessible state.”Source: docs.databricks.com
Checkpoint 3 of 5· Exam question
A data engineer writes the following Unity Catalog column mask and applies it to the `ssn` column of `hr.employees`: ```sql CREATE FUNCTION mask_ssn(ssn STRING, region STRING) RETURN CASE WHEN is_account_group_member('compliance_team') THEN ssn WHEN region = 'US' THEN sha2(ssn, 256) ELSE '***-**-****' END; ALTER TABLE hr.employees ALTER COLUMN ssn SET MASK mask_ssn; ``` Running a query against `hr.employees` fails because the mask function expects two arguments but only one is supplied by the table. What change makes this masking design work as intended?
Correct answer: A — Add `USING COLUMNS (region)` to the `SET MASK` clause so the function's second parameter is bound to the `region` column of the same table.
- A. A masking function that takes extra parameters beyond the masked column itself must be attached with a `USING COLUMNS` clause naming the source of each additional argument, here the table's own `region` column. Without it, Unity Catalog has no way to populate the second parameter and the mask cannot be applied.
- B. Both `sha2` and `md5` return a `STRING`, so swapping hash functions does not resolve an arity mismatch between the function's two parameters and the single column being masked. The return type was never the problem in this scenario.
- C. Missing `SELECT` privileges would cause an authorization error, not a parameter-count failure, and `compliance_team` members are already meant to see the unmasked value through the `is_account_group_member` check. Granting privileges does not supply the missing `region` argument.
- D. Unity Catalog binds the first function parameter to the masked column implicitly and any remaining parameters to columns named in `USING COLUMNS`, in the order listed there — the position of `ssn` within the function signature is not the cause of the failure. Reordering the parameters without adding `USING COLUMNS` still leaves the second argument unbound.
Sources2
3.Python or Scala logic needs a SQL wrapper
Some masking rules are easier to write in Python, such as a regular expression that hides the local part of an email address. That is allowed, but only indirectly. The function you bind as the mask must be a SQL UDF. Python or Scala logic goes in its own UDF, and a thin SQL UDF calls it.
-- Step 1: Create the Python UDF with masking logic
CREATE OR REPLACE FUNCTION email_mask_python(email STRING)
RETURNS STRING
LANGUAGE PYTHON
AS $$
import re
return re.sub(r'^[^@]+', lambda m: '*' * len(m.group()), email)
$$;
-- Step 2: Create a SQL wrapper function that calls the Python UDF
CREATE OR REPLACE FUNCTION email_mask_sql(email STRING)
RETURN email_mask_python(email);Checkpoint 4 of 5· Check yourself
An engineer runs ALTER TABLE contacts ALTER COLUMN email SET MASK email_mask_python, where email_mask_python is a LANGUAGE PYTHON UDF. What happens?
Only a SQL UDF can be bound as a mask. Bind a SQL wrapper such as email_mask_sql instead.
“If you try to use the Python UDF (email_mask_python) directly as a column mask, you will receive a [ROUTINE_NOT_FOUND] error.”Source: docs.databricks.com
4.Type matching and the ANSI-mode hazard
The value a mask returns must match, or be castable to, the column's data type. The input side is easier to get wrong. If a column is passed to a UDF parameter of a different type, Databricks casts the value implicitly. A value that cannot be cast then behaves differently depending on ANSI mode.
For confidential data this is the worst kind of failure: nothing errors, and the mask produces wrong output. Databricks recommends enabling ANSI mode so a bad cast raises an error and is noticed straight away. The safest fix is to declare every UDF parameter with exactly the type of the column passed to it.
Checkpoint 5 of 5· Check yourself
Which setting turns a silent masking error caused by a type mismatch into an error you can see?
With ANSI mode enabled, a failed cast raises an error instead of returning NULL.
“such as a row filter that returns all rows instead of filtering them, or a column mask that masks the wrong values”Source: docs.databricks.com
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A Python UDF can be bound directly as a column mask.Why is that wrong?
Only a SQL UDF can be bound. Put the Python logic in a SQL wrapper and bind the wrapper.
Covered in Python or Scala logic needs a SQL wrapper
2.To retire a mask, dropping the masking function is enough.Why is that wrong?
Run ALTER TABLE ... DROP MASK first. Dropping the function first leaves the table inaccessible until the orphaned mask reference is removed.
Covered in Applying, replacing and removing a mask
3.If a mask's parameter type doesn't match the column, the query will fail, so the mistake can't go unnoticed.Why is that wrong?
With ANSI mode off, values that cannot be cast silently become NULL and no error is raised.
Covered in Type matching and the ANSI-mode hazard
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Each column can have one mask. Column masks can take other columns as inputs to vary behavior based on multiple attributes.”
↩︎ A mask is a function evaluated at query time“Prefer SQL to Python UDFs.”
↩︎ Python or Scala logic needs a SQL wrapper“With ANSI mode disabled (spark.sql.ansi.enabled = false), uncastable values are silently converted to NULL, no error is raised”
↩︎ Type matching and the ANSI-mode hazard“The mask is a SQL UDF that takes the column value as input and returns the original value or a masked version.”
↩︎ Key concept“With ANSI mode disabled (spark.sql.ansi.enabled = false), uncastable values are silently converted to NULL, no error is raised”
↩︎ Exam trap 3“such as a row filter that returns all rows instead of filtering them, or a column mask that masks the wrong values”
↩︎ Checkpoint - 2.https://docs.databricks.com/aws/en/data-governance/unity-catalog/filters-and-masks/manually-applyOfficial docs
“Within the MASK clause, you can use any of the Databricks built-in runtime functions or call other user-defined functions.”
↩︎ A mask is a function evaluated at query time“you must have the EXECUTE privilege on the function, USE SCHEMA on the schema, and USE CATALOG on the parent catalog.”
↩︎ Applying, replacing and removing a mask“you must be the table owner, or have both the MANAGE and SELECT privileges on the table.”
↩︎ Applying, replacing and removing a mask“Dedicated access mode (formerly single user access mode) on Databricks Runtime 15.4 LTS or above.”
↩︎ Applying, replacing and removing a mask“Column masks are also retained if the new table includes columns with the same names as those that had masks in the original table.”
↩︎ Applying, replacing and removing a mask“If you try to use the Python UDF (email_mask_python) directly as a column mask, you will receive a [ROUTINE_NOT_FOUND] error.”
↩︎ Python or Scala logic needs a SQL wrapper“If you try to use the Python UDF (email_mask_python) directly as a column mask, you will receive a [ROUTINE_NOT_FOUND] error.”
↩︎ Exam trap 1“You must perform the ALTER TABLE command before dropping the function or the table will be in an inaccessible state.”
↩︎ Exam trap 2“Queries on that table now return masked ssn column values when the querying user is not a member of the HumanResourceDept group”
↩︎ Prediction“You must perform the ALTER TABLE command before dropping the function or the table will be in an inaccessible state.”
↩︎ Checkpoint