Snowflake query credit calculation

Question

One of my user has asked if it is possible to calculate the credit burnt for executing a particular query in snowflake. Based on my understanding I think it is not possible because the credit burnt is at the warehouse level and not at query level. But I still thought if someone has a way to calculate the credit per query.

Thanks

Answer 1

I ended up writing a query as below

SELECT query_id
     ,warehouse_name
     ,start_time
     ,end_time
     ,total_elapsed_sec
     ,case
            when total_elapsed_sec < 60 then 60 
            else total_elapsed_sec
      end as total_elapsed_sec_1
     ,ROUND(unit_of_credit*total_elapsed_sec_1 / 60/60,2)  total_credit
     ,total_credit*3.00 query_cost --change based on how much you are paying for a credit
FROM (
  select query_id
     ,warehouse_name
     ,start_time
     ,end_time
     ,total_elapsed_time/1000   total_elapsed_sec
     ,CASE WHEN warehouse_size = 'X-Small'    THEN 1
             WHEN warehouse_size = 'Small'      THEN 2
             WHEN warehouse_size = 'Medium'     THEN 4
             WHEN warehouse_size = 'Large'      THEN 8
             WHEN warehouse_size = 'X-Large'    THEN 16
             WHEN warehouse_size = '2X-Large'   THEN 32
             WHEN warehouse_size = '3X-Large'   THEN 64
             WHEN warehouse_size = '4X-Large'   THEN 128
       ELSE 1    
       END unit_of_credit
  from table(information_schema.QUERY_HISTORY_BY_USER
             (user_name => 'USERNAME', 
              END_TIME_RANGE_START => dateadd('hours',-1,current_timestamp()), --you can manipulate this based on your need
              END_TIME_RANGE_END => current_timestamp(),RESULT_LIMIT => 10000)));