Course contents

SnowPro Core COF-C03 Study GuidePart 5: Data protection and sharingTime Travel

Time Travel

Part 5

Data Protection & Data Sharing

How Snowflake recovers from mistakes, survives regional outages, and shares live data with other accounts without ever copying it. Two chapters: continuous data protection (Time Travel, Fail-safe, cloning, replication), then secure sharing and the Marketplace.

Domain 5 is only 10%, but data protection comes up across the other domains too, so this part earns more than its weight. Most mistakes are about who may do what: Fail-safe reachable only through Support, consumers strictly read-only, reader accounts billed to the provider, and the list of things a clone doesn't carry (load history, grants on the cloned database or schema itself, internal named stages). Watch for invented commands too: RESTORE SCHEMA sounds right and doesn't exist; UNDROP does.

Continuous Data Protection

Four features, one underlying mechanism from Part 1: micro-partitions are immutable, so old versions can be kept around and pointed back to. Time Travel, Fail-safe, and zero-copy cloning are three different ways of exploiting that one fact.

Time Travel

Definition

Time Travel lets you query, clone, or restore data as it existed at any point within a configurable retention window, by keeping the old micro-partitions a change would otherwise discard. It covers tables, schemas, and databases.

Retention by edition

EditionDefault retentionConfigurable range
Standard1 day0 or 1 day
Enterprise and above1 day0 to 90 days (permanent tables only)

Retention is controlled by DATA_RETENTION_TIME_IN_DAYS, settable at account, database, schema, or table level, with the more specific level winning. An account-level MIN_DATA_RETENTION_TIME_IN_DAYS acts as a floor: if it's set to 5 and a table tries to set 2, the effective retention is 5, the higher of the two. The effective retention can never dip below the account minimum. The account minimum applies to every object, old or new: Snowflake uses the higher of the two numbers and leaves the table's own setting as written. Retention 0 turns Time Travel off: changed data in a permanent table goes straight to Fail-safe, and in a transient table it's deleted. Raising retention keeps data already in Time Travel for longer. Lowering it moves older history out of Time Travel, into Fail-safe on permanent tables, and the 7 days of Fail-safe start when the data leaves Time Travel.

Three ways to query the past, plus UNDROP to bring back a dropped object

Every Time Travel recovery uses one of four constructs. Learn the mapping, not just the syntax:

ClauseWhat it gives youReach for it when
AT (TIMESTAMP => ...)The table exactly as it was at a wall-clock momentYou know when things were still good
AT (OFFSET => -3600)The table N seconds ago, relative to now"About an hour ago" is precise enough
BEFORE (STATEMENT => '<query_id>')The table one instant before a specific statement ranRecovering from a bad DML whose query ID you have; the surgical option
UNDROP TABLE / SCHEMA / DATABASEA dropped object back in place, children includedThe object is gone entirely, not just its rows

UNDROP restores an entire dropped table, schema, or database, along with every child object, in a single operation, as long as it's still inside the retention window and no object of the same name already occupies that spot (rename or drop the conflicting object first).

Time Travel on table ORDERS, illustrative times. Rows are intact until a bad DELETE at 14:30; it is now 15:00. AT TIMESTAMP 14:00 and AT OFFSET minus 3600 seconds both point at 14:00. BEFORE STATEMENT with the DELETE's query ID points at the instant before the DELETE. All three moments are inside the retention window, so all three return the rows before the DELETE.WHAT ORDERS HOLDS · ILLUSTRATIVE TIMESTHREE WAYS TO POINT AT A MOMENTrows intact (versions kept by Time Travel)rows deleted13:0014:0014:30 DELETE15:00 nowCLAUSEAT (TIMESTAMP =>'… 14:00')a clock timeCLAUSEAT (OFFSET =>-3600)seconds before nowCLAUSEBEFORE (STATEMENT =>'<DELETE query id>')just before that DMLAll three land before 14:30, so all three return the rows the DELETE removed.AT includes the moment given; BEFORE stops just short of the statement. Time Travel on table ORDERS, illustrative times. Rows are intact until a bad DELETE at 14:30; it is now 15:00. AT TIMESTAMP 14:00 and AT OFFSET minus 3600 seconds both point at 14:00. BEFORE STATEMENT with the DELETE's query ID points at the instant before the DELETE. All three moments are inside the retention window, so all three return the rows before the DELETE.WHAT ORDERS HOLDS ·ILLUSTRATIVE TIMESrows intact (versions keptby Time Travel)rowsdeleted13:0014:0015:00 now14:30DELETETHREE WAYS TO POINTAT A MOMENTCLAUSEAT (TIMESTAMP =>'… 14:00')a clock timeCLAUSEAT (OFFSET =>-3600)seconds before nowCLAUSEBEFORE (STATEMENT =>'<DELETE query id>')just before that DMLAll three land before 14:30, so all threereturn the rows the DELETE removed.AT includes the moment given; BEFOREstops just short of the statement.
Figure 5.1 Time Travel addresses any moment inside the retention window. Read the bottom row upward: a clock time, an offset from now, or the instant before a statement; here all three land before the 14:30 DELETE and return the lost rows (times illustrative).

The syntax, all of it

-- Query the past three ways
SELECT * FROM orders AT (TIMESTAMP => '2026-07-01 08:00:00'::TIMESTAMP_TZ);
SELECT * FROM orders AT (OFFSET => -3600);            -- one hour ago
SELECT * FROM orders BEFORE (STATEMENT => '<query_id>'); -- just before that DML

-- Recover: clone the past, or resurrect the dropped
CREATE TABLE orders_before_bug CLONE orders AT (OFFSET => -3600);
UNDROP TABLE orders;

-- Configure retention (Enterprise permanent tables: up to 90)
ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 30;

AT includes changes up to and including the moment given; BEFORE stops just short of the named statement, which makes BEFORE(STATEMENT) the precise tool after a bad DML: Safia deletes the wrong payroll rows, Thomas grabs the query ID from history, and the table one instant before her statement is a single clone away.

Exam trap · the schema-clone-collision scenario

User A runs CREATE OR REPLACE SCHEMA ... CLONE, which drops the schema User B was working in and puts the clone under its name, within the Time Travel window. The fix is not a fresh CREATE TABLE AS SELECT, and not contacting Support. Rename the offending clone to free up the name, then UNDROP SCHEMA the original. Practice this one until the fix is automatic.