Storage lifecycle policies¶

Billing¶

You can use the following views to get information about credit usage and data retrieval from archive storage for lifecycle policies:

• ARCHIVE_STORAGE_DATA_RETRIEVAL_USAGE_HISTORY view

• METERING_DAILY_HISTORY 뷰

• METERING_HISTORY 뷰

The metering history views include the following service types related to storage lifecycle policies:

• STORAGE_LIFECYCLE_POLICY_EXECUTION: Compute cost to apply a policy on a target table and expire or archive rows (policy execution).

• ARCHIVE_STORAGE_WRITE: Cost for writing archived table rows into a lower-cost storage tier.

• ARCHIVE_STORAGE_RETRIEVAL_FILE_PROCESSING: Cost for processing data retrieved from archive storage.

• ARCHIVE_STORAGE_DATA_RETRIEVAL: Cost for retrieving data from archive storage.

For more information about billing related to storage lifecycle policy execution, file processing, and data retrieval, see tables 4(f) and 5 in the Snowflake Service Consumption Table.

Considerations¶

Consider the following information when you create and manage storage lifecycle policies.

• Storage lifecycle policies use performance guidelines that are similar to guidelines for row-level access policies. For more information, see 정책 성능 지침.

• Storage lifecycle policies are only supported for standard Snowflake tables.

• Snowflake disables all governance policies when it evaluates a storage lifecycle policy to ensure that all data can be reviewed for expiration or archiving.

• Storage lifecycle policies aren’t automatically applied to cloned tables.

• User-defined functions (UDFs) with external access and external functions aren’t supported.

• Python, Java, and Scala UDFs aren’t supported.

• Replication for storage lifecycle policies works as follows:

  • Snowflake replicates storage lifecycle policies and their associations with tables to target accounts, but doesn’t run the policies.

  • After failover to a target account, Snowflake pauses storage lifecycle policy execution in the original primary account. Likewise, after failback to the original primary account, Snowflake resumes policy execution.

  • Snowflake never automatically runs secondary storage lifecycle policies on secondary tables, even after failover. However, you can use secondary policies in a target account by attaching them to new tables. For those new tables, Snowflake runs the policies.

• For working with tables that have an archival storage lifecycle policy attached:

  • If you delete the table, Snowflake also deletes the archived table rows.

  • You can use Tri-Secret Secure (TSS) to secure archived data with regular TSS key rotation.

  • Snowflake doesn’t rekey archived data.

  • Changing an existing policy from the COOL tier to the COLD tier (or the other way around) isn’t supported. You must create a new and separate policy.

  • Currently, a table can only have data in one archive tier during its lifetime. Attaching a policy to a table that already has archived data in a different tier isn’t supported. For example, you can’t attach a policy that uses the COLD tier to a table that already archives data in the COOL tier.

  • Removing a policy from a table doesn’t delete the data from archive storage. You can still retrieve data from archive storage after removing the policy.

  • Dropping a table doesn’t delete data from archive storage; you can undrop the table to restore archived data. Likewise, truncating the table doesn’t remove data in archive storage.

• For the CREATE TABLE … FROM ARCHIVE OF command:

  • Using this command requires the OWNERSHIP privilege on the source table.

  • Specifying column definitions, policies, tags, or other constraints isn’t supported. Snowflake automatically retrieves the table schema, policies, tags, and constraints from the source table.

  • The WHERE clause is required. Reading archived data is expensive, and should be performed infrequently. Filtering results using the WHERE clause helps you minimize costs by ensuring that Snowflake reads only the data that you require from archival storage.

  • To retrieve a history of data retrieval from archive storage, use the ARCHIVE_STORAGE_DATA_RETRIEVAL_USAGE_HISTORY view.

  • To retrieve data from the COLD tier of archive storage, Snowflake must first restore the files from external cloud storage. This process can take up to 48 hours.

    To support this process, set the following parameters appropriately:

    • STATEMENT_TIMEOUT_IN_SECONDS must be at least 48 hours.

    • ABORT_DETACHED_QUERY must be FALSE.

Archiving data before expiration¶

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.

You can’t query rows after Snowflake archives them to a lower-cost storage tier. However, you can retrieve archived rows during the archival period by using the CREATE TABLE … FROM ARCHIVE OF command. Running the command doesn’t remove the data from archive storage. Instead, Snowflake makes a temporary copy of the archived data in standard storage, and creates a new table.

Snowflake supports archiving data for the following cloud providers and storage tiers:

Storage lifecycle policy workflow¶

Use the following workflow to get started with storage lifecycle policies.

1. Set up the required privileges to use and manage storage lifecycle policies.

2. Create a storage lifecycle policy using CREATE STORAGE LIFECYCLE POLICY.

3. Add a lifecycle policy to a table. Snowflake runs the storage lifecycle policy daily to archive or expire rows as defined by the policy.

Required privileges¶

Schema privileges

CREATE STORAGE LIFECYCLE POLICY

Allows the grantee to create a new storage lifecycle policy in a schema.

Schema object privileges

OWNERSHIP

Grants full control of the storage lifecycle policy. This privilege is required to alter the policy. Only one role can have this privilege per lifecycle policy object.

APPLY

Allows the grantee to add or drop the storage lifecycle policy on a table. To add the policy to a table, you must also have the OWNERSHIP privilege for the table or the global APPLY STORAGE LIFECYCLE POLICY privilege on the account.

To make changes to or create a storage lifecycle policy you must also have the USAGE privilege on the parent database and schema. For more information about privileges and grant behavior, see GRANT <privileges> … TO ROLE.

Storage lifecycle policies DDL commands¶

You can use the following data definition language (DDL) commands to manage your storage lifecycle policies. These DDLs are similar to other Snowflake row access policies. For more information about row access policies, see 행 액세스 정책 이해하기.

• CREATE STORAGE LIFECYCLE POLICY

• ALTER STORAGE LIFECYCLE POLICY

• DROP STORAGE LIFECYCLE POLICY

• DESCRIBE STORAGE LIFECYCLE POLICY

• SHOW STORAGE LIFECYCLE POLICIES

CREATE STORAGE LIFECYCLE POLICY example_policy
  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;

SELECT GET_DDL('policy','slp');

---------------------------------------------------------------------+
                      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¶

You can use the following commands to manage storage lifecycle policy attachments.

CREATE TABLE <table_name>
  ...
  WITH STORAGE LIFECYCLE POLICY <policy_name> ON (<col1>, [<col2>, ...]);

ALTER TABLE <table_name> ADD STORAGE LIFECYCLE POLICY <policy_name>
  ON (<col1>, [<col2, ...]);

ALTER TABLE <table_name> DROP STORAGE LIFECYCLE POLICY

SELECT *
  FROM TABLE(
    my_db.INFORMATION_SCHEMA.POLICY_REFERENCES(
    POLICY_NAME => 'my_storage_lifecycle_policy'
  )
);

SELECT *
  FROM TABLE(
    my_db.INFORMATION_SCHEMA.POLICY_REFERENCES(
      REF_ENTITY_NAME => 'my_db.my_schema.my_table',
      REF_ENTITY_DOMAIN => 'table'))
  WHERE POLICY_KIND = 'STORAGE_LIFECYCLE_POLICY';

Monitor storage lifecycle policy runs¶

You can monitor storage lifecycle policy executions over the last 14 days by using the STORAGE_LIFECYCLE_POLICY_HISTORY table function.

The following example retrieves the 100 most recent executions for a policy attached to a specified table, scheduled within the last day:

SELECT * FROM
  TABLE(
    INFORMATION_SCHEMA.STORAGE_LIFECYCLE_POLICY_HISTORY(
      REF_ENTITY_NAME => 'my_db.my_schema.my_source_table',
      REF_ENTITY_DOMAIN => 'table',
      TIME_RANGE_START => DATEADD('DAY', -1, CURRENT_TIMESTAMP()),
      RESULT_LIMIT => 100
    )
  );

For information about the function output, see the STORAGE_LIFECYCLE_POLICY_HISTORY page.

Retrieve archived data¶

Read archived data by using the CREATE TABLE … FROM ARCHIVE OF command.

For example, the following statement creates a new table from archived rows where the value in the event_timestamp column is between January 15 and January 20 of 2023.

CREATE TABLE my_table
  FROM ARCHIVE OF my_source_table AS st
  WHERE st.event_timestamp BETWEEN '01/15/2023' AND '01/20/2023';

CREATE [ TRANSIENT ] TABLE [ IF NOT EXISTS ] <new_table_name>
  FROM ARCHIVE OF <source_table> [ [ AS ] <alias_name> ]
  WHERE <expression>