Snowflake Dynamic Data Masking: Complete Guide to Masking Policies

Snowflake Dynamic Data Masking (DDM) masks column values in query results at runtime based on the executing user's role, without modifying the underlying table data. You simply create a Masking Policy object and attach it to a column to protect sensitive data such as PII, credit card numbers, and email addresses. Enterprise Edition or higher is required.

How Dynamic Data Masking Works

DDM is evaluated at query execution time in Snowflake's service layer. The conditional expression in the Masking Policy attached to a column is evaluated against the executing role, and if the condition matches, the masked value is returned.

Query execution flow:

[SELECT email FROM customers]
       │
       ▼
[Service layer: Masking Policy evaluation]
       │
       ├─ Executing role = DATA_ADMIN → returns original: [email protected]
       │
       └─ Executing role = ANALYST   → returns masked:  ****@****.com

Creating a Masking Policy

A Masking Policy is created with CREATE MASKING POLICY. The input data type and the return type must match.

-- Mask an email address
CREATE OR REPLACE MASKING POLICY email_mask AS
  (val STRING) RETURNS STRING ->
    CASE
      WHEN IS_ROLE_IN_SESSION('DATA_ADMIN') THEN val
      WHEN IS_ROLE_IN_SESSION('ANALYST')    THEN REGEXP_REPLACE(val, '.+@', '****@')
      ELSE '********'
    END;

-- Mask a numeric value (salary)
CREATE OR REPLACE MASKING POLICY salary_mask AS
  (val NUMBER) RETURNS NUMBER ->
    CASE
      WHEN IS_ROLE_IN_SESSION('HR_ADMIN') THEN val
      ELSE 0
    END;

-- Mask a date (reduce date of birth to the year only)
CREATE OR REPLACE MASKING POLICY dob_mask AS
  (val DATE) RETURNS DATE ->
    CASE
      WHEN IS_ROLE_IN_SESSION('DATA_ADMIN') THEN val
      ELSE DATE_FROM_PARTS(YEAR(val), 1, 1)
    END;

Role-Inspection Functions

IS_ROLE_IN_SESSION() respects the role hierarchy, so roles inherited from child roles are evaluated correctly. CURRENT_ROLE() only returns the name of the active role and therefore ignores the role hierarchy.

Attaching a Masking Policy

Attach a Masking Policy to a column with ALTER TABLE ... ALTER COLUMN ... SET MASKING POLICY.

-- Attach a policy to a column on an existing table
ALTER TABLE customers
  ALTER COLUMN email SET MASKING POLICY email_mask;

ALTER TABLE employees
  ALTER COLUMN salary SET MASKING POLICY salary_mask;

ALTER TABLE employees
  ALTER COLUMN date_of_birth SET MASKING POLICY dob_mask;

-- Detach a policy
ALTER TABLE customers
  ALTER COLUMN email UNSET MASKING POLICY;

-- Attach a policy at table-creation time
CREATE TABLE sensitive_data (
  id NUMBER,
  ssn STRING WITH MASKING POLICY ssn_mask,
  email STRING WITH MASKING POLICY email_mask
);

Tag-based Masking

Tag-based Masking binds object tags to Masking Policies so that every column carrying the tag is automatically masked. In large environments with hundreds or thousands of columns, it eliminates the chore of attaching policies column by column.

-- Step 1: Create the tag
CREATE OR REPLACE TAG pii_type
  ALLOWED_VALUES 'email', 'phone', 'ssn', 'name';

-- Step 2: Bind Masking Policies to the tag
ALTER TAG pii_type SET
  MASKING POLICY email_mask  FOR 'email',
  MASKING POLICY phone_mask  FOR 'phone',
  MASKING POLICY ssn_mask    FOR 'ssn',
  MASKING POLICY name_mask   FOR 'name';

-- Step 3: Apply the tag to columns (the policy is applied automatically)
ALTER TABLE customers ALTER COLUMN email
  SET TAG pii_type = 'email';

ALTER TABLE customers ALTER COLUMN phone_number
  SET TAG pii_type = 'phone';

-- The matching Masking Policy is auto-applied based on the tag value

Direct Attachment vs Tag-based Masking

Policy Precedence

When both a directly attached Masking Policy and a Tag-based Masking Policy apply to the same column, the directly attached policy wins.

• Direct attachment (ALTER COLUMN SET MASKING POLICY) takes top priority
• Tag-based Masking only takes effect when no policy is directly attached to the column
• Multiple Masking Policies cannot be directly attached to the same column

Edition Requirements

Managing and Inspecting Policies

-- List Masking Policy references in the account
SELECT * FROM TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(
  POLICY_NAME => 'email_mask'
));

-- Inspect which policies are attached to a table's columns
DESCRIBE TABLE customers;

-- List Masking Policies in a schema with SHOW MASKING POLICIES
SHOW MASKING POLICIES IN SCHEMA my_db.my_schema;

-- Inspect a policy's definition
DESCRIBE MASKING POLICY email_mask;

Exam Tips

• DDM requires Enterprise Edition or higher
• A Masking Policy's input type and return type must be the same
• IS_ROLE_IN_SESSION() respects the role hierarchy; CURRENT_ROLE() does not
• Only one Masking Policy can be directly attached to a single column
• A directly attached policy takes precedence over Tag-based Masking
• The underlying data is unchanged; only query results are masked (Dynamic)
• Tag-based Masking is wired up with ALTER TAG SET MASKING POLICY

Sample Question

Frequently Asked Questions