What you will be able to do
- Recover changed or dropped data with AT | BEFORE, CLONE and UNDROP while it is still inside its retention period
- Work out the effective Time Travel retention of a table from account, database, schema and table settings, including MIN_DATA_RETENTION_TIME_IN_DAYS
- Explain what Fail-safe is, when it starts, and why it is not part of a retention policy you control
- Choose between permanent, transient and temporary tables as a deliberate retention decision
Key concept
Data retention period — The number of days Snowflake keeps the earlier state of changed or dropped data so you can query, clone or restore it yourself. Each change starts its own countdown, and when that period ends the data leaves your reach: it moves to Fail-safe, or is gone if the table has no Fail-safe.
1.What Time Travel lets you recover
Time Travel is Snowflake's self-service recovery window. Whenever data in a table is updated, deleted, or dropped along with its object, Snowflake keeps the state from before the change. For a set number of days afterwards, anyone with the right privileges can get it back without opening a ticket. Every account has it: the standard retention period is 1 day, and Time Travel cannot be switched off for a whole account.
Within the retention period you can do three things:
- Query the past. Add an
AT | BEFOREclause to aSELECTand point it at aTIMESTAMP, anOFFSET(seconds back from now) or aSTATEMENT(a query ID).BEFORE(STATEMENT => ...)is how you read a table as it was just before a badDELETE. - Clone the past. The same clause goes immediately after the object name in
CREATE ... CLONE, so you can copy a table, schema or database as it stood at a point in time. - Restore what was dropped.
UNDROPbrings back tables, schemas, databases, accounts, external volumes and tags.
CREATE SCHEMA S2 CLONE S1 AT(TIMESTAMP => '2025-04-01 12:00:00');Historical clones leave some things out. External tables and internal stages are not cloned. Hybrid tables come along when you clone a database but not a schema. User tasks are left out when you clone a schema at a timestamp, though a plain clone with no timestamp does copy them. A historical query also uses the table's current schema, not the schema it had at that moment.
The clock belongs to each piece of history, not to the object. End of Time Travel = time of the change + data retention period, so one table can hold versions that expire at many different times. Once a version's period ends, all three actions stop working for it. It can no longer be queried, a clone at that point can no longer be made, and an object dropped at that point can no longer be restored. One timing detail: a long-running Time Travel query delays moving data anywhere in the account into Fail-safe until that query finishes.
Checkpoint 1 of 7· Fill the gap
This statement creates S2 as a copy of schema S1 as it stood at noon on 1 April 2025. Which parameter completes the AT clause?
CREATE SCHEMA S2 CLONE S1 AT( ? => '2025-04-01 12:00:00');The value is a calendar date and time, so the AT clause needs TIMESTAMP. OFFSET takes a number of seconds back from now, and STATEMENT takes a query ID. BEFORE is a separate keyword that replaces AT; it cannot go inside the parentheses as a parameter.
Source: docs.snowflake.comSources1
2.Setting and inheriting DATA_RETENTION_TIME_IN_DAYS
DATA_RETENTION_TIME_IN_DAYS sets how long Time Travel lasts, and you can set it at four levels. An ACCOUNTADMIN sets the account default. You can override it when you create a database, schema or table, and change it later at any time. A value set on a database or schema is inherited by default by every object created inside it. A table with no value of its own therefore takes its schema's value, and the schema falls back to its database and then the account.
Your edition limits which values are allowed. Standard Edition permits only 0 or 1 day anywhere. To give a permanent table, schema or database more than 1 day, up to 90, you need Enterprise Edition or higher. A request for 30 days on a Standard Edition account asks for something the edition does not offer. The fix is Enterprise Edition, not a different privilege or a Fail-safe setting. Longer retention also means more storage on your monthly bill.
| Table type | Time Travel retention (days) | Fail-safe (days) |
|---|---|---|
| Permanent, Standard Edition | 0 or 1 (default 1) | 7 |
| Permanent, Enterprise Edition and higher | 0 to 90 (default configurable) | 7 |
| Transient | 0 or 1 (default 1) | 0 |
| Temporary | 0 or 1 (default 1) | 0 |
MIN_DATA_RETENTION_TIME_IN_DAYS sets a floor, and it is how you enforce a retention policy across the whole account. Only an ACCOUNTADMIN can set it, and only at account level. It does not change or replace anyone's DATA_RETENTION_TIME_IN_DAYS value. What it changes is the effective retention, which becomes MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS) for every database, schema and table.
Here is a worked case. The account minimum is 14, schema FIN.CORE is set to 7, table ORDERS is explicitly set to 30, and table LEDGER has no value of its own. ORDERS keeps 30 days, because 30 is already above the floor. LEDGER inherits 7 from the schema, and the floor raises it to 14. The two values are never added together, and the minimum never lowers a higher explicit setting. Even an object owner who sets 0 to turn off Time Travel gets the higher account minimum.
Checkpoint 2 of 7· Check yourself
On an Enterprise Edition account, an ACCOUNTADMIN sets MIN_DATA_RETENTION_TIME_IN_DAYS = 7. Schema HR.PAY has DATA_RETENTION_TIME_IN_DAYS = 3. In that schema, table CLAIMS has no retention value of its own, and table AUDIT_LOG is explicitly set to 45. What is the effective Time Travel retention of each table?
CLAIMS inherits 3 days from its schema, and the account minimum raises it to 7. AUDIT_LOG is already above the floor, so it keeps 45 days. Effective retention is the larger of the two values. The values are never added, and the minimum never lowers a higher setting.
“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 7· 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.
Checkpoint 4 of 7· 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.
Sources1
3.Fail-safe: the 7 days only Snowflake can use
Fail-safe is not an extension of Time Travel. It is a separate disaster-recovery layer that Snowflake runs. When a version's Time Travel period ends, that data enters a 7-day Fail-safe period for permanent objects. That length cannot be configured for any table type. You cannot query Fail-safe data, clone from it or UNDROP it. Fail-safe exists so Snowflake can recover data lost or damaged in an extreme operational failure, such as a system failure or a security breach.
Recovery from Fail-safe goes through Snowflake Support. You open a case, and Snowflake makes a best-effort attempt that can take several hours to several days. It runs on Snowflake-managed serverless compute, billed under the FAILSAFE_RECOVERY service type. Treat it as the last option, after every other recovery path has failed. One exception: tables holding data ingested through the classic Snowpipe Streaming architecture cannot be recovered through Fail-safe at all.
Take a permanent table with 7-day retention on Enterprise Edition that was dropped 10 days ago. Its Time Travel ended on day 7, so UNDROP fails even for ACCOUNTADMIN. A historical clone fails too, because past objects can no longer be cloned once they leave Time Travel. On day 10 the table is three days into Fail-safe, and the only route left is a support case. On day 15 Fail-safe has also ended.
Checkpoint 5 of 7· Check yourself
A compliance rule says the data team must be able to restore any table to its state from up to 14 days ago without outside help. On an Enterprise Edition account, an engineer proposes DATA_RETENTION_TIME_IN_DAYS = 7 on the permanent tables and argues that Fail-safe covers the other 7 days. Does this design meet the rule?
The team cannot query, clone or UNDROP Fail-safe data, not even as ACCOUNTADMIN. Recovery from it is a best-effort Snowflake Support case. Self-service recovery therefore has to come entirely from Time Travel, and the 7-day Fail-safe period cannot be configured anyway.
“Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended.”Source: docs.snowflake.com
Checkpoint 6 of 7· Exam question
A table was dropped by mistake 10 days ago. It was a permanent table with DATA_RETENTION_TIME_IN_DAYS = 7, and the account is on Enterprise Edition. A data owner runs UNDROP TABLE as ACCOUNTADMIN and the statement fails. What is the only remaining path to recover the data?
Correct answer: D — Open a support case with Snowflake, because the table is now inside the 7-day Fail-safe period that only Snowflake can use to attempt a best-effort recovery.
- A. Cloning relies on Time Travel and cannot read Fail-safe storage. After the retention period ends, no user-level statement can reach the data.
- B. Increasing retention does not recover data that has already left Time Travel. It only extends the window for data still inside it, and dropped objects are unaffected by schema changes.
- C. ACCOUNT_USAGE views hold metadata about objects, not table rows. They cannot be used to read data or restore it.
- D. Fail-safe starts when Time Travel ends and lasts 7 days. Users cannot query or restore from it, so only Snowflake Support can attempt recovery, on a best-effort basis.
4.Transient and temporary tables as a retention choice
The table type decides the most history Snowflake will ever keep. Permanent is the default. Choosing TRANSIENT or TEMPORARY at creation gives up Fail-safe and limits Time Travel to 0 or 1 day. That caps extra historical storage at 1 day. A permanent table keeps between 7 and 97 days on Enterprise Edition, because its Fail-safe is always added on top.
CREATE TRANSIENT TABLE mytranstable (id NUMBER, creation_date DATE);Transient tables stay until someone drops them and are visible to anyone with privileges, but they have no Fail-safe. If a transient table is lost in a system failure, after its 1 day nobody can recover it, not you and not Snowflake. Every table in a transient schema is transient, and so is every schema in a transient database. Snowflake Backups do not support transient tables either.
Temporary tables belong to a single session. They are invisible to other sessions and purged when the session ends, after which no one can recover them. Their 1 day of Time Travel really means 24 hours or the rest of the session, whichever is shorter. A temporary table also hides a permanent table with the same name in the same schema. In that session, a DROP followed by an UNDROP can therefore act on a different table than you meant.
Neither type can be converted to another table type after creation. Moving data from permanent to transient therefore takes several steps:
- Run
CREATE TABLE ... AS SELECTto build the transient copy. - Re-apply the original grants to it.
- Drop the original.
- Optionally rename the new table to the old name.
Cloning has one cost trap. A transient clone of a permanent table shares its micro-partitions, so those shared bytes enter Fail-safe only when the transient clone is dropped.
Checkpoint 7 of 7· Match them up
Match each table choice to the retention and recovery behaviour it gives you.
Tap a term, then the definition that fits it.
Transient tables work like permanent tables but drop Fail-safe, which caps extra history at 1 day. Temporary tables are also limited to the session that created them. A permanent table on Enterprise Edition always gets 7 days of Fail-safe on top of its 0 to 90 days of Time Travel. A transient schema makes every table created in it transient.
“Transient tables are similar to permanent tables with the key difference that they do not have a Fail-safe period.”Source: docs.snowflake.com
Sources
(4)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Restore tables, schemas, databases, and some other kinds of objects that have been dropped.”
↩︎ What Time Travel lets you recover“UNDROP <object> command for tables, schemas, databases, accounts, external volumes, and tags.”
↩︎ What Time Travel lets you recover“Time Travel cannot be deactivated for an account.”
↩︎ What Time Travel lets you recover“the period is inherited by default for all objects created in the database/schema.”
↩︎ Setting and inheriting DATA_RETENTION_TIME_IN_DAYS“For Snowflake Standard Edition, the retention period can be set to 0 (or unset back to the default of 1 day)”
↩︎ Setting and inheriting DATA_RETENTION_TIME_IN_DAYS“Setting MIN_DATA_RETENTION_TIME_IN_DAYS does not alter or replace the DATA_RETENTION_TIME_IN_DAYS parameter value.”
↩︎ Setting and inheriting DATA_RETENTION_TIME_IN_DAYS“Past objects can no longer be cloned.”
↩︎ Fail-safe: the 7 days only Snowflake can use“The data retention period specifies the number of days for which this historical data is preserved”
↩︎ Key concept“the effective minimum data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).”
↩︎ Exam trap 2“When an object with no retention period is dropped, you will not be able to restore the object.”
↩︎ Exam trap 3“After Day 6 at 1:00 PM, that version of the row moves into Fail-safe for 7 days.”
↩︎ 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 - 2.
“Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake.”
↩︎ Fail-safe: the 7 days only Snowflake can use“To recover data from Fail-safe, open a case with Snowflake Support.”
↩︎ Fail-safe: the 7 days only Snowflake can use“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 is not provided as a means for accessing historical data after the Time Travel retention period has ended.”
↩︎ Checkpoint - 3.
“Transient tables are similar to permanent tables with the key difference that they do not have a Fail-safe period.”
↩︎ Transient and temporary tables as a retention choice“data in the table is purged and is not recoverable, either for the user who created the table or for Snowflake”
↩︎ Transient and temporary tables as a retention choice - 4.
“Transient and temporary tables have no Fail-safe period.”
↩︎ Transient and temporary tables as a retention choice