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
- 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.
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 type | Retention range | Default |
|---|---|---|
| Permanent table, schema, database | 0 to 1 day on Standard edition, 0 to 90 days on Enterprise or higher | 1 day |
| Transient or temporary table | 0 or 1 day | 1 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.
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 STREAMSlistsSTALE_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.
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 Travel | Fail-safe | |
|---|---|---|
| Who can use it | You, with SQL | Snowflake support only |
| Length | 0 to 90 days, configurable | 7 days, not configurable |
| Purpose | Query, clone, undo | Disaster recovery of last resort |
| Applies to | Permanent, transient, temporary | Permanent 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.
| Object | Contains | Notes |
|---|---|---|
| Replication group | Databases, shares, users, roles, warehouses and other account objects, chosen by you | Secondary refreshed on a schedule or manually; databases and shares on any edition, other account objects need Business Critical or higher |
| Failover group | Same, but the secondary can be promoted to primary | Requires Business Critical edition or higher |
| Database replication | A 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 ... PRIMARYswitches 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.