As such it is necessary to keep monitoring my constant usage as the team starts to grow and engineers become more accountable.
One good technique is to query the built-in view:
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYIt consists of the USER_NAME, ROLE_NAME, and even the QUERY_TEXT of query that was run in snowflake.
What we need in order to find out the number of after hours worked on a day to day basis is the START_TIME of the query that was executed. With that we can then filter by any queries run after 5PM (i.e. the 17th hour of the day), and the difference between max and min dates for each day to find out how many hours were worked after that time.
SELECT USER_NAME, ROUND(TIMESTAMPDIFF('MINUTE', MIN(START_TIME), MAX(START_TIME)) / 60, 2) AFTER_HOURS, DAYNAME(MIN(START_TIME)) DAY, MIN(START_TIME) START_DATE, MAX(START_TIME) END_DATE FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE USER_NAME = '<user_name>' AND DATE_PART('HOUR', START_TIME) > 17 GROUP BY USER_NAME, DATE_PART('DAY', START_TIME), DATE_PART('MONTH', START_TIME), DATE_PART('YEAR', START_TIME) ORDER BY MIN(START_TIME) DESC;
No comments :
Post a Comment