Snowflake Guide: Data Retention and Purge

A metadata-driven framework for managing data retention, PII redaction and legal holds in Snowflake.

Overview

This repository contains SQL scripts and stored procedures to implement a comprehensive data lifecycle management system that supports:

• Retention Management: Automatically delete data based on configurable retention periods
• PII Redaction: Redact personally identifiable information (PII) before deletion
• Legal Holds: Prevent deletion of data subject to litigation, audits, or investigations
• Right to Forget: Support GDPR and privacy compliance with immediate PII redaction

Architecture

Metadata Tables

1. DATA_RETENTION_TABLE_INFO: Master table defining retention policies for each table

   • Retention period (years)
   • Redaction period (years)
   • PII flags
   • Reference join conditions for dependent tables

2. DATA_RETENTION_PII_COLUMNS: Defines PII columns and their redaction rules

   • Column name
   • PII type (NAME, ADDRESS, PHONE, etc.)
   • Redaction rule (NULL, HASH, MASK, ENCRYPT)

3. DATA_RETENTION_HOLD_RECORDS: Tracks legal holds and retention exceptions

   • Entity identification (CUSTOMER ID, etc.)
   • Hold reason (Litigation, Regulatory, Investigation)
   • Hold duration (start/end dates)

Key Concepts

Retention + Redaction Timeline

For tables with PII (e.g., CUSTOMER with 30-year retention + 2-year redaction):

0 years ←→ 30 years ←→ 32 years ←→ older
[Keep as-is]  [Redact PII]  [Delete]

DELETE Cutoff = RETENTION + REDACTION years

This ensures:

1. Data is retained with PII for 30 years (retention period)
2. PII is redacted after 30 years and kept for 2 additional years (redaction period)
3. Total lifecycle = 32 years before deletion

Directory Structure

sfguide-data-retention-and-purge/
├── sql/
│   ├── setup/
│   │   ├── 01_SETUP_ENVIRONMENT.sql
│   │   ├── 02_LOAD_SAMPLE_METADATA.sql
│   │   └── 99_CLEANUP.sql
│   ├── metadata_tables/
│   │   ├── 01_DATA_RETENTION_TABLE_INFO.sql
│   │   ├── 02_DATA_RETENTION_PII_COLUMNS.sql
│   │   └── 03_DATA_RETENTION_HOLD_RECORDS.sql
│   ├── stored_procedures/
│   │   ├── SP_REDACT_CUSTOMER_PII.sql
│   │   └── TEST_SP_REDACT_CUSTOMER_PII.sql
│   ├── storage_policies/
│   │   ├── 01_STORAGE_LIFECYCLE_POLICIES.sql
│   │   ├── 02_APPLY_STORAGE_POLICIES.sql
│   │   └── 03_MONITORING_AND_RETRIEVAL.sql
│   ├── direct_delete/
│   │   └── DELETE_EXAMPLES.sql
│   └── pii_and_holds/
│       ├── CUSTOMER_PII_REDACTION.sql
│       └── LEGAL_HOLD_EXAMPLES.sql
└── README.md

Setup Instructions

Run the setup scripts:

-- Step 1: Create environment (warehouse, database, schemas, TPCH data, metadata tables)
@sql/setup/01_SETUP_ENVIRONMENT.sql

-- Step 2: Load sample metadata
@sql/setup/02_LOAD_SAMPLE_METADATA.sql

This creates:

• Database: DB_SAMPLE_SLP
• Schemas:
  • GOVERNANCE (metadata tables)
  • DATA_SLP (for Storage Lifecycle Policy approach)
  • DATA_DELETE (for Direct Delete approach)
• Sample Data: TPCH tables in both DATA_SLP and DATA_DELETE schemas (~6M rows)
• Metadata: Pre-configured retention policies and PII column definitions

Cleanup

To remove all objects created by the setup:

-- Warning: This will permanently delete all data and objects
@sql/setup/99_CLEANUP.sql

This drops:

• Database (DB_SAMPLE_SLP) with all schemas and tables
• Warehouse (DLM_WH)
• Storage lifecycle policies
• Stored procedures

Usage Examples

Storage lifecycle policies

Automated data lifecycle management using Snowflake's native storage policies:

See sql/storage_policies/ for:

• 01_STORAGE_LIFECYCLE_POLICIES.sql - Policy definitions
• 02_APPLY_STORAGE_POLICIES.sql - Applying policies to tables
• 03_MONITORING_AND_RETRIEVAL.sql - Monitoring and retrieving archived data

Data Deletion

Direct SQL examples for deleting old data based on retention policies:

See sql/direct_delete/DELETE_EXAMPLES.sql for ORDERS and LINEITEM deletion examples.

Legal Holds

Place, manage and release legal holds on customer data:

See sql/pii_and_holds/LEGAL_HOLD_EXAMPLES.sql for complete legal hold examples including:

• Checking for active holds
• Placing new holds
• Releasing holds
• Deletion with hold checks

PII Redaction

Use the stored procedure to redact PII for specific customers:

CALL SP_REDACT_CUSTOMER_PII('100,101,102,103,104');

Storage lifecycle policies

Snowflake's native storage lifecycle policies provide automated, declarative data lifecycle management. These policies complement the stored procedure approach.

Policy Lifecycle

1. Active Data
   ↓
2. Policy Evaluation (automatic)
   ↓
3. Archive to COOL Storage (retrievable)
   ↓
4. Wait ARCHIVE_FOR_DAYS (90 days)
   ↓
5. Permanent Deletion (not retrievable)

See the following scripts for detailed examples:

• sql/storage_policies/02_APPLY_STORAGE_POLICIES.sql - Applying policies to tables
• sql/storage_policies/03_MONITORING_AND_RETRIEVAL.sql - Monitoring and retrieving archived data

For complete documentation on Storage lifecycle policies, refer to Snowflake Documentation.

Sample Redaction Rules

Sample Data (TPCH Database)

The PoC uses TPCH sample data in two separate schemas:

Schema Organization

Table Configuration

Data Volume (TPCH_SF1):

• LINEITEM: ~6M rows
• ORDERS: ~1.5M rows
• CUSTOMER: ~150K rows
• Other tables: <1M rows

Stored Procedures

SP_REDACT_CUSTOMER_PII

Redacts PII for specified customer keys.

Parameters:

• P_CUSTKEY_LIST (VARCHAR): Comma-separated list of customer keys (e.g., '1,2,3,4,5')

Features:

• Metadata-driven: reads PII columns and rules from metadata
• Supports NULL, HASH, MASK, ENCRYPT redaction rules
• Bulk operation using LISTAGG

Example:

CALL SP_REDACT_CUSTOMER_PII('100,101,102');

-- With explicit schema
CALL SP_REDACT_CUSTOMER_PII('100,101,102', 'DATA_DELETE');

Best Practices

1. Redact PII Before Deletion

For tables with PII, always redact first:

1. Run redaction (time-based or on-demand)
2. Then run deletion (using direct SQL or storage policies)

2. Check for Legal Holds

Before any deletion, verify no active holds:

SELECT * FROM DATA_RETENTION_HOLD_RECORDS
WHERE HOLD_ENTITY_NAME = 'CUSTOMER'
  AND IS_ACTIVE = TRUE
  AND (HOLD_END_DATE IS NULL OR HOLD_END_DATE > CURRENT_DATE());

3. Monitor Impact

Use preview queries to check record counts:

-- See sql/direct_delete/DELETE_EXAMPLES.sql
-- For CUSTOMER-specific examples, see sql/pii_and_holds/CUSTOMER_PII_REDACTION.sql

4. Monitor Storage Policies

Regularly review policy execution:

SELECT * FROM TABLE(
    INFORMATION_SCHEMA.STORAGE_LIFECYCLE_POLICY_HISTORY(
        RESULT_LIMIT => 1000
    )
);

5. Archive Retrieval Window

Remember the lifecycle timeline:

• Data moves to COOL storage (retrievable)
• After ARCHIVE_FOR_DAYS (90 days), permanently deleted
• Test retrieval before data expires

Compliance Features

GDPR Support

• Right to Forget: Immediate PII redaction by customer ID
• Right to Restrict: Legal hold functionality
• Data Minimization: Automated retention policies

Audit Requirements

• All changes tracked with timestamps and user
• Legal hold tracking with reasons and durations
• Dry run mode for impact analysis

Data Protection

• PII identified at column level
• Multiple redaction strategies
• Separation of redaction and deletion

Future Enhancements

• [ ] Automated scheduling via tasks
• [ ] Email notifications for holds nearing expiration
• [ ] Dashboard for lifecycle management metrics
• [ ] Integration with Snowflake Data Classification
• [ ] Audit and observability tables for tracking all operations
• [ ] Log tables for removed records to enable downstream and upstream application synchronization

Contact

Rahul Agrawal
Email: [email protected]

For additional assistance with data lifecycle management implementation, please get in touch with the Professional Services team at Snowflake.

License

Copyright (c) Snowflake Inc. All rights reserved.

Licensed under the Apache 2.0 license.