Snowflake Time Travel, Fail-safe and Zero-Copy Cloning

1.Time Travel: going back to data that has changed

Every UPDATE, DELETE or DROP in Snowflake leaves something behind. When data in a table is modified, Snowflake keeps the state of the data from before the change. Time Travel is the feature that lets you reach that earlier state: it gives you access to data that has been changed or deleted, at any point within a defined period.

The documentation names three things you can do inside that period:

- Query past data that has since been updated or deleted. - Clone entire tables, schemas and databases at or before specific points in the past. - Restore tables, schemas, databases and some other objects that have been dropped.

Two SQL extensions make this possible. The AT | BEFORE clause goes in a SELECT statement or a CREATE … CLONE command, right after the object name. It locates the historical data with one of three parameters: TIMESTAMP, OFFSET (seconds back from now) or STATEMENT (a query ID). The UNDROP <object> command brings back dropped tables, schemas, databases, accounts, external volumes and tags. When you query historical data in a table or non-materialized view, Snowflake applies the current schema of that table or view.

Sources1

2.The retention period: how far back you can go

You don't have to turn Time Travel on. Every account gets the standard 1-day retention period automatically. How far you can change that depends on the edition and on whether the object is permanent:

The retention period is controlled by the DATA_RETENTION_TIME_IN_DAYS parameter. A user with the ACCOUNTADMIN role sets the account default with it. The same parameter overrides that default when you create a database, schema or table, and you can change it at any time. A value set on a database or schema is inherited by the objects created inside it. Longer retention means more stored data, and that shows up in your monthly storage charges.

Two details come up often:

- Retention 0. Setting 0 on an object turns off Time Travel for that object. If you then drop it, you can't restore it. Time Travel can't be turned off for a whole account, though. Setting 0 at the account level only changes the default for new objects, and each object can still override it. - A minimum floor. ACCOUNTADMIN can also set MIN_DATA_RETENTION_TIME_IN_DAYS at the account level. It doesn't replace an object's own setting. Instead, the effective retention becomes MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS). So an object set to 0 still keeps history if the account minimum is higher.

To see an object's current retention, look at the retention_time column returned by SHOW TABLES, SHOW SCHEMAS or SHOW DATABASES:

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

SHOW DATABASES  ? 
  ->> SELECT "name", "retention_time", "dropped_on"
        FROM $1
        WHERE "retention_time" > 1;

Sources1

3.Fail-safe: Snowflake's last resort after Time Travel ends

When an object's retention period ends, its historical data moves into Fail-safe. From then on, you can't query it, clone past states of the object, or restore it if it was dropped. Fail-safe is separate from Time Travel and has a different job: it protects historical data in case of a system failure or another event, such as a security breach.

The Fail-safe period is 7 days, can't be configured, and starts as soon as Time Travel retention ends. One exception: a long-running Time Travel query delays the move into Fail-safe until the query finishes. Fail-safe isn't a longer Time Travel window. Only Snowflake uses it, to recover data lost or damaged in extreme operational failures, and it works on a best-effort basis.

Not all historical data gets to Fail-safe. With retention set to 0, a background process moves modified or deleted data into Fail-safe for permanent tables, but deletes it for transient tables. Fail-safe storage counts toward your account's storage. With the ACCOUNTADMIN role, you can see it in Snowsight under Admin » Cost management » Consumption, filtered to Storage.

Sources2

4.Zero-copy cloning: instant copies that share storage

Cloning gives you a quick snapshot of a table, schema or database: a new object that starts out sharing the source's underlying storage. Because nothing is physically copied, a clone works as an instant backup with no extra cost until someone changes the clone. After that, each object has its own life cycle, and changes to one don't affect the other. Combined with Time Travel, you can also clone an object as it was at an earlier point.

A clone does not carry everything over:

- Grants. For most objects, CREATE … CLONE doesn't copy grants on the source object. Commands that support COPY GRANTS, such as CREATE TABLE, can copy every privilege except OWNERSHIP. When you clone a database or schema, the cloned child objects inherit the privileges granted on the source children, but the cloned container itself doesn't. - History. A cloned table's Time Travel history starts when the clone is created. Unconsumed records in cloned streams can't be accessed. - Scheduled work. Tasks and alerts in a cloned database or schema start suspended. A cloned pipe starts paused, or in the STOPPED_CLONED state if AUTO_INGEST = TRUE. Automatic Clustering is suspended on a cloned table until you resume it. - Exclusions when cloning with Time Travel. External tables and internal (Snowflake) stages aren't cloned.

Object parameters set on the source carry over to the clone. Masking and row access policies stay attached to the cloned table.

ALTER TABLE <name> RESUME RECLUSTER

Sources34

Exam traps

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

1. 1.Fail-safe gives users 7 more days to query or UNDROP data once Time Travel ends.Why is that wrong?

   Users can't reach Fail-safe themselves. Only Snowflake recovers data from it, on a best-effort basis, after you open a Support case.

   Covered in Fail-safe: Snowflake's last resort after Time Travel ends

2. 2.Every table's expired history goes into Fail-safe.Why is that wrong?

   With retention at 0, the background process moves permanent-table data into Fail-safe but deletes transient-table data.

   Covered in Fail-safe: Snowflake's last resort after Time Travel ends

3. 3.Setting DATA_RETENTION_TIME_IN_DAYS = 0 on a table always turns off its Time Travel.Why is that wrong?

   If MIN_DATA_RETENTION_TIME_IN_DAYS is set at the account level, the effective retention is the larger of the two values.

   Covered in The retention period: how far back you can go

4. 4.A cloned table automatically gets the same grants as its source table.Why is that wrong?

   For most objects, CREATE … CLONE doesn't copy grants on the source object. You need COPY GRANTS where it's supported, or new GRANT statements.

   Covered in Zero-copy cloning: instant copies that share storage