How to Mask Data in Snowflake

How do I use Snowflake masking policies to hide sensitive data?

Snowflake masking policies let you dynamically hide or obfuscate sensitive column data based on the querying user’s role.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What problems does Snowflake data masking solve?

Data masking hides sensitive values—like emails or card numbers—so analysts can query tables without seeing private information. Policies return either the real value or a masked version depending on the user’s role.

How does Snowflake masking policy syntax work?

A masking policy is a schema object. You define an input parameter, return type, and a CASE expression that checks CURRENT_ROLE(). You then bind the policy to one or more table columns.

CREATE OR REPLACE MASKING POLICY policy_name AS (val <data_type>) RETURNS <data_type> -> CASE WHEN <role condition> THEN val ELSE <masked value> END;

How to create a column-level masking policy?

Identify the sensitive column, decide which roles see the clear value, and build a simple CASE expression. Keep the logic short for performance.

Example: Mask customer email except for admins

CREATE OR REPLACE MASKING POLICY mask_customer_email AS (email STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN','SECURITYADMIN') THEN email ELSE regexp_replace(email,'[^@]+','***') END;

How to apply a masking policy to an existing table?

Use ALTER TABLE … MODIFY COLUMN to bind the policy. Queries start honoring the rule instantly; no data rewrite occurs.

ALTER TABLE Customers MODIFY COLUMN email SET MASKING POLICY mask_customer_email;

Can I mask joined or derived columns?

Yes. Masking is enforced at query time, so the policy still runs when columns are joined, selected into CTEs, or materialized into views.

Best practices for Snowflake masking

Store policies in a dedicated security schema, version them in source control, test with different roles, and avoid complex UDFs inside the CASE expression.

Common mistakes and fixes

Forgetting to grant roles access to the policy’s schema causes “policy does not exist” errors. Also, applying multiple policies on the same column via tags overrides earlier bindings.

Key takeaways

Masking policies give fine-grained, role-based data protection without copying tables. Create the policy once, bind it to sensitive columns, and audit regularly.

Why How to Mask Data in Snowflake is important

How to Mask Data in Snowflake Example Usage


-- Show masked vs. unmasked results
SET ROLE PUBLIC;
SELECT id, email FROM Customers LIMIT 3; -- emails masked

SET ROLE ACCOUNTADMIN;
SELECT id, email FROM Customers LIMIT 3; -- full emails

How to Mask Data in Snowflake Syntax


-- Create a masking policy for credit card numbers
CREATE OR REPLACE MASKING POLICY mask_card_number
  AS (card STRING)
  RETURNS STRING
  -> CASE
       WHEN CURRENT_ROLE() = 'FINANCE_ROLE' THEN card
       ELSE 'XXXX-XXXX-XXXX-' || RIGHT(card,4)
     END;

-- Apply it to the Orders table
ALTER TABLE Orders
  MODIFY COLUMN total_amount SET MASKING POLICY mask_card_number;

-- Drop or replace a policy
DROP MASKING POLICY IF EXISTS mask_card_number;

Common Mistakes

Mistake: Applying a masking policy without sufficient role checks. Why wrong: Everyone may see clear data. Fix: Always restrict clear data to specific roles using CURRENT_ROLE().
Mistake: Dropping a policy before unbinding it. Why wrong: Leaves dangling references and future DDL errors. Fix: ALTER TABLE … UNSET MASKING POLICY first, then DROP POLICY.