STORAGE_LIFECYCLE_POLICY_HISTORY¶

Syntax¶

By object

STORAGE_LIFECYCLE_POLICY_HISTORY(
  REF_ENTITY_NAME => '<string>',
  REF_ENTITY_DOMAIN => '<string>'
  [, TIME_RANGE_START => <constant_expr> ]
  [, TIME_RANGE_END => <constant_expr> ]
  [, RESULT_LIMIT => <integer> ] )

By storage lifecycle policy

STORAGE_LIFECYCLE_POLICY_HISTORY(
  POLICY_NAME => '<string>'
  [, TIME_RANGE_START => <constant_expr> ]
  [, TIME_RANGE_END => <constant_expr> ]
  [, RESULT_LIMIT => <integer> ] )

Arguments¶

Required:

REF_ENTITY_NAME => 'string'

The identifier for the object (table) that the execution occurred on; for example, the name of the table that the storage lifecycle policy is attached to.

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.

REF_ENTITY_DOMAIN => 'string'

The object type to which the storage lifecycle policy is attached:

• 'Table': Specifies that the storage lifecycle policy is attached to a table.

POLICY_NAME => 'string'

The identifier of a storage lifecycle policy to retrieve execution history for. If you don’t specify a policy name, you must specify values for REF_ENTITY_NAME and REF_ENTITY_DOMAIN.

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.

Optional:

TIME_RANGE_START => constant_expr, . TIME_RANGE_END => constant_expr

Time range, within the last 14 days, in which the policy execution occurred.

If neither parameter is specified, the function returns rows (up to the RESULT_LIMIT) for the latest policy executions in descending order by END_TIME.

RESULT_LIMIT => integer

The maximum number of rows returned by the function.

Range: 1 to 1000

Default: 1000.

Returns¶

The function returns execution history records for storage lifecycle policies. Each record contains information about the policy execution, including the policy name, associated table, execution state, start and end times, and execution results.

For detailed column descriptions, see Output.

Output¶

The function returns the following columns:

EXECUTION_RESULT =
{
  “EXPIRE”: {
    “start_time”: "Thu, 27 Jun 2024 02:57:57 -0700",
    “end_time”: "Thu, 27 Jun 2024 02:58:01 -0700",
    “state”: "SUCCEEDED",
    “rows_expired”: 100,
    “error_message_code”: null,
    “error_message”: null
  }
}

Access control requirements¶

A role used to execute this SQL command must have at least one of the following privileges at a minimum:

Usage notes¶

• Results are returned based on the privileges granted to the role that executes the query:

  • If the role has the global APPLY STORAGE LIFECYCLE POLICY privilege, Snowflake returns all policy executions related to any policy and table associations in the account.

  • If the role has the APPLY privilege on a specific storage lifecycle policy, Snowflake returns executions for that policy only for objects that are owned by the role that calls the function.

  • If the role has either the APPLY privilege or the OWNERSHIP privilege on the policy, but does not have the OWNERSHIP privilege on the table that the policy is attached to, Snowflake doesn’t show policy executions for the policy in the results.

  • If the role has no policy privileges, but has the OWNERSHIP privilege on the table that a policy is attached to, Snowflake returns an error message and doesn’t return any policy executions.

Examples¶

Specify the REF_ENTITY_NAME and REF_ENTITY_DOMAIN arguments to retrieve the storage lifecycle policy history for a table named t1:

SELECT * FROM
  TABLE (
    INFORMATION_SCHEMA.STORAGE_LIFECYCLE_POLICY_HISTORY(
      REF_ENTITY_NAME => 'my_db.my_schema.t1',
      REF_ENTITY_DOMAIN => 'Table'
    )
  );

Retrieve the storage lifecycle policy history for each table that has the policy named slp associated with it, and limit the results to 100 rows:

SELECT * FROM
  TABLE(
    INFORMATION_SCHEMA.STORAGE_LIFECYCLE_POLICY_HISTORY(
      POLICY_NAME => 'my_db.my_schema.slp',
      RESULT_LIMIT => 100
    )
  );

Retrieve the 100 most recent executions for a specified policy, scheduled within the last hour:

SELECT * FROM
TABLE(
  INFORMATION_SCHEMA.STORAGE_LIFECYCLE_POLICY_HISTORY(
    POLICY_NAME => 'my_db.my_schema.slp',
    TIME_RANGE_START => DATEADD('HOUR', -1, CURRENT_TIMESTAMP()),
    RESULT_LIMIT => 100
  )
);

Retrieve the policy execution history for a given table within a 30-minute time range:

SELECT * FROM
TABLE (
  INFORMATION_SCHEMA.STORAGE_LIFECYCLE_POLICY_HISTORY(
    REF_ENTITY_NAME => 'my_db.my_schema.t1',
    REF_ENTITY_DOMAIN => 'Table',
    TIME_RANGE_START => TO_TIMESTAMP_LTZ('2024-07-08 12:00:00.000 -0700'),
    TIME_RANGE_END => TO_TIMESTAMP_LTZ('2024-07-08 12:30:00.000 -0700')
  )
);