Disaster Recovery, Backup and Data Replication · Topic 8 of 9

Time Travel, Fail-safe, Retention Periods and Historical Queries

Manage Time Travel and Fail-safe, set data retention periods at each level, and query historical data with AT and BEFORE, including limits by table type.

  • 5 min
  • Includes a diagram
In a nutshell
  • Parameter: DATA_RETENTION_TIME_IN_DAYS; default 1; 0 disables Time Travel; lower levels override higher levels.
  • Standard 0 or 1 day; Enterprise and above up to 90 days for permanent objects; transient and temporary 0 or 1 day.
  • Fail-safe is 7 days for permanent tables, Snowflake only, none for transient and temporary tables.

What it is

Time Travel lets you see or restore data as it was at an earlier point inside a retention period. You can query old data, clone old data, and undrop objects. It is a self-service feature for the administrator and for users.

Fail-safe is a further, fixed period after Time Travel ends. Only Snowflake can recover data in it, and only as a last resort. It is not a feature you query or configure.

Live datachange or dropTime TravelDATA_RETENTION_TIME_IN_DAYS, self serviceperiod endsFail safe7 days, Snowflake onlyendsPurged

How it works

Retention periods

Time Travel and Fail-safe by object type: permanent tables get 0 or 1 day on Standard Edition and 0 to 90 days on Enterprise or higher, both with 7 days of Fail-safe; transient and temporary objects get 0 or 1 day and no Fail-safe. The parameter is DATA_RETENTION_TIME_IN_DAYS. It can be set on the account, a database, a schema or a table, and a lower level overrides the level above it.

Object typeTime Travel rangeFail-safe
Permanent table, Standard Edition0 or 1 day7 days
Permanent table, Enterprise Edition or higher0 to 90 days (default 1)7 days
Transient table, schema or database0 or 1 dayNone
Temporary table0 or 1 day, ends with the sessionNone

The default is 1 day. Setting the value to 0 turns Time Travel off for that object.

ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 7;
ALTER DATABASE sales SET DATA_RETENTION_TIME_IN_DAYS = 30;
ALTER TABLE sales.public.orders SET DATA_RETENTION_TIME_IN_DAYS = 90;

Account level settings need ACCOUNTADMIN. The account parameter MIN_DATA_RETENTION_TIME_IN_DAYS sets a floor: the effective retention of an object is the larger of its own value and this minimum. An administrator uses it to stop teams from lowering retention below a policy value.

ParameterLevelEffect
DATA_RETENTION_TIME_IN_DAYSAccount, database, schema, tableSets the Time Travel period
MIN_DATA_RETENTION_TIME_IN_DAYSAccountEnforces a minimum; the larger value wins

Changing retention has a direct effect. Raising it extends how far back data can be reached for data still in the period. Lowering it makes data older than the new value leave Time Travel, and for permanent tables it then moves into Fail-safe.

Exam tip

Long retention costs storage, because Snowflake keeps every changed micro-partition for the whole period. Use 90 days only for tables that need it, and keep staging or intermediate data transient with 0 or 1 day.

Fail-safe

Fail-safe lasts 7 days for permanent tables and starts when Time Travel ends. It cannot be changed, turned off or queried, and you recover from it only by contacting Snowflake Support.

  • Transient and temporary tables have no Fail-safe.
  • Fail-safe storage is billed as part of storage cost.
  • TABLE_STORAGE_METRICS in SNOWFLAKE.ACCOUNT_USAGE and in INFORMATION_SCHEMA shows TIME_TRAVEL_BYTES and FAILSAFE_BYTES per table, so you can find where this storage sits.
CompareTime TravelFail-safe
Who recoversYouSnowflake Support only
LengthConfigurableFixed 7 days (permanent tables)
AccessAT, BEFORE, UNDROP, cloneNone by SQL
PurposeSelf service restoreLast resort recovery

Querying historical data

Use the AT or BEFORE clause in a SELECT. Choose one of three anchors.

-- a point in time
SELECT * FROM orders AT(TIMESTAMP => '2026-11-10 09:00:00'::TIMESTAMP_LTZ);

-- an offset in seconds before now (here 10 minutes)
SELECT * FROM orders AT(OFFSET => -60*10);

-- just before a statement ran
SELECT * FROM orders BEFORE(STATEMENT => '<query_id>');
AnchorMeaning
TIMESTAMPData as of that moment
OFFSETData as of a number of seconds ago, as a negative number
STATEMENTData as of a statement's query ID; AT includes its changes, BEFORE shows the state just before it

You can also create a clone as of a past point, which is the usual way to recover without touching the live table.

CREATE TABLE orders_before_fix CLONE orders BEFORE(STATEMENT => '<query_id>');

Rules and limits:

  • The requested time must fall inside the retention period for that object. Older data returns an error.
  • A query cannot reach back before the table was created.
  • Cloning a database or schema as of a time can fail if a child table's Time Travel does not reach that point.
  • Querying historical data of a dropped table works only until it is purged; undrop is easier (next page).
Bad update at 10:05find query ID|QUERY_HISTORY -clone BEFORE statementClean copyorders_before_fixcompare or swapLive tablecorrected
Watch out

A fix that overwrites the live table with an old copy also removes valid changes made after the bad statement. Clone first, compare, then decide.

Common mistakes

Assuming 90 days on every edition. Standard Edition allows 0 or 1 day.

Expecting Fail-safe on transient tables. They have none, so recovery stops when Time Travel ends.

Trying to query Fail-safe data. It is reachable only through Snowflake Support.

Setting a schema to 90 days and expecting transient tables inside to follow. A transient object is limited to 1 day.

Reading OFFSET as positive. It is negative seconds into the past.

What to remember for the exam

  • Parameter: DATA_RETENTION_TIME_IN_DAYS; default 1; 0 disables Time Travel; lower levels override higher levels.
  • Standard 0 or 1 day; Enterprise and above up to 90 days for permanent objects; transient and temporary 0 or 1 day.
  • Fail-safe is 7 days for permanent tables, Snowflake only, none for transient and temporary tables.
  • MIN_DATA_RETENTION_TIME_IN_DAYS is an account level floor.
  • AT and BEFORE take TIMESTAMP, OFFSET or STATEMENT.

Check yourself

  • What is the longest Time Travel for a permanent table on Standard Edition?

    1 day, so the setting is 0 or 1.

  • Who can read data in Fail-safe?

    Only Snowflake, through a request to Snowflake Support; users cannot query it.

  • How do you see a table as it was just before a bad statement ran?

    Use BEFORE(STATEMENT => '<query_id>') or clone with that clause.

Topic 8 of 9 in Disaster Recovery, Backup and Data Replication

Test yourself on Disaster Recovery, Backup and Data Replication

The full Snowflake ADA-C02 question bank has 814 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.