Monitoring Individual Table Storage in Snowflake

Monitoring individual table storage in Snowflake is crucial for efficient data management. Snowflake provides several tools to help you keep track of your storage usage. This article explores three main options to monitor table storage: the SHOW TABLES command, Table Storage Metrics Views in Information Schema, and the Account Usage Schema.

Options to Monitor Table Storage#

SHOW TABLES Command#

Purpose: Provides general statistics about table storage and properties.

Usage: This simple command offers an overview of storage usage, allowing you to quickly assess the storage properties of your tables.

Output Includes:

• Table properties
• General storage statistics

Command:

SHOW TABLES;

Table Storage Metrics Views#

Information Schema#

Purpose: Provides detailed information about table storage, including the amount of storage used for active databases.

Details Include:

• Amount of storage used for active databases

• Storage breakdown into time travel and failsafe

Account Usage Schema#

Purpose: Similar to the Information Schema but specific to account usage. It provides comprehensive details about table storage, including active bytes, time travel bytes, and failsafe bytes.

Access: Requires the role ACCOUNTADMIN to query.

Details Include:

Detailed table information, including active bytes, time travel bytes, and failsafe bytes.

Information on deleted tables (drop retention).

Monitoring Table Storage#

Using SHOW TABLES Command#

The SHOW TABLES command provides a general overview of your tables, including basic storage statistics and properties.

Query Example:

SHOW TABLES;

Output: This command gives you a snapshot of table properties, such as clustering and search optimization, and the amount of bytes used. However, it offers less detail compared to Table Storage Metrics views.

Using Table Storage Metrics Views#

For more detailed information, you can use the Table Storage Metrics views. These views are available in the Account Usage Schema and require the ACCOUNTADMIN role to access.

Query Example:

To query the table_storage_metrics view from the Account Usage Schema:

-- USE ACCOUNTADMIN ROLE
USE ROLE ACCOUNTADMIN;

-- GET STORAGE METRIC INFORMATION FROM ACCOUNT USAGE SCHEMA
SELECT
    *
FROM
    SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS;

This query provides comprehensive details about your table storage, including active bytes, time travel bytes, and failsafe bytes. It also includes information on deleted tables, helping you manage your storage more effectively.