Dynamic Data Masking
Snowflake · Security & Governance
Theory
Dynamic data masking hides column values at query time based on who is asking. A masking policy is a schema-level object containing a SQL expression that takes the column value and returns either the real value or a masked one, usually by checking CURRENT_ROLE() or IS_ROLE_IN_SESSION(). You attach it with ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask.
The stored data never changes; only the result is masked. For example, a data engineer sees asha@gmail.com while an analyst sees ***@gmail.com from the same table. Masking policies are an Enterprise edition feature, and one policy can be reused on many columns of the same data type.
Why we use it
Sensitive data like emails, phone numbers and ID numbers often sit in tables many people need to query. Masking lets everyone use the same table while only authorised roles see real values, without maintaining separate redacted copies.
When to use it
Use masking for PII and confidential columns that many roles query. Use row access policies when whole rows must be hidden, and secure views when you want to expose a reduced, fixed set of columns.
How a Masking Policy Works
SQL logic attached to a column decides, at query time, whether to show the real value or a masked one, commonly based on the querying role.
Why Masking Beats a Separate Redacted Table
Because masking happens live rather than on a duplicate copy, one policy attached once protects a column (email, SSN, phone number) everywhere it's queried, with no redacted tables to keep in sync.
Typical Policy Logic
A policy usually checks CURRENT_ROLE() and returns the real value for privileged roles, a partially masked value (like showing only the last 4 digits) for others.
How it works
- Query selects the email column
- Snowflake finds a masking policy on the column
- Evaluate the policy with the current role and context
- Is the role authorised to see real values? Yes: Return the real email. No: Return the masked value.
- Result shows masked or real data; stored data is unchanged
Example
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING)
RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN','DATA_ENGINEER') THEN val
WHEN CURRENT_ROLE() = 'DATA_ANALYST' THEN REGEXP_REPLACE(val, '.+@', '***@')
ELSE '********'
END;
ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask;
USE ROLE data_analyst;
SELECT email FROM customers; -- ***@example.com
USE ROLE accountadmin;
SELECT email FROM customers; -- full value visibleKey points
- Applied at query time; stored data is never changed
- One policy can be attached to many columns of the same data type
- A column can have only one masking policy at a time
- IS_ROLE_IN_SESSION() respects role hierarchy; CURRENT_ROLE() does not
- Commonly used for PII like emails, ID numbers and phone numbers