Add SnowDDL Support for Snapshot Sets and Snapshot Policies

[jfiore-im]

Is your feature request related to a problem? Please describe.
Snowflake has a new feature coming out for snapshotting tables, schemas, databases.
It is currently in Private Preview, and should be going to Public Preview soon.
https://docs.snowflake.com/LIMITEDACCESS/snapshots
https://docs.snowflake.com/LIMITEDACCESS/snapshots/create-snapshot-policy
https://docs.snowflake.com/LIMITEDACCESS/snapshots/create-snapshot-set
https://docs.snowflake.com/LIMITEDACCESS/snapshots/show-snapshot-sets
https://docs.snowflake.com/LIMITEDACCESS/snapshots/show-snapshot-policies
NOTE: this feature was recently announced at Snowflake Summit 2025

Describe the solution you'd like
I would like SnowDDL to support these as soon as practical so we can use them.
I understand it probably doesn't make sense to add support until it is in Public Preview.
Does SnowDDL implement support for things that are in Public Preview? or only once it is in GA?

Describe alternatives you've considered
None

Additional context
This feature will be something we want to use as soon as possible.

[littleK0i]

New object type can be implemented as soon as it becomes available in AWS in public preview.

In theory, it can be done faster via consulting, if client provide access to a test account with specific private preview feature enabled. But usually it makes sense to wait for public preview.

[jfiore-im]

Waiting for public preview sounds good. We don't have it in private preview and I think that should be coming soon.

[littleK0i]

@jfiore-im , ok, snapshots seems to be in public preview now.

I see one major problem with this feature. It seems, snapshots with retention lock cannot be deleted after creation. It would naturally cause problems for automated testing and for SnowDDL "destroy" feature. It is the first ever object type which cannot (?) be destroyed.

Not sure what is the best course of action here.

[jfiore-im]

@littleK0i I saw that. Thanks for jumping on it!

Could you do automated testing with snapshots without retention lock against actual Snowflake, and then cover snapshots with retention lock with just unit tests that mock Snowflake out?

[jfiore-im]

@littleK0i any update on this one?
My team is happy to do some beta testing with it for you with a pre-release version.

[littleK0i]

@jfiore-im , i've pushed some initial implementations for snapshot policies and snapshot sets in 0.57.0.

Not documented yet, since this feature is incomplete and kinda weird on Snowflake side. But you may give it a try and see how it goes.

Example parameters for SNAPSHOT POLICY:

schedule: 120 minutes
expire_after_days: 60

Example parameters for SNAPSHOT SET:

object_type: TABLE
object_name: ss001_tb2
snapshot_policy: MY_SNAPSHOT_POLICY

object_type: SCHEMA
object_name: db1.sc1

object_type: DATABASE
object_name: db1

No retention lock for now, since there is no way to test it safely.

---

Traditionally new Snowflake features are half-baked. The main problems right now are:

1. Weird limitations around "schedule" and "expire_after_days".
2. Weird limitations around object types which are being or not being included in database and schema snapshots.
3. POLICY_REFERENCES table function does not support snapshot policies. So there seems to be no way to find all snapshot sets with specific policy.
4. Cannot "unset" policy from set, but can set another policy with "FORCE" option. Not sure why.

In general, I would not recommend using this for actual production environment. You are probably going to be much better off with creating your own backup schemas and some basic automation to CLONE specific tables regularly. At least you will be free from weirdness, random changes and arbitrary limitations which Snowflake tries to impose for seemingly no good reason.

This might change in a couple of months once this feature becomes generally available.

[jfiore-im]

Thanks @littleK0i We will give this a try.

[inkapbang]

@littleK0i I have a question on snapshot policy setting. I see schedule but no base date where the schedule should start from. If I were to create a policy that will be once every Sunday, is there a way to set that currently? Is there a support for crontab-like schedule notations for the policies?

[littleK0i]

@inkapbang , it goes directly to SCHEDULE parameter: https://docs.snowflake.com/en/sql-reference/sql/create-snapshot-policy

No transformations (for now). It seems, metadata perserve the exact value which was passed.

So feel free to try USING CRON ....

[inkapbang]

@littleK0i I am looking to codify the snapshots now, and I am not sure where I will need to add the configurations to.

I am planning to store all snapshot objects to its own database/schema, because snapshots for a particular schema will also be deleted when that schema is dropped. So, I am planning to create a database and schema as snapshot_store.snapshot_store, and define all the policies/sets/snapshots for other databases/schemas.

I think this is safer approach than storing snapshots in the same schema.

How would I define these snapshot policies/sets in our case? Would I just create snapshot.yaml file under snapshot_store/snapshot_store schema directory and define both policies and sets in one file? Or, do I need to create snapshot_policy folder and snapshot_set folder, and create yaml file?

A bit of clarification will be greatly appreciated.

Thank you,

[littleK0i]

@inkapbang , It works the same way as other schema-level objects.

Config file path is: <database>/<schema>/snapshot_policy/<name>.yaml

I've added some documentation.

Snapshot policies: https://docs.snowddl.com/basic/yaml-configs/snapshot-policy
Snapshot sets: https://docs.snowddl.com/basic/yaml-configs/snapshot-set

You may also find some examples in tests:
https://github.com/littleK0i/SnowDDL/tree/master/test/_config/step1/db1/sc1/snapshot_policy
https://github.com/littleK0i/SnowDDL/tree/master/test/_config/step1/db1/sc1/snapshot_set

[inkapbang]

Thank you @littleK0i

I have another question. There will need to be more permissions granted to the schema owner of snapshot_store according to the documentation:
https://docs.snowflake.com/en/user-guide/snapshots#grant-privileges-required-to-create-snapshot-policies-and-sets
https://docs.snowflake.com/en/user-guide/snapshots#grant-the-apply-privilege-on-a-snapshot-policy-to-a-role

We didn't grant ACCOUNTADMIN role to snowDDL (just granted SYSADMIN/SECURITYADMIN), so I expect it will fail when I apply the change. I can't figure out where to add CREATE SNAPSHOT POLICY and CREATE SNAPSHOT SET privileges so that the schema owner of snapshot_store will be able to create the objects without issue.

Also, for APPLY ON SNAPSHOT POLICY, I am not sure how to overcome that without having to manually execute that as ACCOUNTADMIN.

If you can share your insight into how I can add these properly, that'll be really helpful.

Thank you,

[littleK0i]

@inkapbang , since SnowDDL normally runs at least with SECURITYADMIN role, permissions should not be an issue for admin role itself.

If you have a custom setup without SECURITYADMIN (not recommended due to Snowflake limitations and weirdness), you'll see error messages asking to add missing permissions.

In general, it applies to everything. Snowflake may change permissions for any object type silently at any time.

[inkapbang]

Thank you @littleK0i I really appreciate your reply!

I am going to apply it and see if everything goes well. I'll let you know.

[inkapbang]

@littleK0i It did generate DDLs as suggested DDL, and I was not able to run them successfully as the schema owner of snapshot_store.snapshot_store, but they completed successfully when I ran them as SNOWDDL_ADMIN role.

I am curious to know when the snapshots are expired, and I wonder if there are any housekeeping statements that could arise. I intentionally set some of the database/schema snapshots to expire in two days, so I will be able to tell you next week if everything looks all good.

Thank you!