CertSafari
    Snowflake SnowPro Advanced: Security Engineer (SEA-C01)· Lessons

    Domain 2 · Lesson 8/21

    Time Travel, Fail-safe and data retention settings in Snowflake

    Establish and manage data retention and data lifecycle management.

    14 min read
    4.29% of exam
    4 sources
    Published 20 Oct 2026
    Docs as of 18 Oct 2026

    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 | BEFORE clause to a SELECT and point it at a TIMESTAMP, an OFFSET (seconds back from now) or a STATEMENT (a query ID). BEFORE(STATEMENT => ...) is how you read a table as it was just before a bad DELETE.
    • 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. UNDROP brings back tables, schemas, databases, accounts, external volumes and tags.
    Cloning a schema as it stood at a past timestampsql
    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');

    Sources1

    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.

    Allowed Time Travel retention by table type and edition
    Table typeTime Travel retention (days)Fail-safe (days)
    Permanent, Standard Edition0 or 1 (default 1)7
    Permanent, Enterprise Edition and higher0 to 90 (default configurable)7
    Transient0 or 1 (default 1)0
    Temporary0 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?

    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?

    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?

    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?

    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?

    Sources21

    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.

    Creating a transient tablesql
    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:

    1. Run CREATE TABLE ... AS SELECT to build the transient copy.
    2. Re-apply the original grants to it.
    3. Drop the original.
    4. 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.

    Sources34

    Sources

    (4)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 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. 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. 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

    Continue to part 2 of 2

    Data retention strategy and storage lifecycle policies

    Spotted a mistake, or was something unclear? Tell us.