Data security is a critical part of modern data engineering, and Snowflake provides powerful features to protect sensitive information at scale. In this blog, we’ll explore Column-Level Security, Dynamic Data Masking, Conditional Data Masking, and Tag-Based Dynamic Masking — with hands-on examples to make things crystal clear.

---

Table of Contents

1. Introduction to Data Masking in Snowflake

2. Section 1 — Column-Level Security & Dynamic Data Masking

3. Section 2 — Conditional Data Masking (Region-based Masking Example)

4. Section 3 — Tag-Based Dynamic Data Masking

5. Summary & Key Takeaways

---

Introduction to Data Masking in Snowflake

When working with sensitive data, such as Gov IDs, Emails, Phone Numbers, and Financial Info, you must ensure that only authorized users can view the original values. Snowflake provides:

• Column-Level Security → Controls who can access a column

• Dynamic Data Masking → Automatically masks sensitive fields at query time based on policies

• Conditional Data Masking → Applies masking based on conditions (e.g., region, role, department)

• Tag-Based Dynamic Masking → Uses tags to apply masking policies at scale

---

Section 1 — Column-Level Security & Dynamic Data Masking

Step 1 — Create a Sample Table

CREATE OR REPLACE TABLE employee_data (
    emp_id INT,
    emp_name STRING,
    gov_id STRING,
    salary NUMBER
);

INSERT INTO employee_data VALUES
(1, 'John', 'GOV12345', 80000),
(2, 'Alice', 'GOV67890', 95000),
(3, 'Bob', 'GOV98765', 75000);

---

Step 2 — Create a Dynamic Masking Policy

CREATE OR REPLACE MASKING POLICY mask_gov_id AS (val STRING) 
RETURNS STRING ->
CASE
    WHEN CURRENT_ROLE() IN ('FULL_ACCESS_ROLE') THEN val
    ELSE 'XXXX-XXXX'
END;

---

Step 3 — Apply the Masking Policy on the Column

ALTER TABLE employee_data
MODIFY COLUMN gov_id
SET MASKING POLICY mask_gov_id;

---

Step 4 — Test the Masking Policy

-- User with limited role
USE ROLE ANALYST_ROLE;
SELECT * FROM employee_data;

-- Output for ANALYST_ROLE:
-- emp_id | emp_name | gov_id    | salary
--   1    | John     | XXXX-XXXX | 80000
--   2    | Alice    | XXXX-XXXX | 95000

-- User with FULL_ACCESS_ROLE
USE ROLE FULL_ACCESS_ROLE;
SELECT * FROM employee_data;

-- Output for FULL_ACCESS_ROLE:
-- emp_id | emp_name | gov_id    | salary
--   1    | John     | GOV12345  | 80000
--   2    | Alice    | GOV67890  | 95000

Key Benefit → The original data is stored securely but shown masked to unauthorized users.

---

Section 2 — Conditional Data Masking (Region-Based Example)

Sometimes, you want to mask data only when a certain condition is met*. For example, **mask** gov_id only when* region = 'Europe'.

---

Step 1 — Create a Table

CREATE OR REPLACE TABLE citizen_data (
    citizen_id INT,
    name STRING,
    region STRING,
    gov_id STRING
);

INSERT INTO citizen_data VALUES
(1, 'Tom', 'Europe', 'EU123456'),
(2, 'Mike', 'USA', 'US998877'),
(3, 'Sara', 'Europe', 'EU456789');

---

Step 2 — Create a Conditional Masking Policy

CREATE OR REPLACE MASKING POLICY conditional_gov_mask AS (val STRING, region STRING)
RETURNS STRING ->
CASE
    WHEN region = 'Europe' THEN 'XXXXX'
    ELSE val
END;

---

Step 3 — Apply the Policy

ALTER TABLE citizen_data
MODIFY COLUMN gov_id
SET MASKING POLICY conditional_gov_mask USING (gov_id, region);

---

Step 4 — Test It

SELECT * FROM citizen_data;

-- Output:
-- citizen_id | name | region | gov_id
--      1     | Tom  | Europe | XXXXX
--      2     | Mike | USA    | US998877
--      3     | Sara | Europe | XXXXX

Key Benefit → Masking is dynamic and depends on data values.

---

Section 3 — Tag-Based Dynamic Data Masking

Tag-based masking helps when you have multiple sensitive columns across many tables.
Instead of applying policies column by column, you attach a tag to columns and apply the masking policy at tag level.

---

Step 1 — Create a Tag

CREATE OR REPLACE TAG sensitive_data_tag
COMMENT = 'Tag for sensitive columns like gov_id, phone_number, email';

---

Step 2 — Attach the Tag to a Column

ALTER TABLE employee_data
MODIFY COLUMN gov_id
SET TAG sensitive_data_tag = 'true';

---

Step 3 — Create a Tag-Based Masking Policy

CREATE OR REPLACE MASKING POLICY tag_based_mask AS (val STRING) 
RETURNS STRING ->
CASE
    WHEN CURRENT_ROLE() IN ('FULL_ACCESS_ROLE') THEN val
    ELSE '********'
END;

---

Step 4 — Associate the Policy with the Tag

ALTER TAG sensitive_data_tag
SET MASKING POLICY tag_based_mask;

Now, all columns with this tag will automatically have the masking policy applied.

---

Summary & Key Takeaways

---

Final Thoughts

Snowflake’s dynamic data masking and column-level security features make it simple to secure sensitive data while maintaining flexibility for analytics.
If you're working on enterprise-scale data platforms, tag-based masking is the most efficient way to manage security policies.