How to check table size in Snowflake

Galaxy Glossary

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().

Sign up for the latest in SQL knowledge from the Galaxy Team!
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.

Description

Table of Contents

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 10242 or 10243.

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

Frequently Asked Questions (FAQs)

Does SYSTEM$ESTIMATE_TABLE_SIZE() lock the table?

No. The function reads metadata only and has no impact on concurrent DML.

How often does TABLE_STORAGE_METRICS refresh?

Approximately every 5 minutes, but heavy workloads can delay updates by a few minutes.

Can I monitor size growth over time?

Yes. Schedule a daily INSERT into a custom monitoring table selecting from TABLE_STORAGE_METRICS, then chart the results.

Want to learn about other SQL terms?

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.