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.
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.
|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.|
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:
t2is cloned from
t3is cloned from
All three tables list the ID for
t1as their CLONE_GROUP_ID, even if
t1is 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).