Snowflake Time Travel, Fail-Safe & Zero-Copy Cloning
Day 43 closed Domain 4. Today opens Domain 5 with its three recovery and copy features: Time Travel, Fail-safe, and Zero-Copy Cloning.
This sub-objective uses recovery words that sound interchangeable. The exam treats each as a separate mechanism with its own owner and window.
| Term | What it sounds like | What it means in Snowflake |
|---|---|---|
| Time Travel | A sci-fi trick | Querying or restoring data as it existed at a past point, within the object’s retention period. |
| Retention period | How long files are kept | The number of days Time Travel can reach back, set by DATA_RETENTION_TIME_IN_DAYS. |
| UNDROP | An undo button | A command that restores a dropped table, schema, or database while it is still inside Time Travel. |
| Fail-safe | A backup you control | A seven-day layer after Time Travel ends. Only Snowflake support can recover from it, not you. |
| Zero-Copy Clone | A duplicated file | A new object that shares the source micro-partitions and costs no extra storage until changed. |
| Copy-on-write | Write a copy | The rule behind cloning. Source and clone share storage until one of them is modified. |
Time Travel: You Recover the Data
What Time Travel reaches
Time Travel queries data as it existed at a past moment. You can read rows that were updated or deleted. You can clone an object as it looked earlier. You can restore a dropped object with UNDROP. All of this works only inside the object’s retention period.
Time Travel covers tables, schemas, and databases. It does not cover external tables, because their data lives outside Snowflake storage. The reach is set per object by DATA_RETENTION_TIME_IN_DAYS.
AT and BEFORE
You point at history with two clauses. AT includes the change at the moment you name. BEFORE stops just short of it, returning the state immediately prior. That inclusive-versus-exclusive split is a tested detail.
Each clause takes one of three keywords. TIMESTAMP names a clock time. OFFSET names seconds back from now, as a negative number. STATEMENT names a query ID, so BEFORE(STATEMENT => '...') reads the table as it was before that query ran.
| Goal | Syntax |
|---|---|
| State at a clock time | SELECT * FROM t AT(TIMESTAMP => '2026-06-29 09:00:00'::TIMESTAMP); |
| State 5 minutes ago | SELECT * FROM t AT(OFFSET => -300); |
| State before a query ran | SELECT * FROM t BEFORE(STATEMENT => '01abc...'); |
Retention depends on edition and table type
The default retention is one day on every edition. Standard Edition caps a permanent object at one day. Enterprise Edition and higher raise the ceiling to 90 days for permanent objects. Transient and temporary tables cap at one day on all editions, with no exception.
Setting retention to 0 turns Time Travel off for that object. A dropped object with 0 retention cannot be restored. This is the trap behind many UNDROP questions.
| Object type | Standard Edition | Enterprise+ Edition |
|---|---|---|
| Permanent table, schema, database | 0 to 1 day | 0 to 90 days |
| Transient table, schema, database | 0 to 1 day | 0 to 1 day |
| Temporary table | 0 to 1 day | 0 to 1 day |
UNDROP restores within the window
UNDROP brings back a dropped table, schema, or database. It works only while the object sits in Time Travel. Once retention ends, UNDROP fails. A permanent object then passes into Fail-safe instead.
Fail-Safe: Only Snowflake Recovers
Fail-safe is a seven-day layer that begins when Time Travel ends. It exists for permanent tables only. You cannot query it, clone from it, or run UNDROP against it. Recovery happens through a Snowflake support request, on a best-effort basis.
The owner question decides most Fail-safe items. Time Travel is self-service, so you recover. Fail-safe is Snowflake-managed, so support recovers. A stem that says the user restores data directly is describing Time Travel, never Fail-safe.
Transient and temporary tables have no Fail-safe at all. A dropped transient table can be restored with UNDROP for up to one day, then it is gone for good. That single fact answers a recurring exam question on transient recovery.
| Aspect | Time Travel | Fail-safe |
|---|---|---|
| Who recovers | You, the user | Snowflake support only |
| Window | 0 to 90 days, by edition and type | Fixed 7 days |
| Query or clone it | Yes, with AT or BEFORE | No access of any kind |
| Applies to | Permanent, transient, temporary | Permanent tables only |
| You configure it | Yes, via retention days | No, it is fixed |
Zero-Copy Cloning: Metadata, Not Bytes
Copy-on-write
Zero-Copy Cloning creates a new object that points at the same micro-partitions as its source. No data is copied at clone time. The clone costs no extra storage until you change it. A later insert, update, or delete writes new micro-partitions. Only those new ones add cost.
The clone is fully independent after creation. Changes to the source do not appear in the clone. The reverse holds too. This is why cloning suits instant dev, test, and backup copies of large tables.
CREATE TABLE orders_dev CLONE orders_prod;
-- Shares storage with orders_prod until orders_dev is modified.Privileges do not clone at the top level
Cloning copies structure and data. It does not copy the explicit grants on the cloned object itself. The role that runs the clone owns the new object. You grant access again from there. Add COPY GRANTS to carry the source grants forward.
One nuance is tested. When you clone a database or schema, the grants on its child objects are preserved on the cloned children. The gap is only at the top level, on the cloned database, schema, or table you named.
What does not clone
Cloning reaches most schema objects, but not all. Internal named stages are skipped unless you add the INCLUDE INTERNAL STAGES clause. External tables are not cloned inside a database or schema clone. Virtual warehouses cannot be cloned, since they are compute, not a schema object.
Some children clone in a paused or empty state. A cloned stream starts empty. A cloned task or pipe is suspended or paused by default, so it does not fire on its own. External named stages do clone. They reference the same cloud storage.
| Object | Cloneable | Note |
|---|---|---|
| Database, schema, table | Yes | The core targets of CLONE |
| Dynamic table, stream, task, pipe | Yes | Stream empty, task and pipe paused |
| External named stage | Yes | Points at the same cloud storage |
| Internal named stage | No by default | Only with INCLUDE INTERNAL STAGES |
| External table | No | Skipped in a database or schema clone |
| Virtual warehouse | No | Compute, not a schema object |
Clone plus Time Travel
You can clone an object as it existed in the past. Add an AT or BEFORE clause to the CLONE command. This pairs the two features, giving a copy that reflects an earlier state.
CREATE TABLE orders_snapshot
CLONE orders_prod
BEFORE(STATEMENT => '01abc-bad-update-id');Cheat Sheet
| Concept | What to remember | Exam keyword |
|---|---|---|
| Time Travel owner | You query and restore historical data yourself | “user recovers” |
| Fail-safe owner | Only Snowflake support recovers, 7 fixed days | “contact support” |
| Default retention | 1 day on every edition | “1 day default” |
| Permanent max | 1 day Standard, 90 days Enterprise+ | “90 days Enterprise” |
| Transient and temporary | Max 1 day Time Travel, no Fail-safe | “no Fail-safe” |
| AT vs BEFORE | AT includes the point. BEFORE stops just short | “BEFORE is exclusive” |
| UNDROP | Restores a dropped object while in Time Travel | “undrop within retention” |
| Clone cost | Zero storage until the clone is changed | “copy-on-write” |
| Clone grants | Top-level grants not copied without COPY GRANTS | “privileges not cloned” |
| Not cloneable | Warehouses, external tables, internal named stages by default | “cannot clone warehouse” |
Exam Tip
The recovery owner settles most items in this sub-objective. If the stem says the user restores or queries the old data, the answer is Time Travel. If the stem says recovery needs Snowflake support, the answer is Fail-safe. An option that lets a user query Fail-safe is always wrong.
Watch the table type in retention questions. A permanent table can hold up to 90 days on Enterprise. A transient or temporary table caps at one day on any edition. It has no Fail-safe. A dropped transient table is recoverable for one day, then gone.
For cloning, two traps repeat. First, cloning does not copy the source object’s own grants unless COPY GRANTS is named. Second, internal named stages, external tables, and virtual warehouses do not clone. A multi-select that lists a warehouse as cloneable is using that as the catch.
On AT versus BEFORE, remember BEFORE returns the state just prior to the named point. So to undo a bad UPDATE, clone or query with BEFORE(STATEMENT => 'bad_query_id').
Hands-On Lab
lab_xs warehouse. This lab creates tt_lab, clone_src, and clone_cp, then drops all three. It reads SNOWFLAKE_SAMPLE_DATA only. It does not alter any shared object.Create a small table and load two rows.
USE WAREHOUSE lab_xs;
CREATE OR REPLACE TABLE tt_lab (id INT, name STRING);
INSERT INTO tt_lab VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM tt_lab;Update one row, then capture the update’s query ID. The variable holds the ID for a deterministic Time Travel read.
UPDATE tt_lab SET name = 'Charlie' WHERE id = 1;
SET upd_id = LAST_QUERY_ID();
SELECT * FROM tt_lab;upd_id stores the UPDATE statement’s ID for the next step.Read the pre-update state with BEFORE. BEFORE returns the table as it stood just before that UPDATE ran.
SELECT * FROM tt_lab BEFORE(STATEMENT => $upd_id);Drop the table, then bring it back with UNDROP. The table is still inside its retention window.
DROP TABLE tt_lab;
UNDROP TABLE tt_lab;
SELECT * FROM tt_lab;Clone a table, change the clone, then check its grants. The clone shares storage until the DELETE writes new micro-partitions.
CREATE OR REPLACE TABLE clone_src AS
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS LIMIT 10000;
CREATE TABLE clone_cp CLONE clone_src;
DELETE FROM clone_cp WHERE O_ORDERSTATUS = 'F';
SELECT
(SELECT COUNT(*) FROM clone_src) AS src_rows,
(SELECT COUNT(*) FROM clone_cp) AS clone_rows;
SHOW GRANTS ON TABLE clone_cp;Cleanup. Drop the three lab tables and suspend the shared warehouse.
DROP TABLE tt_lab;
DROP TABLE clone_src;
DROP TABLE clone_cp;
UNSET upd_id;
ALTER WAREHOUSE lab_xs SUSPEND;
-- Keep lab_xs itself. It is the shared warehouse reused across days.Practice Questions
Options:
A. Run UNDROP on the table
B. Query the table with a BEFORE clause
C. Submit a request to Snowflake support
D. Clone the table AT an earlier timestamp
Why C: Fail-safe is Snowflake-managed. The owner has no direct access, so recovery runs through a support request on a best-effort basis.
Why not A: UNDROP works only while the object is in Time Travel. Once data moves to Fail-safe, UNDROP fails.
Why not B: AT and BEFORE read Time Travel history. They cannot reach Fail-safe data.
Why not D: Cloning from a past point also relies on Time Travel. A point inside Fail-safe is no longer cloneable.
Options:
A. It can be undropped using Fail-safe
B. It can be recovered only with help from Snowflake support
C. It can be recovered for 1 day only, then it is no longer available
D. It is removed instantly with no recovery option
Why C: A transient table has a maximum Time Travel of one day and no Fail-safe. UNDROP works for up to one day, after which the table is gone.
Why not A: Transient tables have no Fail-safe layer, so Fail-safe cannot restore them.
Why not B: Support recovers from Fail-safe. With no Fail-safe here, support cannot help either.
Why not D: There is a recovery window. UNDROP still works during the one-day retention.
Options:
A. CREATE TABLE fix CLONE t AT(STATEMENT => ‘<id>’)
B. CREATE TABLE fix CLONE t BEFORE(STATEMENT => ‘<id>’)
C. CREATE TABLE fix LIKE t BEFORE(STATEMENT => ‘<id>’)
D. CREATE TABLE fix AS SELECT * FROM t WHERE query_id => ‘<id>’
Why B: BEFORE returns the state just prior to the named statement. Cloning with that clause copies the table as it stood before the bad update.
Why not A: AT includes the named statement, so the corrupting update is part of the result. Use BEFORE to exclude it.
Why not C: CREATE TABLE LIKE copies structure only. It takes no data and accepts no Time Travel clause.
Why not D: There is no query_id column on a table. That syntax is also not valid SQL.
Options:
A. The clone inherits every explicit grant from clone_src
B. The clone inherits no explicit grant from clone_src, and the creating role owns it
C. The clone shares a single set of grants with clone_src
D. The clone blocks all access until an admin runs UNDROP
Why B: Without COPY GRANTS, a clone does not inherit the source object’s explicit grants. The role that ran the clone becomes its owner.
Why not A: That is what COPY GRANTS would do. The command here omits it, so grants do not carry over.
Why not C: The clone is independent. It does not share a grant set with its source.
Why not D: UNDROP restores dropped objects. It has nothing to do with privileges on a clone.
Options:
A. Virtual warehouses
B. Internal named stages
C. External named stages
D. Dynamic tables
E. External tables
Why C and D: CREATE CLONE supports external named stages and dynamic tables, along with databases, schemas, tables, streams, tasks, pipes, file formats, and sequences.
Why not A: A virtual warehouse is compute, not a schema object, so it cannot be cloned.
Why not B: An internal named stage is not cloned individually. It comes along only with INCLUDE INTERNAL STAGES on a database or schema clone.
Why not E: External tables are skipped inside a database or schema clone, because their data lives outside Snowflake.
Snowflake Documentation
Official docs for today’s topics. The exam pulls directly from these.
Today you learned: how Snowflake protects and copies data. Time Travel lets you query and restore historical data within a retention period. AT includes the named point. BEFORE stops just short of it. Retention defaults to one day on every edition. Permanent objects reach 90 days on Enterprise. Transient and temporary tables cap at one day. UNDROP restores a dropped object while it is still in Time Travel. Fail-safe is a fixed seven-day layer for permanent tables, recoverable only by Snowflake support. Zero-Copy Cloning shares micro-partitions with the source and costs no storage until the clone is changed.
Key takeaway: decide every recovery question by the owner. The user recovers through Time Travel. Snowflake support recovers from Fail-safe. For cloning, the source object’s own grants do not carry over without COPY GRANTS. Internal named stages, external tables, and virtual warehouses do not clone.
Tomorrow (Day 45): Replication, Failover, and Secure Data Sharing. You move from protecting data to collaborating with it across regions and accounts. Topics include database and account replication, failover groups for disaster recovery, and the metadata-only nature of secure data sharing.
