TABLE_STORAGE_METRICS View

This view displays table-level storage utilization information, which is used to calculate the storage billing for each table in the account, including tables that have been dropped, but are still incurring storage costs.

In addition to table metadata, the view displays the number of storage bytes billed for each table. Snowflake breaks down the bytes into the following categories:

  • Active bytes, representing data in the table that can be queried.

  • Deleted bytes that are still accruing storage charges because they have not been purged yet from the system. These bytes are classified into the following sub-categories:

    • Bytes in Time Travel (i.e. recently deleted, but still within the Time Travel retention period for the table).
    • Bytes in Fail-safe (i.e. deleted bytes that are past the Time Travel retention period, but within the Fail-safe period for the table).
    • Bytes retained for clones (i.e. deleted bytes that are no longer in Time Travel or Fail-safe, but are still retained because clones of the table reference the bytes).

In other words, rows are maintained in this view until the corresponding tables are no longer billed for any storage, regardless of various states that the data in the tables may be in (i.e. active, Time Travel, Fail-safe, or retained for clones).

For more details about data storage in tables, see Data Storage Considerations.

Note

To query this view, you must use the ACCOUNTADMIN role. The view is visible to other views and can be queried, but the queries will return no rows.

Columns

Column Name Data Type Description
TABLE_CATALOG TEXT Database that the table belongs to.
TABLE_SCHEMA TEXT Schema that the table belongs to.
TABLE_NAME TEXT Name of the table.
ID NUMBER Unique identifier for the table.
CLONE_GROUP_ID NUMBER Unique identifier for the oldest clone ancestor of this table. Same as ID if the table is not a clone.
IS_TRANSIENT TEXT ‘YES’ if table is transient or temporary, otherwise ‘NO’. Transient and temporary tables have no Fail-safe period.
ACTIVE_BYTES NUMBER Bytes owned by (and billed to) this table that are in the active state for the table.
TIME_TRAVEL_BYTES NUMBER Bytes owned by (and billed to) this table that are in the Time Travel state for the table.
FAILSAFE_BYTES NUMBER Bytes owned by (and billed to) this table that are in the Fail-safe state for the table.
RETAINED_FOR_CLONE_BYTES NUMBER Bytes owned by (and billed to) this table that are retained after deletion because they are referenced by one or more clones of this table.
TABLE_CREATED TIMESTAMP_LTZ Date and time at which the table was created.
TABLE_DROPPED TIMESTAMP_LTZ Date and time at which the table was dropped. NULL if table has not been dropped.
TABLE_ENTERED_FAILSAFE TIMESTAMP_LTZ Date and time at which the table, if dropped, entered the Fail-safe state, or NULL. In this state, the table cannot be restored using UNDROP.
CATALOG_CREATED TIMESTAMP_LTZ Date and time at which the database containing the table was created.
CATALOG_DROPPED TIMESTAMP_LTZ Date and time at which the database containing the table was dropped.
SCHEMA_CREATED TIMESTAMP_LTZ Date and time at which the schema containing the table was created.
SCHEMA_DROPPED TIMESTAMP_LTZ Date and time at which the schema containing the table was dropped.
COMMENT TEXT Comment for the table.

Usage Notes

  • ID and CLONE_GROUP_ID:

    • ID does not change for a table throughout its lifecycle, including if the table is renamed or dropped.

    • CLONE_GROUP_ID is the ID of the oldest ancestor of a clone, including if the table has been dropped, but is still accruing storage costs. For example:

      • Table t2 is cloned from t1.
      • Table t3 is cloned from t2.

      All three tables list the ID for t1 as their CLONE_GROUP_ID, even if t1 is dropped and eventually purged from Snowflake.

    • If ID and CLONE_GROUP_ID are identical, the table is not a clone.

    • Storage bytes are always owned by, and therefore billed to, the table where the bytes were initially added. If the table is then cloned, storage metrics for these initial bytes never transfer to the clones, even if the bytes are deleted from the source table.

  • Cloned tables share the same underlying storage (at the micro-partition level) until either the original table or cloned table is modified. With each change made to either table, the table takes “ownership” of the changed bytes.

  • Dropped tables are displayed in the view as long as they still incur storage costs:

    • Dropped tables retain their active storage metrics, indicating how many bytes will be active if the table is restored.

    • Dropped tables in the Time Travel retention period for the table can be restored using UNDROP.

    • Dropped tables in Fail-safe (TABLE_ENTERED_FAILSAFE not NULL) will potentially display NULL values in most columns, except for:

      ID columns:ID , CLONE_GROUP_ID
      Bytes columns:ACTIVE_BYTES , TIME_TRAVEL_BYTES , FAILSAFE_BYTES , RETAINED_FOR_CLONE_BYTES

      These tables cannot be restored using UNDROP.

  • FAILSAFE_BYTES denotes bytes that have passed beyond Time Travel. All such bytes are billed to the current table.

  • If multiple rows have the same value in the TABLE_NAME column, this indicates that multiple versions of the table exist. A version is created each time a table is dropped and a new table with the same name is created, including when a CREATE OR REPLACE TABLE command is issued on an existing table. Note that the current version will have a NULL value for the TABLE_DROPPED column; all other versions will have a timestamp value. This is important to note because each version of a table incurs storage costs associated with Time Travel (and Fail-safe, if the table is permanent).