Monitoring storage lifecycle policies¶

Note

Storage lifecycle policies aren’t currently available in government regions.

Identify which tables have storage lifecycle policies attached, and monitor storage lifecycle policy runs by using Snowflake’s built-in functions.

Note

For information about monitoring storage lifecycle policy costs, see Billing for storage lifecycle policies.

Monitoring policy assignments¶

To view storage lifecycle policy metadata, use the following views:

See lifecycle policy attachments¶

To see which tables a particular lifecycle policy is attached to, you can call the POLICY_REFERENCES table function in the Snowflake Information Schema. The function displays only the tables that you have the OWNERSHIP privilege on.

The function returns a row for each table in a database that has the specified policy attached to it.

Example: List all tables associated with a policy¶

The following query retrieves a list of tables with a specified storage lifecycle policy attached.

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

Copy

Example: Find the policy assigned to a table¶

Retrieve the policy assigned to a specified table:

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';

Copy

Monitor storage lifecycle policy runs¶

Use the STORAGE_LIFECYCLE_POLICY_HISTORY table function to monitor storage lifecycle policy executions over the last 14 days. For information about the function output, see the STORAGE_LIFECYCLE_POLICY_HISTORY page.

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
    )
  );

Copy

Alternatively, use the following views to retrieve historical data for storage lifecycle policy runs: