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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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](https://docs.snowflake.com/en/user-guide/tag-based-masking-policies)
- [Row Access Policies](https://docs.snowflake.com/en/user-guide/security-row-intro)
- [Data Classification](https://docs.snowflake.com/en/user-guide/governance-classify-concepts)
- [Access History](https://docs.snowflake.com/en/sql-reference/account-usage/access_history)

## Next Steps

For enterprise RBAC, see `snowflake-enterprise-rbac`.