Snowflake Architecture · Topic 16 of 18
Time Travel - Table Types, Costs, Availability and Query Performance
Time Travel for architects: retention by edition and table type, AT and BEFORE clauses, UNDROP, storage cost and the effect of retention on query speed.
- 5 min
- Includes a diagram
- Default retention is 1 day; 0 to 90 days only for permanent objects on Enterprise and higher.
- Transient and temporary tables: 0 or 1 day. External tables: none.
DATA_RETENTION_TIME_IN_DAYSinherits down;MIN_DATA_RETENTION_TIME_IN_DAYSis an account floor.
What it is
Time Travel keeps the previous state of changed or deleted data for a retention period, so you can query it, clone it or restore a dropped object. It works by keeping the old micro partitions rather than copying the table, which is why it is cheap to enable and easy to misjudge on cost.
The exam asks about four things: which retention you get for an edition and table type, how to read or restore past data, what it costs, and whether it slows queries.
How it works
Availability and retention

| Edition | Object | Retention (days) | Default |
|---|---|---|---|
| Standard | Databases, schemas, tables | 0 or 1 | 1 |
| Enterprise and higher | Permanent databases, schemas, tables | 0 to 90 | 1 |
| Enterprise and higher | Transient databases, schemas, tables and temporary tables | 0 or 1 | 1 |
Setting retention to 0 turns Time Travel off for that object. The parameter is DATA_RETENTION_TIME_IN_DAYS. Set it on the account, a database, a schema or a table; a lower level inherits the parent's value unless it sets its own. The account level MIN_DATA_RETENTION_TIME_IN_DAYS sets a floor, and the effective retention is the larger of the two values, so a minimum above 0 overrides a 0 on an object.
External tables do not have Time Travel. A dropped object can be restored only while it is still in its history.
Querying and restoring
Three ways to point at a past moment, used with AT (includes changes at that point) or BEFORE (stops just before it):
| Clause | Meaning |
|---|---|
TIMESTAMP => ... | A specific time |
OFFSET => -3600 | Seconds back from now |
STATEMENT => '<query id>' | The moment of a query, ideal for undoing a bad statement |
SELECT * FROM orders BEFORE (STATEMENT => '01b2c3d4-0000-1111-0000-aabbccddeeff');
CREATE TABLE orders_restored CLONE orders AT (OFFSET => -3600);
UNDROP TABLE orders;
Restore patterns an architect should know:
- Clone, validate, swap. Create a clone
ATorBEFOREthe bad change, check it, thenALTER TABLE ... SWAP WITH. - Selective repair.
INSERT INTO t SELECT ... FROM t AT (...)to bring back only some rows. - Undrop.
UNDROP TABLE,UNDROP SCHEMAandUNDROP DATABASErestore the most recent version before the drop, and fail if an object with the same name exists, so rename that one first. The role needs ownership of the object.
A query fails if the requested point is beyond the retention period. Cloning a schema or database at a past point fails if any current child is beyond its own retention, unless you add IGNORE TABLES WITH INSUFFICIENT DATA RETENTION.
When a question says "undo the last statement", the cleanest answer is BEFORE (STATEMENT => query_id), not a timestamp guess.
Table types
| Table type | Time Travel | Why it matters |
|---|---|---|
| Permanent | Up to 90 days on Enterprise and higher | Critical data, longest recovery window |
| Transient | 0 or 1 day | Cheaper; no Fail-safe |
| Temporary | 0 or 1 day, session lifetime | Gone when the session ends |
| External | None | Data is outside Snowflake |
Costs
Time Travel has no per query charge. The cost is storage for the old micro partitions kept during retention.
- Only data that changed or was deleted is held. Unchanged micro partitions are shared with the current table, so retention does not double the storage.
- A high churn table (frequent updates, deletes or reloads) accumulates much more retained data than a table that is rarely modified.
- Cost grows with the retention length. Going from 1 day to 90 days on a high churn table can multiply stored bytes.
- Time Travel bytes and Fail-safe bytes are reported separately for each table (for example
TIME_TRAVEL_BYTESandFAILSAFE_BYTESin the table storage metrics views), so you can find the tables that cost the most.
Architect's levers: long retention only on tables that need it, transient tables for staging and intermediate data, and rebuild instead of update where practical.
Query performance impact
Time Travel does not slow normal queries.
- Queries on current data read only the current micro partitions. Retained historical micro partitions are not scanned, so a longer retention period does not degrade them.
- A query that uses
ATorBEFOREreads the historical micro partitions of that moment, with the usual pruning and caching rules for that data. - Time Travel queries see the table's current schema, so a column dropped later is not shown in an older read.
- A long running Time Travel query delays moving the data it reads into Fail-safe until the query finishes.
The real impact of Time Travel is cost and operations, not speed. If the question is about query performance, the answer lies in clustering, warehouse size or caching, not in the retention period.
Common mistakes
Believing 90 days is available on every edition. Standard allows 0 or 1 day only.
Expecting 90 days on transient or temporary tables. They are limited to 0 or 1 day.
Assuming retention doubles storage. Only changed data is retained, so the cost depends on churn.
Blaming retention for slow queries. It does not affect scans of current data.
Trying to undrop over an existing name. Rename the current object first.
What to remember for the exam
- Default retention is 1 day; 0 to 90 days only for permanent objects on Enterprise and higher.
- Transient and temporary tables: 0 or 1 day. External tables: none.
DATA_RETENTION_TIME_IN_DAYSinherits down;MIN_DATA_RETENTION_TIME_IN_DAYSis an account floor.ATandBEFOREacceptTIMESTAMP,OFFSETandSTATEMENT.- Cost is storage of changed data, driven by churn and retention length.
- Retention does not slow queries on current data.
Check yourself
A team on Standard Edition asks for 30 days of Time Travel on a permanent table. Possible?
No. Standard allows 0 or 1 day; 30 days needs Enterprise Edition or higher.
Why can a heavily updated table with 90 day retention cost much more than a read only one?
Each change leaves old micro partitions that must be retained, so storage grows with churn and retention length.
Does setting retention to 90 days make queries on current data slower?
No. Queries on current data do not scan retained historical micro partitions.
Topic 16 of 18 in Snowflake Architecture
Test yourself on Snowflake Architecture
The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.