Tag: data-masking

In today’s data-driven world, providing access to data is undoubtedly crucial. However, what happens when that data contains sensitive Personally Identifiable Information (PII) like emails, phone numbers, or credit card details? Clearly, you can’t just grant open access. For this reason, dynamic data masking in Snowflake becomes an essential tool for modern data governance.

Specifically, Dynamic Data Masking allows you to protect sensitive data by masking it in real-time within your query results, based on the user’s role. Crucially, the underlying data in the table remains unchanged; instead, only the query result is masked. As a result, your data analysts can run queries on a production table without ever seeing the raw sensitive information.

With that in mind, this guide will walk you through the practical steps of creating and applying a masking policy in Snowflake to protect your PII.

The Scenario: Protecting Customer PII

Imagine we have a customers table with the following columns, containing sensitive information:

CUSTOMER_ID	FULL_NAME	EMAIL	PHONE_NUMBER
101	Jane Doe	jane.doe@email.com	555-123-4567
102	John Smith	john.smith@email.com	555-987-6543

Our goal is to create a policy where:

• Users with the ANALYST_ROLE see a masked version of the email and phone number.
• Users with a privileged PII_ACCESS_ROLE can see the real, unmasked data.

Step 1: Create the Masking Policy

First, we define the rules of how the data should be masked. A masking policy is a schema-level object that uses a CASE statement to apply conditional logic.

This policy will check the user’s current role. If their role is PII_ACCESS_ROLE, it will show the original value. For all other roles, it will show a masked version.

SQL Code to Create the Policy:SQL

-- Create a masking policy for email addresses
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() = 'PII_ACCESS_ROLE' THEN val
    ELSE '***-MASKED-***'
  END;

-- Create a masking policy for phone numbers
CREATE OR REPLACE MASKING POLICY phone_mask AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() = 'PII_ACCESS_ROLE' THEN val
    ELSE REGEXP_REPLACE(val, '.', '*', 1, 8) -- Masks the first 8 characters
  END;

• The email_mask policy is simple: it shows the real value for the privileged role and a fixed string for everyone else.
• The phone_mask policy is slightly more advanced, using a regular expression to replace the first 8 characters of the phone number with asterisks, showing only the last part of the number.

Step 2: Apply the Masking Policy to Your Table

Once the policy is created, you need to apply it to the specific columns in your table that you want to protect. You use the ALTER TABLE command to do this.

SQL Code to Apply the Policy:SQL

-- Apply the email_mask policy to the EMAIL column
ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask;

-- Apply the phone_mask policy to the PHONE_NUMBER column
ALTER TABLE customers MODIFY COLUMN phone_number SET MASKING POLICY phone_mask;

That’s it! The policy is now active.

Step 3: Test the Policy with Different Roles

Now, let’s test our setup. We will run the same SELECT query as two different users with two different roles.

Test 1: Querying as a user with ANALYST_ROLESQL

USE ROLE ANALYST_ROLE;
SELECT * FROM customers;

Result (Data is Masked):

CUSTOMER_ID	FULL_NAME	EMAIL	PHONE_NUMBER
101	Jane Doe	*-MASKED-*	********-4567
102	John Smith	*-MASKED-*	********-6543

Test 2: Querying as a user with the privileged PII_ACCESS_ROLESQL

USE ROLE PII_ACCESS_ROLE;
SELECT * FROM customers;

Result (Data is Unmasked):

CUSTOMER_ID	FULL_NAME	EMAIL	PHONE_NUMBER
101	Jane Doe	jane.doe@email.com	555-123-4567
102	John Smith	john.smith@email.com	555-987-6543

As you can see, the same query on the same table produces different results based on the user’s role. The masking happens dynamically at query time, and the underlying data is never changed.

Conclusion: Security and Analytics in Harmony

Dynamic Data Masking is undoubtedly a powerful feature that allows you to democratize data access without compromising on security. Specifically, by implementing masking policies, you can provide broad access to your tables for analytics while at the same time ensuring that sensitive PII is only visible to the specific roles that have a legitimate need to see it. Ultimately, this is a fundamental component of building a secure and well-governed data platform in Snowflake.