Snapshots for backups and immutable storage¶

Snapshot¶

A snapshot represents a point-in-time backup of an object.

• The object can be a single table, a schema, or an entire database.

• A specific snapshot can be identified by a unique ID generated by Snowflake.

• A snapshot can’t be modified. It can, however, be deleted, and the snapshot expiration period can be modified (unless a retention lock is applied).

During day-to-day operations, you rarely interact with individual snapshots. Instead, you manage the snapshot sets that contain them. For example, you get a list of snapshots by running the SHOW SNAPSHOTS IN SNAPSHOT SET command. You create a new snapshot by running an ALTER SNAPSHOT SET command.

Snapshot set¶

A snapshot set is a schema-level object that contains a set of snapshots for a specific database, schema, or table. Snowflake has SQL commands to CREATE, ALTER, DROP, SHOW, and DESCRIBE snapshot sets.

You can have multiple snapshot sets for the same object. The number of snapshot sets for the same object is limited during the public preview. For more information, see Limitations of snapshots during the public preview period.

The life cycle of the snapshots within a set is determined by an optional snapshot policy that you can attach to the snapshot set. You can also add or delete snapshots manually in a snapshot set. Your ability to delete snapshots is affected by other factors, in particular retention lock and legal hold.

Snapshot policy¶

A snapshot policy is a schema-level object that contains the settings that define the life cycle of the snapshots within a snapshot set. These settings include schedule, expiration, and retention lock.

• The schedule determines when snapshots are created. The schedule can be defined as an interval in minutes, or as a cron expression. For example, if the schedule is set to one hour, a snapshot of the object is taken every 60 minutes.

• The expiration period is the length of time the snapshot is valid. After a snapshot expires, Snowflake deletes it automatically, unless a legal hold is applied to that particular snapshot.

  Tip

  If the snapshot set doesn’t have a retention lock and the particular snapshot doesn’t have a legal hold applied, you can delete the snapshot manually before the end of the expiration period. You can manually delete snapshots one at a time, always starting with the oldest snapshot that doesn’t have a legal hold.

Each snapshot policy must have one or both of the schedule and expiration period properties. For example, you can create a policy with a schedule and an expiration period, and let Snowflake handle all creation and removal of the snapshots in all snapshot sets where that policy is applied. Alternatively, you might create a policy with a schedule and no expiration period if you want to manage removing older snapshots yourself. Or, you can create a policy with an expiration period but without a schedule, and then manage snapshot creation yourself. You can’t create a policy with no schedule and no expiration period.

If you associate a snapshot policy with a snapshot set, you can do so when you create the snapshot set, or you can apply the policy later. Or, you can have a snapshot set that doesn’t have an associated snapshot policy. In that case, you manually control when to take new snapshots and expire old ones.

You can apply a snapshot policy to multiple snapshot sets. If you modify a snapshot policy, Snowflake applies the changes to all snapshot sets that the policy is attached to.

Retention lock¶

A retention lock protects a snapshot from deletion for the defined expiration period. You can use a snapshot with a retention lock for backups for regulatory compliance and cyber resilience. The following restrictions apply for a snapshot set with retention lock:

• Snapshots can’t be deleted by any role, including the ACCOUNTADMIN role.

• You can’t decrease the snapshot expiration period, although you can increase the expiration period.

• You can’t drop a snapshot set if there are any unexpired snapshots in the set.

• You can’t drop a schema that contains a snapshot set with any unexpired snapshots.

• You can’t drop a database that contains a snapshot set with any unexpired snapshots.

• You can’t drop an account that contains a database with a snapshot set that has any unexpired snapshots.

Legal hold¶

The legal hold feature of Snowflake snapshots prevents snapshots from being overwritten or deleted. That way, you can preserve Snowflake databases, schemas, or tables based on your own legal requirements.

Snowflake lets you place a legal hold on specific snapshots. When a Snowflake snapshot is under legal hold, the following conditions apply:

• Nobody can modify the snapshot.

• Nobody can delete the snapshot. That’s true even if the snapshot has passed its EXPIRE_AFTER_DAYS period.

• Access to the snapshot is logged and auditable.

• The legal hold can be removed by a privileged user, unlike a retention lock.

Overview of the snapshot lifecycle¶

The following diagram shows how the Snowflake objects, snapshots, snapshot sets, and snapshot policies relate to each other. The diagram involves the simplest kind of snapshot: one for a single table. Each backup operation produces a new snapshot. All the snapshots for that particular object are grouped together in a snapshot set. The automatic addition and removal of snapshots in the snapshot set is governed by the snapshot policy. To recover the information from a snapshot, you use a CREATE command to create a new object from a specific snapshot.

Limitations of snapshots during the public preview period¶

The following limitations might change when the snapshots feature becomes generally available:

• When you set up a regular schedule for snapshots, Snowflake imposes a maximum retention period based on the frequency of the scheduled snapshots. In a snapshot policy, the EXPIRE_AFTER_DAYS property is limited based on the associated SCHEDULE property, as follows:

  • Snapshot policies with a frequency of 60 to 119 minutes have a maximum EXPIRE_AFTER_DAYS value of 90.

  • Snapshot policies with a frequency of 120 minutes to 23 hours and 59 minutes have a maximum EXPIRE_AFTER_DAYS value of 180.

  • Snapshot policies with a frequency of 24 hours or more have a maximum EXPIRE_AFTER_DAYS value of 366.

  • Snapshot policies with no schedule have a maximum EXPIRE_AFTER_DAYS value of 3653.

• You can create a maximum of two database snapshot sets for a specific database. Likewise, you can create a maximum of two schema snapshot sets for a specific schema, and two table snapshot sets for a specific table. An object might still appear in more than two snapshot sets. For example, a table might have one or two associated table snapshot sets. The same table might also be included in one or two schema snapshot sets, and one or two database snapshot sets.

• After a snapshot policy is applied to a snapshot set, you can’t remove the policy from that snapshot set.

References from tables to other objects¶

Objects, such as views or functions, can refer to objects outside the schema or database in the snapshot. To ensure that such references continue functioning after you restore from a snapshot, use one of the following strategies:

• If the tables and the other objects that they refer to are all in the same schema or the same database, create a snapshot set for the entire schema or database. That way, Snowflake restores all the interconnected objects at once when you restore from the snapshot.

• If objects in a snapshot set refer to objects that aren’t included in the snapshot set, be aware that when a snapshot is restored, the references from the restored objects point to the original objects from the other database or schema. If you dropped those other objects or changed their properties after taking the snapshot, you might encounter errors when you access the restored objects.

• For account-level objects, any references from restored objects always point to the original account-level object. That’s because the account-level objects aren’t part of any snapshot. For example, a schema snapshot might contain a secret that refers to a security integration. The security integration is an account-level object and can’t be included in any snapshot.

Types of objects in database and schema snapshots¶

The following table lists the objects that are included in a database or schema snapshot:

How Snowflake associates objects with their snapshot sets¶

When you create a snapshot set for a database, schema, or table, Snowflake associates the snapshot set with the internal ID of that database, schema, or table. If you delete the original object, you can’t add any more snapshots to that snapshot set. This behavior applies even if you recreate an object with the same name, or replace it with an object that was restored from a snapshot.

If you instead rename the original object, then you can continue making more backups of it by adding more snapshots to the same snapshot set. In that case, the output of SHOW SNAPSHOT SETS changes to reflect the OBJECT_NAME value of the renamed object.

If you want to make backups of a table but you frequently drop and recreate that table, perhaps through CREATE OR REPLACE statements, include it in a snapshot set for the schema or database that contains the table. That way, you can keep using the same snapshot set regardless of changes to the table.

When you restore a table from a snapshot, the restored table starts with a different name than the original. Suppose that you want to completely replace the contents of the original table with the snapshot data, and continue to use the same snapshot set for more backups of that same table. In that case, use a TRUNCATE or DELETE statement to remove the contents of the original table, and an INSERT … SELECT statement to copy the data from the restored table. Don’t drop the original table and rename the restored table to the name of the original table.

Snapshots and encryption¶

The data within snapshot sets is protected by the same end-to-end encryption as other Snowflake objects and table data. For more information about Snowflake encryption, see Understanding end-to-end encryption in Snowflake.

Key rotation also applies to the data within snapshots.

Snapshots and data lineage¶

Snowflake doesn’t preserve data lineage metadata with database, schema, and table snapshots. After you restore an object from a snapshot, you can’t use Snowsight to view lineage information for the restored data.

Grant privileges required to create snapshot policies and sets¶

To enable the role myrole to create a snapshot policy in schema myschema, execute the following statement:

GRANT CREATE SNAPSHOT POLICY ON SCHEMA policy_schema TO ROLE myrole;

To enable the role myrole to create a snapshot set in schema myschema, execute the following statement:

GRANT CREATE SNAPSHOT SET ON SCHEMA policy_schema TO ROLE myrole;

Grant the APPLY privilege on a snapshot policy to a role¶

To enable the role myrole to apply the snapshot policy hourly_snapshot_policy to a snapshot set, execute the following statement:

GRANT APPLY ON SNAPSHOT POLICY hourly_snapshot_policy TO ROLE myrole;

Grant the APPLY SNAPSHOT RETENTION LOCK privilege to a role¶

You can grant a role the privilege to apply snapshot policies with retention lock on snapshot sets.

Only a user with the ACCOUNTADMIN role can grant this privilege.

To enable the role retention_lock_admin_role to apply a snapshot policy with retention lock on a snapshot set, execute the following statement:

GRANT APPLY SNAPSHOT RETENTION LOCK ON ACCOUNT TO ROLE retention_lock_admin_role;

Create scheduled snapshots¶

Create a snapshot set that automatically creates snapshots on a schedule.

1. Create a snapshot policy using the CREATE SNAPSHOT POLICY command. For example, the following policy creates a snapshot every hour, starting from when the snapshot set is created. Each snapshot expires after 90 days.

   CREATE SNAPSHOT POLICY hourly_snapshot_policy
     SCHEDULE = '60 MINUTE'
     EXPIRE_AFTER_DAYS = 90
     COMMENT = 'Hourly backups expire after 90 days';
   

   Copy

2. Create a snapshot set for table t1 with the snapshot policy hourly_snapshot_policy:

   CREATE SNAPSHOT SET t1_snapshots
     FOR TABLE t1
     WITH SNAPSHOT POLICY hourly_snapshot_policy;
   

   Copy

3. Create a snapshot set for schema s1 with the snapshot policy hourly_snapshot_policy:

   CREATE SNAPSHOT SET s1_snapshots
     FOR SCHEMA s1
     WITH SNAPSHOT POLICY hourly_snapshot_policy;
   

   Copy

4. Create a snapshot set for database d1 with the snapshot policy hourly_snapshot_policy:

   CREATE SNAPSHOT SET d1_snapshots
     FOR DATABASE d1
     WITH SNAPSHOT POLICY hourly_snapshot_policy;
   

   Copy

Create scheduled snapshots with retention lock¶

Create a snapshot set that automatically creates snapshots with a retention lock on a schedule. The retention lock prevents anyone, even privileged users, from deleting or modifying snapshots in any snapshot set that the policy is attached to.

Only a role that has the APPLY SNAPSHOT RETENTION LOCK privilege on the account can create a snapshot policy with a retention lock.

1. Create a policy with a retention lock that creates a daily snapshot with an expiration period of 90 days:

   CREATE SNAPSHOT POLICY daily_snapshot_policy_with_lock
     WITH RETENTION LOCK
     SCHEDULE = '1440 MINUTE'
     EXPIRE_AFTER_DAYS = 90
     COMMENT = 'regulatory backups: they have a retention lock and expire after 90 days';
   

   Copy

2. Create a snapshot set for table t2 with the snapshot policy daily_snapshot_policy_with_lock:

   CREATE SNAPSHOT SET t2_snapshots
     FOR TABLE t2
     WITH SNAPSHOT POLICY daily_snapshot_policy_with_lock;
   

   Copy

3. Create a snapshot set for schema s2 with the snapshot policy daily_snapshot_policy_with_lock:

   CREATE SNAPSHOT SET s2_snapshots
     FOR SCHEMA s2
     WITH SNAPSHOT POLICY daily_snapshot_policy_with_lock;
   

   Copy

4. Create a snapshot set for database d2 with the snapshot policy daily_snapshot_policy_with_lock:

   CREATE SNAPSHOT SET d2_snapshots
     FOR DATABASE d2
     WITH SNAPSHOT POLICY daily_snapshot_policy_with_lock;
   

   Copy

Create snapshots manually¶

You can manually add a snapshot to a snapshot set at any time. Doing so makes a snapshot of the database, schema, or table that’s associated with the snapshot set. You can create snapshots manually whether or not the snapshot set also has snapshots that are scheduled by a snapshot policy. If there’s a snapshot policy associated with the snapshot set, and the policy defines an expiration period, that expiration period also applies to the manual snapshot.

The following example creates a table snapshot set t1_snapshots and then adds the first snapshot to it:

CREATE SNAPSHOT SET t1_snapshots FOR TABLE t1;
ALTER SNAPSHOT SET t1_snapshots ADD SNAPSHOT;

The following example creates a snapshot policy with hourly backups, a table snapshot set t2_snapshots that uses the policy, and then adds a manual snapshot to the snapshot set:

CREATE SNAPSHOT POLICY hourly_snapshot_policy
  SCHEDULE = '60 MINUTE'
  EXPIRE_AFTER_DAYS = 7;

CREATE SNAPSHOT SET t2_snapshots FOR TABLE t2 WITH SNAPSHOT POLICY hourly_snapshot_policy;
-- Wait several hours. Then the snapshot set already contains several scheduled snapshots.
-- You can manually add a snapshot at any time, in addition to the scheduled snapshots.
ALTER SNAPSHOT SET t2_snapshots ADD SNAPSHOT;

You can run similar commands to add a snapshot to a schema or database snapshot set. Substitute the name of the schema or database snapshot set in the ALTER SNAPSHOT SET command.

Suspend a snapshot policy on a snapshot set¶

When you suspend a snapshot policy on a snapshot set, you prevent the snapshot policy from being used to create new scheduled snapshots in that snapshot set. You also suspend the expiration of existing snapshots in that snapshot set that use the snapshot policy. Other snapshot sets that use the same policy aren’t affected.

The following example suspends a snapshot policy on the snapshot set t2_snapshots:

ALTER SNAPSHOT SET t2_snapshots SUSPEND SNAPSHOT POLICY;

Copy

For more information about the ALTER SNAPSHOT SET command, see ALTER SNAPSHOT SET.

Resume a snapshot policy on a snapshot set¶

You can resume suspended snapshot policies. Doing so resumes the creation and expiration of snapshots according to the snapshot policy. If any snapshots reached their expiration time while the policy was suspended, Snowflake deletes those snapshots as soon as the policy is resumed.

The following example resumes a snapshot policy on the snapshot set t1_snapshot:

ALTER SNAPSHOT SET t1_snapshots
  RESUME SNAPSHOT POLICY;

Copy

For more information about the ALTER SNAPSHOT SET command, see ALTER SNAPSHOT SET.

Restore a snapshot¶

You can restore an object from a snapshot set by using the ID of the specific snapshot. For example, to restore table t1 from snapshot set t1_snapshots in the current schema, execute the following statements:

1. Find the ID of the table snapshot to restore in the snapshot_id column:

   SHOW SNAPSHOTS IN SNAPSHOT SET t1_snapshots ->> SELECT "created_on", "snapshot_id", "expire_on" FROM $1;
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | snapshot_id                          | expire_on                     |
   |-------------------------------+------------------------------------------+---------------------------|
   | 2024-08-19 17:12:28.991 -0700 | 983e0b66-91eb-41cb-8a0b-037abfec1914 | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | b5624ef0-1f35-452f-b132-09d8f0592e52 | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | eca1a94a-fd40-46db-a2bc-4afba6a38c0a | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | 8ee2fd7e-1afe-42e1-acd7-79582765a910 | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | d38caf14-f8a5-4ba8-a248-8287e0cdcf40 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-----------+-------------------+
   

2. Find the ID of the schema snapshot to restore in the snapshot_id column:

   SHOW SNAPSHOTS IN SNAPSHOT SET s1_snapshots;
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | snapshot_id                          | expire_on                     |
   |-------------------------------+--------------------------------------+-------------------------------|
   | 2024-08-19 17:12:28.991 -0700 | 0a0382e1-d265-46e9-b152-4c3b2b859e65 | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | 8dbcf919-3393-4590-928f-5481d7f2502f | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | 8ee2fd7e-1afe-42e1-acd7-79582765a910 | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | bd729a79-01bc-444d-a550-adaaa31ab62f | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | 9a8802c5-5fbd-4200-a09d-43e046103939 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-------------------------------+
   

3. Find the ID of the database snapshot to restore in the snapshot_id column:

   SHOW SNAPSHOTS IN SNAPSHOT SET d1_snapshots;
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | snapshot_id                          | expire_on                     |
   |-------------------------------+--------------------------------------+-------------------------------|
   | 2024-08-19 17:12:28.991 -0700 | 42435925-4e77-4b01-ba89-8163ac03e12f | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | 29c2c1b9-6599-4f0b-87b8-d43377fd7c77 | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | a4283984-a063-4415-acc4-0e3c19259fad | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | ffe25397-64b9-4c5f-b061-23a1885dc2dc | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | 28e12b8a-aab8-40a8-ae39-9a5a5f654d66 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-------------------------------+
   

4. Restore the snapshot for table t1 taken on 2024-08-19 18:12:33:

   CREATE TABLE restored_t1 FROM SNAPSHOT SET t1_snapshots IDENTIFIER 'b5624ef0-1f35-452f-b132-09d8f0592e52';
   

   Copy

5. Restore the snapshot for schema s1 taken on 2024-08-19 18:12:33:

   CREATE SCHEMA restored_s1 FROM SNAPSHOT SET s1_snapshots IDENTIFIER '8dbcf919-3393-4590-928f-5481d7f2502f';
   

   Copy

6. Restore the snapshot for database d1 taken on 2024-08-19 18:12:33:

   CREATE DATABASE restored_d1 FROM SNAPSHOT SET d1_snapshots IDENTIFIER '29c2c1b9-6599-4f0b-87b8-d43377fd7c77';
   

   Copy

Delete a snapshot from a snapshot set¶

For any snapshot set, you can only delete the oldest snapshot that doesn’t have a legal hold. You do so by specifying the snapshot ID. You can find the snapshots that don’t have a legal hold by examining the is_under_legal_hold property. You can find the oldest snapshot by examining the created_on property.

Note

You can’t delete any snapshot from a snapshot set if a snapshot policy with retention lock is attached to that snapshot set, or if that particular snapshot has a legal hold applied.

The snapshot that you delete from the snapshot set must be the earliest snapshot in the set.

1. Find the ID of the table snapshot to delete in the snapshot_id column in the following output. Sorting in ascending order by the created_on column puts the oldest snapshot first. You could add LIMIT 1 to the SELECT command to return only the row with the details of the oldest snapshot.

   SHOW SNAPSHOTS IN SNAPSHOT SET t1_snapshots ->>
     SELECT "created_on", "snapshot_id", "expire_on" FROM $1
       WHERE "is_under_legal_hold" = 'N'
       ORDER BY "created_on";
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | snapshot_id                          | expire_on                     |
   |-------------------------------+--------------------------------------+-------------------------------|
   | 2024-08-19 17:12:28.991 -0700 | 983e0b66-91eb-41cb-8a0b-037abfec1914 | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | b5624ef0-1f35-452f-b132-09d8f0592e52 | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | eca1a94a-fd40-46db-a2bc-4afba6a38c0a | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | 8ee2fd7e-1afe-42e1-acd7-79582765a910 | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | d38caf14-f8a5-4ba8-a248-8287e0cdcf40 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-------------------------------+
   

2. Delete the t1_snapshots snapshot created on 2024-08-19 17:12:28 using the snapshot_id:

   ALTER SNAPSHOT SET t1_snapshots DELETE SNAPSHOT IDENTIFIER '983e0b66-91eb-41cb-8a0b-037abfec1914';
   

   Copy

3. Find the ID of the schema snapshot to delete in the snapshot_id column in the following output:

   SHOW SNAPSHOTS IN SNAPSHOT SET s1_snapshots ->>
     SELECT "created_on", "snapshot_id", "expire_on" FROM $1 ORDER BY "created_on";
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | snapshot_id                          | expire_on                     |
   |-------------------------------+--------------------------------------+-------------------------------|
   | 2024-08-19 17:12:28.991 -0700 | 28e12b8a-aab8-40a8-ae39-9a5a5f654d66 | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | 46a1e22a-8557-432f-a14c-1261a4ca2b34 | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | 3e42fef6-b895-4055-a59f-179744d015d3 | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | 7807d24e-285e-4741-b332-87c32bad5cb6 | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | e022e619-ee83-45a0-b2b7-9007e284bdb3 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-------------------------------+
   

4. Delete the s1_snapshots snapshot created on 2024-08-19 17:12:28 using the snapshot_id:

   ALTER SNAPSHOT SET s1_snapshots DELETE SNAPSHOT IDENTIFIER '28e12b8a-aab8-40a8-ae39-9a5a5f654d66';
   

   Copy

5. Find the ID of the database snapshot to delete in the snapshot_id column in the following output:

   SHOW SNAPSHOTS IN SNAPSHOT SET d1_snapshots ->>
     SELECT "created_on", "snapshot_id", "expire_on" FROM $1 ORDER BY "created_on";
   

   Copy

   +-------------------------------+--------------------------------------+-------------------------------+
   | created_on                    | snapshot_id                          | expire_on                     |
   |-------------------------------+--------------------------------------+-------------------------------|
   | 2024-08-19 17:12:28.991 -0700 | d3a77432-c98d-4969-91a9-fffae5dd655c | 2024-08-20 17:12:28.991 -0700 |
   | 2024-08-19 18:12:33.824 -0700 | 0a0382e1-d265-46e9-b152-4c3b2b859e65 | 2024-08-20 18:12:33.824 -0700 |
   | 2024-08-19 19:12:43.830 -0700 | 25e01ee0-ea9d-4bb7-af7f-f3fe87f9409e | 2024-08-20 19:12:43.830 -0700 |
   | 2024-08-19 20:12:45.446 -0700 | a12294f5-fc63-49cf-84f1-c7b72f7664af | 2024-08-20 20:12:45.446 -0700 |
   | 2024-08-19 21:12:55.305 -0700 | 28e12b8a-aab8-40a8-ae39-9a5a5f654d66 | 2024-08-20 21:12:55.305 -0700 |
   +-------------------------------+--------------------------------------+-------------------------------+
   

6. Delete the d1_snapshots snapshot created on 2024-08-19 17:12:28 using the snapshot_id:

   ALTER SNAPSHOT SET d1_snapshots DELETE SNAPSHOT IDENTIFIER 'd3a77432-c98d-4969-91a9-fffae5dd655c';
   

   Copy

7. Attempt to delete a more recent d1_snapshots snapshot created on 2024-08-19 21:12:55. Notice how Snowflake prevents you from deleting a snapshot other than the oldest one in the snapshot set.

   ALTER SNAPSHOT SET d1_snapshots DELETE SNAPSHOT IDENTIFIER '28e12b8a-aab8-40a8-ae39-9a5a5f654d66';
   

   Copy

   Snapshot '28e12b8a-aab8-40a8-ae39-9a5a5f654d66' cannot be deleted as it is not the oldest active snapshot in the snapshot set D1_SNAPSHOTS.
   

Delete a snapshot set¶

You can delete a snapshot set using the DROP SNAPSHOT SET command.

Delete the t1_snapshots snapshot set:

DROP SNAPSHOT SET t1_snapshots;

Delete the s1_snapshots snapshot set:

DROP SNAPSHOT SET s1_snapshots;

Delete the d1_snapshots snapshot set:

DROP SNAPSHOT SET d1_snapshots;

Find all the snapshot sets that contain backups of a specific table¶

The following example shows how to find all the snapshot sets that contain a specific table inside a specific schema and database. The SHOW TABLES command uses a pipe operator to retrieve the names of the database, schema, and table and store them in variables. The SHOW SNAPSHOT SETS output is filtered to show the snapshot sets that back up the database containing the table, or the schema containing the table, or that contain that single table.

The filtered output from SHOW SNAPSHOT SETS shows that there are two database snapshot sets for the database MY_BIG_IMPORTANT_DATABASE, one schema snapshot set for the schema MY_BIG_IMPORTANT_DATABASE.PUBLIC, and one table snapshot set for the table MY_BIG_IMPORTANT_DATABASE.PUBLIC.MY_SMALL_SECONDARY_TABLE.

SHOW TABLES IN SCHEMA public ->>
  SET (dname, sname, tname) =
    (SELECT "database_name", "schema_name", "name" FROM $1
      WHERE "name" = 'MY_SMALL_SECONDARY_TABLE' AND "kind" = 'TABLE');

SHOW SNAPSHOT SETS ->> SELECT "object_kind", "name", "database_name", "schema_name", "object_name" FROM $1
  WHERE ("object_kind" = 'TABLE' AND "database_name" = $dname AND "schema_name" = $sname AND "object_name" = $tname)
    OR ("object_kind" = 'SCHEMA' AND "database_name" = $dname AND "object_name" = $sname)
    OR ("object_kind" = 'DATABASE' AND "object_name" = $dname);

+-------------+------------------+---------------------------+-------------+---------------------------+
| object_kind | name             | database_name             | schema_name | object_name               |
|-------------+------------------+---------------------------+-------------+---------------------------|
| DATABASE    | DATABASE_BACKUP  | MY_BIG_IMPORTANT_DATABASE | PUBLIC      | MY_BIG_IMPORTANT_DATABASE |
| DATABASE    | DATABASE_BACKUP2 | MY_BIG_IMPORTANT_DATABASE | PUBLIC      | MY_BIG_IMPORTANT_DATABASE |
| SCHEMA      | SCHEMA_BACKUP3   | MY_BIG_IMPORTANT_DATABASE | PUBLIC      | PUBLIC                    |
| TABLE       | TABLE_BACKUP2    | MY_BIG_IMPORTANT_DATABASE | PUBLIC      | MY_SMALL_SECONDARY_TABLE  |
+-------------+------------------+---------------------------+-------------+---------------------------+

Create a snapshot for a table with dependencies¶

The following examples show how you might create a table snapshot for a table that refers to a sequence and a foreign key in a different schema. To prepare, we create the schema other_schema containing a sequence and a table. Then we create the main table in the public schema, referring to the sequence and the other table.

USE DATABASE my_big_important_database;

CREATE SCHEMA other_schema;
USE SCHEMA other_schema;

CREATE SEQUENCE my_sequence;
CREATE TABLE my_dimension_table (id INT AUTOINCREMENT PRIMARY KEY);

USE SCHEMA public;
CREATE TABLE dependent_table
(
   id INT DEFAULT my_big_important_database.other_schema.my_sequence.NEXTVAL PRIMARY KEY,
   foreign_id INT,
   FOREIGN KEY (foreign_id) REFERENCES my_big_important_database.other_schema.my_dimension_table(id)
 );

SELECT GET_DDL('TABLE','dependent_table');

The GET_DDL() output shows the references that point to the other schema:

+-------------------------------------------+
| GET_DDL('TABLE','DEPENDENT_TABLE')        |
|-------------------------------------------|
| create or replace TABLE DEPENDENT_TABLE ( |
|     ID NUMBER(38,0) NOT NULL DEFAULT MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.MY_SEQUENCE.NEXTVAL,
|     FOREIGN_ID NUMBER(38,0),                |
|     primary key (ID),                       |
|     foreign key (FOREIGN_ID) references MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.MY_DIMENSION_TABLE(ID)
| );                                        |
+-------------------------------------------+

Next, we create the snapshot set for the table and add a snapshot to it:

CREATE SNAPSHOT SET dependency_experiments FOR TABLE dependent_table;
ALTER SNAPSHOT SET dependency_experiments ADD SNAPSHOT;
SHOW SNAPSHOTS IN SNAPSHOT SET dependency_experiments;

The SHOW SNAPSHOTS output contains the snapshot_id value to use for the restore operation:

+-------------------------------+--------------------------------------+------------------------+---------------------------+--------------+-----------+
| created_on                    | snapshot_id                          | snapshot_set_name      | database_name             | schema_name  | expire_on |
|-------------------------------+--------------------------------------+------------------------+---------------------------+--------------+-----------|
| 2025-07-01 11:53:27.860 -0700 | 0fd44138-b571-449b-be0a-72779501f80e | DEPENDENCY_EXPERIMENTS | MY_BIG_IMPORTANT_DATABASE | OTHER_SCHEMA | NULL      |
+-------------------------------+--------------------------------------+------------------------+---------------------------+--------------+-----------+

We restore that table under a new name, and confirm that the restored table refers to the objects in the other schema:

CREATE TABLE restored_dependent_table FROM SNAPSHOT SET dependency_experiments
  IDENTIFIER '0fd44138-b571-449b-be0a-72779501f80e';

SELECT GET_DDL('TABLE','restored_dependent_table');

+----------------------------------------------------+
| GET_DDL('TABLE','RESTORED_DEPENDENT_TABLE')        |
|----------------------------------------------------|
| create or replace TABLE RESTORED_DEPENDENT_TABLE ( |
|     ID NUMBER(38,0) NOT NULL DEFAULT MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.MY_SEQUENCE.NEXTVAL,
|     FOREIGN_ID NUMBER(38,0),                         |
|     foreign key (FOREIGN_ID) references MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.MY_DIMENSION_TABLE(ID),
|     primary key (ID)                                 |
| );                                                 |
+----------------------------------------------------+

To illustrate what happens if the referred-to object no longer exists, we drop the sequence and then restore the table again from the same snapshot:

DROP SEQUENCE my_big_important_database.other_schema.my_sequence;
CREATE TABLE OR REPLACE restored_dependent_table FROM SNAPSHOT SET dependency_experiments
  IDENTIFIER '0fd44138-b571-449b-be0a-72779501f80e';

SELECT * FROM restored_dependent_table;

Querying the table still works:

+----+------------+
| ID | FOREIGN_ID |
|----+------------|
+----+------------+
0 Row(s) produced. Time Elapsed: 0.129s

However, operations such as GET_DDL(), DESCRIBE, and INSERT all fail because they depend on a sequence that no longer exists:

SELECT GET_DDL('TABLE','restored_dependent_table');

002073 (02000): SQL compilation error:
Sequence used as a default value in table 'MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.RESTORED_DEPENDENT_TABLE'
  column 'ID' was not found or could not be accessed.

DESC TABLE restored_dependent_table;

+------------+--------------+--------+-------+----------------------------------------+-------------+------------+-------+------------+---------+-------------+----------------+
| name       | type         | kind   | null? | default                                | primary key | unique key | check | expression | comment | policy name | privacy domain |
|------------+--------------+--------+-------+----------------------------------------+-------------+------------+-------+------------+---------+-------------+----------------|
| ID         | NUMBER(38,0) | COLUMN | N     | [sequence cannot be found or accessed] | Y           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| FOREIGN_ID | NUMBER(38,0) | COLUMN | Y     | NULL                                   | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+------------+--------------+--------+-------+----------------------------------------+-------------+------------+-------+------------+---------+-------------+----------------+

INSERT INTO restored_dependent_table (foreign_id) VALUES (2);

002073 (02000): SQL compilation error:
Sequence used as a default value in table 'MY_BIG_IMPORTANT_DATABASE.OTHER_SCHEMA.RESTORED_DEPENDENT_TABLE'
  column 'ID' was not found or could not be accessed.

Create a snapshot for a dynamic table¶

A dynamic table always involves a reference to some other table. For that reason, you might prefer to use schema snapshots or database snapshots for dynamic tables, so that the original table and the dynamic table can be included in the same snapshot.

If you make a table snapshot for a dynamic table, you include the keyword DYNAMIC in the CREATE SNAPSHOT SET command, and in the CREATE TABLE when you restore from a snapshot. The following example sets up the dynamic table, a table snapshot set for that table, and creates the first snapshot:

CREATE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = '1 minute'
  WAREHOUSE = my_wh
  AS SELECT * FROM my_base_table WHERE col1 IS NOT NULL;

CREATE SNAPSHOT SET dynamic_table_snapshots
  FOR DYNAMIC TABLE my_dynamic_table;

ALTER SNAPSHOT SET dynamic_table_snapshots ADD SNAPSHOT;

The following example shows how to determine the snapshot IDs for snapshots created at various times. In this case, the newest snapshot is the first row in the result set. Then you use the ID of the snapshot in the CREATE DYNAMIC TABLE command.

SHOW SNAPSHOTS IN SNAPSHOT SET dynamic_table_snapshots
  ->> SELECT "created_on", "snapshot_id" FROM $1
        ORDER BY "created_on" DESC;

CREATE DYNAMIC TABLE restored_dynamic_table
  FROM SNAPSHOT SET dynamic_table_snapshots
    IDENTIFIER '<snapshot_id_from_SHOW_SNAPSHOTS_output>';

Add and remove legal holds¶

Before you work with legal holds for Snowflake snapshots, learn their purpose and requirements. For more information, see Legal hold.

Suppose that your organization’s legal or compliance team sends a litigation hold request, specifying what types of data need to be preserved. In that case, you might follow a process as follows:

• You work with the legal team to identify where the relevant data is stored, and which snapshot sets contain the associated objects.

• You put a legal hold on a snapshot from the applicable timeframe within a snapshot set. Doing so disables any automatic expiration for that snapshot. You can put a legal hold on a snapshot that Snowflake created automatically based on a schedule, or that you created manually. The legal hold applies whether or not the snapshot set has an associated snapshot policy, or an expiration period, or a retention lock.

• You perform refresh operations for any secondary accounts where the database containing the snapshot set is replicated. That way, the legal hold and associated snapshot are preserved across any failover and failback operations.

• You use the Snowflake access controls and logs to audit access to the data that’s under the legal hold.

• Once the legal case concludes and the legal team approves removing the legal hold, a user with the APPLY LEGAL HOLD privilege releases the legal hold. Then the normal automation for expiry resumes.

This example shows the sequence of SQL commands you might use during the lifecycle of a legal hold for a snapshot within a particular snapshot set. You find the identifier of the relevant snapshot by using the SHOW SNAPSHOTS IN SNAPSHOT SET command, and checking the "is_under_legal_hold" column to see if a legal hold is already in place. Then you add or remove the legal hold from the specific snapshot.

USE ROLE <role_name>; -- use a role that has the APPLY LEGAL HOLD privilege
SHOW SNAPSHOTS IN SNAPSHOT SET <snapshot_set_name>
  ->> SELECT * FROM $1 WHERE "is_under_legal_hold" = 'N';
ALTER SNAPSHOT SET <snapshot_set_name>
  MODIFY SNAPSHOT IDENTIFIER '<snapshot_identifier>'
  ADD LEGAL HOLD;

USE ROLE <role_name>; -- use a role that has the APPLY LEGAL HOLD privilege
SHOW SNAPSHOTS IN SNAPSHOT SET <snapshot_set_name>
  ->> SELECT * FROM $1 WHERE "is_under_legal_hold" = 'Y';
ALTER SNAPSHOT SET <snapshot_set_name>
  MODIFY SNAPSHOT IDENTIFIER '<snapshot_identifier>'
  REMOVE LEGAL HOLD;

Replicate snapshot-related objects¶

When you use replication in combination with database, schema, and table snapshots, you specify the databases that contain the snapshot sets and snapshot policies in your replication groups and failover groups. You can control how your snapshot-related objects are replicated by organizing your replication groups and failover groups, and choosing which databases and schemas contain your snapshot sets and snapshot policies. For more information, see Snapshot replication for database, schema, and table snapshots.

The snapshot sets and snapshot policies are database objects. Snowflake replicates the snapshot sets and snapshot policies along with the databases and schemas that contain them.

Snowflake minimizes the time and storage usage for snapshots by using a mechanism similar to cloning, so that each snapshot doesn’t require a complete new copy of all the table data. If the snapshot set is part of a different failover group than the database, schema, or table that the snapshot set applies to, there’s a one-time full transfer of the data for the first refresh of that replication group or failover group.

When a replication group or failover group includes a snapshot set, the increase in refresh latency is proportional to the number of snapshots created since the last refresh.

If you define an expiry period for older snapshots, the automatic deletion happens on the primary account. Those expired snapshots are removed from the secondary account when you perform a refresh operation.

Therefore, you can fine-tune the replication for snapshot-related objects by following these practices:

• To minimize the chance of refresh failures, put the snapshot set and the optional snapshot policy within the same database and schema. If that’s not practical, put those things in the same replication group or failover group. That way, all these related objects are replicated at the same time.

• To maximize flexibility about which snapshot-related objects are replicated and the replication schedule, put the snapshot set and optional snapshot policy in a different database than the associated database, schema, or table. That way, you can specify whether the snapshot-related objects are replicated, and how often.

• If you apply a policy with an expiry period for older snapshots, make the expiry period longer than the interval between replication refresh operations. That way, every new snapshot is replicated to the secondary account at least once before it expires.

• Immediately after adding a legal hold, perform refresh operations on all secondary accounts where the database that contains the snapshot set is replicated. That way, the legal hold and associated snapshot are preserved across any failover and failback operations.

• Suppose that you put a snapshot policy and an associated snapshot set into databases that are part of different replication groups or failover groups. In that case, make sure to do the initial refresh of the group containing the snapshot policy first. Otherwise, the refresh operation fails because you can’t create a snapshot set that’s missing its associated snapshot policy in the secondary account.

  Note

  Putting the snapshot set into a different replication group or failover group than the object of the snapshot set does require a full transfer of all the data, during the first refresh of the group that contains the snapshot set.

  If you restore a schema snapshot or database snapshot on a secondary account, references to objects within the restored schema or database might not resolve properly unless the referenced objects are part of the same failover group as the snapshot.

Monitor snapshots and snapshot operations¶

You can determine which snapshot-related objects exist, their properties, and how much storage they use by querying the following views.

Information schema:

• SNAPSHOT_POLICIES view

• SNAPSHOT_SETS view

• SNAPSHOTS view

Account usage:

• SNAPSHOT_OPERATION_HISTORY view

• SNAPSHOT_POLICIES view

• SNAPSHOT_SETS view

• SNAPSHOT_STORAGE_USAGE view

• SNAPSHOTS view

Snapshot policy¶

• CREATE SNAPSHOT POLICY

• ALTER SNAPSHOT POLICY

• DROP SNAPSHOT POLICY

• SHOW SNAPSHOT POLICIES

Snapshot set¶

• CREATE SNAPSHOT SET

• ALTER SNAPSHOT SET

• DROP SNAPSHOT SET

• SHOW SNAPSHOT SETS

Snapshots¶

You don’t run an actual CREATE SNAPSHOT command. To create a new snapshot, you run ALTER SNAPSHOT SET … ADD SNAPSHOT. Or when you associate the snapshot set with a snapshot policy that has a schedule, Snowflake automatically creates snapshots in the snapshot set based on the specified schedule. To delete an older snapshot, you run ALTER SNAPSHOT SET … DELETE SNAPSHOT. Such operations require you to specify the identifier for a specific snapshot. You can find the snapshot identifiers, along with other information such as when each snapshot was created, by using the following command.

• SHOW SNAPSHOTS IN SNAPSHOT SET

Restoring objects from snapshots¶

You use the syntax CREATE object_kind FROM SNAPSHOT SET to restore each kind of object from the appropriate kind of snapshot set.

Further snapshots in the snapshot set use the original object, not the restored one. That’s true even if you rename the restored object to the same name as the original object. If you want to continue using the same snapshot set after doing a restore, you restore the object under a new name and then transfer data back to the original object.

• CREATE DATABASE FROM SNAPSHOT SET

• CREATE SCHEMA FROM SNAPSHOT SET

• CREATE TABLE FROM SNAPSHOT SET

Views¶

The following system views contain metadata related to snapshots, snapshot sets, and snapshot policies.