How to check table size in Snowflake

How do I check table size in Snowflake?

Retrieve the storage footprint of any Snowflake table using metadata views, SHOW commands, or SYSTEM$ESTIMATE_TABLE_SIZE().

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

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.

Why How to check table size in Snowflake is important

How to check table size in Snowflake Example Usage


-- Find top 5 largest ecommerce tables
SELECT table_name,
       bytes/1024/1024 AS size_mb
FROM SHOPDB.INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
ORDER BY bytes DESC
LIMIT 5;

How to check table size in Snowflake Syntax


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

-- Call estimation function on-demand
SELECT SYSTEM$ESTIMATE_TABLE_SIZE('SHOPDB.PUBLIC.PRODUCTS') AS size_estimate_json;

-- SHOW TABLES summary (wrap with RESULT_SCAN to query)
SHOW TABLES LIKE 'PRODUCTS' IN SCHEMA SHOPDB.PUBLIC;
SELECT table_name, bytes/1024/1024 AS size_mb
FROM RESULT_SCAN(LAST_QUERY_ID());

Common Mistakes

Relying solely on SHOW TABLES for size can be misleading during heavy loads because its bytes column may lag. Always cross-check with TABLE_STORAGE_METRICS or ESTIMATE_TABLE_SIZE for up-to-date values.
Reading raw byte counts without conversion makes trends hard to spot. Divide by 1024^2 for MB or 1024^3 for GB before dashboarding or alerting.