Skip to main content

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​

FeatureTime TravelFail-safe
PurposeSelf-service recovery (user-driven)Emergency recovery (Snowflake-driven)
Retention0–90 days (configurable)7 days (fixed)
ControlControlled by usersControlled by Snowflake only
Use casesUndo accidental deletes, clone historical dataDisaster 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! πŸš€βœ¨