Creating and managing storage lifecycle policies

The following sections explain how to create, recreate, and manage storage lifecycle policies on your tables.

Create a storage lifecycle policy

To create a storage lifecycle policy, use the CREATE STORAGE LIFECYCLE POLICY command.

When you create a storage lifecycle policy, you can choose an archive tier and optionally set an archival period (in days). If you set an archival period, Snowflake moves table rows that match the policy expression into a lower-cost storage tier for the specified number of days before expiring the rows. Snowflake also enables change tracking on any tables that you attach the policy to.

For example:

CREATE STORAGE LIFECYCLE POLICY my_slp
  AS (event_ts TIMESTAMP, account_id NUMBER)
  RETURNS BOOLEAN ->
    event_ts < DATEADD(DAY, -60, CURRENT_TIMESTAMP())
    AND EXISTS (
      SELECT 1 FROM closed_accounts
      WHERE id = account_id
    )
  ARCHIVE_TIER = COOL
  ARCHIVE_FOR_DAYS = 90;
Copy

Note

For considerations when working with tables that have archival storage policies, see Archival storage policies.

Best practice: Use date conversions for time-based expressions

To improve performance and ensure consistent policy execution, convert timestamps to dates in your policy expressions when you compare time values.

For example, consider this policy expression:

event_time < DATEADD(DAY, -400, CURRENT_TIMESTAMP())
Copy

This comparison includes the time component of the timestamp, which can cause inconsistent behavior. When data gets inserted in chronological order by event_time, the policy’s execution time affects how many rows get deleted from each file.

To avoid this issue, convert timestamps to dates in your expression:

TO_DATE(event_time) < TO_DATE(DATEADD(DAY, -400, CURRENT_TIMESTAMP()))
Copy

This approach provides consistent policy execution regardless of the time of day.

Recreate a storage lifecycle policy

This feature extends the GET_DDL command to recreate a specified storage lifecycle policy. You might do this if you want to change the archival tier for a policy.

Return the DDL to recreate a storage lifecycle policy named my_slp.

SELECT GET_DDL('policy','my_slp');
Copy

Output:

---------------------------------------------------------------------+
                      GET_DDL('POLICY','SLP')                        |
---------------------------------------------------------------------+
create or replace storage lifecycle policy SLP as                    |
  (event_ts timestamp, account_id number)
    returns boolean ->
    event_ts < dateadd(day, -60, current_timestamp())
    and exists (
      select 1 from closed_accounts
      where id = account_id
  )
  ARCHIVE_FOR_DAYS = 365                                             |
;                                                                    |
---------------------------------------------------------------------+

Manage storage lifecycle policies on tables

Use the following options to manage storage lifecycle policy attachments.

Attach a policy to a table

You can manage multiple tables with one storage lifecycle policy. Attach the policy when you create or alter the table.

Use CREATE TABLE to create a table and attach the policy to a new table by using the specified columns.

Note

  • You must have the necessary privileges to apply the policy. For information about required privileges, see Storage lifecycle policy privileges.

  • A table can have only one attached storage lifecycle policy.

  • The number of columns must match the argument count in the policy function signature, and the column data must be compatible with the argument types.

  • Associated policies aren’t affected if you rename table columns. Snowflake associates policies to tables by using the column IDs.

  • In order to evaluate and apply storage lifecycle policy expressions, Snowflake internally and temporarily bypasses any governance policies on a table.

CREATE TABLE my_table
  ...
  WITH STORAGE LIFECYCLE POLICY my_slp ON (col1);
Copy

Use ALTER TABLE to attach the policy to an existing table using the specified columns.

ALTER TABLE my_table ADD STORAGE LIFECYCLE POLICY my_slp
  ON (col1);
Copy

Apply a policy as a one-time operation

If you only need to expire or archive historical data once (a one-time operation), we recommend the following approach:

  1. Create and attach a storage lifecycle policy to your table.

  2. Wait for the policy to execute and archive or expire the data. Monitor the INFORMATION_SCHEMA.STORAGE_LIFECYCLE_POLICY_HISTORY table function to confirm the process is complete.

  3. To prevent recurring charges, remove the storage lifecycle policy from the table. Storage lifecycle policies incur cost per execution.

This approach ensures that you only pay for a single execution instead of ongoing daily charges for a policy that has already processed all eligible data. For more information about cost, see Billing for storage lifecycle policies.

Remove a policy from a table

Use ALTER TABLE to remove a storage lifecycle policy from a table.

ALTER TABLE my_table DROP STORAGE LIFECYCLE POLICY;
Copy

  • This removes all future policy executions for this table.

  • Running policy executions might complete before they are dropped from the table.

  • To drop a storage lifecycle policy, you must have the OWNERSHIP privilege on the table the policy is attached to.

Language: English