Time Travel Clones: Validate, Promote and Roll Back Changes

1.Cloning a past state with AT | BEFORE

Time Travel extends cloning to the past. Within an object's data retention period, you can clone entire tables, schemas and databases as they were at, or just before, a chosen point. You add the AT | BEFORE clause right after the source name in CREATE … CLONE, and you pin the point with one of three parameters:

CREATE SCHEMA restored_schema CLONE my_schema AT(OFFSET => -3600);

AT includes the point you name. BEFORE stops just short of it. With a statement ID, BEFORE returns the data up to, but not including, the changes that statement made. That is exactly the state you want when one bad statement needs undoing.

A point-in-time clone does not rebuild everything from the past. As the prediction showed, user tasks are dropped from timestamp clones. Internal stages are not cloned when using Time Travel, and child objects that didn't exist at the chosen point are skipped. If the source itself didn't exist at that point, the statement returns an error. Name and structure come from the chosen point, but other metadata such as comments and clustering keys comes from the source as it is now.

Sources123

2.Validating changes in a clone, then promoting with SWAP WITH

Cloning, Time Travel and swapping fit together as a release workflow. Clone production, apply the change to the clone, check it, then make the checked version the live one. A clone is a good place for validation because it is writable and independent: migration scripts can run end to end and production never sees them. Nothing in the clone starts on its own. Tasks and alerts arrive suspended, so you can resume just the tasks you want to test with ALTER TASK … RESUME. The docs don't describe a specific test harness. Validation means whatever queries and task runs you choose to execute against the clone.

Promotion is done with a swap, not a copy. ALTER SCHEMA … SWAP WITH swaps all objects and metadata, including identifiers, between two schemas. It is effectively a rename of both schemas in a single operation. For a single table, ALTER TABLE … SWAP WITH renames two tables in one transaction. After the swap, the old production version still exists under the other name. If the release goes wrong, swapping again puts it back.

ALTER SCHEMA [ IF EXISTS ] <name> SWAP WITH <target_schema_name>

Sources452

3.Rolling back: UNDROP, point-in-time clones, and where recovery ends

Rolling back a dropped object takes one command. UNDROP restores a dropped table, schema or database to its state just before the DROP. The object is restored in place, not created as a new one. You need OWNERSHIP of the object, plus the CREATE privilege for that object type in the database or schema it returns to. If an object with the same name already exists, UNDROP fails, so rename the existing object first.

UNDROP TABLE mytable; UNDROP SCHEMA myschema; UNDROP DATABASE mydatabase;

A bad UPDATE or DELETE doesn't drop anything, so UNDROP can't help. Instead, clone the object as it was before the change, with BEFORE(STATEMENT => …) or AT(TIMESTAMP => …). Then repair from the clone or swap it in.

CREATE TABLE restored_table CLONE my_table AT(TIMESTAMP => 'Wed, 26 Jun 2024 01:01:00 +0300'::timestamp_tz);

Both methods stop working when the retention period ends. The standard period is 1 day. On Enterprise Edition and higher, permanent objects can be set anywhere from 0 to 90 days. Transient objects, and temporary tables, can only be 0 or 1. Once the period ends, history moves to Fail-safe and can no longer be queried, cloned or undropped. A permanent table in Fail-safe (7 days) can be recovered only by Snowflake. A transient or temporary table has no Fail-safe and is purged. With retention set to 0, a dropped object can't be restored at all. Check retention before you count on a rollback:

SHOW TABLES
  ->> SELECT "name", "retention_time"
        FROM $1
        WHERE "name" IN ('MY_TABLE1', 'MY_TABLE2');

Sources16

4.When a point-in-time database clone fails

Rolling back a whole database or schema with a Time Travel clone can fail even when the container's own retention covers the target time. Each child table keeps its own retention period. A child with a shorter period loses its history before its parent does. Say database db1 keeps 7 days and its table t1 keeps 1 day. Cloning db1 as of 12 hours ago works. Cloning it as of two days ago fails, because t1's history from then is gone. The clone also fails if a pipe with AUTO_INGEST = TRUE has been recreated or dropped since the chosen point, or if the source contains hybrid tables and IGNORE HYBRID TABLES isn't specified.

For the retention problem, the fix is IGNORE TABLES WITH INSUFFICIENT DATA RETENTION. It skips any table whose history no longer reaches the chosen time, and clones everything else.

CREATE DATABASE restored_db CLONE my_db AT(TIMESTAMP => DATEADD(days, -4, current_timestamp)::timestamp_tz) IGNORE TABLES WITH INSUFFICIENT DATA RETENTION;

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Once Time Travel retention has passed, you can still UNDROP a permanent table yourself during its 7 days in Fail-safe.Why is that wrong?

   Users can't use Fail-safe themselves. Only Snowflake can recover data from it.

   Covered in Rolling back: UNDROP, point-in-time clones, and where recovery ends

2. 2.UNDROP replaces any table that currently has the same name.Why is that wrong?

   UNDROP fails if an object with that name exists. You must rename the existing object first.

   Covered in Rolling back: UNDROP, point-in-time clones, and where recovery ends

3. 3.A schema cloned AT a timestamp contains everything a current clone would, including its user tasks.Why is that wrong?

   User tasks are not cloned when the clone uses a timestamp, even though a current clone of the same schema would include them.

   Covered in Cloning a past state with AT | BEFORE