๐ก๏ธ 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;
| id | name | phone_number | aadhaar_number |
|---|---|---|---|
| 1 | Aarav | 9876543210 | 123412341234 |
| 2 | Priya | 8765432109 | 567856785678 |
| 3 | Rahul | 7654321098 | 901290129012 |
๐ As ANALYST:
SELECT * FROM employees;
| id | name | phone_number | aadhaar_number |
|---|---|---|---|
| 1 | Aarav | XXXXXX3210 | 1234-XXXX-1234 |
| 2 | Priya | XXXXXX2109 | 5678-XXXX-5678 |
| 3 | Rahul | XXXXXX1098 | 9012-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!