What you will be able to do
- Choose between self-service Time Travel recovery and support-led Fail-safe recovery, given how long ago the data changed
- Set and read the retention period with DATA_RETENTION_TIME_IN_DAYS and MIN_DATA_RETENTION_TIME_IN_DAYS
- Pick permanent, transient or temporary tables by weighing recovery needs against storage cost
- Use zero-copy cloning to take a point-in-time backup, and know how query behaviour affects recovery
Key concept
Data retention period — The number of days that Snowflake keeps the earlier state of changed or dropped data. While that window is open you can recover data yourself with SELECT, CLONE and UNDROP. Once it closes, only Snowflake can attempt recovery, and only for permanent objects.
1.Backup/recovery with Time Travel: restoring after deletes and data corruption
Snowflake has two built-in layers that protect historical data. The first, Time Travel, is the one you use yourself. When someone deletes rows, runs a bad UPDATE that corrupts data, or drops an object, Snowflake keeps the earlier state. Within the retention period you can do three things with it. You can query the past state with the AT | BEFORE clause, using a TIMESTAMP, an OFFSET in seconds, or a STATEMENT query ID. You can clone a table, schema or database as it was at that point. And you can bring back a dropped object with UNDROP, which works for tables, schemas, databases, accounts, external volumes and tags.
Every account has Time Travel with a default retention of 1 day. Standard Edition can set retention to 0 or 1 day. On Enterprise Edition and higher, permanent databases, schemas and tables can keep data for any period from 0 to 90 days. Transient and temporary objects are still limited to 0 or 1 day.
Retention is set with DATA_RETENTION_TIME_IN_DAYS. A user with the ACCOUNTADMIN role sets the account default, and you can override it on any database, schema or table. Objects inherit the retention period of the database or schema that contains them. Setting the value to 0 turns Time Travel off for that object, and the cost is serious: a dropped object with no retention period cannot be restored. An ACCOUNTADMIN can guard against this by setting the account parameter MIN_DATA_RETENTION_TIME_IN_DAYS. It does not change any object's own setting. Instead, the effective retention becomes the larger of the two values. You can audit retention through the retention_time column of SHOW commands. Add HISTORY to include objects that have already been dropped.
SHOW SCHEMAS
->> SELECT "name", "retention_time"
FROM $1
WHERE "retention_time" = 0;Checkpoint 1 of 8· Check yourself
A table has DATA_RETENTION_TIME_IN_DAYS = 0. An ACCOUNTADMIN has set MIN_DATA_RETENTION_TIME_IN_DAYS = 7 at the account level. What is the table's effective Time Travel retention?
MIN_DATA_RETENTION_TIME_IN_DAYS leaves the object's own value unchanged, but the effective period is MAX of the two, so 7 days applies.
“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
Sources1
2.Fail-safe: Snowflake's disaster recovery for historical data
The second layer is Fail-safe, and it is kept separate from Time Travel. It protects historical data against system failures and other events, such as a security breach. Fail-safe starts as soon as the Time Travel retention period ends. It lasts 7 days, and you cannot configure that period. During those 7 days, nothing you run can reach the data: you cannot query it, clone it or undrop it. Snowflake states plainly that Fail-safe is not a way to access historical data after Time Travel ends. It is a best-effort service that Snowflake itself uses to recover data lost or damaged in extreme operational failures, and only after you have tried every other option.
How this affects availability: to start a recovery you open a case with Snowflake Support, and the recovery can take several hours to several days. That makes Fail-safe a last resort, not something to build a recovery-time target on. The recovery runs on Snowflake-managed serverless compute, which is billed. You can track those credits in METERING_HISTORY or METERING_DAILY_HISTORY under the FAILSAFE_RECOVERY service type. There is also one gap: Fail-safe cannot recover tables containing data ingested through the Snowpipe Streaming classic architecture. Recovery operations on those tables fail completely. To see how much storage Fail-safe uses, use the ACCOUNTADMIN role in Snowsight under Admin » Cost management » Consumption.
Checkpoint 2 of 8· Check yourself
A director asks how to get data back from a permanent table that was overwritten after its Time Travel window closed. Which answer is accurate?
After Time Travel ends, only Snowflake can recover the data, through a support case, as a best-effort operation.
“To recover data from Fail-safe, open a case with Snowflake Support.”Source: docs.snowflake.com
Checkpoint 3 of 8· Exam question
A team creates a TRANSIENT table to stage intermediate ELT results and wants the longest possible Time Travel window to recover from a bad transformation before the data lands in a permanent table. On this Enterprise Edition account, which retention period should they plan around for this transient table, regardless of the account's DATA_RETENTION_TIME_IN_DAYS setting?
Correct answer: B — A maximum of 1 day, because transient tables cap Time Travel retention regardless of edition or account-level settings.
- A. Incorrect - the 90-day maximum only applies to permanent tables; transient tables never reach that ceiling no matter how DATA_RETENTION_TIME_IN_DAYS is set.
- B. Correct - transient tables are capped at 1 day of Time Travel retention regardless of edition, so that is the realistic recovery window to plan around.
- C. Incorrect - transient tables carry no Fail-safe period at all, so there is no 7-day extension to rely on once Time Travel expires.
- D. Incorrect - a transient table does get a default Time Travel window without any type change; the type itself just caps that window at 1 day.
3.Table types and costs: choosing how much protection to pay for
Storage fees apply to historical data for every day it sits in Time Travel and Fail-safe. Snowflake keeps these fees down by storing only what it needs to restore the rows that changed, so the cost grows with the share of the table that changed. The exception is a dropped or truncated table: for those, Snowflake keeps a full copy. The table type decides how long historical data can last. A permanent table on Enterprise Edition can hold history for up to 97 days: 90 days of Time Travel plus 7 days of Fail-safe. Transient and temporary tables have no Fail-safe at all.
| Type | Persistence | Time Travel retention (days) | Fail-safe (days) |
|---|---|---|---|
| Temporary | Remainder of session | 0 or 1 (default is 1) | 0 |
| Transient | Until explicitly dropped | 0 or 1 (default is 1) | 0 |
| Permanent (Standard Edition) | Until explicitly dropped | 0 or 1 (default is 1) | 7 |
| Permanent (Enterprise Edition and higher) | Until explicitly dropped | 0 to 90 (default is configurable) | 7 |
How this affects recovery design: once a transient table's 1-day window has passed, its data is gone for good, and neither you nor Snowflake can get it back. Use transient tables only for data you can rebuild outside Snowflake, such as ETL work tables. Long-lived tables such as fact tables should stay permanent. If downtime and the effort of reloading data matter, permanent tables may be the better choice overall, even with the extra Fail-safe cost. A temporary table is purged when its session ends, and nobody can recover it after that. The type is fixed when the table is created. Moving data from permanent to transient means creating a new table with CREATE TABLE … AS SELECT, re-granting privileges on it, and dropping the original.
One restore pitfall: inside a session, a temporary table hides any table of the same name in the same schema. If you drop a table and then use Time Travel to restore it in that session, check that the operations hit the table you meant, and not a temporary table with the same name.
Checkpoint 4 of 8· Match them up
Match each table type to its recovery behaviour
Tap a term, then the definition that fits it.
Temporary tables last only for their session. Transient tables persist, but have no Fail-safe. Only permanent tables get Fail-safe, and on Enterprise Edition they can keep up to 90 days of Time Travel.
“Transient and temporary tables have no Fail-safe period.”Source: docs.snowflake.com
Checkpoint 5 of 8· Fill the gap
Which keyword creates a table that persists after the session ends but avoids Fail-safe costs?
CREATE ? TABLE mytranstable (id NUMBER, creation_date DATE);Transient tables last until they are explicitly dropped and have no Fail-safe. Temporary tables also have no Fail-safe, but they disappear when the session ends.
Source: docs.snowflake.comCheckpoint 6 of 8· Exam question
A finance team raises DATA_RETENTION_TIME_IN_DAYS on a large permanent table from 1 day to 90 days ahead of an audit. What is the most accurate way to describe the effect this has on their Snowflake bill?
Correct answer: A — Storage costs rise because Snowflake must retain the changed and deleted rows needed to reconstruct historical versions across the full 90-day window.
- A. Correct - extending retention means more historical row versions stay on disk, and that additional historical storage is billed as ordinary storage cost.
- B. Incorrect - ordinary queries against the current table do not scan 90 days of history by default; only explicit AT/BEFORE queries read historical versions.
- C. Incorrect - Time Travel storage is billed as its own storage line item, separate from compute credits, so raising retention does change the storage bill.
- D. Incorrect - Time Travel storage is charged on its own as soon as historical versions accumulate, well before any data ever reaches Fail-safe.
4.Zero-copy cloning as a point-in-time backup
Time Travel lists "duplicating and backing up data from key points in the past" among its uses, and cloning is how you do that. CREATE … CLONE with AT | BEFORE copies a table, schema or database exactly as it was at a chosen moment. That gives you a copy you can check before you swap it in, or keep as a backup. Cloning is cheap because it is zero-copy. A new clone shares the source's existing micro-partitions and uses no extra storage. Storage grows only when you add, delete or update data in the clone, because those changes create micro-partitions that belong to the clone alone.
For recovery, three limits apply. First, a clone made with Time Travel leaves out some objects: external tables and internal stages are not cloned. Hybrid tables are cloned with a database but not with a schema. Tasks are not cloned when you clone a schema at a timestamp. Second, a cloned table does not carry over the source table's load history, so files already loaded into the source can be loaded into the clone a second time. Third, there is a cost interaction. If a transient table is cloned from a permanent table, any micro-partitions they still share enter Fail-safe only after the transient clone is deleted, which can delay Fail-safe storage for the dropped original.
Checkpoint 7 of 8· Check yourself
You take a zero-copy clone of a 2 TB permanent table at a past timestamp and do not modify it. How much extra data storage does the clone add?
A clone shares the source's micro-partitions and adds storage only when operations modify existing data or add new data.
“a clone does not contribute to the overall data storage for the object until operations are performed on the clone”Source: docs.snowflake.com
5.Query performance impacts on recovery
Some recovery questions are really about how queries behave. Three points are documented. First, when you query historical data, Snowflake applies the table's or view's current schema. If a column was renamed or dropped since then, the result follows today's definition, not the definition at the time you are querying. Second, a long-running Time Travel query holds up background work. It delays moving data and objects across the whole account into Fail-safe, and it delays purging temporary and transient tables, until the query completes. Storage you expected to be released may therefore stay billed for longer. Third, a workload that creates large numbers of temporary tables slows down queries against the COLUMNS and TABLES views in the Information Schema. Snowflake recommends dropping temporary tables explicitly and logging out idle sessions.
Checkpoint 8 of 8· Check yourself
A team creates thousands of temporary tables per day and never drops them explicitly. Which documented side effect should they expect?
Large numbers of temporary tables slow down Information Schema queries. Temporary tables have no Fail-safe, and they are not replicated.
“you are very likely to experience degraded performance when you query the COLUMNS view or TABLES view in the Information Schema.”Source: docs.snowflake.com
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 after Time Travel to query or undrop your own data.Why is that wrong?
Fail-safe is for Snowflake's use only. You request recovery through a support case, it is best-effort, and it can take days.
Covered in Fail-safe: Snowflake's disaster recovery for historical data
2.Snowflake Support can recover a lost transient table, just like a permanent one.Why is that wrong?
Transient tables have no Fail-safe. Once their 0–1 day of Time Travel has passed, the data cannot be recovered by anyone.
Covered in Table types and costs: choosing how much protection to pay for
3.Setting retention to 0 only stops you from querying history. You can still UNDROP the object.Why is that wrong?
With a retention period of 0, a dropped object cannot be restored at all.
Covered in Backup/recovery with Time Travel: restoring after deletes and data corruption
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.”
↩︎ Backup/recovery with Time Travel: restoring after deletes and data corruption“UNDROP <object> command for tables, schemas, databases, accounts, external volumes, and tags.”
↩︎ Backup/recovery with Time Travel: restoring after deletes and data corruption“Restoring objects that might have been accidentally or intentionally deleted.”
↩︎ Backup/recovery with Time Travel: restoring after deletes and data corruption“Duplicating and backing up data from key points in the past.”
↩︎ Zero-copy cloning as a point-in-time backup“When using Time Travel, the following object types are not cloned:”
↩︎ Zero-copy cloning as a point-in-time backup“When querying historical data in a table or non-materialized view, the current table or view schema is used.”
↩︎ Query performance impacts on recovery“A long-running Time Travel query will delay moving any data and objects”
↩︎ Query performance impacts on recovery“The data retention period specifies the number of days for which this historical data is preserved”
↩︎ Key concept“When an object with no retention period is dropped, you will not be able to restore the object.”
↩︎ Exam trap 3“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“Historical data is no longer available for querying.”
↩︎ Prediction - 2.
“Fail-safe provides a (non-configurable) 7-day period during which historical data may be recoverable by Snowflake.”
↩︎ Fail-safe: Snowflake's disaster recovery for historical data“Data recovery through Fail-safe may take from several hours to several days to complete.”
↩︎ Fail-safe: Snowflake's disaster recovery for historical data“Data recovery through Fail-safe uses Snowflake-managed serverless compute.”
↩︎ Fail-safe: Snowflake's disaster recovery for historical data“Fail-safe doesn’t support tables that contain data ingested by the Snowpipe Streaming classic architecture.”
↩︎ Fail-safe: Snowflake's disaster recovery for historical data“Fail-safe is not provided as a means for accessing historical data after the Time Travel retention period has ended.”
↩︎ Exam trap 1“To recover data from Fail-safe, open a case with Snowflake Support.”
↩︎ Checkpoint - 3.
“Disaster recovery of historical data (by Snowflake) through Snowflake Fail-safe.”
↩︎ Fail-safe: Snowflake's disaster recovery for historical data - 4.
“Snowflake only maintains full copies of tables when tables are dropped or truncated.”
↩︎ Table types and costs: choosing how much protection to pay for“Long-lived tables, such as fact tables, should always be defined as permanent to ensure they are fully protected by Fail-safe.”
↩︎ Table types and costs: choosing how much protection to pay for“permanent tables, even with their added Fail-safe costs, might offer a better overall solution than transient tables.”
↩︎ Table types and costs: choosing how much protection to pay for“Historical data in transient tables can’t be recovered by Snowflake after the Time Travel retention period ends.”
↩︎ Exam trap 2“Transient and temporary tables have no Fail-safe period.”
↩︎ Checkpoint - 5.
“This behavior is particularly important to note when dropping a table in a session and then using Time Travel to restore the table.”
↩︎ Table types and costs: choosing how much protection to pay for“it utilizes no data storage because it shares all of the existing micro-partitions of the original permanent table.”
↩︎ Zero-copy cloning as a point-in-time backup“those shared bytes will only enter Fail-safe when the transient table is deleted.”
↩︎ Zero-copy cloning as a point-in-time backup“A long-running Time Travel query will delay the purging of temporary and transient tables until the query completes.”
↩︎ Query performance impacts on recovery“you are very likely to experience degraded performance when you query the COLUMNS view or TABLES view in the Information Schema.”
↩︎ Checkpoint - 6.
“A cloned table does not include the load history of the source table.”
↩︎ Zero-copy cloning as a point-in-time backup“a clone does not contribute to the overall data storage for the object until operations are performed on the clone”
↩︎ Checkpoint