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 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';
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';
SELECT * FROM TABLE(INFORMATION_SCHEMA.TAG_REFERENCES(
'users', 'TABLE'
));
SELECT *
FROM TABLE(
INFORMATION_SCHEMA.EXTRACT_SEMANTIC_CATEGORIES('users')
);
Step 2: Column-Level Masking Policies
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, '.+@', '***@')
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))
END;
CREATE OR REPLACE MASKING POLICY ssn_mask AS (val STRING)
RETURNS STRING ->
CASE
WHEN () () val
(val, )
;
users MODIFY email MASKING POLICY email_mask;
users MODIFY phone MASKING POLICY phone_mask;
TAG pii_type MASKING POLICY email_mask;
Step 3: Row Access Policies
CREATE OR REPLACE ROW ACCESS POLICY department_access AS (department_col VARCHAR)
RETURNS BOOLEAN ->
CURRENT_ROLE() = 'SYSADMIN'
OR department_col = CURRENT_ROLE()
OR EXISTS (
SELECT 1 FROM access_grants
WHERE user_name = CURRENT_USER()
AND department = department_col
);
ALTER TABLE employees ADD ROW ACCESS POLICY department_access ON (department);
USE ROLE ANALYST_ROLE;
SELECT * FROM employees;
Step 4: GDPR Data Subject Rights
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
$$;
CREATE OR REPLACE PROCEDURE delete_user_data(user_email VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
DELETE FROM events WHERE user_email = :user_email;
orders customer_email :user_email;
users email :user_email;
gdpr_audit_log (action, subject_email, executed_at, executed_by)
(, :user_email, (), ());
:user_email;
;
$$;
users name email ;
gdpr_audit_log (action, subject_email, executed_at, executed_by)
(, , (), ());
Step 5: Data Retention and Cleanup
CREATE OR REPLACE TASK enforce_retention
WAREHOUSE = ADMIN_WH
SCHEDULE = 'USING CRON 0 2 * * * UTC'
AS
BEGIN
DELETE FROM audit_logs
WHERE created_at < DATEADD(years, -7, CURRENT_TIMESTAMP());
DELETE FROM session_logs
WHERE created_at < DATEADD(days, -90, CURRENT_TIMESTAMP());
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
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
Next Steps
For enterprise RBAC, see snowflake-enterprise-rbac.
