What you will be able to do
- Work out the effective Time Travel retention period for an object from its edition, table type, DATA_RETENTION_TIME_IN_DAYS and MIN_DATA_RETENTION_TIME_IN_DAYS
- Query and clone historical data with AT and BEFORE using TIMESTAMP, OFFSET or STATEMENT
- Restore dropped tables, schemas and databases with UNDROP, including when the name has already been reused
- Explain what Fail-safe is for, who can use it, and which table types have none
Key concept
Data retention period — The number of days Snowflake keeps the earlier state of changed or dropped data so you can query it, clone it or restore it yourself. When the period ends, that data moves into Fail-safe, and only Snowflake can recover it from there.
1.How long history is kept: the retention period
Whenever data in a table changes, Snowflake keeps the state it had before the change. This includes deleting rows and dropping the object. The data retention period controls how long that earlier state stays available for the three Time Travel operations: SELECT, CREATE … CLONE and UNDROP. Every account gets the standard 1-day period automatically, and you don't need to do anything to turn it on.
Two things set the limits: the edition and the table type. On Standard Edition, the retention period is not fixed at one day: it can be set to 0, or unset back to the default of 1 day, at the account level and on databases, schemas and tables. On Enterprise Edition and higher, permanent databases, schemas and tables can be set anywhere from 0 to 90 days. Transient objects and temporary tables stay at 0 or 1 day. Longer retention uses more storage, and your monthly storage charges reflect it.
The setting is the DATA_RETENTION_TIME_IN_DAYS parameter. A user with the ACCOUNTADMIN role sets the account default, and you can override it when you create a database, schema or table, or change it later. If you set a period on a database or schema, the objects created inside it inherit that period by default. This inheritance can catch you out: if you change the period at account or schema level, every lower-level object that has no explicit value of its own picks up the new value.
A value of 0 turns Time Travel off for that object, and if the object is then dropped you can't restore it. You can't turn Time Travel off for a whole account, though. Setting 0 at account level only changes the default for new objects. ACCOUNTADMIN can also set a minimum with MIN_DATA_RETENTION_TIME_IN_DAYS. This doesn't replace the object's own value. Instead, the effective retention period becomes the larger of the two.
Checkpoint 1 of 7· Fill the gap
Which parameter completes this statement, which shortens a table's Time Travel window to 30 days?
ALTER TABLE mytable SET ? =30;DATA_RETENTION_TIME_IN_DAYS is the object parameter for Time Travel retention. MIN_DATA_RETENTION_TIME_IN_DAYS is an account-level minimum, and MAX_DATA_EXTENSION_TIME_IN_DAYS limits how far retention is extended for streams.
Source: docs.snowflake.comTo see the current retention period, look at the retention_time column in the output of SHOW TABLES, SHOW SCHEMAS or SHOW DATABASES. Adding the HISTORY clause also includes objects that have already been dropped. The query below finds schemas where Time Travel is turned off.
SHOW SCHEMAS
->> SELECT "name", "retention_time"
FROM $1
WHERE "retention_time" = 0;Checkpoint 2 of 7· Check yourself
An ACCOUNTADMIN sets MIN_DATA_RETENTION_TIME_IN_DAYS = 30 on the account. A developer then creates a permanent table with DATA_RETENTION_TIME_IN_DAYS = 0. What is the table's effective retention period?
MIN_DATA_RETENTION_TIME_IN_DAYS doesn't change the object's own parameter, but the effective retention period is the larger of the two values, so it is 30 days.
“the 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
Sources1
2.Querying and cloning the past with AT and BEFORE
Within the retention period, you read historical data by putting an AT or BEFORE clause straight after the object name in a SELECT or CREATE … CLONE. The clause takes one of three parameters: TIMESTAMP for a point in time, OFFSET for a number of seconds before now, or STATEMENT for a query ID. AT means exactly at that point. BEFORE means immediately before it, so with STATEMENT, BEFORE gives you the table as it was just before that statement ran.
SELECT * FROM my_table AT(TIMESTAMP => 'Wed, 26 Jun 2024 09:20:00 -0700'::timestamp_tz);SELECT * FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');There are three things to watch for. First, if the point you ask for is outside the table's retention period, the query fails with an error. Second, cast timestamp literals explicitly. The AT | BEFORE reference shows AT(TIMESTAMP => '2018-07-27 12:00:00') failing and the same value with ::TIMESTAMP succeeding. Third, historical queries use the table's current schema, not the schema it had at that point.
You can use the same clause with CLONE to rebuild a table, schema or database as it stood at an earlier point. This is often the quickest way to recover from a bad update without changing the live object. A database or schema clone fails if the time you pick is beyond the retention period of any current child table. The IGNORE TABLES WITH INSUFFICIENT DATA RETENTION parameter skips those tables. Some objects are not cloned this way: external tables, internal stages and, for schemas, hybrid tables. Also, user tasks are not cloned when you clone a schema with a timestamp.
CREATE TABLE restored_table CLONE my_table AT(TIMESTAMP => 'Wed, 26 Jun 2024 01:01:00 +0300'::timestamp_tz);Checkpoint 3 of 7· Check yourself
A faulty UPDATE with a known query ID corrupted a table an hour ago. You want the table exactly as it was before that UPDATE, without any of its changes. Which clause do you use?
BEFORE with STATEMENT returns the data up to, but not including, that statement's changes. An offset of -3600 only approximates the moment and could include other changes made around the same time.
“selects historical data from a table up to, but not including any changes made by the specified statement”Source: docs.snowflake.com
Checkpoint 4 of 7· Exam question
Which statement correctly describes how Time Travel retention periods differ across Snowflake editions for permanent tables?
Correct answer: A — Standard Edition provides a fixed one-day retention period, while Enterprise Edition and higher allow retention to be extended up to ninety days for permanent objects.
- A. This matches Snowflake's documented behavior: the standard retention period is one day (24 hours) and is automatically enabled for every account, and Enterprise Edition and higher unlock retention values up to ninety days for permanent databases, schemas, and tables.
- B. This confuses Time Travel with Fail-safe; the seven-day figure belongs to the non-configurable Fail-safe period, not Time Travel, and Enterprise Edition's maximum is ninety days, not thirty.
- C. A ninety-day default for every edition is incorrect since Standard Edition is fixed at one day, and transient/temporary tables can have retention set anywhere from zero to ninety days on Enterprise Edition and higher, not a flat one-day cap.
- D. This reverses the actual relationship; Standard Edition cannot be extended beyond one day through any session parameter, and Enterprise Edition and higher is the tier that supports the longer, configurable retention window.
3.Restoring dropped objects with UNDROP
Dropping a table, schema or database doesn't remove it straight away. Snowflake keeps it for the object's retention period, and during that time you can restore it. To list dropped objects, add the HISTORY keyword to SHOW TABLES, SHOW SCHEMAS or SHOW DATABASES. The output gets a DROPPED_ON column, and an object dropped several times appears once per version. When the retention period has passed and the object has been purged, it no longer appears in this output.
SHOW TABLES HISTORY LIKE 'load%' IN mytestdb.myschema; SHOW SCHEMAS HISTORY IN mytestdb; SHOW DATABASES HISTORY;UNDROP restores the object in place, in its most recent state before the DROP. It works for tables, schemas, databases, dynamic and Iceberg tables, notebooks, external volumes, tags and accounts. Creating a new object with the old name does not restore anything. It creates a new version, and the dropped one can still be restored. Because UNDROP fails if an object with that name already exists, you first rename the new object out of the way.
Dropping a parent object has one more effect. When you drop a database or schema, any different retention period set explicitly on its child schemas or tables is ignored, and the children are kept only as long as the parent. To keep their own periods, drop the children explicitly first.
UNDROP TABLE mytable; UNDROP SCHEMA myschema; UNDROP DATABASE mydatabase; UNDROP NOTEBOOK mynotebook;Checkpoint 5 of 7· Put it in order
Someone dropped table ORDERS, and a colleague has since created a new, empty ORDERS table. Put the recovery steps in order.
- 1.Run UNDROP TABLE ORDERS to restore the dropped version in place
- 2.Run SHOW TABLES HISTORY to confirm the dropped ORDERS version is still listed
- 3.Rename the new, empty ORDERS table to free up the name
A dropped object can only be restored while it still appears in the HISTORY output, and UNDROP fails while another object holds the name. So you confirm it is there, free the name, then restore.
“If an object with the same name already exists, UNDROP fails.”Source: docs.snowflake.com
Sources1
4.Fail-safe: Snowflake's last-resort recovery
When the retention period ends, the historical data moves into Fail-safe. From that point you can't query it, clone it or undrop it. Fail-safe is separate from Time Travel. It is a fixed 7-day period that you can't configure, and it starts as soon as Time Travel ends. One exception: a long-running Time Travel query delays the move into Fail-safe until the query finishes. If the retention period is set to 0, a background process moves modified or deleted data into Fail-safe (for permanent tables) or deletes it (for transient tables). This may take a short time, so TIME_TRAVEL_BYTES in table storage metrics can briefly show a non-zero value even with 0 days of retention.
Fail-safe is not a way for you to reach old data. Only Snowflake uses it, to recover data lost or damaged in extreme operational failures, and it is best effort. To use it, you open a case with Snowflake Support, and recovery can take from several hours to several days. The recovery runs on Snowflake-managed serverless compute and is billed as standard serverless compute. You can see those credits in METERING_HISTORY or METERING_DAILY_HISTORY under the FAILSAFE_RECOVERY service type. Fail-safe also has one limitation: it can't recover tables containing data ingested by the classic Snowpipe Streaming architecture.
To see how much data sits in Fail-safe, use the ACCOUNTADMIN role in Snowsight: go to Admin » Cost management » Consumption, filter Usage Type to Storage, and review the Fail-safe storage in the graph and table.
Streams are affected by history too. When a database or schema containing a stream and its source table is cloned, any unconsumed records in the stream clone are inaccessible. This matches Time Travel for tables, where a clone's history begins when the clone is created.
Both Time Travel and Fail-safe add storage charges, but Snowflake keeps only what it needs to restore the rows that were updated or deleted. It keeps full copies only when a table is dropped or truncated. The table type decides how many days of history you pay for, because transient and temporary tables have no Fail-safe at all.
| Table type | Time Travel retention (days) | Fail-safe (days) | Min, max history kept (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 |
This leads to a design rule. Define long-lived tables such as fact tables as permanent, so Fail-safe protects them fully. Use transient tables for short-lived data you can reproduce, such as ETL work tables, to avoid Fail-safe costs. Once a transient table's retention period ends, Snowflake can't recover its history. A temporary table is dropped when its session ends and can't be recovered after that.
Checkpoint 6 of 7· Match them up
Match each table type or service to its data-protection behaviour
Tap a term, then the definition that fits it.
Only permanent tables get the 7-day Fail-safe period. Transient and temporary tables top out at 1 day of history, and Fail-safe recovery is performed by Snowflake through Support, not by users.
“Transient and temporary tables have no Fail-safe period.”Source: docs.snowflake.com
Checkpoint 7 of 7· Exam question
An analyst runs `DROP SCHEMA reporting` by mistake, and five minutes later a second analyst creates a brand-new schema also named `reporting` in the same database. The retention period on the original schema was 3 days. What happens when someone now runs `UNDROP SCHEMA reporting`?
Correct answer: A — The command fails because an active object with the same name already exists in the database, so the new schema must be renamed or dropped before the original schema can be restored.
- A. UNDROP restores an object under its original name, and Snowflake blocks the restore when another object of the same type and name is already active, so the conflicting current schema must be renamed or removed first.
- B. UNDROP does not merge object contents from two different schema instances; it simply reinstates the dropped object's prior state, so no automatic table-level merging occurs.
- C. UNDROP does not auto-rename the restored object to avoid a collision; the command errors out instead of silently picking a new name for you.
- D. UNDROP never silently deletes an existing active object to make room for a restored one; doing so would destroy the second analyst's new work without any confirmation, which is not how the command behaves.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Fail-safe gives you 7 more days to query or clone data after Time Travel ends.Why is that wrong?
Fail-safe is a best-effort service used only by Snowflake for extreme operational failures. You request it through Support, and it can take hours to days.
Covered in Fail-safe: Snowflake's last-resort recovery
2.Setting DATA_RETENTION_TIME_IN_DAYS = 0 on a table always turns its Time Travel off.Why is that wrong?
If MIN_DATA_RETENTION_TIME_IN_DAYS is set above 0 at account level, the higher value wins and the table keeps that much history.
3.Recreating a dropped table with the same name brings its data back.Why is that wrong?
CREATE makes a new version of the object. The dropped version stays restorable with UNDROP once the name is free again.
Covered in Restoring dropped objects with UNDROP
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“For permanent databases, schemas, and tables, the retention period can be set to any value from 0 up to 90 days.”
↩︎ How long history is kept: the retention period“For Snowflake Standard Edition, the retention period can be set to 0 (or unset back to the default of 1 day)”
↩︎ How long history is kept: the retention period“Time Travel cannot be deactivated for an account.”
↩︎ How long history is kept: the retention period“falls outside the data retention period for the table, the query fails and returns an error”
↩︎ Querying and cloning the past with AT and BEFORE“When querying historical data in a table or non-materialized view, the current table or view schema is used.”
↩︎ Querying and cloning the past with AT and BEFORE“Calling UNDROP restores the object to its most recent state before the DROP command was issued.”
↩︎ Restoring dropped objects with UNDROP“The child schemas or tables are retained for the same period of time as the database.”
↩︎ Restoring dropped objects with UNDROP“any modified or deleted data is moved into Fail-safe (for permanent tables) or deleted (for transient tables) by a background process”
↩︎ Fail-safe: Snowflake's last-resort recovery“The data retention period specifies the number of days for which this historical data is preserved”
↩︎ Key concept“if DATA_RETENTION_TIME_IN_DAYS is set to a value of 0, and MIN_DATA_RETENTION_TIME_IN_DAYS is set at the account level and is greater than 0”
↩︎ Exam trap 2“After dropping an object, creating an object with the same name does not restore the object.”
↩︎ Exam trap 3“For transient databases, schemas, and tables, the retention period can be set to 0 (or unset back to the default of 1 day)”
↩︎ Prediction“the data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).”
↩︎ Checkpoint“selects historical data from a table up to, but not including any changes made by the specified statement”
↩︎ Checkpoint“If an object with the same name already exists, UNDROP fails.”
↩︎ Checkpoint - 2.
“AT(TIMESTAMP => '2018-07-27 12:00:00') -- fails ... AT(TIMESTAMP => '2018-07-27 12:00:00'::TIMESTAMP) -- succeeds”
↩︎ Querying and cloning the past with AT and BEFORE - 3.
“Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake.”
↩︎ Fail-safe: Snowflake's last-resort recovery“You must use the ACCOUNTADMIN role to view the amount of data that is stored in Snowflake.”
↩︎ Fail-safe: Snowflake's last-resort recovery“Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended.”
↩︎ Exam trap 1 - 4.
“any unconsumed records in the stream clone are inaccessible”
↩︎ Fail-safe: Snowflake's last-resort recovery - 5.
“Snowflake only maintains full copies of tables when tables are dropped or truncated.”
↩︎ Fail-safe: Snowflake's last-resort recovery“Transient and temporary tables have no Fail-safe period.”
↩︎ Checkpoint