[New Feature] "Snapshot," which allows you to capture database backups that cannot be deleted or edited even by ACCOUNTADMIN, is now available in public preview

2025.08.19
I'm Sagara.
Snowflake's new feature, Snapshots, has become available in public preview.
https://docs.snowflake.com/en/release-notes/2025/other/2025-08-18-worm-snapshots
I've tried this feature, so I'll summarize what it's about.
 What is SnapshotSnapshots is a feature that allows you to take backups of databases, schemas, and tables at a specific point in time, with settings that make them undeletable even by ACCOUNTADMIN.
When taking a Snapshot, it doesn't create physical copies of all data each time but performs zero-copy backups, similar to clones. Therefore, if there are no changes to the target data, storage costs won't increase even if you take Snapshots daily.
The key point of "settings that make them undeletable even by ACCOUNTADMIN" is that by creating Snapshots using a snapshot policy with Retention lock, you can create Snapshots that cannot be deleted by ACCOUNTADMIN or even Snowflake support. You can also set up Legal hold for a specific period, making it impossible for anyone to delete the Snapshot while the Legal hold is active. (Note that Retention lock and Legal hold are available only in Business Critical editions or higher.)
Conversely, if you casually create Snapshots of terabyte-scale tables with frequent changes using a snapshot policy with Retention lock and something like EXPIRE_AFTER_DAYS=365, your storage costs could become enormous. I strongly recommend testing with EXPIRE_AFTER_DAYS=1 during verification.
 Snapshot LimitationsFrom the restrictions listed in the official docs, here are some key limitations to be aware of:
You cannot change the RETENTION LOCK setting of a snapshot policy.
If a snapshot policy has RETENTION LOCK, you can extend the expiration period but cannot shorten it.
The minimum scheduling interval for scheduled Snapshots is "1 hour (60 minutes)".
Additionally, the following limitations during the public preview period were mentioned. Please be aware of these as well:
The maximum retention period for Snapshots varies depending on the Snapshot schedule:
Schedule every 60-119 minutes: Maximum 90 days
Schedule every 120 minutes to 23 hours 59 minutes: Maximum 180 days
Schedule every 24 hours or more: Maximum 366 days
No schedule: Maximum 3653 days (approximately 10 years)

You can create a maximum of 2 snapshot sets for each specific object (database, schema, table).
However, a single table can be included in both the snapshot set of its schema and the snapshot set of its database.

Once a snapshot policy is applied to a snapshot set, it cannot be removed later.### About Objects That Can Be Backed Up Using Snapshots
According to the official documentation, as of August 19, 2025, the list of objects that can and cannot be backed up using Snapshots is as follows.
Since there are many unsupported objects, it's more accurate to understand that "this feature is primarily centered around table snapshots."
 Objects Supported by SnapshotsTables
Permanent tables
Transient tables
Dynamic tables

Table constraints
Sequences
Views
Views
Secure views

File formats
Stored procedures (SQL, Javascript, Python, Java, Scala)
User-defined functions (UDFs / UDTFs) (SQL, Javascript, Python, Java, Scala)
Tasks (will be in a suspended state after restoration)
Policies (only Column-level security, Row access policies, Tag-based masking policies)
Grants
Object tagging
Alerts
Network rules
 Objects Not Supported by SnapshotsTables
Temporary tables
External tables
Hybrid tables
Apache Iceberg™ tables
Event tables

Materialized views
Stages (Internal stages, External stages, Temporary stages)
Streams
Pipes
Database roles
Github repos
Policies (Aggregation policy, Projection policy, and others not listed above)
Others (Many relatively new features like Models, Streamlits, Cortex search services, etc.)
 About Snapshot CostsAlso from the official documentation, while Snapshots are a public preview feature, costs are incurred when you set them up.
Compute costs: Snowflake-managed compute is used when creating and restoring Snapshots
Storage costs: Storage costs are incurred for keeping the Snapshot data
The actual storage costs can be checked using the TABLE_STORAGE_METRICS view or the SNAPSHOT_STORAGE_USAGE view.
 About Other SpecificationsFor more detailed specifications, please refer to the following documentation. There is also mention of legal holds and other features.
https://docs.snowflake.com/en/user-guide/snapshots## I tried it out
I'm going to set up a Snapshot for a database I created for testing.
The test environment is in the AWS Tokyo region, using a Business Critical edition trial account.
 Preliminary step: Creating a database for SnapshotI executed the queries from the 3.Load data section of the following blog to create the database for taking snapshots.
https://dev.classmethod.jp/articles/dbt-quickstart-for-dbt-and-snowflake-with-dbt-projects-on-snowflake/#3.load-data
 Creating necessary databases and schemasI'll create a database and schemas for managing Snapshots.

					
			-- Using the ACCOUNTADMIN role
USE ROLE ACCOUNTADMIN;

-- Creating a management database
CREATE DATABASE IF NOT EXISTS admin;

-- Creating a dedicated schema for storing policies
CREATE SCHEMA IF NOT EXISTS admin.snapshot_policies;

-- Creating a dedicated schema for storing snapshot sets
CREATE SCHEMA IF NOT EXISTS admin.snapshots;

		
		
 Creating a Snapshot policy with Retention lockI'll create a Snapshot policy with Retention lock so that nobody can delete it.

					
			-- Setting the working location to the policy schema
USE SCHEMA admin.snapshot_policies;

-- Creating a Snapshot policy with Retention lock
CREATE SNAPSHOT POLICY raw_db_retention_policy
  WITH RETENTION LOCK
  SCHEDULE = 'USING CRON 0 20 * * * UTC' -- Executes daily at 20:00 (UTC)
  EXPIRE_AFTER_DAYS = 1;                -- Snapshots become deletable 1 day after creation

		
		
*Note: As shown in the image below, it's properly rejected when executed on Enterprise edition lol
 Creating a Snapshot setI'll create a Snapshot set that will serve as the base for Snapshots. When creating it, I'll specify the Retention lock-enabled Snapshot policy I created earlier.

					
			-- Setting the working location to the Snapshot set schema
USE SCHEMA admin.snapshots;

-- Creating a Snapshot set
CREATE SNAPSHOT SET raw_db_snapshots
  FOR DATABASE RAW
  WITH SNAPSHOT POLICY admin.snapshot_policies.raw_db_retention_policy;

		
		
 Manually creating a SnapshotSince this is for testing purposes, I'll create a Snapshot manually.

					
			-- Manually adding a Snapshot
ALTER SNAPSHOT SET admin.snapshots.raw_db_snapshots ADD SNAPSHOT;
```### Verify if Retention Lock is working

Let's verify if the Retention Lock is working on the created Snapshot. Specifically, we'll confirm that even an ACCOUNTADMIN cannot delete it.

First, let's check the ID of the created Snapshot. As shown in the image below, the ID is displayed, which we'll make note of. In this case, the value is `710964d1-b9ed-4c87-a618-fe3f21dda0b3`.

```sql
-- Display the list of created Snapshots and their IDs
SHOW SNAPSHOTS IN SNAPSHOT SET admin.snapshots.raw_db_snapshots;

		
		
Using this value, let's try to execute a command to delete the Snapshot. Thanks to the Retention Lock being in effect, the deletion was prevented!

					
			-- Attempt to delete with ACCOUNTADMIN role
ALTER SNAPSHOT SET admin.snapshots.raw_db_snapshots
  DELETE SNAPSHOT IDENTIFIER '710964d1-b9ed-4c87-a618-fe3f21dda0b3';

		
		
### Attempting to Restore from a Snapshot
Snapshots can also be used to restore data when there are abnormalities, so let's try it out.
First, let's delete the JAFFLE_SHOP schema in the RAW database where we took the snapshot.

					
			-- Delete the JAFFLE_SHOP schema
DROP SCHEMA RAW.JAFFLE_SHOP;

-- Confirm that the schema has been deleted
SHOW SCHEMAS IN DATABASE RAW;

		
		
Next, using the Snapshot ID we confirmed earlier, we'll execute the following command to restore the RAW database from the snapshot. We can see that the deleted JAFFLE_SHOP schema has been properly restored.

					
			-- Use the snapshot ID to restore the entire database with a new name
CREATE DATABASE RAW_RESTORED
  FROM SNAPSHOT SET admin.snapshots.raw_db_snapshots
  IDENTIFIER '710964d1-b9ed-4c87-a618-fe3f21dda0b3';

-- Confirm the deleted schema exists
SHOW SCHEMAS IN DATABASE RAW_RESTORED;

		
		
To complete the restoration, we'll swap the RAW database (where we deleted the schema) with the restored RAW_RESTORED. (After swapping, it's fine to delete RAW_RESTORED if needed.)

					
			-- Swap the current RAW database with the restored RAW_RESTORED database
ALTER DATABASE RAW SWAP WITH RAW_RESTORED;

-- Confirm that the deleted schema exists
SHOW SCHEMAS IN DATABASE RAW;

		
		
For reference, the snapshot continues to exist even after restoration.
 Bonus: Can a schema be restored from a database snapshot?Since database snapshots include schemas, I tried to see if we could restore just a schema from a database snapshot, but as shown in the image below, it resulted in an error.

					
			CREATE SCHEMA RAW.JAFFLE_SHOP_RESTORED
  FROM SNAPSHOT SET admin.snapshots.raw_db_snapshots
  IDENTIFIER '710964d1-b9ed-4c87-a618-fe3f21dda0b3';

		
		
 ConclusionI've tested the new Snapshot feature that has entered public preview in Snowflake.
While Retention lock requires Business Critical or higher editions, being able to keep data backups in a tamper-proof form is a valuable capability that wasn't previously available in Snowflake! Since it uses a zero-copy approach like cloning, storage costs remain low when there are few data updates, which is also great!
Please give it a try!