Data Retention Policies
π What is a Data Retention Policy?β
In Snowflake, a data retention policy determines how long deleted or changed data is kept for recovery or auditing. Itβs mainly about controlling the time travel and fail-safe windows:
-
β Time Travel: Allows you to query, clone, or restore historical data for a specified number of days after changes/deletion.
-
β Fail-safe: An extra 7-day window beyond time travel, allowing Snowflake to recover data in emergencies.
π Why is Data Retention Important?β
βοΈ Accidental Data Loss: Recover dropped tables or rows accidentally deleted/modified.
βοΈ Audit & Compliance: Retain historical data changes for regulatory or internal reviews.
βοΈ Cost Management: Balancing longer retention for safety vs. shorter retention for cost control.
π§ How to Configure Data Retention in Snowflakeβ
1. Time Travel Retention Periodβ
Each database, schema, and table in Snowflake has a DATA_RETENTION_TIME_IN_DAYS property.
Default:
- 1 day for most objects (standard edition)
- Up to 90 days for enterprise edition
To set it when creating a table:
CREATE TABLE my_table (
id INT,
data STRING
)
DATA_RETENTION_TIME_IN_DAYS = 5; -- Retains data for 5 days
To modify retention for an existing table:
ALTER TABLE my_table
SET DATA_RETENTION_TIME_IN_DAYS = 5;
2. Using Time Travel to Query/Restore Historical Dataβ
- β Query data at a specific point in the past:
SELECT * FROM my_table AT (OFFSET => -60*5); -- 5 minutes ago
- β Clone a historical version of a table:
CREATE TABLE my_table_clone CLONE my_table AT (TIMESTAMP => '2025-06-01 12:00:00');
- β Undrop a dropped table:
UNDROP TABLE my_table;
3. Fail-Safe Periodβ
- Fixed at 7 days for Snowflake to assist with recovery after time travel ends.
- Not user-configurable.
π Best Practices for Data Retention Policiesβ
-
β Balance Recovery Needs and Cost
- Longer retention = better recovery capability but higher storage costs (since Snowflake keeps historical data).
- Use shorter retention for non-critical or low-sensitivity data.
-
β Set retention at the table level
- Granular control β set DATA_RETENTION_TIME_IN_DAYS at the table level, not just the database level.
- E.g., production tables may need longer retention than staging tables.
-
β Review Compliance Requirements
- Align retention with internal policies or regulatory mandates (GDPR, HIPAA, etc.).
-
β Monitor Storage Usage
- Use the ACCOUNT_USAGE views or WAREHOUSE LOAD dashboard to monitor storage consumed by historical data.
-
β Automate with Tasks
- Use tasks to clean up old data or automate changes to retention policies as needed.
π·οΈ Comparison: Time Travel vs. Fail-safeβ
| Feature | Time Travel | Fail-safe |
|---|---|---|
| Purpose | Self-service recovery (user-driven) | Emergency recovery (Snowflake-driven) |
| Retention | 0β90 days (configurable) | 7 days (fixed) |
| Control | Controlled by users | Controlled by Snowflake only |
| Use cases | Undo accidental deletes, clone historical data | Disaster recovery only |
β‘ Key Takeawaysβ
βοΈ Data retention in Snowflake revolves around time travel and fail-safe windows.
βοΈ Set data retention at the table level for precision and cost management.
βοΈ Time travel retention is flexible (up to 90 days for enterprise), while fail-safe is a fixed 7 days.
βοΈ Monitor usage and align with compliance to ensure best practices.
βοΈ Automation with streams, tasks, and scripts can help manage retention systematically.
Would you like me to create a detailed .md document with real-world examples, diagrams, and advanced best practices? Let me know! πβ¨