Even more Query History in Snowflake

In previous posts, we have discussed differing ways of getting at the query history. In those posts, we discussed getting that data by the session or by a specific user. Today, I want to examine another way of getting to that data. This new way – is by warehouse.

I have tried to set up my Snowflake instance so that a specific block of users will only be able to use one warehouse. For example, my Accounting team uses the extra-small warehouse WH_ACCOUNT_XS, while my Snowflake Development team uses WH_SFDEV_S. I also set up my users that are used for individual applications, such as an application used to push data to SalesForce or one of many third-party applications, so that they use a warehouse named WH_3RDPARTY_M.

Setting the users up this way allows me to track which applications or users are using more of our hard-earned money for Snowflake processing. It also lets me charge each department exactly the right amount each month for how much processing work they truly did in Snowflake because it gracefully displays how much money Snowflake costs based on the warehouse.

Now, to see what’s going on with those users, I simply type the following query:

SELECT *
FROM TABLE(SNOWFLAKE.INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE(
     WAREHOUSE_NAME => 'WH_ACCOUNT_XS',
     RESULT_LIMIT => 10000
))
ORDER BY START_TIME DESC;  

Now I can see the last 10,000 rows of queries that were run by the Accounting department over the last 14 days. If I did this query by username, I’d have to remember that Bob works in Accounting, while Susie used to but she moved over to Operations in June. (Or some similar situation)

If you have your warehouses set up in a way that groups your users in a way that makes it easy for you to work with, this method of looking at query history can be of great value to you.

Now, with all of this information, I can hear what you’re thinking – “Sherpa, this is great and all, but what the heck can I do with it except berate individual users for writing stupid queries?”

Well, my friend, that answer and more will be revealed next time!