What you will be able to do
- Set and audit DATA_RETENTION_TIME_IN_DAYS at the account, database, schema and table levels
- Predict the effective retention period when MIN_DATA_RETENTION_TIME_IN_DAYS is set
- Explain why Fail-safe cannot satisfy a customer-controlled recovery or compliance requirement
- Compare the historical-data window of permanent, transient and temporary tables
- Write AT | BEFORE queries that read a table as of an offset or as it stood before a given statement
Key concept
Data retention period — The number of days Snowflake keeps the prior state of changed or dropped data so that you can query, clone or restore it yourself. When that period ends, the data leaves your control and moves into Fail-safe, where only Snowflake can recover it.
1.What Time Travel lets you recover
Every retention decision in Snowflake starts with one question: how far back must someone be able to recover the data without help? Time Travel answers it. It gives you access to historical data, meaning data that has been changed or deleted, at any point within a defined period. Within that period you can do three things: query past states of a table, clone tables, schemas and databases as they stood at an earlier point, and restore objects that were dropped.
Snowflake adds a few SQL extensions for this. The AT | BEFORE clause goes in SELECT statements and in CREATE … CLONE, immediately after the object name. It pinpoints the historical moment you want with TIMESTAMP, OFFSET (seconds back from now) or STATEMENT (a query ID). UNDROP restores dropped tables, schemas, databases, accounts, external volumes and tags.
The feature is on in every account. Standard Time Travel is 1 day, and extended Time Travel of up to 90 days requires Enterprise Edition. Both Time Travel and Fail-safe use extra storage, and that storage is billed. Some objects are left out of Time Travel clones: external tables and internal stages are not cloned, and user tasks are not cloned when you create a schema clone with a timestamp.
Checkpoint 1 of 5· Fill the gap
Which keyword completes this point-in-time schema clone from the Snowflake documentation?
CREATE SCHEMA S2 CLONE S1 ? (TIMESTAMP => '2025-04-01 12:00:00');Time Travel uses the AT | BEFORE clause directly after the object name in CREATE … CLONE. HISTORY is a SHOW-command clause, and SINCE and ASOF are not Time Travel keywords.
Source: docs.snowflake.comHere is how the AT | BEFORE parameters look in practice. For example, to see a table as it stood five minutes ago, give OFFSET a negative number of seconds, as in the query below. To see a table as it was before a particular statement changed it, pass that statement's query ID to BEFORE(STATEMENT => …). The result then leaves out the changes made by that statement.
The moment you ask for has to be inside the retention period. If the requested data is beyond the Time Travel retention period, the statement fails. Historical results also use the table's current definition. For example, if you add a column today and query a point before it existed, the results still include the new column.
SELECT * FROM my_table AT(OFFSET => -60*5) AS T WHERE T.flag = 'valid';2.Setting retention at account, schema and table level
The length of the retention period is controlled by the DATA_RETENTION_TIME_IN_DAYS parameter. A user with ACCOUNTADMIN sets it at the account level as the default. The same parameter can override that default when you create a database, schema or table, and you can change it on any of those objects later. Settings flow downward: the period is inherited by default for all objects created in the database or schema.
The allowed values depend on the edition and the object type. On Standard Edition you can only set the period to 0 or leave it at the default of 1 day. On Enterprise Edition and higher, permanent databases, schemas and tables accept any value from 0 to 90 days. Transient objects and temporary tables are still limited to 0 or 1. A value of 0 turns Time Travel off for that object, and once such an object is dropped it cannot be restored. Snowflake recommends keeping at least 1 day on every object.
You cannot turn Time Travel off for a whole account. Setting the account value to 0 only changes the default for new databases, and any database, schema or table can still override it. For governance, the more important control is MIN_DATA_RETENTION_TIME_IN_DAYS, which ACCOUNTADMIN sets at the account level. It does not overwrite anyone's DATA_RETENTION_TIME_IN_DAYS value, but it puts a floor under the retention that actually applies.
This makes MIN_DATA_RETENTION_TIME_IN_DAYS the enforcement tool. Teams can still tune DATA_RETENTION_TIME_IN_DAYS on their own objects, but none of them can push recoverability below the account minimum. To audit what is in effect, look at the retention_time column in the output of SHOW TABLES, SHOW SCHEMAS or SHOW DATABASES. For streams, check stale_after instead. The following query finds schemas where Time Travel is off:
SHOW SCHEMAS
->> SELECT "name", "retention_time"
FROM $1
WHERE "retention_time" = 0;Checkpoint 2 of 5· Check yourself
A security engineer wants to guarantee that no permanent table in the account can be configured with less than 7 days of Time Travel, while still letting teams set longer periods. Which control does this?
Account-level DATA_RETENTION_TIME_IN_DAYS is only a default that objects can override. MIN_DATA_RETENTION_TIME_IN_DAYS enforces a floor through the MAX rule. Time Travel cannot be deactivated for an account.
“the effective minimum data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).”Source: docs.snowflake.com
Checkpoint 3 of 5· Exam question
An Enterprise Edition account sets `MIN_DATA_RETENTION_TIME_IN_DAYS = 14` at the account level. In schema `FIN.CORE`, which has `DATA_RETENTION_TIME_IN_DAYS = 7`, the permanent table `ORDERS` has an explicit `DATA_RETENTION_TIME_IN_DAYS = 30`, and the permanent table `LEDGER` has no explicit setting. What Time Travel retention applies to each table?
Correct answer: A — `ORDERS` keeps 30 days because its explicit value is the larger of the two, while `LEDGER` keeps 14 days because the account minimum raises its inherited 7 days.
- A. The effective period is MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS). ORDERS resolves to max(30,14)=30 and LEDGER inherits 7 from the schema, which the account floor lifts to 14.
- B. The minimum is a floor, not an override. A table whose own value is higher than the floor keeps its higher value, so ORDERS stays at 30 days.
- C. The minimum applies to existing objects as well, through the MAX calculation. LEDGER is therefore raised from its inherited 7 days to 14.
- D. Snowflake never adds the two parameters. It takes the larger of the object's resolved value and the account minimum.
Sources2
3.Fail-safe: why it is not part of your retention policy
When Time Travel ends, data from permanent tables enters Fail-safe. Fail-safe is separate from Time Travel and exists to protect historical data from a system failure or another event, such as a security breach. It lasts 7 days, and you cannot configure that period on any table type.
For security and compliance work, what matters is who controls the recovery. Fail-safe is a best-effort service meant only for cases where every other recovery option has already been tried. It is not a way to reach historical data after Time Travel has ended. To use it, you open a case with Snowflake Support. Recovery can take anywhere from several hours to several days and runs on Snowflake-managed serverless compute that is billed. So if a requirement says your own team must be able to recover data within N days, only the Time Travel retention period counts toward N. Fail-safe is extra protection for disasters. It does not extend your self-service window.
Table type sets the outer limit on how long historical data exists:
| Table type | Time Travel (days) | Fail-safe (days) | Min, max historical data (days) |
|---|---|---|---|
| Permanent (Standard Edition) | 0 or 1 | 7 | 7, 8 |
| Permanent (Enterprise Edition) | 0 to 90 | 7 | 7, 97 |
| Transient | 0 or 1 | 0 | 0, 1 |
| Temporary | 0 or 1 | 0 | 0, 1 |
Storage for both periods is charged per 24-hour period starting when the data changed. For updates and deletes, Snowflake keeps only what is needed to restore the affected rows. It keeps full copies only when a table is dropped or truncated. ACCOUNTADMIN can see Fail-safe storage in Snowsight under Admin » Cost management » Consumption, with the Usage Type filter set to Storage.
Checkpoint 4 of 5· Match them up
Match each recovery feature or table type to the description that fits it
Tap a term, then the definition that fits it.
Time Travel is the period you configure and recover from yourself. Fail-safe is a fixed, best-effort period that only Snowflake can recover from. Transient tables skip Fail-safe entirely, and an Enterprise permanent table can keep up to 90 days of Time Travel plus 7 days of Fail-safe.
“Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake.”Source: docs.snowflake.com
Checkpoint 5 of 5· Exam question
A security architect proposes enabling 30-day Time Travel on a critical permanent table by running `ALTER TABLE vault.pii.customers SET DATA_RETENTION_TIME_IN_DAYS = 30`. The account runs Standard Edition. What is the outcome of this statement?
Correct answer: C — The statement is rejected because Standard Edition caps Time Travel for permanent objects at 1 day, so 30 days needs Enterprise Edition or higher.
- A. Fail-safe is fixed at 7 days and is not extended by Time Travel settings. Time Travel itself is limited on this edition, so this is not what happens.
- B. No special privilege unlocks a longer period. The ceiling is determined by the account edition, not by the role that runs the statement.
- C. Standard Edition supports a maximum of 1 day of Time Travel, and values up to 90 days require Enterprise Edition or higher. A value of 30 is therefore rejected.
- D. Snowflake does not silently cap the value. An out-of-range value for the edition raises an error instead of being accepted.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Fail-safe's 7 days can be added to Time Travel to meet a requirement that the data team can recover data themselves for a given number of days.Why is that wrong?
Only Snowflake can recover from Fail-safe, through a Support case, on a best-effort basis. A self-service recovery requirement has to be met entirely by the Time Travel retention period.
Covered in Fail-safe: why it is not part of your retention policy
2.Setting DATA_RETENTION_TIME_IN_DAYS = 0 on a table always disables its Time Travel.Why is that wrong?
If MIN_DATA_RETENTION_TIME_IN_DAYS is set at the account level and is higher, the higher value applies, so the table keeps that much Time Travel.
Covered in Setting retention at account, schema and table level
3.An AT | BEFORE query can reach any point in a table's past as long as the table still exists.Why is that wrong?
Time Travel queries only work inside the retention period. A request for data older than that period fails.
Covered in What Time Travel lets you recover
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Extended Time Travel (up to 90 days) requires Snowflake Enterprise Edition.”
↩︎ What Time Travel lets you recover - 2.
“UNDROP <object> command for tables, schemas, databases, accounts, external volumes, and tags.”
↩︎ What Time Travel lets you recover“the period is inherited by default for all objects created in the database/schema”
↩︎ Setting retention at account, schema and table level“Time Travel cannot be deactivated for an account.”
↩︎ Setting retention at account, schema and table level“When the retention period ends for an object, the historical data is moved into Snowflake Fail-safe”
↩︎ Key concept“is greater than 0, the higher value setting takes precedence.”
↩︎ Exam trap 2“is greater than 0, the higher value setting takes precedence.”
↩︎ Prediction“the effective minimum data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).”
↩︎ Checkpoint - 3.
“Select historical data from a table as of 5 minutes ago:”
↩︎ What Time Travel lets you recover“Select historical data from a table up to, but not including any changes made by the specified transaction:”
↩︎ What Time Travel lets you recover“When you access historical table data, the results include the columns, default values, etc. from the current definition of the table.”
↩︎ What Time Travel lets you recover“If requested data is beyond the Time Travel retention period (default is 1 day), the statement fails.”
↩︎ Exam trap 3 - 4.
“Fail-safe ensures historical data is protected in the event of a system failure or other event (e.g. a security breach).”
↩︎ Fail-safe: why it is not part of your retention policy“Data recovery through Fail-safe may take from several hours to several days to complete.”
↩︎ Fail-safe: why it is not part of your retention policy“Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended.”
↩︎ Exam trap 1“Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake.”
↩︎ Checkpoint - 5.
“Transient and temporary tables have no Fail-safe period.”
↩︎ Fail-safe: why it is not part of your retention policy