Dynamic Data Masking in Snowflake

Architecture Overview

Architecture Diagram 2: Masking Policy Types Comparison

Detailed Explanation

What is Dynamic Data Masking (DDM)?

Security feature that transforms sensitive data at query time based on the executing role — without modifying stored data.

How Masking Policies Work

1. Query arrives → column accessed
2. Policy lookup (cached in memory)
3. CASE expression evaluates role
4. Execute branch for matching role → return transformed value

Transformation Functions

• Zero storage overhead — original data preserved
• < 1% query performance degradation (negligible)

Column-Level Security

Different users query the same table but receive different data views:

• Customer service: last 4 digits of credit card
• Fraud analyst: full card number
• Auditor: audit trail of access

Conditional Masking

• Time-based: business hours vs. after-hours
• Network-based: internal vs. external access
• Session-based: warehouse or role context

Compliance Support

Key Takeaway: Use full masking for PII (SSN, credit card). Use partial masking for phone numbers (show last 4). Audit quarterly for policy coverage gaps.

Key Concepts

Code Examples

1. Creating a Basic Masking Policy

-- Create a masking policy for PII data
-- Parameters:
--   (val STRING): Input column value to mask
--   RETURNS STRING: Output type (must match column type)
--   CURRENT_ROLE(): Built-in function returning the executing role
--   CASE WHEN: Role-based branching logic
CREATE OR REPLACE MASKING POLICY pii_masking_policy AS (val STRING)
RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('ADMIN', 'SECURITY_OFFICER') THEN val  -- Full access
        WHEN CURRENT_ROLE() = 'ANALYST' THEN REGEXP_REPLACE(val, '.', '*')  -- Full mask
        WHEN CURRENT_ROLE() = 'SUPPORT' THEN 
            CONCAT(SUBSTRING(val, 1, 2), REPEAT('*', LENGTH(val) - 4), SUBSTRING(val, -2))  -- Partial mask
        ELSE '***MASKED***'  -- Default mask for other roles
    END;

-- Apply masking policy to a column
-- ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY attaches the policy
-- One active policy per column; re-applying replaces the previous policy
ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY pii_masking_policy;

-- Apply same policy to multiple columns
ALTER TABLE customers MODIFY COLUMN phone_number SET MASKING POLICY pii_masking_policy;
ALTER TABLE customers MODIFY COLUMN ssn SET MASKING POLICY pii_masking_policy;

-- Remove a masking policy (column returns to original values)
ALTER TABLE customers MODIFY COLUMN email UNSET MASKING POLICY;

2. Conditional Masking Based on Context

-- Create a conditional masking policy with time-based rules
-- HOUR(CURRENT_TIMESTAMP()) enables business-hours vs after-hours masking
-- This is useful for compliance where data access varies by time of day
CREATE OR REPLACE MASKING POLICY conditional_masking_policy AS (val STRING)
RETURNS STRING ->
    CASE
        -- Full access during business hours for admins
        WHEN CURRENT_ROLE() = 'ADMIN' 
             AND HOUR(CURRENT_TIMESTAMP()) BETWEEN 8 AND 18 THEN val
        -- Partial mask during business hours for analysts
        WHEN CURRENT_ROLE() = 'ANALYST' 
             AND HOUR(CURRENT_TIMESTAMP()) BETWEEN 8 AND 18 THEN 
            CONCAT(SUBSTRING(val, 1, 3), '***', SUBSTRING(val, -3))
        -- Full mask outside business hours for everyone
        ELSE '***RESTRICTED***'
    END;

-- Create a masking policy with warehouse-based conditions
-- Different masking based on which warehouse is being used
-- Useful for external/partner access scenarios
CREATE OR REPLACE MASKING POLICY network_masking_policy AS (val STRING)
RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() = 'ADMIN' THEN val
        WHEN CURRENT_WAREHOUSE() IN ('EXTERNAL_WH', 'PARTNER_WH') THEN 
            CONCAT('EXTERNAL_', HASH(val, 256))  -- Hash for external access
        ELSE val
    END;

-- Create a masking policy with session context
-- Use CURRENT_SESSION() or custom session variables for more context
CREATE OR REPLACE MASKING POLICY session_masking_policy AS (val STRING)
RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() = 'DATA_ENGINEER' THEN val
        WHEN CURRENT_ROLE() = 'ANALYST' THEN 
            CASE 
                WHEN CURRENT_WAREHOUSE() = 'PROD_WH' THEN val  -- Full access in prod
                ELSE CONCAT(SUBSTRING(val, 1, 3), '***')  -- Masked in dev
            END
        ELSE '***MASKED***'
    END;

3. Numeric and Date Masking

-- Create a masking policy for numeric data
-- Progressive granularity: Finance sees exact, Analysts see rounded, Others see zero
CREATE OR REPLACE MASKING POLICY numeric_masking_policy AS (val NUMBER)
RETURNS NUMBER ->
    CASE
        WHEN CURRENT_ROLE() = 'ADMIN' THEN val                           -- Exact value
        WHEN CURRENT_ROLE() = 'FINANCE' THEN ROUND(val, -1)             -- Round to nearest 10
        WHEN CURRENT_ROLE() = 'ANALYST' THEN ROUND(val, -2)             -- Round to nearest 100
        ELSE 0                                                            -- Zero for others
    END;

-- Create a masking policy for date data
-- Progressive granularity: exact date -> month -> year -> fixed date
CREATE OR REPLACE MASKING POLICY date_masking_policy AS (val DATE)
RETURNS DATE ->
    CASE
        WHEN CURRENT_ROLE() = 'ADMIN' THEN val                           -- Exact date
        WHEN CURRENT_ROLE() = 'ANALYST' THEN DATE_TRUNC('MONTH', val)   -- First day of month
        WHEN CURRENT_ROLE() = 'SUPPORT' THEN DATE_TRUNC('YEAR', val)    -- First day of year
        ELSE '1900-01-01'::DATE                                          -- Fixed date for others
    END;

-- Create a masking policy for TIMESTAMP data
CREATE OR REPLACE MASKING POLICY timestamp_masking_policy AS (val TIMESTAMP_NTZ)
RETURNS TIMESTAMP_NTZ ->
    CASE
        WHEN CURRENT_ROLE() = 'ADMIN' THEN val
        WHEN CURRENT_ROLE() = 'ANALYST' THEN DATE_TRUNC('DAY', val)     -- Truncate to day
        ELSE '1900-01-01'::TIMESTAMP_NTZ
    END;

-- Apply policies to financial table
ALTER TABLE financial_transactions MODIFY COLUMN amount SET MASKING POLICY numeric_masking_policy;
ALTER TABLE financial_transactions MODIFY COLUMN transaction_date SET MASKING POLICY date_masking_policy;
ALTER TABLE financial_transactions MODIFY COLUMN created_at SET MASKING POLICY timestamp_masking_policy;

4. Tokenization and Hashing Policies

-- Create a tokenization masking policy (irreversible hash)
-- HASH() function produces a deterministic hash for data matching
-- Useful for joining across systems without exposing raw values
CREATE OR REPLACE MASKING POLICY tokenization_policy AS (val STRING)
RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('ADMIN', 'TOKENIZER') THEN val
        ELSE HASH(val, 256)  -- SHA-256 hash (irreversible)
    END;

-- Create a reversible tokenization policy using AES encryption
-- AES_ENCRYPT produces encrypted values that can be decrypted
-- Note: Key management is critical for security
CREATE OR REPLACE MASKING POLICY aes_tokenization_policy AS (val STRING)
RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('ADMIN', 'DETOKENIZER') THEN val
        ELSE AES_ENCRYPT(val, 'your-secret-key-here')  -- Encrypted token
    END;

-- Create a masking policy for email with partial reveal
CREATE OR REPLACE MASKING POLICY email_masking_policy AS (val STRING)
RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('ADMIN', 'DATA_ENGINEER') THEN val
        WHEN CURRENT_ROLE() = 'ANALYST' THEN 
            CONCAT(SUBSTRING(val, 1, 2), '***@', SPLIT_PART(val, '@', 2))
        ELSE '***@***.com'
    END;

-- Create a masking policy for phone numbers
CREATE OR REPLACE MASKING POLICY phone_masking_policy AS (val STRING)
RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('ADMIN', 'SUPPORT') THEN val
        WHEN CURRENT_ROLE() = 'ANALYST' THEN 
            CONCAT('(***) ***-', SUBSTRING(val, -4))  -- Show last 4 digits
        ELSE '(***) ***-****'
    END;

-- Apply tokenization and masking to sensitive columns
ALTER TABLE customer_data MODIFY COLUMN credit_card_number SET MASKING POLICY tokenization_policy;
ALTER TABLE customer_data MODIFY COLUMN ssn SET MASKING POLICY aes_tokenization_policy;
ALTER TABLE customer_data MODIFY COLUMN email SET MASKING POLICY email_masking_policy;
ALTER TABLE customer_data MODIFY COLUMN phone SET MASKING POLICY phone_masking_policy;

5. Policy Management and Auditing

-- List all masking policies in the account
SHOW MASKING POLICIES;

-- View masking policy details
DESC MASKING POLICY pii_masking_policy;

-- Find which columns have masking policies applied
SELECT 
    policy_name,
    ref_database_name,
    ref_schema_name,
    ref_entity_name,
    ref_entity_domain
FROM TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(
    REF_ENTITY_NAME => 'customers',
    REF_ENTITY_DOMAIN => 'TABLE'
));

-- Audit masking policy usage via ACCESS_HISTORY
SELECT 
    query_id,
    user_name,
    query_text,
    direct_objects_accessed
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
WHERE query_start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
  AND direct_objects_accessed LIKE '%customers%'
ORDER BY query_start_time DESC;

-- Check policy references for a specific table
SELECT * FROM TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(
    REF_ENTITY_NAME => 'financial_transactions',
    REF_ENTITY_DOMAIN => 'TABLE'
));

6. Python Implementation

import snowflake.connector
from snowflake.connector import DictCursor

def create_masking_policies():
    """Create comprehensive masking policies in Snowflake"""
    conn = snowflake.connector.connect(
        user='your_user',
        password='your_password',
        account='your_account',
        warehouse='COMPUTE_WH',
        database='SECURITY_DB',
        schema='MASKING'
    )
    
    try:
        cursor = conn.cursor()
        
        # Create masking policy for email
        cursor.execute("""
            CREATE OR REPLACE MASKING POLICY email_masking_policy AS (val STRING)
            RETURNS STRING ->
                CASE
                    WHEN CURRENT_ROLE() IN ('ADMIN', 'DATA_ENGINEER') THEN val
                    WHEN CURRENT_ROLE() = 'ANALYST' THEN 
                        CONCAT(SUBSTRING(val, 1, 2), '***@', SPLIT_PART(val, '@', 2))
                    ELSE '***@***.com'
                END
        """)
        
        # Create masking policy for phone numbers
        cursor.execute("""
            CREATE OR REPLACE MASKING POLICY phone_masking_policy AS (val STRING)
            RETURNS STRING ->
                CASE
                    WHEN CURRENT_ROLE() IN ('ADMIN', 'SUPPORT') THEN val
                    WHEN CURRENT_ROLE() = 'ANALYST' THEN 
                        CONCAT('(***) ***-', SUBSTRING(val, -4))
                    ELSE '(***) ***-****'
                END
        """)
        
        # Create masking policy for financial data
        cursor.execute("""
            CREATE OR REPLACE MASKING POLICY financial_masking_policy AS (val NUMBER)
            RETURNS NUMBER ->
                CASE
                    WHEN CURRENT_ROLE() = 'FINANCE_ADMIN' THEN val
                    WHEN CURRENT_ROLE() = 'FINANCE_ANALYST' THEN ROUND(val, -2)
                    WHEN CURRENT_ROLE() = 'ANALYST' THEN ROUND(val, -3)
                    ELSE 0
                END
        """)
        
        # Apply policies to tables
        cursor.execute("""
            ALTER TABLE customer_data 
            MODIFY COLUMN email SET MASKING POLICY email_masking_policy;
        """)
        
        cursor.execute("""
            ALTER TABLE customer_data 
            MODIFY COLUMN phone SET MASKING POLICY phone_masking_policy;
        """)
        
        cursor.execute("""
            ALTER TABLE financial_data 
            MODIFY COLUMN amount SET MASKING POLICY financial_masking_policy;
        """)
        
        print("Masking policies created and applied successfully!")
        
    finally:
        conn.close()

def query_masked_data():
    """Demonstrate how different roles see different data"""
    conn = snowflake.connector.connect(
        user='your_user',
        password='your_password',
        account='your_account',
        warehouse='COMPUTE_WH',
        database='SECURITY_DB',
        schema='MASKING'
    )
    
    try:
        cursor = conn.cursor()
        
        # Query as analyst role
        cursor.execute("USE ROLE ANALYST")
        cursor.execute("""
            SELECT 
                customer_id,
                email,
                phone,
                credit_card_number
            FROM customer_data
            LIMIT 5
        """)
        
        print("Data viewed as ANALYST:")
        for row in cursor.fetchall():
            print(f"  ID: {row[0]}, Email: {row[1]}, Phone: {row[2]}, CC: {row[3]}")
        
        # Query as admin role
        cursor.execute("USE ROLE ADMIN")
        cursor.execute("""
            SELECT 
                customer_id,
                email,
                phone,
                credit_card_number
            FROM customer_data
            LIMIT 5
        """)
        
        print("\nData viewed as ADMIN:")
        for row in cursor.fetchall():
            print(f"  ID: {row[0]}, Email: {row[1]}, Phone: {row[2]}, CC: {row[3]}")
        
    finally:
        conn.close()

if __name__ == "__main__":
    create_masking_policies()
    query_masked_data()

Performance Metrics

Best Practices

1. Use Role-Based Masking: Always design masking policies around user roles rather than individual users for scalability and maintainability.

2. Implement Least Privilege: Start with the most restrictive masking and gradually grant exceptions based on business need.

3. Test Policy Impact: Measure query performance before and after applying masking policies to ensure they don't introduce unexpected latency.

4. Version Control Policies: Store masking policy definitions in Git and deploy through CI/CD pipelines for audit trail and rollback capability.

5. Monitor Policy Usage: Use ACCESS_HISTORY to track which policies are being applied and identify potential abuse patterns.

6. Combine with Row-Level Security: Masking policies work best when combined with row-level security policies for comprehensive data protection.

7. Use External Tokenization: For PCI-DSS compliance, consider using external tokenization services instead of built-in masking for payment card data.

8. Regular Policy Audits: Review masking policies quarterly to ensure they align with current compliance requirements and data classification standards.

Additional Theory: Compliance Frameworks and Masking

Dynamic data masking supports compliance with major regulations:

Data classification for masking:

• Public: No masking required
• Internal: Basic masking for external roles
• Confidential: Full masking for non-authorized roles
• Restricted: Tokenization/encryption + strict access control

Additional Theory: Masking Policy Evaluation Flow

Query arrives → Column accessed → Policy lookup → Role check → Transform → Return value
                     ↓                    ↓              ↓
              Policy metadata      CASE expression   Execute branch
              (cached in memory)   evaluates role     for matching role

Performance characteristics:

• Policy evaluation: < 1ms per row (negligible overhead)
• Cache hit rate: > 99% (policies cached in cloud services)
• No storage overhead: Original data preserved; transformation is query-time only
• No data duplication: Single copy of data with role-based views

See Also

• 05-Access-Control-RBAC - Role-based access control
• 10-Data-Sharing - Securing shared data
• 14-Monitoring-Queries - Auditing data access patterns
• PySpark Iceberg - Data lake security patterns
• Delta Lake on Databricks - Delta Lake security model
• Data Warehouse Concepts - Data warehouse design principles