A Practical Guide to Snowflake Data Masking: Column & Row Policies
10 minutes to read
November 24, 2025
In the world of cloud data platforms, you benefit by balancing data accessibility with ironclad security. You empower your teams with the data they need to drive insights while simultaneously protecting sensitive information and adhering to a growing list of compliance regulations. Snowflake’s powerful data governance features are essential for this balance.
Data masking in Snowflake is a critical tool for any modern data governance strategy. It protects sensitive data at the column level by obscuring it for unauthorized users, while row-level access policies filter which rows a user can see. Together, they create a comprehensive security framework.
This guide is designed as your hands-on resource for implementing these controls. We provide step-by-step SQL examples for creating and applying both column-level masking and row-level access policies, ensuring your sensitive data is protected, your operations are compliant, and your data teams are empowered.
Implementing data masking is a fundamental business requirement in today’s regulatory landscape. For organizations handling customer data, the stakes have never been higher. Robust masking acts as your first line of defense in building a secure and trustworthy data environment.
Regulations like the General Data Protection Regulation (GDPR), the HIPAA Security Rule, and the California Privacy Rights Act (CPRA) impose strict rules on how Personally Identifiable Information (PII) and Protected Health Information (PHI) are handled. Data masking directly supports key principles of these mandates, such as:
You can avoid severe financial penalties, reputational damage, and loss of customer trust by protecting this data. Data masking is a direct, enforceable control demonstrating due diligence in protecting sensitive information.
Snowflake enables two primary approaches to data masking, each serving a distinct purpose. Understanding the difference is key to implementing the right solution for the right use case.
This guide concentrates on Dynamic Data Masking, as it provides the flexibility and real-time governance needed for modern analytics platforms. Learn more in the official Snowflake documentation on Dynamic Data Masking.
Column-level masking directly protects specific fields containing sensitive data such as emails, phone numbers, or social security numbers. It works by creating a policy defining how a column’s value should appear based on the user’s role.
The foundation of column-level security in Snowflake is the CREATE MASKING POLICY command. The policy is a reusable, schema-level object containing a CASE statement. This statement checks the user’s current role and returns either the original value or a masked version.
Here is the basic syntax:
CREATE OR REPLACE MASKING POLICY <policy_name> AS (val <data_type>)
RETURNS <data_type> ->
CASE
WHEN CURRENT_ROLE() IN ('PRIVILEGED_ROLE') THEN val
ELSE 'masked_value'
END;
Let’s create a practical policy to mask email addresses for users who do not have the PII_ADMIN role.
Step 1: Create the masking policy. This policy shows the real email to anyone with the PII_ADMIN role and a generic masked value to everyone else.
-- Create a policy to mask email addresses
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING)
RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() = 'PII_ADMIN' THEN val
ELSE '***@***.com'
END;
Step 2: Apply the policy to a table column. After creating the policy, apply it to the desired column in your table using an ALTER TABLE statement.
-- Assume you have a customer table
CREATE OR REPLACE TABLE customers (
id INT,
full_name STRING,
email STRING
);
-- Insert some sample data
INSERT INTO customers (id, full_name, email) VALUES
(1, 'John Doe', 'john.doe@example.com'),
(2, 'Jane Smith', 'jane.smith@example.com');
-- Apply the masking policy to the email column
ALTER TABLE customers
MODIFY COLUMN email
SET MASKING POLICY email_mask;
Now, users without the PII_ADMIN role who query SELECT email FROM customers; will see ‘***@***.com’ instead of the actual email address.
You sometimes need more nuance than a simple full mask. For example, a support team might need to see the last four digits of a Social Security Number (SSN) for verification, while a finance team sees the full value.
This policy shows the full SSN to FINANCE roles, a partial SSN to SUPPORT roles, and a full mask to everyone else.
-- Create a policy for conditional SSN masking
CREATE OR REPLACE MASKING POLICY ssn_mask AS (val STRING)
RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() = 'FINANCE' THEN val
WHEN CURRENT_ROLE() = 'SUPPORT' THEN '***-**-' || RIGHT(val, 4)
ELSE '***-**-****'
END;
-- Apply the policy to an employees table
ALTER TABLE employees
MODIFY COLUMN ssn
SET MASKING POLICY ssn_mask;
This tiered approach aligns data access precisely with job function, respecting the principle of least privilege.
Applying policies column by column can become tedious in a large environment. Snowflake’s tag-based masking offers a scalable solution:
Now, the email_mask policy automatically applies to any column carrying the pii_data tag. This simplifies governance, managing the policy in one place and scaling its application as your data grows.
Column-level masking protects the content within a cell, whereas row-level security governs which rows a user is allowed to see. This is crucial in scenarios involving multi-tenant data, regional segregation, or sales hierarchies.
Row Access Policies are Snowflake’s row-level security implementation. They act as filters applied to a table during query execution. The policy evaluates each row against a condition, including only those rows in the query result for which the condition returns TRUE.
The syntax resembles a masking policy but must return a BOOLEAN value.
Basic structure:
CREATE OR REPLACE ROW ACCESS POLICY <policy_name> AS (column_name <data_type>)
RETURNS BOOLEAN ->
<condition>;
Imagine sales representatives should see only data for their assigned region.
Step 1: Create a mapping table. It connects user roles to data attributes they can access.
CREATE OR REPLACE TABLE user_attributes (
user_role STRING,
user_region STRING
);
-- Map sales roles to specific regions
INSERT INTO user_attributes (user_role, user_region) VALUES
('SALES_NA', 'North America'),
('SALES_EU', 'Europe'),
('SALES_ADMIN', 'ALL'); -- An admin role that can see all regions
Step 2: Create the row access policy. The policy checks if the current user’s role maps to the region in the sales_data table using a subquery.
CREATE OR REPLACE ROW ACCESS POLICY sales_region_policy AS (region STRING)
RETURNS BOOLEAN ->
-- Allow access if the user's role is the admin role
CURRENT_ROLE() = 'SALES_ADMIN'
-- OR if the row's region matches the region assigned to the user's role
OR EXISTS (
SELECT 1 FROM user_attributes
WHERE user_role = CURRENT_ROLE()
AND user_region = region
);
Step 3: Apply the policy to the table. Apply a row access policy to the entire table, specifying columns the policy logic depends on.
-- Assume you have a sales_data table
CREATE OR REPLACE TABLE sales_data (
order_id INT,
product STRING,
amount DECIMAL(10, 2),
region STRING
);
-- Insert sample data for different regions
INSERT INTO sales_data VALUES
(1, 'Laptop', 1200.00, 'North America'),
(2, 'Mouse', 25.00, 'Europe'),
(3, 'Keyboard', 75.00, 'North America');
-- Apply the policy to the sales_data table
ALTER TABLE sales_data
ADD ROW ACCESS POLICY sales_region_policy ON (region);
Users with the SALES_NA role running SELECT * FROM sales_data; will see only rows for ‘North America’. The SALES_EU role sees only ‘Europe’ rows.
Creating policies is just the beginning. Rigorous testing and auditing ensure they work as expected and prove compliance. Without testing, even the best-designed policy may have gaps.
We recommend a straightforward, role-based testing process to validate every deployed policy.
GRANT USAGE ON DATABASE my_db TO ROLE test_analyst;
GRANT USAGE ON SCHEMA my_schema TO ROLE test_analyst;
GRANT SELECT ON TABLE customers TO ROLE test_analyst;
-- Test as the unprivileged role
USE ROLE test_analyst;
SELECT * FROM customers; -- Email column should be masked.
-- Test as the privileged role
USE ROLE PII_ADMIN;
SELECT * FROM customers; -- Email column should be fully visible.
To monitor policy usage and detect unauthorized changes, audit their application across your Snowflake account. Snowflake offers a specific view in its ACCOUNT_USAGE schema.
Query the POLICY_REFERENCES view to see a full inventory of every masking and row access policy application.
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
ORDER BY policy_name;
This query shows policy names, protected objects (e.g., table and column), and metadata. Regular reviews help maintain strong governance and quickly identify tables with sensitive data missing required policies.
Snowflake provides powerful, granular tools for data security. Implementing these policies is just the start. Managing them at scale across dozens of regulations, hundreds of tables, and thousands of users demands a robust data governance framework.
Many organizations struggle with this challenge. It moves quickly from writing SQL to answering complex questions:
At Stellans, we help you solve these challenges. We partner with you beyond individual policies to build a comprehensive governance framework in Snowflake. We design, implement, and automate data governance, ensuring you are secure and provably compliant. Our approach turns data security from a reactive chore into a proactive, well-oiled machine.
Learn more about our Data Governance and Compliance services to build a trustworthy, compliant data-driven culture.
Snowflake’s data masking and row access policies are indispensable for organizations serious about data security and compliance. Column-level masking protects sensitive data elements within tables, while row access policies ensure users view only data relevant to their function.
Mastering these features requires a blend of technical implementation and strategic governance. The SQL examples in this guide lay the technical foundation. Building a scalable, auditable, and compliant data environment demands a clear strategy. Combining these tools with thoughtful governance unlocks your data’s full potential while maintaining top security.
Ready to build a more secure, compliant Snowflake environment? Explore our expert Snowflake consulting services.
What is a masking policy in Snowflake?
A masking policy in Snowflake is a schema-level object using a SQL expression to selectively obscure data in a column at query time. It allows different users to see different levels of data (masked, partially masked, fully visible) based on their role, without changing the underlying stored data.
What is the difference between column-level and row-level security in Snowflake?
Column-level security uses Dynamic Data Masking to hide or redact content within specific columns. Row-level security uses Row Access Policies to filter rows a user can see based on role and conditions.
How do you apply a masking policy in Snowflake?
Apply a masking policy to a table column using ALTER TABLE … MODIFY COLUMN … SET MASKING POLICY <policy_name>; For tag-based masking, apply the policy to a tag, which applies to all columns assigned that tag.
Lead Data Analyst at Stellans
Hello,
Thank you for your interest in Stellan’s Data Services. After you fill out this form, one of our team members will get in touch with you at the requested time to discuss your tech initiative.
We can’t wait to deliver the best results to you!
Hello,
Thank you for your interest in Stellan’s Data Services. After you fill out this form, one of our team members will get in touch with you at the requested time to discuss your tech initiative.
We can’t wait to deliver the best results to you!
Hello,
Thank you for your interest in Stellan’s Data Services. After you fill out this form, one of our team members will get in touch with you at the requested time to discuss your tech initiative.
We can’t wait to deliver the best results to you!
For contacting us!
We'll contact you as soon as
possible.
The report is on its way and should arrive at the email address you provided shortly.
