Skip to main content

๐Ÿ›ก๏ธ Data Masking Policies in Snowflake

Data Masking Policies in Snowflake enable dynamic, role-based protection of sensitive data by controlling how columns appear to different users at query time. Theyโ€™re essential for data privacy, security, and regulatory compliance.


๐ŸŽฏ What is a Data Masking Policy?โ€‹

A Masking Policy is a Snowflake object that defines rules for how a columnโ€™s data should be displayed based on:

  • User roles (CURRENT_ROLE() logic)

  • Session context

  • Other custom logic (like regex)

  • โœ… Data itself is never modifiedโ€”only the view of it is masked dynamically.

  • โœ… Useful for protecting PII, financial data, and more.


โš™๏ธ How it Worksโ€‹

1๏ธโƒฃ Create a masking policy (like a function).
2๏ธโƒฃ Apply it to a column using ALTER TABLE ... MODIFY COLUMN.
3๏ธโƒฃ At query time, Snowflake runs the masking logic and decides whether to show real or masked data.


๐Ÿ”ง Creating and Applying a Masking Policyโ€‹

Basic Masking Policyโ€‹

CREATE MASKING POLICY phone_mask AS (val STRING) 
RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('HR_ADMIN', 'SUPPORT') THEN val
ELSE 'XXX-XXX-XXXX'
END;

Advanced Masking: Partial Maskingโ€‹

Suppose you want to show only the last 4 digits of phone numbers to most users:

CREATE MASKING POLICY phone_partial_mask AS (val STRING) 
RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('HR_ADMIN', 'SUPPORT') THEN val
ELSE CONCAT('XXXXXX', RIGHT(val, 4))
END;
  • โœ… This logic keeps the last 4 digits visibleโ€”common in financial or identity data scenarios.

Regex-Based Masking (Advanced)โ€‹

For more advanced masking (like masking Aadhaar numbers to show only the first 4 and last 4 digits):

CREATE MASKING POLICY aadhaar_mask AS (val STRING) 
RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('ADMIN', 'AUDITOR') THEN val
ELSE REGEXP_REPLACE(val, '^(\\d{4})(\\d{4})(\\d{4})$', '\\1-XXXX-\\3')
END;
  • โœ… This uses regex to replace the middle 4 digits with XXXX.

Apply Masking to a Columnโ€‹

ALTER TABLE employees 
MODIFY COLUMN phone_number
SET MASKING POLICY phone_partial_mask;

๐Ÿ“Š Example Workflowโ€‹

CREATE TABLE employees (
id INT,
name STRING,
phone_number STRING,
aadhaar_number STRING
);

INSERT INTO employees VALUES
(1, 'Aarav', '9876543210', '123412341234'),
(2, 'Priya', '8765432109', '567856785678'),
(3, 'Rahul', '7654321098', '901290129012');
  • โœ… Apply masking:
ALTER TABLE employees 
MODIFY COLUMN phone_number
SET MASKING POLICY phone_partial_mask;

ALTER TABLE employees
MODIFY COLUMN aadhaar_number
SET MASKING POLICY aadhaar_mask;

Queries with Role-Based Resultsโ€‹

๐Ÿ”Ž As HR_ADMIN:

SELECT * FROM employees;
idnamephone_numberaadhaar_number
1Aarav9876543210123412341234
2Priya8765432109567856785678
3Rahul7654321098901290129012

๐Ÿ”Ž As ANALYST:

SELECT * FROM employees;
idnamephone_numberaadhaar_number
1AaravXXXXXX32101234-XXXX-1234
2PriyaXXXXXX21095678-XXXX-5678
3RahulXXXXXX10989012-XXXX-9012

๐Ÿ”— Integration with Other Snowflake Featuresโ€‹

  • โœ… Secure Views Combine masking policies with secure views to further restrict data access:
CREATE SECURE VIEW employee_view AS
SELECT id, name, phone_number, aadhaar_number FROM employees;
  • โœ… Row Access Policies Use with row access policies to control which rows (and which columns) users can see.

  • โœ… Snowflake Streams & Tasks Masked data is reflected in query results, but underlying data in streams (like CDC) is unaffected (actual values are still there).

  • โœ… Time Travel & Cloning Masking policies are included in table metadata, so clones and time-traveled tables will retain the masking policies.


๐ŸŒ Capacity and Scaleโ€‹

๐Ÿ”น Fully Managed โ€“ No need to worry about performance impact or extra setupโ€”Snowflake handles masking dynamically at query time.

๐Ÿ”น Scale โ€“ Works for tables with billions of rows; masking is evaluated at read time, leveraging Snowflakeโ€™s compute power.

๐Ÿ”น Zero Data Movement โ€“ No need to create duplicate columns or tables with masked dataโ€”no ETL, no extra storage.


๐Ÿ’ก Best Practicesโ€‹

  • โœ… Use descriptive names for policies (phone_partial_mask, aadhaar_mask).
  • โœ… Keep logic simple and easy to maintain.
  • โœ… Test policies with different roles to validate correct masking.
  • โœ… Combine with RBAC and Row Access Policies for holistic security.
  • โœ… Regularly review masking policies to keep up with changing compliance needs.

โš ๏ธ Limitationsโ€‹

โŒ Canโ€™t mask columns in system views (like INFORMATION_SCHEMA). โŒ Doesnโ€™t physically change dataโ€”someone with full privileges can still see real data. โŒ Requires explicit policy application to each column.


๐Ÿš€ Key Use Casesโ€‹

โœ”๏ธ Protecting Aadhaar, PAN, phone numbers in financial or healthcare apps. โœ”๏ธ Masking customer data for support teams. โœ”๏ธ Securing data for third-party analytics or multi-tenant environments.


๐Ÿท๏ธ Key Takeawaysโ€‹

โœ”๏ธ Dynamic, role-based masking โ€“ no physical data changes. โœ”๏ธ Supports simple and advanced (regex, partial) masking. โœ”๏ธ Works seamlessly with Secure Views, Row Access Policies, and RBAC. โœ”๏ธ Fully managed, scalable, and perfect for GDPR, HIPAA, SOC 2 compliance!