Snowflake query credit calculation

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)));

If you are running sequential queries, like from the web UI using "run all", and nobody else is sharing the warehouse, then execution_time * warehouse_credits_per_time = cost.

If you have a warehouse that is always queued up/running, then the cost is prorate of total_warehouse_cost * sum(query_execution_time) / total_execution_time.

If you processing is in a loop, then any one query is "free", because without it the other code would run. But if you have a loop then you are caring about latency, or reducing your warehouse size, auto-scaling. Thus it's not really free..

So both the first to methods are actually the same thing, which you have to prorate the time.

For our processing most of it in a loop, so we are looking to reduce/manage latency, so we watch 'long running' or 'total time' of parts of our pipeline to find things to improve. As if the SQL is running by itself, the time is the cost, and if the warehouse is running many concurrent requests, then they are "slowed down" by the N concurrency, or they are not (a free lunch), and we discount that last bucket..

The actual credit burnt for a specific query would be little bit difficult to calculate because of various factors , you can reach some what closure with the elapsed time calculation

select sum(TOTAL_ELAPSED_TIME),WAREHOUSE_SIZE from query_history
where QUERY_TEXT='select * from query_history' -- Your Query
and WAREHOUSE_NAME='XXXXXX' -- replace Your WH name 
and USER_NAME='XXXXXX'-- Replace your User Name
group by WAREHOUSE_SIZE

With this elapsed time and based on some assumption

1. Size of the warehouse was consistent during various execution
2. Warehouse credits also burnt based on the auto-suspend setting ( if execution time is 30 sec you have to pay for 5 minutes, if the auto-suspend is set as 300 sec)
3. As suggested above post, it will also shared the credit usage if multiple user is sharing the Warehouse at the same time for different query execution
4. During Query execution whether result is getting fetched from catch or remote storage

If above pointers are known to you calculate the total credit spent specific to the warehouse size, sum that up

Thanks - Palash Chatterjee