What you will be able to do
- Set and inspect retention with DATA_RETENTION_TIME_IN_DAYS at the account, database, schema and table level
- Predict what happens to historical data when retention goes up or down, or when a container is dropped
- Turn Time Travel off for one object, and explain why it cannot be turned off for an account or when MIN_DATA_RETENTION_TIME_IN_DAYS is set
- Work out the retention each edition and table type allows, and say when data falls through to Fail-safe
Key concept
Data retention period — The number of days Snowflake keeps the earlier state of changed or dropped data. Within that window you can query it, clone it and UNDROP it. When the window ends, the data leaves Time Travel for good.
1.Establishing data retention periods
When a table's data is modified, deleted or dropped, Snowflake keeps the state it had before the change. Every Time Travel operation (SELECT ... AT/BEFORE, CREATE ... CLONE and UNDROP) only works inside the retention period. You control that period with one parameter, DATA_RETENTION_TIME_IN_DAYS, which you can set at several levels:
- Account: a user with the ACCOUNTADMIN role sets the default for the whole account. - Database, schema, table: the same parameter overrides the account default when you create the object, and you can change it at any time with the matching ALTER command. - Inheritance: a retention period set on a database or schema becomes the default for every object created inside it.
Inheritance works in both directions. If you change the period at the account or schema level, the change reaches every lower-level object that has no explicit period of its own. The docs warn that this can have Time Travel consequences you did not intend.
To see the effective setting, read the retention_time column from SHOW TABLES, SHOW SCHEMAS or SHOW DATABASES. Add the HISTORY clause to include objects that have already been dropped. For streams, read stale_after from SHOW STREAMS instead.
SHOW DATABASES HISTORY
->> SELECT "name", "retention_time", "dropped_on"
FROM $1
WHERE "retention_time" > 1;Checkpoint 1 of 5· Fill the gap
Which parameter shortens this table's Time Travel window to 30 days?
ALTER TABLE mytable SET ? =30;DATA_RETENTION_TIME_IN_DAYS is the object parameter for retention. MIN_DATA_RETENTION_TIME_IN_DAYS can only be set on the account, and retention_time is a column in SHOW output, not a parameter.
Source: docs.snowflake.comA change to a table's retention period also affects the data already in Time Travel. Increasing the period keeps that data for longer. Data that had already moved to Fail-safe before the change does not come back. Decreasing the period applies the shorter window to new changes. Data already in Time Travel stays if it falls inside the new window and moves to Fail-safe if it does not. Snowflake guarantees the move but does not say when it will finish.
Two rules about containers come up often. First, if you change a database's or schema's period, only its active objects are affected. Tables already dropped keep the period they had when they were dropped. To change a dropped object's period, you have to UNDROP it first. Second, when you drop a database or schema, any child objects with their own explicit periods are kept only as long as the parent. To keep a child's own period, drop the child explicitly before dropping its parent.
Checkpoint 2 of 5· Check yourself
Schema s1 has a 5-day retention period. Table s1.orders has DATA_RETENTION_TIME_IN_DAYS = 30 set explicitly. Someone drops the whole schema s1. How long can orders be recovered?
When a schema is dropped, its child tables are kept for the schema's period, and any longer explicit period on a child is ignored. Dropping orders on its own first would have kept its 30 days.
“The child tables are retained for the same period of time as the schema.”Source: docs.snowflake.com
Sources1
2.Enabling and disabling Time Travel
You do not have to enable Time Travel. Every account gets it automatically with the standard 1-day retention period. Turning it off is more limited than it sounds:
- It cannot be deactivated for an account. An ACCOUNTADMIN can set the account-level DATA_RETENTION_TIME_IN_DAYS to 0. New databases, and the schemas and tables inside them, then get no retention by default. Any database, schema or table can still override that default. The docs specifically advise against setting 0 at the account level.
- It can be deactivated for one object. Setting DATA_RETENTION_TIME_IN_DAYS = 0 on a database, schema or table turns off Time Travel for that object.
An ACCOUNTADMIN can also set a floor on retention with the account parameter MIN_DATA_RETENTION_TIME_IN_DAYS. This parameter does not change the DATA_RETENTION_TIME_IN_DAYS values stored on objects. It changes the *effective* period, which becomes MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS). So setting 0 on a table only turns Time Travel off when no higher account minimum applies.
| Object DATA_RETENTION_TIME_IN_DAYS | Account MIN_DATA_RETENTION_TIME_IN_DAYS | Effective retention (days) | Time Travel for the object |
|---|---|---|---|
| 0 | not set | 0 | Deactivated |
| 0 | 3 | 3 | Active: the minimum takes precedence |
| 30 | 3 | 30 | Active: the object's own value is higher |
Think before choosing 0. An object dropped with no retention period cannot be restored, so Snowflake recommends keeping at least 1 day on every object. Also, after you set 0, changed data is moved by a background process: to Fail-safe for permanent tables, or deleted for transient tables. Until that finishes, TIME_TRAVEL_BYTES can still show a non-zero value.
Checkpoint 3 of 5· Check yourself
A cost-conscious administrator wants no Time Travel anywhere in the account. Which statement is accurate?
An account-level 0 is only a default for new objects, and any database, schema or table can override it. Standard Edition still includes 1-day Time Travel.
“Time Travel cannot be deactivated for an account.”Source: docs.snowflake.com
Sources1
3.Snowflake edition implications for Time Travel
Edition sets the upper limit on retention, and table type sets a second one. In Standard Edition, retention can be set to 0 or left at the default of 1 day, at the account and object level. Enterprise Edition and higher allow permanent databases, schemas and tables any value from 0 to 90 days, and let you raise the account default as high as 90. The edition matrix lists standard Time Travel (up to 1 day) for every edition. Extended Time Travel (up to 90 days) is listed for Enterprise, Business Critical and VPS.
Upgrading does not affect transient and temporary objects. Even on Enterprise, their retention can only be 0 or 1 day. A temporary table's retention also ends as soon as the table is dropped or its session ends. Longer retention also costs more, because extended retention needs more storage, and that storage appears in your monthly charges.
| Table type | Time Travel: Standard Edition | Time Travel: Enterprise Edition | Fail-safe days | Min, max historical days kept |
|---|---|---|---|---|
| Permanent | 0 or 1 | 0 to 90 | 7 | 7, 8 (Standard); 7, 97 (Enterprise) |
| Transient | 0 or 1 | 0 or 1 | 0 | 0, 1 |
| Temporary | 0 or 1 | 0 or 1 | 0 | 0, 1 |
Checkpoint 4 of 5· Check yourself
An Enterprise Edition account runs ALTER TABLE stage_tmp SET DATA_RETENTION_TIME_IN_DAYS = 30 on a transient table. What is the right expectation?
Retention above 1 day is only for permanent objects on Enterprise or higher. Transient and temporary tables are limited to 0 or 1 day whatever the edition.
“Transient tables can have a Time Travel retention period of either 0 or 1 day.”Source: docs.snowflake.com
4.Fail-safe: what happens after retention ends
When an object's retention period ends, its historical data moves into Fail-safe. From then on, you can no longer query it, clone past versions of the object, or restore it if it was dropped. Fail-safe is a separate, non-configurable 7-day period that begins as soon as Time Travel ends. Only Snowflake can use it, to recover data lost in extreme operational failures. Recovery is best effort and should be tried only after every other option. It can take from several hours to several days, and you request it by opening a case with Snowflake Support.
Two more details matter. A long-running Time Travel query delays the move of data and objects into Fail-safe until the query finishes. And transient and temporary tables have no Fail-safe period at all, so once their Time Travel window has passed, Snowflake cannot recover their data. That is why the docs say long-lived tables such as fact tables should be permanent. Short-lived ETL work tables can be transient to avoid Fail-safe storage costs, since both Time Travel and Fail-safe add storage charges.
Checkpoint 5 of 5· Check yourself
Data was changed 3 days ago in a permanent table with 1-day retention. An analyst needs to SELECT the old values. What can they do?
Once retention ends, the data is in Fail-safe, which users cannot query. Raising retention does not recover data that has already moved to Fail-safe.
“Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Setting DATA_RETENTION_TIME_IN_DAYS = 0 on a table always turns off its Time Travel.Why is that wrong?
If the account sets a higher MIN_DATA_RETENTION_TIME_IN_DAYS, effective retention is the larger of the two values, so Time Travel stays on.
Covered in Enabling and disabling Time Travel
2.When a database is dropped, each child table keeps its own explicitly set retention period.Why is that wrong?
Children of a dropped database are kept only for the database's period. To keep a child's own period, drop the child explicitly first.
Covered in Establishing data retention periods
3.Enterprise Edition allows 90 days of Time Travel on every table.Why is that wrong?
The 0–90 day range only covers permanent databases, schemas and tables. Transient and temporary tables are limited to 0 or 1 day.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“the period is inherited by default for all objects created in the database/schema.”
↩︎ Establishing data retention periods“Causes the data currently in Time Travel to be retained for the longer time period.”
↩︎ Establishing data retention periods“To alter the retention period of a dropped object, you must undrop the object, then alter its retention period.”
↩︎ Establishing data retention periods“A retention period of 0 days for an object effectively deactivates Time Travel for the object.”
↩︎ Enabling and disabling Time Travel“When an object with no retention period is dropped, you will not be able to restore the object.”
↩︎ Enabling and disabling Time Travel“The data retention period specifies the number of days for which this historical data is preserved”
↩︎ Key concept“the higher value setting takes precedence.”
↩︎ Exam trap 1“The child schemas or tables are retained for the same period of time as the database.”
↩︎ Exam trap 2“For permanent databases, schemas, and tables, the retention period can be set to any value from 0 up to 90 days.”
↩︎ Exam trap 3“If the data is outside the new period, it moves into Fail-safe.”
↩︎ Prediction“The child tables are retained for the same period of time as the schema.”
↩︎ Checkpoint“the higher value setting takes precedence.”
↩︎ Prediction“Time Travel cannot be deactivated for an account.”
↩︎ Checkpoint - 2.
“Extended Time Travel (up to 90 days) requires Snowflake Enterprise Edition.”
↩︎ Snowflake edition implications for Time Travel - 3.
“Extended Time Travel (up to 90 days).”
↩︎ Snowflake edition implications for Time Travel - 4.
“Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake.”
↩︎ Fail-safe: what happens after retention ends“To recover data from Fail-safe, open a case with Snowflake Support.”
↩︎ Fail-safe: what happens after retention ends“Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended.”
↩︎ Checkpoint - 5.
“Transient and temporary tables have no Fail-safe period.”
↩︎ Fail-safe: what happens after retention ends“Transient tables can have a Time Travel retention period of either 0 or 1 day.”
↩︎ Checkpoint