I have a requirement to prepare a detail report that helps to validate the compute credit usage shown in warehouse_metering_history. As I understand, Compute credit is calculated based on the time the warehouse is running. I am trying to calculate the duration a particular warehouse was on from warehouse_events_history and then multiply it by the corresponding credit charge based on warehouse size. As per Snowflake documentation, for a small warehouse it is 0.0006/sec. Does snowflake charge it as 2/3600 per second or 0.0006 ? Also, is this the correct approach to get a close enough credit usage value for a warehouse ?
1 Answers
Something like this might be helpful?
SET compute_price=<$value>;
SELECT DATE_TRUNC(month, start_time) AS usage_month
, SUM(COALESCE(credits_used, 0.00)) AS total_credits
, SUM($compute_price * COALESCE(credits_used, 0.00)) AS billable_warehouse_usage
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATE_TRUNC(month, DATEADD(MONTH,-3,CURRENT_TIMESTAMP))
AND start_time < DATE_TRUNC(month, CURRENT_TIMESTAMP)
GROUP BY usage_month;
Related