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_DAYShigher 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
| Parameter | Description | Allowed Values | Default |
|---|---|---|---|
| DATA_RETENTION_TIME_IN_DAYS | Days to keep historical data for time travel | 0 to 90 (depends on edition) | 1 day |
| Scope | Level where retention is set | DATABASE or SCHEMA | DATABASE 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.
