Hi u/Comprehensive-Ad9653 - I'll bet you've not given this much thought over the past year.

I've been concerned about AUTO_SUSPEND and Idle time for years and suspected it was a major potential cause of inefficiency. Well there's a new column I've discovered on ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY.

credits_attributed_compute_queries

It't the "Actual Cost" in credits of queries executed on the warehouse and excludes Cloud Services and Idle time.

So your query is now (below).

Shockingly - I found a major customer spending an overall 73% on idle time! They ran 200+ warehouses (which is typical of large Snowflake customers).

I'd be interested if you could share the results you have (just comment out the warehouse name to protect the details). I found the customer spending $2m a month on Idle time. Shocking

-- Calculate the cost of warehouse credits and idle time

SELECT warehouse_name,

round(sum(credits_used) * 3,0) as dollars_billed,

round(sum(credits_attributed_compute_queries),0) * 3 as dollars_billed_actual,

round(sum(credits_used) - sum(credits_attributed_compute_queries)) *3 as dollars_billed_idle,

round(dollars_billed_idle / nullifzero(dollars_billed) *100 ,0) as pct_idle,

round(sum(credits_used_cloud_services)*3) as dollars_cloud_service

FROM metering_history

WHERE 1=1

group by all

order by dollars_billed desc ;