CODEWITHSUNDEEP
snowflake-cloud-data-platform
Virally
Virally

Reputation: 69

Snowflake Storage Metrics and Database Metrics

is there any way to capture overall storage metrics and database/table metrics in one query.

I have used these views

  1. table storage metrics (left join)
  2. Database storage usage history

But my overall storage is not matching as per snowflake account

This is the query used

Select date_trunc('month',RTR.USAGE_DATE) AS USAGE_MONTH, 
RTR.database_name, ABC.TABLE_NAME AS TABLENAME, avg(ABC.ACTIVE_BYTES) AS 
AVERAGE_DATABASE_BYTES, avg(ABC.FAILSAFE_BYTES) AS 
AVERAGE_FAILSAVE_BYTES, avg(ABC.TIME_TRAVEL_BYTES) AS 
AVERAGE_TIME_TRAVEL_BYTES, FROM 
SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS ABC JOIN 
SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY RTR ON 
ABC.TABLE_CATALOG = RTR.DATABASE_NAME AND ABC.DELETED='FALSE' where 
coalesce(ABC.TABLE_DROPPES,ABC.SCHEMA_DROPPED,ABC.CATALOG_DROPPED) is 
null GROUP BY 1,2,3

Upvotes: 2

Views: 661

Answers (1)

FKayani
FKayani

Reputation: 1021

You might be missing the data storage for stages and failsafe.

Storage: Which indicates the data stored in Snowflake tables. Stages: Which indicates the data stored in files in Snowflake stages Failsafe: Which indicates the storage in the 7-day failsafe area to protect against accidental data loss.

-- Data Storage by Month and Type

select to_char(usage_date,'YYYYMM') as sort_month 
,      to_char(usage_date,'Mon-YYYY') as month 
,      avg(storage_bytes) as storage
,      avg(stage_bytes) as stage
,      avg(failsafe_bytes) as failsafe
from snowflake.account_usage.storage_usage
group by month, sort_month
order by sort_month;

The report above shows the monthly cost for storage broken by type.

More details: https://www.analytics.today/blog/monitoring-snowflake-usage-and-costs

Upvotes: 3

Related Questions