From snowflake-pack
Implements Snowflake data governance with column masking policies, row access policies, object tagging, and data classification for PII handling and GDPR/CCPA compliance.
npx claudepluginhub jeremylongshore/claude-code-plugins-plus-skills --plugin snowflake-packThis skill is limited to using the following tools:
Implement data governance in Snowflake using column-level masking policies, row access policies, object tagging, and data classification for GDPR/CCPA compliance.
Implements Snowflake governance with network rules/policies, authentication policies, session policies, and statement timeouts for security and compliance enforcement.
Implements GDPR compliance, PII tagging/masking, data retention policies, and deletion workflows in Databricks Delta Lake using Unity Catalog and PySpark.
Assists Snowflake developers with SQL best practices, data pipelines (Dynamic Tables, Streams, Tasks, Snowpipe), Cortex AI functions/Agents, Snowpark Python, dbt integration, performance tuning, and security hardening.
Share bugs, ideas, or general feedback.
Implement data governance in Snowflake using column-level masking policies, row access policies, object tagging, and data classification for GDPR/CCPA compliance.
-- 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')
);
-- 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
-- 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
-- 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());
-- 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;
-- 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;
| 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 |
For enterprise RBAC, see snowflake-enterprise-rbac.