How to Find Snowflake Table Size?

Snowflake is a fully managed cloud data warehouse solution. It is fast, reliable and comes with near zero management. Similar to many relational databases, Snowflake support many system tables and views. System tables can store the important information such as records count, constraint details, etc. In this article, we will check how to find the Snowflake table size using system tables present in the information schema.

As mentioned earlier, 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.

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 for all tables present in ‘TEST_DB’ database.

SELECT table_catalog, 
       table_name, 
       active_bytes / 1024 AS storage_usage_MB 
FROM   "INFORMATION_SCHEMA".table_storage_metrics 
where TABLE_CATALOG in ('TEST_DB');

+---------------+-------------------+------------------+
| TABLE_CATALOG | TABLE_NAME        | STORAGE_USAGE_MB |
|---------------+-------------------+------------------|
| TEST_DB       | TEST              |         0.500000 |
| TEST_DB       | SAMPLE_TEST_SINGLE|         1.000000 |
| TEST_DB       | SAMPLE_CSV        |         1.000000 |
| TEST_DB       | TEST1             |         0.000000 |
| TEST_DB       | SAMPLE_EMPTY_TAB  |         0.500000 |
+---------------+-------------------+------------------+

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 table sample_empty_tab (col1 int, col2 int);
+----------------------------------------------+
| status                                       |
|----------------------------------------------|
| Table SAMPLE_EMPTY_TAB successfully created. |
+----------------------------------------------+

If you check the size, it will be zero.

For example,

SELECT table_catalog, 
       table_name, 
       active_bytes / 1024 AS storage_usage_MB 
FROM   "INFORMATION_SCHEMA".table_storage_metrics 
WHERE  table_name = 'SAMPLE_EMPTY_TAB'; 
+---------------+------------------+------------------+
| TABLE_CATALOG | TABLE_NAME       | STORAGE_USAGE_MB |
|---------------+------------------+------------------|
| TEST_DB       | SAMPLE_EMPTY_TAB |         0.000000 |
+---------------+------------------+------------------+

Now, insert few records and check the size again.

For example, consider following example to insert records.

insert into SAMPLE_EMPTY_TAB values (1,1),(2,2),(3,3),(4,4),(5,5);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       5 |
+-------------------------+

Now, check the size again.

For example,

SELECT table_catalog, 
       table_name, 
       active_bytes / 1024 AS storage_usage_MB 
FROM   "INFORMATION_SCHEMA".table_storage_metrics 
WHERE  table_name = 'SAMPLE_EMPTY_TAB'; 
+---------------+------------------+------------------+
| TABLE_CATALOG | TABLE_NAME       | STORAGE_USAGE_MB |
|---------------+------------------+------------------|
| TEST_DB       | SAMPLE_EMPTY_TAB |         0.500000 |
+---------------+------------------+------------------+

Empty Fields in TABLE_STORAGE_METRICS View

Sometimes, there may be a situation you may get empty records when you query TABLE_STORAGE_METRICS view. This is because, the role that you are using to query view does not have access to it.

For example,

>use role SYSADMIN;

SELECT table_catalog, 
       table_name, 
       active_bytes / 1024 AS storage_usage_MB 
FROM   "INFORMATION_SCHEMA".table_storage_metrics 
where TABLE_CATALOG in ('TEST_DB');

+---------------+------------+------------------+
| TABLE_CATALOG | TABLE_NAME | STORAGE_USAGE_MB |
|---------------+------------+------------------|
+---------------+------------+------------------+

You should use ACCOUNTADMIN role to query these system views.

For example,

>use role ACCOUNTADMIN;

SELECT table_catalog, 
       table_schema, 
       table_name, 
       active_bytes / 1024 AS storage_usage_MB 
FROM   "INFORMATION_SCHEMA".table_storage_metrics 
where TABLE_CATALOG in ('TEST_DB');

+---------------+-------------------+------------------+
| TABLE_CATALOG | TABLE_NAME        | STORAGE_USAGE_MB |
|---------------+-------------------+------------------|
| TEST_DB       | TEST              |         0.500000 |
| TEST_DB       | SAMPLE_TEST_SINGLE|         1.000000 |
| TEST_DB       | SAMPLE_CSV        |         1.000000 |
| TEST_DB       | TEST1             |         0.000000 |
| TEST_DB       | SAMPLE_EMPTY_TAB  |         0.500000 |
+---------------+-------------------+------------------+