Storage and Data Protection · Topic 1 of 4

Time Travel, Fail-safe and Replication

Choose the right Snowflake recovery feature: Time Travel retention, Fail-safe, how streams extend retention, and cross-region and cross-cloud replication.

  • 5 min
  • Includes a diagram
In a nutshell
  • Time Travel: AT, BEFORE, UNDROP, retention 0 to 90 days on Enterprise, 1 day on Standard, 1 day maximum for transient and temporary.
  • Fail-safe: 7 days, permanent tables only, support only, not queryable.
  • Unconsumed streams extend retention up to MAX_DATA_EXTENSION_TIME_IN_DAYS (default 14), then go stale.

What it is

Snowflake gives you several layers of protection, and each answers a different failure. Time Travel lets you query, clone or restore data as it was in the recent past. Fail-safe is a last resort copy that only Snowflake can restore. Replication copies objects to another account, region or cloud for business continuity.

A data engineer must pick the layer that fits the failure. A bad UPDATE ten minutes ago is Time Travel. A lost region is replication.

Bad DML or DROPuser erroruseTime Travelself serviceafter retention endsFail safeSnowflake support onlyRegion outageor cloud outageuseReplicationsecondary copypromoteFailoverbusiness continuity

How it works

Time Travel

Time Travel keeps the old versions of changed or dropped data for the retention period, set with DATA_RETENTION_TIME_IN_DAYS.

Object typeRetention rangeDefault
Permanent table, schema, database0 to 1 day on Standard edition, 0 to 90 days on Enterprise or higher1 day
Transient or temporary table0 or 1 day1 day

You can set the parameter on the account, database, schema or table, and a lower level overrides a higher one. An account level MIN_DATA_RETENTION_TIME_IN_DAYS sets a floor that objects cannot go below.

SELECT * FROM orders AT (OFFSET => -60*10);                          -- 10 minutes ago
SELECT * FROM orders AT (TIMESTAMP => '2026-11-01 08:00:00'::TIMESTAMP_LTZ);
SELECT * FROM orders BEFORE (STATEMENT => '01b2c3d4-0000-1111-0000-aaaabbbbcccc');
UNDROP TABLE orders;
  • AT includes changes made at the given point. BEFORE returns data just before a statement ran, which is the usual way to undo one bad query.
  • UNDROP restores a dropped table, schema or database. It fails if an object with the same name already exists, so rename the new one first.
  • Querying past the retention period fails. Setting retention to 0 turns Time Travel off for that object.
  • Data kept for Time Travel is billed as storage.
Exam tip

When the question says "restore the table to the state before a specific query", the answer is BEFORE (STATEMENT => query ID), usually combined with a clone.

Impact of streams

A stream records an offset into the source table's history. Time Travel data is what makes that offset readable, so an unconsumed stream extends the table's retention.

  • Snowflake extends retention up to MAX_DATA_EXTENSION_TIME_IN_DAYS, which defaults to 14 days and can be raised up to 90.
  • If the stream is not consumed before the extended period passes, it becomes stale and must be recreated. SHOW STREAMS lists STALE_AFTER.
  • The extension costs extra storage, so a forgotten stream on a busy table quietly grows the bill.
  • Dropping and recreating the source table, for example with CREATE OR REPLACE TABLE, also breaks the stream.
  • A stream in a clone does not see the unconsumed changes of the original stream.
Source tableretention 1 dayoffset not consumedStreamhas pending changesextends retentionExtra Time Travel storageup to 14 days by defaultstill unreadStale streamrecreate it

Fail-safe

Changed or dropped data stays in Time Travel for the retention period, then 7 days of Fail-safe for permanent tables, recoverable only by Snowflake Support, then it is purged; transient and temporary tables have no Fail-safe. Fail-safe starts when Time Travel ends. It is 7 days, fixed, for permanent tables only. Transient and temporary tables have no Fail-safe.

Time TravelFail-safe
Who can use itYou, with SQLSnowflake support only
Length0 to 90 days, configurable7 days, not configurable
PurposeQuery, clone, undoDisaster recovery of last resort
Applies toPermanent, transient, temporaryPermanent only

You cannot query Fail-safe data or restore it yourself, and recovery is best effort and can take hours to days. Treat it as a safety net, not a backup plan. Use transient tables for staging data you can reload, to avoid paying for 7 days of Fail-safe storage.

Cross-region and cross-cloud replication

Replication keeps a read only secondary copy of objects in another account, region or cloud platform, refreshed from the primary.

ObjectContainsNotes
Replication groupDatabases, shares, users, roles, warehouses and other account objects, chosen by youSecondary refreshed on a schedule or manually; databases and shares on any edition, other account objects need Business Critical or higher
Failover groupSame, but the secondary can be promoted to primaryRequires Business Critical edition or higher
Database replicationA single database (ALTER DATABASE ... ENABLE REPLICATION TO ACCOUNTS)Older, per database approach
CREATE FAILOVER GROUP fg_sales
  OBJECT_TYPES = DATABASES, ROLES
  ALLOWED_DATABASES = sales_db
  ALLOWED_ACCOUNTS = myorg.dr_account
  REPLICATION_SCHEDULE = '10 MINUTE';
  • Secondary objects are read only until promoted. Promotion with ALTER FAILOVER GROUP ... PRIMARY switches roles.
  • Refreshes use serverless compute and cross-region data transfer, which both cost money.
  • A bad DELETE replicates too, so replication is not a substitute for Time Travel.

Common mistakes

Thinking Fail-safe is self service. Only Snowflake support can use it, and it applies to permanent tables only.

Expecting 90 days on any table. Standard edition stops at 1 day, and transient or temporary tables stop at 1 day on every edition.

Ignoring idle streams. They extend retention and eventually go stale.

Calling replication a backup of mistakes. Time Travel fixes mistakes, replication fixes outages.

What to remember for the exam

  • Time Travel: AT, BEFORE, UNDROP, retention 0 to 90 days on Enterprise, 1 day on Standard, 1 day maximum for transient and temporary.
  • Fail-safe: 7 days, permanent tables only, support only, not queryable.
  • Unconsumed streams extend retention up to MAX_DATA_EXTENSION_TIME_IN_DAYS (default 14), then go stale.
  • Failover groups need Business Critical; replicated secondaries are read only until promoted.

Check yourself

  • A table has a 1 day retention period and a stream that nobody reads. What happens to the table's history?

    Snowflake extends its retention, up to 14 days by default, until the stream is consumed or goes stale.

  • Which tables have no Fail-safe period?

    Transient and temporary tables.

  • Which feature protects against a whole region becoming unavailable?

    Replication with a failover group, which promotes a secondary account in another region or cloud.

Topic 1 of 4 in Storage and Data Protection

Test yourself on Storage and Data Protection

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