ALTER SNAPSHOT POLICY¶

Modifies the properties of a snapshot policy. The following changes are supported:

Rename the policy.
Add or update the comment for the policy.
Change the schedule and expiration settings for the policy. The schedule determines how often Snowflake automatically makes a backup and adds the resulting snapshot to the snapshot set that’s governed by the policy. The expiration period determines how long each snapshot is retained before Snowflake automatically deletes it from the associated snapshot set.
Unset properties of the policy, so that they revert back to their default values.

See also:: CREATE SNAPSHOT POLICY, DROP SNAPSHOT POLICY, SHOW SNAPSHOT POLICIES

Syntax¶

ALTER SNAPSHOT POLICY <name> RENAME TO <new_name>

ALTER SNAPSHOT POLICY <name> SET
  [ COMMENT = '<string_literal>' ]
  [ SCHEDULE = '{ <num> MINUTE | USING CRON <expr> <time_zone> }' ]
  [ EXPIRE_AFTER_DAYS = <days_integer> ]

ALTER SNAPSHOT POLICY <name> UNSET { COMMENT | SCHEDULE | EXPIRE_AFTER_DAYS }

Copy

Parameters¶

name

Specifies the identifier for the snapshot policy.

If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

RENAME TO new_name

Specifies a new identifier for the snapshot policy; must be unique for your account.

For more information, see Identifier requirements.

SET...

Specifies one or more properties to set for the snapshot policy (separated by blank spaces, commas, or new lines):

COMMENT = 'string_literal'

Specifies a comment for the snapshot policy.

SCHEDULE = '{ num MINUTE | USING CRON expr time_zone }'

Specifies the schedule for creating snapshots of an object.

Note

The minimum schedule for snapshots is 60 minutes.

Every policy must include a SCHEDULE clause, an EXPIRE_AFTER_DAYS clause, or both.

USING CRON expr time_zone
Specifies a cron expression and time zone for the point in time a snapshot of an object is created. Supports a subset of standard cron utility syntax.

For a list of time zones, see the list of tz database time zones (in Wikipedia).

The cron expression consists of the following fields:
# __________ minute (0-59) # | ________ hour (0-23) # | | ______ day of month (1-31, or L) # | | | ____ month (1-12, JAN-DEC) # | | | | __ day of week (0-6, SUN-SAT, or L) # | | | | | # | | | | | * * * * *
The following special characters are supported:

*
Wildcard. Specifies any occurrence of the field.

L
Stands for “last”. When used in the day-of-week field, it lets you specify constructs such as “the last Friday” (“5L”) of a given month. In the day-of-month field, it specifies the last day of the month.

/n
Indicates the nth instance of a given unit of time. Each quanta of time is computed independently. For example, if 4/3 is specified in the month field, then the snapshot is scheduled for April, July and October (that is, every 3 months, starting with the 4th month of the year). The same schedule is maintained in subsequent years. That is, the snapshot is not scheduled to run in January (3 months after the October run).
Note
- The cron expression currently evaluates against the specified time zone only. Altering the TIMEZONE parameter value for the account (or setting the value at the user or session level) does not change the time zone for the snapshot.
- The cron expression defines all valid run times for the snapshot. Snowflake attempts to create a snapshot based on this schedule; however, any valid run time is skipped if a previous run has not completed before the next valid run time starts.
- When both a specific day of month and day of week are included in the cron expression, then the snapshot is scheduled on days satisfying either the day of the month or the day of the week. For example, SCHEDULE = 'USING CRON 0 0 10-20 * TUE,THU UTC' schedules a snapshot at 0AM (midnight) on any 10th to 20th day of the month and also on any Tuesday or Thursday outside of those dates.
num MINUTE
Specifies an interval (in minutes) of wait time between snapshots. Accepts positive integers only.

Also supports num M syntax.

To avoid ambiguity, a base interval time is set in the following circumstances:
- When the object is created (using CREATE SNAPSHOT SET … WITH SNAPSHOT POLICY).
- When a different interval is set (using ALTER SNAPSHOT SET … APPLY SNAPSHOT POLICY or ALTER SNAPSHOT POLICY … SET SCHEDULE).
The base interval time starts the interval counter from the current clock time. For example, if an INTERVAL value of 10 is set and the scheduled snapshot is enabled at 9:03 AM, then the next snapshot is created at 9:13 AM, 9:23 AM, and so on. Note that we make a best effort to ensure absolute precision, but only guarantee that a snapshot does not execute before the set interval occurs (that is, in the current example, the snapshot could first run at 9:14 AM, but will definitely not run at 9:12 AM).

EXPIRE_AFTER_DAYS = days_integer

Specifies the number of days until the snapshot expires. Snowflake automatically deletes expired snapshots. If this parameter isn’t specified, snapshots remain in the snapshot set until they are manually deleted from the set.

Minimum value: 1
Maximum value: 3653 (roughly 10 years) if you don’t specify the SCHEDULE clause. If you specify the SCHEDULE clause, the maximum depends on how often snapshots are taken. For more information, see Limitations of snapshots during the public preview period.

Note

If the policy has a retention lock, you can increase the EXPIRE_AFTER_DAYS value, but you can’t decrease that value.

Every policy must include a SCHEDULE clause, an EXPIRE_AFTER_DAYS clause, or both.

UNSET...

Unset one of the following properties for the snapshot policy. The property reverts to its default value.

COMMENT
SCHEDULE
EXPIRE_AFTER_DAYS

Note

You can unset the SCHEDULE property, or the EXPIRE_AFTER_DAYS property, but not both. For example, you might keep the EXPIRE_AFTER_DAYS property when you don’t intend to create new snapshots, but you want existing snapshots to expire after a certain time.

Access control requirements¶

A role used to execute this operation must have the following privileges at a minimum:

Privilege	Notes
OWNERSHIP	The role used to modify a snapshot policy must have the OWNERSHIP privilege on the snapshot policy.
APPLY SNAPSHOT RETENTION LOCK	The role used to modify a snapshot policy with a retention lock must have this privilege on the account.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

Regarding metadata:

Attention

Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

Examples¶

Add a comment to snapshot policy hourly_snapshot_policy:

ALTER SNAPSHOT POLICY hourly_snapshot_policy
  SET COMMENT = 'hourly snapshot expires in 90 days';

Copy

Change schedule for snapshot policy every_two_hours:

ALTER SNAPSHOT POLICY every_two_hours SET SCHEDULE = '120 MINUTE';

Copy

Revert the EXPIRE_AFTER_DAYS property back to its default value:

ALTER SNAPSHOT POLICY sample_snapshot_policy UNSET EXPIRE_AFTER_DAYS;

Copy