Dynamic Data Masking

Snowflake · Security & Governance

Theory

Dynamic data masking hides column values at query time based on who is asking. A masking policy is a schema-level object containing a SQL expression that takes the column value and returns either the real value or a masked one, usually by checking CURRENT_ROLE() or IS_ROLE_IN_SESSION(). You attach it with ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask.

The stored data never changes; only the result is masked. For example, a data engineer sees asha@gmail.com while an analyst sees ***@gmail.com from the same table. Masking policies are an Enterprise edition feature, and one policy can be reused on many columns of the same data type.

Why we use it

Sensitive data like emails, phone numbers and ID numbers often sit in tables many people need to query. Masking lets everyone use the same table while only authorised roles see real values, without maintaining separate redacted copies.

When to use it

Use masking for PII and confidential columns that many roles query. Use row access policies when whole rows must be hidden, and secure views when you want to expose a reduced, fixed set of columns.

How a Masking Policy Works

SQL logic attached to a column decides, at query time, whether to show the real value or a masked one, commonly based on the querying role.

Why Masking Beats a Separate Redacted Table

Because masking happens live rather than on a duplicate copy, one policy attached once protects a column (email, SSN, phone number) everywhere it's queried, with no redacted tables to keep in sync.

Typical Policy Logic

A policy usually checks CURRENT_ROLE() and returns the real value for privileged roles, a partially masked value (like showing only the last 4 digits) for others.

How it works

  1. Query selects the email column
  2. Snowflake finds a masking policy on the column
  3. Evaluate the policy with the current role and context
  4. Is the role authorised to see real values? Yes: Return the real email. No: Return the masked value.
  5. Result shows masked or real data; stored data is unchanged

Example

CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING)
RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN','DATA_ENGINEER') THEN val
    WHEN CURRENT_ROLE() = 'DATA_ANALYST' THEN REGEXP_REPLACE(val, '.+@', '***@')
    ELSE '********'
  END;

ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask;

USE ROLE data_analyst;
SELECT email FROM customers;  -- ***@example.com
USE ROLE accountadmin;
SELECT email FROM customers;  -- full value visible

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic