Snowflake maintains the important information in system tables. One of such view is TABLE_STORAGE_METRICS view.
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.
This view contains storage information about all tables that particular account hold.
Where we can find TABLE_STORAGE_METRICS view ?
Role: ACCOUNTADMIN
Database: SNOWFLAKE
Schema: INFORMATION_SCHEMA
Snowflake Query to Calculate Table Size:
You can use the information stored in TABLE_STORAGE_METRICS view to calculate table size.
For example, Consider following query to calculate the table size in GB for table TRIPS present in ‘CITIBIKE’ database.
Table Script:
SELECT TABLE_CATALOG, TABLE_NAME,
((ACTIVE_BYTES / 1024)/1024)/1024 AS STORAGE_USAGE_GB
FROM “INFORMATION_SCHEMA”.TABLE_STORAGE_METRICS
WHERE TABLE_NAME IN (‘TRIPS’);
From above screenshot,
Storage of TRIPS table — 1.8 GB.
Similar to other relational database, Snowflake does not consume any space when you create the table until you insert one or more rows to the tables. Initially, it will simply create the structure with bytes as zero.
For example, create a empty table,
CREATE OR REPLACE TABLE TEST_TABLE (COL1 INT, COL2 INT,COL3 INT);
Table Script:
SELECT TABLE_CATALOG, TABLE_NAME,
((ACTIVE_BYTES / 1024)/1024)/1024 AS STORAGE_USAGE_GB
FROM “INFORMATION_SCHEMA”.TABLE_STORAGE_METRICS
WHERE TABLE_NAME IN (‘TEST_TABLE’);
Points to remember:
- 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.
- There may be a 1–2 hour delay in updating storage related statistics for active_bytes, time_travel_bytes, failsafe_bytes, and retained_for_clone_bytes in this view.
- Dropped tables are displayed in the view as long as they still incur storage costs.
References:-