Data retention settings in Snowflake are crucial configurations that determine how long historical data and deleted/modified data remain accessible for recovery purposes. These settings are primarily governed by two key features: Time Travel and Fail-safe.
Time Travel allows users to access histor…Data retention settings in Snowflake are crucial configurations that determine how long historical data and deleted/modified data remain accessible for recovery purposes. These settings are primarily governed by two key features: Time Travel and Fail-safe.
Time Travel allows users to access historical data within a defined retention period. The DATA_RETENTION_TIME_IN_DAYS parameter controls this window, which can be set at the account, database, schema, or table level. For Snowflake Standard Edition, the maximum retention period is 1 day, while Enterprise Edition and higher support up to 90 days. During this period, users can query data as it existed at any point, clone objects from historical states, and restore dropped objects using the UNDROP command.
Fail-safe provides an additional 7-day period of data protection after the Time Travel retention period expires. This feature is managed exclusively by Snowflake and serves as a disaster recovery mechanism. Unlike Time Travel, Fail-safe data cannot be accessed by users through standard queries; only Snowflake support can retrieve this data in case of system failures or catastrophic events.
Storage costs are directly impacted by retention settings. Longer retention periods mean more historical data is maintained, increasing storage consumption. Organizations must balance data protection requirements against cost considerations when configuring these settings.
Transient and temporary tables have different retention characteristics. Transient tables support Time Travel retention of 0 or 1 day only and have no Fail-safe period. Temporary tables exist only for the session duration and similarly lack Fail-safe protection.
Best practices include setting appropriate retention periods based on compliance requirements, regularly reviewing retention configurations across objects, and understanding that changes to retention settings apply to new data modifications rather than retroactively affecting existing historical data. Proper configuration of data retention settings ensures optimal balance between data recoverability, compliance adherence, and cost management in your Snowflake environment.
Data Retention Settings in Snowflake
Why Data Retention Settings Are Important
Data retention settings in Snowflake are crucial for managing storage costs, ensuring regulatory compliance, and enabling data recovery capabilities. These settings determine how long Snowflake retains historical data versions, which affects your ability to use Time Travel and Fail-safe features. Understanding these settings is essential for both practical Snowflake administration and the SnowPro Core certification exam.
What Are Data Retention Settings?
Data retention settings control the DATA_RETENTION_TIME_IN_DAYS parameter, which specifies how many days historical data is preserved for Time Travel purposes. This parameter can be set at multiple levels:
• Account level - Sets the default for all objects • Database level - Overrides account settings for all schemas within • Schema level - Overrides database settings for all tables within • Table level - Most granular control for specific tables
How Data Retention Works
The retention period determines how far back you can query historical data using Time Travel. Key points include:
• Standard Edition: Maximum retention period is 1 day (0-1 days configurable) • Enterprise Edition and higher: Maximum retention period is 90 days (0-90 days configurable) • Default value: 1 day for all editions • Transient and Temporary tables: Maximum of 1 day regardless of edition
After the retention period expires, data moves to Fail-safe for an additional 7 days (except for transient and temporary objects, which have no Fail-safe).
Setting Data Retention
You can configure retention using ALTER statements:
ALTER TABLE my_table SET DATA_RETENTION_TIME_IN_DAYS = 30;
ALTER DATABASE my_db SET DATA_RETENTION_TIME_IN_DAYS = 14;
Setting the value to 0 effectively disables Time Travel for that object.
Storage Cost Implications
Longer retention periods mean: • More historical data stored • Higher storage costs • Greater flexibility for data recovery
Organizations must balance recovery needs against storage expenses.
Exam Tips: Answering Questions on Data Retention Settings
1. Remember edition limits: Standard Edition caps at 1 day; Enterprise and above allow up to 90 days. This is a frequently tested concept.
2. Know the hierarchy: Table settings override schema, schema overrides database, and database overrides account defaults.
3. Understand transient objects: Transient tables and databases are limited to 0-1 day retention maximum, regardless of your Snowflake edition.
4. Differentiate Time Travel from Fail-safe: Retention settings control Time Travel only. Fail-safe is always 7 days and cannot be modified by users.
5. Default value: The default DATA_RETENTION_TIME_IN_DAYS is 1 for all account types.
6. Cost considerations: Questions may ask about reducing storage costs - setting retention to 0 or using transient tables are valid approaches.
7. Watch for trick questions: Temporary tables have no Fail-safe period and maximum 1 day retention, making them unsuitable for critical data.
8. Parameter scope: Remember you can check current settings using SHOW PARAMETERS command at various object levels.