snowflake-data-handling

Snowflake Data Handling

Overview

Implement data governance in Snowflake using column-level masking policies, row access policies, object tagging, and data classification for GDPR/CCPA compliance.

Prerequisites

• Enterprise Edition or higher (for masking and row access policies)
• SECURITYADMIN or ACCOUNTADMIN role
• Understanding of GDPR/CCPA data subject rights

Instructions

Step 1: Data Classification with Tags

-- Create tag taxonomy
CREATE TAG IF NOT EXISTS pii_type
  ALLOWED_VALUES 'email', 'phone', 'ssn', 'name', 'address';

CREATE TAG IF NOT EXISTS data_sensitivity
  ALLOWED_VALUES 'public', 'internal', 'confidential', 'restricted';

-- Apply tags to columns
ALTER TABLE users MODIFY COLUMN email SET TAG pii_type = 'email';
ALTER TABLE users MODIFY COLUMN phone SET TAG pii_type = 'phone';
ALTER TABLE users MODIFY COLUMN name SET TAG pii_type = 'name';
ALTER TABLE users MODIFY COLUMN email SET TAG data_sensitivity = 'confidential';

-- Find all tagged columns
SELECT * FROM TABLE(INFORMATION_SCHEMA.TAG_REFERENCES(
  'users', 'TABLE'
));

-- Discover PII with Snowflake's automatic classification (Enterprise+)
SELECT *
FROM TABLE(
  INFORMATION_SCHEMA.EXTRACT_SEMANTIC_CATEGORIES('users')
);

Step 2: Column-Level Masking Policies

-- Dynamic masking — shows real data to privileged roles, masked to others
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING)
  RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('DATA_ENGINEER', 'SYSADMIN') THEN val
    WHEN CURRENT_ROLE() = 'DATA_ANALYST' THEN
      REGEXP_REPLACE(val, '.+@', '***@')  -- Show domain only
    ELSE '***MASKED***'
  END;

CREATE OR REPLACE MASKING POLICY phone_mask AS (val STRING)
  RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('DATA_ENGINEER', 'SYSADMIN') THEN val
    ELSE CONCAT('***-***-', RIGHT(val, 4))  -- Show last 4 digits
  END;

CREATE OR REPLACE MASKING POLICY ssn_mask AS (val STRING)
  RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('SYSADMIN') THEN val
    ELSE '***-**-' || RIGHT(val, 4)
  END;

-- Apply masking policies to columns
ALTER TABLE users MODIFY COLUMN email SET MASKING POLICY email_mask;
ALTER TABLE users MODIFY COLUMN phone SET MASKING POLICY phone_mask;

-- Tag-based masking (apply policy to all columns with a tag)
ALTER TAG pii_type SET MASKING POLICY email_mask;
-- Now ALL columns tagged pii_type='email' are automatically masked

Step 3: Row Access Policies

-- Row-level security — users only see their own department's data
CREATE OR REPLACE ROW ACCESS POLICY department_access AS (department_col VARCHAR)
  RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'SYSADMIN'
  OR department_col = CURRENT_ROLE()  -- Role name matches department
  OR EXISTS (
    SELECT 1 FROM access_grants
    WHERE user_name = CURRENT_USER()
      AND department = department_col
  );

-- Apply to table
ALTER TABLE employees ADD ROW ACCESS POLICY department_access ON (department);

-- Verify: analyst role only sees their department
USE ROLE ANALYST_ROLE;
SELECT * FROM employees;  -- Only rows matching their department

Step 4: GDPR Data Subject Rights

-- Right to Access (DSAR): Export all user data
CREATE OR REPLACE PROCEDURE export_user_data(user_email VARCHAR)
  RETURNS TABLE (source VARCHAR, data VARIANT)
  LANGUAGE SQL
AS
$$
  SELECT 'users' AS source, OBJECT_CONSTRUCT(*) AS data
  FROM users WHERE email = user_email
  UNION ALL
  SELECT 'orders', OBJECT_CONSTRUCT(*)
  FROM orders WHERE customer_email = user_email
  UNION ALL
  SELECT 'events', OBJECT_CONSTRUCT(*)
  FROM events WHERE user_email = user_email
$$;

-- Right to Erasure: Delete all user data
CREATE OR REPLACE PROCEDURE delete_user_data(user_email VARCHAR)
  RETURNS VARCHAR
  LANGUAGE SQL
AS
$$
BEGIN
  -- Delete from all tables containing user data
  DELETE FROM events WHERE user_email = :user_email;
  DELETE FROM orders WHERE customer_email = :user_email;
  DELETE FROM users WHERE email = :user_email;

  -- Audit log (must retain for compliance)
  INSERT INTO gdpr_audit_log (action, subject_email, executed_at, executed_by)
  VALUES ('ERASURE', :user_email, CURRENT_TIMESTAMP(), CURRENT_USER());

  RETURN 'Deletion complete for ' || :user_email;
END;
$$;

-- Right to Rectification
UPDATE users SET name = 'New Name' WHERE email = 'user@example.com';
INSERT INTO gdpr_audit_log (action, subject_email, executed_at, executed_by)
VALUES ('RECTIFICATION', 'user@example.com', CURRENT_TIMESTAMP(), CURRENT_USER());

Step 5: Data Retention and Cleanup

-- Automated data retention with tasks
CREATE OR REPLACE TASK enforce_retention
  WAREHOUSE = ADMIN_WH
  SCHEDULE = 'USING CRON 0 2 * * * UTC'  -- 2 AM UTC daily
AS
BEGIN
  -- Delete audit logs older than 7 years
  DELETE FROM audit_logs
  WHERE created_at < DATEADD(years, -7, CURRENT_TIMESTAMP());

  -- Delete session logs older than 90 days
  DELETE FROM session_logs
  WHERE created_at < DATEADD(days, -90, CURRENT_TIMESTAMP());

  -- Anonymize old order data (keep for analytics, remove PII)
  UPDATE orders SET
    customer_email = SHA2(customer_email),
    customer_name = 'ANONYMIZED'
  WHERE order_date < DATEADD(years, -2, CURRENT_DATE())
    AND customer_name != 'ANONYMIZED';
END;

ALTER TASK enforce_retention RESUME;

Step 6: Audit Trail

-- Query access history — who accessed what
SELECT user_name, query_text, start_time,
       direct_objects_accessed
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
WHERE start_time >= DATEADD(days, -7, CURRENT_TIMESTAMP())
  AND ARRAY_CONTAINS('USERS'::VARIANT,
    TRANSFORM(direct_objects_accessed, x -> x:objectName))
ORDER BY start_time DESC;

Error Handling

Issue	Cause	Solution
Masking policy error on query	Policy function error	Test with SELECT email_mask('test@test.com')
Row access blocks all rows	Policy too restrictive	Check CURRENT_ROLE() logic
Tag not found	Wrong scope	Ensure tag is in same or parent schema
GDPR deletion incomplete	Foreign key dependencies	Delete child records first

Resources

• Masking Policies
• Row Access Policies
• Data Classification
• Access History

Next Steps

For enterprise RBAC, see snowflake-enterprise-rbac.