Dynamic Data Masking in Snowflake

Architecture Overview

┌─────────────────────────────────────────────────────────────────────────────────┐
│                        SNOWFLAKE DYNAMIC DATA MASKING                           │
│                                                                                 │
│  ┌──────────────┐    ┌──────────────────────────────────────────────────────┐   │
│  │  CLIENT APP  │───▶│              MASKING POLICY ENGINE                   │   │
│  └──────────────┘    │                                                      │   │
│                      │  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐  │   │
│                      │  │  CONDITION   │  │   COLUMN    │  │  ROW-LEVEL  │  │   │
│                      │  │   RULES      │  │   SECURITY  │  │  MASKING    │  │   │
│                      │  └──────┬──────┘  └──────┬──────┘  └──────┬──────┘  │   │
│                      │         │                │                │         │   │
│                      │         ▼                ▼                ▼         │   │
│                      │  ┌──────────────────────────────────────────────┐  │   │
│                      │  │            MASKING FUNCTIONS                 │  │   │
│                      │  │  ┌────────┐ ┌────────┐ ┌────────┐ ┌──────┐  │  │   │
│                      │  │  │ SHA256 │ │ SHA512 │ │AES_ENC │ │HASH │  │  │   │
│                      │  │  └────────┘ └────────┘ └────────┘ └──────┘  │  │   │
│                      │  └──────────────────────────────────────────────┘  │   │
│                      └──────────────────────────────────────────────────┘   │
│                                      │                                       │
│                                      ▼                                       │
│  ┌──────────────────────────────────────────────────────────────────────┐   │
│  │                    ENCRYPTED DATA LAYER                              │   │
│  │  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────┐            │   │
│  │  │ Column 1 │  │ Column 2 │  │ Column 3 │  │ Column 4 │            │   │
│  │  │ (Masked) │  │ (Original)│  │ (Tokenized)│ │(Redacted)│            │   │
│  │  └──────────┘  └──────────┘  └──────────┘  └──────────┘            │   │
│  └──────────────────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────────────────┘

Detailed Explanation

Dynamic Data Masking (DDM) in Snowflake is a powerful security feature that provides real-time transformation of sensitive data at the query layer without modifying the underlying stored data. This mechanism operates as a security abstraction layer, intercepting SQL queries and applying predefined transformation rules based on the executing user's role, attributes, and context.

The masking policy engine evaluates each column access against a set of conditional rules that determine whether to return the original value, a masked version, or an error. This evaluation happens at query execution time, ensuring zero latency impact on data storage while providing consistent security enforcement across all access paths.

Snowflake's masking policies support multiple transformation functions including full redaction (replacing values with static strings), partial masking (preserving partial data like last 4 digits of SSN), hashing (SHA-256, SHA-512), encryption (AES-256), and tokenization. Each function can be combined with conditional logic to create context-aware masking that adapts based on user roles, time of day, IP address, or any custom session parameter.

Column-level security through masking policies enables fine-grained access control where different users can query the same table but receive different data views. For example, a customer service representative might see only the last 4 digits of a credit card number, while a fraud analyst sees the full number, and an auditor sees an audit trail of who accessed what.

The conditional masking feature allows organizations to implement complex business rules such as masking data differently during business hours versus off-hours, or applying stricter masking for users accessing from external networks. This flexibility is critical for compliance with regulations like GDPR, CCPA, HIPAA, and PCI-DSS that require different levels of data protection based on data classification and user authorization.

Snowflake's approach to dynamic data masking differs from traditional static masking by eliminating the need for separate masked copies of datasets. This reduces storage costs, eliminates data synchronization issues, and ensures that masked data always reflects the most current state of the source data. The masking policies are stored as metadata and applied transparently, making the implementation invisible to end-user applications.

Key Concepts

Code Examples

1. Creating a Basic Masking Policy

-- Create a masking policy for PII data
CREATE OR REPLACE MASKING POLICY pii_masking_policy AS (val STRING)
RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('ADMIN', 'SECURITY_OFFICER') THEN val
        WHEN CURRENT_ROLE() = 'ANALYST' THEN REGEXP_REPLACE(val, '.', '*')
        WHEN CURRENT_ROLE() = 'SUPPORT' THEN 
            CONCAT(SUBSTRING(val, 1, 2), REPEAT('*', LENGTH(val) - 4), SUBSTRING(val, -2))
        ELSE '***MASKED***'
    END;

-- Apply masking policy to a column
ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY pii_masking_policy;

-- Apply masking 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;

2. Conditional Masking Based on Context

-- Create a conditional masking policy with time-based rules
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 network-based conditions
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))
        ELSE val
    END;

3. Numeric and Date Masking

-- Create a masking policy for numeric data
CREATE OR REPLACE MASKING POLICY numeric_masking_policy AS (val NUMBER)
RETURNS NUMBER ->
    CASE
        WHEN CURRENT_ROLE() = 'ADMIN' THEN val
        WHEN CURRENT_ROLE() = 'ANALYST' THEN ROUND(val, -2)  -- Round to nearest 100
        WHEN CURRENT_ROLE() = 'FINANCE' THEN ROUND(val, -1)  -- Round to nearest 10
        ELSE 0
    END;

-- Create a masking policy for date data
CREATE OR REPLACE MASKING POLICY date_masking_policy AS (val DATE)
RETURNS DATE ->
    CASE
        WHEN CURRENT_ROLE() = 'ADMIN' THEN val
        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
    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;

4. Tokenization Policy

-- Create a tokenization masking policy
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 for tokenization
    END;

-- Create a reversible tokenization policy using AES encryption
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;

-- Apply tokenization 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;

5. 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.

See Also

• PySpark Iceberg - Data lake security patterns
• Delta Lake on Databricks - Delta Lake security model
• Data Warehouse Concepts - Data warehouse design principles