How to check table size in Snowflake

What is the fastest way to see a table’s size?

Query INFORMATION_SCHEMA.TABLE_STORAGE_METRICS. It updates every few minutes and returns compressed bytes, row count, and time of measurement.

Which metadata view shows table size?

Use DBNAME.INFORMATION_SCHEMA.TABLE_STORAGE_METRICS. Filter by table_schema and table_name for one table or omit filters to list all.

Example: PRODUCTS table

SELECT bytes/1024/1024 AS size_mb, row_count
FROM SHOPDB.INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
WHERE table_schema = 'PUBLIC' AND table_name = 'PRODUCTS';

How do I get an on-demand size estimate?

Call SYSTEM$ESTIMATE_TABLE_SIZE('SHOPDB.PUBLIC.PRODUCTS'). It returns JSON with compressed and uncompressed byte estimates—helpful before loading more data.

Can I use SHOW TABLES to get sizes?

Yes. SHOW TABLES IN SCHEMA SHOPDB.PUBLIC; returns bytes and rows columns. Wrap it with RESULT_SCAN(LAST_QUERY_ID()) for filtering or calculations.

How do I list the largest tables?

Order TABLE_STORAGE_METRICS by bytes. Example:
SELECT table_name, bytes/1024/1024 AS size_mb
FROM SHOPDB.INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
ORDER BY bytes DESC
LIMIT 10;

When are the metrics refreshed?

TABLE_STORAGE_METRICS updates roughly every 5 minutes. For real-time checks, use SYSTEM$ESTIMATE_TABLE_SIZE().

Best practices for monitoring storage

Automate nightly jobs that write table sizes to a monitoring table, alert on sudden spikes, and archive cold data to lower-cost warehouses.

Common mistakes to avoid

1. Trusting SHOW TABLES alone; it may display stale bytes during heavy loads. Cross-check with TABLE_STORAGE_METRICS.
2. Forgetting to convert bytes to MB/GB; raw bytes are hard to interpret. Always divide by 1024² or 1024³.

Need a quick recap?

Use TABLE_STORAGE_METRICS for scheduled reports, SYSTEM$ESTIMATE_TABLE_SIZE for ad-hoc estimates, and SHOW TABLES for a simple overview.