Suppression, generalization and hashing with column masks

1.Suppression: replace the value outright

A Unity Catalog column mask is a SQL UDF bound to a column. It is evaluated at query time and returns either the real value or a protected one. Each anonymization method is a different body for that function. The bluntest is suppression: the protected branch returns a constant, so nothing about the original value is visible. The SSN mask returning '***-**-****' and the address mask returning 'REDACTED' both work this way. So does this one-line PII mask from the ABAC pattern library.

CREATE FUNCTION catalog.schema.mask_pii(val STRING)
RETURNS STRING
RETURN '***';

The constant has to fit the column. A STRING column can return '***'. A DOUBLE column has to suppress with a number such as 0.0, because the return value must be the column's type or castable to it.

Sources1

2.Generalization: keep the shape, lose the precision

Generalization keeps some analytic value. Instead of hiding the value completely, the mask makes it coarser. In the salary mask below, admins and HR see exact figures, managers see salaries rounded to the nearest thousand, and everyone else sees 0.0. One function therefore gives three audiences full access, generalized values and suppressed values.

-- Succeeds: Masks a DOUBLE column, returns DOUBLE in every branch
CREATE FUNCTION mask_salary(salary DOUBLE, user_role STRING)
RETURNS DOUBLE
RETURN CASE
  WHEN user_role IN ('admin', 'hr') THEN salary
  WHEN user_role = 'manager' THEN  ? (salary / 1000) * 1000
  ELSE 0.0
END;

Partial reveal is the string version of generalization: show the last few characters and hide the rest. Databricks recommends string functions for this rather than regular expressions, because regex masking scans the whole value on every row.

CREATE FUNCTION mask_ssn(ssn STRING, show_last INT) RETURNS STRING
DETERMINISTIC
  RETURN CONCAT('***-**-', RIGHT(ssn, show_last));

Sources1

3.Hashing: deterministic pseudonymization

Suppression and generalization throw information away. Hashing keeps identity but hides the real value: the same input always produces the same pseudonym, so joins, counts of distinct customers and grouping still work. The documented pattern adds a version number to the value before hashing. Changing the version rotates the pseudonyms, while data hashed under the earlier version keeps its existing hashes. The DETERMINISTIC keyword tells the engine the output depends only on the inputs, which helps it optimize the query.

CREATE FUNCTION pseudonymize(val STRING, version INT) RETURNS STRING
DETERMINISTIC
  RETURN SHA2(CONCAT(val, CAST(version AS STRING)), 256);

Sources1

4.Conditional masking and applying a method at scale

The USING COLUMNS clause passes extra arguments to a mask. The first parameter is always the masked column. Further parameters can be other columns in the same row or constants. This is how the rotation version reaches pseudonymize, and it also lets one mask treat rows differently. In the example below, a user sees an address only if they belong to the group for that row's country.

CREATE FUNCTION mask_address_by_country(address STRING, country STRING, group_suffix STRING DEFAULT '_address_viewers')
RETURN IF(
  is_account_group_member(country || group_suffix),
  address,
  'REDACTED'
);

Binding a mask to each column with ALTER TABLE stops being practical once PII is spread across many tables. Databricks recommends ABAC policies for this. A policy is attached to a catalog or schema and applies the same masking function to every column whose governed tag matches. The policy below applies the mask_pii suppression function to any column tagged as a name or address, once the table is no longer tagged as unverified.

CREATE POLICY mask_reviewed_pii
ON CATALOG my_catalog
COLUMN MASK catalog.schema.mask_pii
TO `account users`
EXCEPT `data_admins`
FOR TABLES
WHEN NOT has_tag_value('classification', 'unverified')
MATCH COLUMNS (has_tag_value('pii', 'name') OR has_tag_value('pii', 'address')) AS m
ON COLUMN m;

ABAC policies also allow one UDF that takes and returns VARIANT to mask columns of many types. That casting works only inside ABAC policies, not in table-level masks. Whichever mechanism you use, keep the number of masks down: mask only the columns that really are sensitive, and reuse functions.

Sources231

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A suppression mask can return a text placeholder such as 'CONFIDENTIAL' for any column.Why is that wrong?

   Every branch must return the column's type or a type castable to it. For a DOUBLE column, suppress with a number such as 0.0.

   Covered in Suppression: replace the value outright

2. 2.A generic VARIANT masking function works as a table-level mask set with ALTER TABLE.Why is that wrong?

   The automatic VARIANT casting for complex types works only inside ABAC policies.

   Covered in Conditional masking and applying a method at scale

Practise it for real

Bind a suppression mask to an SSN column, confirm that users outside the HR group see it masked, then remove it safely

1. 1.Run CREATE FUNCTION ssn_mask(ssn STRING) RETURN CASE WHEN is_account_group_member('HumanResourceDept') THEN ssn ELSE '*--****' END;

   Why: The mask must exist as a SQL UDF in Unity Catalog before it can be bound

   You should see: The function is created in your current schema

2. 2.Run CREATE TABLE users (name STRING, ssn STRING); then ALTER TABLE users ALTER COLUMN ssn SET MASK ssn_mask;

   Why: This binds the mask to an existing column

   You should see: The ssn column shows a column mask in Catalog Explorer

3. 3.Insert a row, then run SELECT * FROM users as a user who is not in HumanResourceDept

   Why: The mask is evaluated per query against the caller's group membership

   You should see: ssn returns *--****

4. 4.Run ALTER TABLE users ALTER COLUMN ssn DROP MASK; and only then DROP FUNCTION ssn_mask;

   Why: Dropping the function first would leave the table inaccessible

   You should see: Queries return original ssn values and the table remains readable