How to get the total byte size of schemas & databases in Snowflake

Question

I love how the SHOW TABLES query return the rows and the bytes for each table listed, but unfortunately, that's not the case for the SHOW SCHEMAS query.

What's the best way to get the total byte size of a list of schemas?

Answer 1

I've managed to do this by querying information_schema.tables:

SELECT
  table_schema AS schema,
  SUM(bytes) AS bytes
FROM information_schema.tables
GROUP BY schema;

Note that this only works for the current database, as each database has its own information_schema. So this wouldn't work to get the size of a list of databases. However, it's possible to get the total size of each DB individually:

SELECT SUM(bytes) AS bytes
FROM [database_name].information_schema.tables;

A nice complement to this is to use a UDF to format the byte size nicely, like the nicebytes example here, resulting in nicebytes(SUM(bytes)) AS bytes in the query.

---

EDIT: Those two queries can be unioned in order to get the results for multiple databases at once. They can also be made to run daily and store the result into another table, which provides a nice way to monitor the size of databases & schemas over time!

SELECT
  table_catalog AS database,
  table_schema AS schema,
  SUM(bytes) AS bytes
FROM database_1.information_schema.tables
GROUP BY database, schema
UNION ALL
SELECT
  table_catalog AS database,
  table_schema AS schema,
  SUM(bytes) AS bytes
FROM database_2.information_schema.tables
GROUP BY database, schema;

SELECT
  table_catalog AS database,
  SUM(bytes) AS bytes
FROM database_1.information_schema.tables
GROUP BY database
UNION ALL
SELECT
  table_catalog AS database,
  SUM(bytes) AS bytes
FROM database_2.information_schema.tables
GROUP BY database;

Answer 2

If you have accountadmin access or have been granted access to the Account Usage Views you could try using TABLE_STORAGE_METRICS.

select 
TABLE_CATALOG,
TABLE_SCHEMA, 
nicebytes(sum(ACTIVE_BYTES)) ACTIVE_STORAGE, 
nicebytes(sum(TIME_TRAVEL_BYTES)) TIME_TRAVEL_STORAGE, 
nicebytes(sum(FAILSAFE_BYTES)) FAILSAFE_STORAGE
from SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
where TABLE_DROPPED IS NULL
and SCHEMA_DROPPED IS NULL
and CATALOG_DROPPED IS NULL
group by 1,2
order by 1,2,3

This also leverages the nicebytes UDF