How to access snowflake query profile overview statistics via SQL?

Question

In Snowflake SnowSight UI, in the Query Profile view, there is a section called Profile Overview where you can see the breakdown of the total execution time. It contains statistics like Processing, Local Disk I/O, Remote Disk I/O, Synchronization etc.

Full list here https://docs.snowflake.com/en/user-guide/ui-snowsight-activity.html#profile-overview

I want to access those statistics programmatically instead of having to navigate to that section for each query that I want to analyze. The only system view I know that provides query statistics is the QUERY_HISTORY however it doesn't contain those stats.

https://docs.snowflake.com/en/sql-reference/account-usage/query_history.html

Question is, can I get those stats in any of the system views? If so, where and how?

user_132438 · Answer

Bad news: There's no programmatic way to get this.

Good news: This is a frequent request, so we might eventually have news.

In the internal tracker I left a note to update this answer once there is progress we can report.

user_8292258 · Answer

You can do it via https://github.com/Snowflake-Labs/sfsnowsightextensions#get-sfqueryprofile. Doing it at scale (scraping-style) will likely yield ~60%-80% success rate. Please don't abuse it.

Inspired by a clever customer who did that to get what is now offered by https://docs.snowflake.com/en/sql-reference/account-usage/access_history.html

Completely unsupported as it says so on the repo homepage.

user_8292258 · Answer

Just FYI, there is an upcoming feature called GET_QUERY_STATS (currently in private preview) https://docs.snowflake.com/en/LIMITEDACCESS/get_query_stats.html that will do just this and obviate the reason for Get-SFQueryProfile once it ships.

How to access snowflake query profile overview statistics via SQL?

3 Answers