Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Set Data Retention Period in Snowflake: Step-by-Step Guide

In Snowflake, you set the data retention period by configuring the DATA_RETENTION_TIME_IN_DAYS parameter on a database or schema. This controls how long historical data is kept for time travel and fail-safe recovery.
📐

Syntax

The DATA_RETENTION_TIME_IN_DAYS parameter defines the number of days Snowflake retains historical data for time travel. It can be set at the database or schema level.

  • DATABASE: Use ALTER DATABASE <name> SET DATA_RETENTION_TIME_IN_DAYS = <days>
  • SCHEMA: Use ALTER SCHEMA <name> SET DATA_RETENTION_TIME_IN_DAYS = <days>

The value must be between 0 and 90 days, depending on your Snowflake edition.

sql
ALTER DATABASE my_database SET DATA_RETENTION_TIME_IN_DAYS = 7;
💻

Example

This example sets the data retention period to 5 days on a database named sales_db. This means you can query historical data up to 5 days in the past using time travel.

sql
ALTER DATABASE sales_db SET DATA_RETENTION_TIME_IN_DAYS = 5;
Output
Database SALES_DB altered.
⚠️

Common Pitfalls

  • Setting DATA_RETENTION_TIME_IN_DAYS higher than your Snowflake edition allows will cause an error.
  • Failing to set this parameter means the default retention period (usually 1 day) applies.
  • Remember that longer retention increases storage costs.
sql
/* Wrong: Setting retention beyond allowed limit */
ALTER DATABASE sales_db SET DATA_RETENTION_TIME_IN_DAYS = 100;

/* Right: Set within allowed range */
ALTER DATABASE sales_db SET DATA_RETENTION_TIME_IN_DAYS = 7;
Output
SQL execution error: Retention period exceeds maximum allowed for your edition. Database SALES_DB altered.
📊

Quick Reference

ParameterDescriptionAllowed ValuesDefault
DATA_RETENTION_TIME_IN_DAYSDays to keep historical data for time travel0 to 90 (depends on edition)1 day
ScopeLevel where retention is setDATABASE or SCHEMADATABASE default
✅

Key Takeaways

Set DATA_RETENTION_TIME_IN_DAYS on database or schema to control data retention.
Retention period controls how long you can access historical data via time travel.
Maximum retention depends on your Snowflake edition; exceeding it causes errors.
Longer retention increases storage costs, so balance needs accordingly.
Default retention is usually 1 day if not explicitly set.