3

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

Improve this question

5 Answers 5

Reset to default
5

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)));
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is technically correct but it does not reflect the full picture. Say, if I have 4 queries that are all run under 1 minute using the same warehouse, then QUERY_HISTORY_BY_USER will treat it as 4 separate queries. Thus, the cost here is 4x larger. I ended up using WAREHOUSE_METERING_HISTORY instead.
1

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..

Improve this answer

Comments

1

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

Improve this answer

Comments

0

Attribute cost for queries:

You can use the QUERY_ATTRIBUTION_HISTORY view to attribute cost based on queries. The cost per query is the warehouse credit usage for executing the query. This cost does not include any other credit usage that is incurred as a result of query execution. For example, the following are not included in the query cost:

  • Data transfer costs
  • Storage costs
  • Cloud services costs
  • Costs for serverless features
  • Costs for tokens processed by AI services

For queries that are executed concurrently, the cost of the warehouse is attributed to individual queries based on the weighted average of their resource consumption during a given time interval.

The cost per query does not include warehouse idle time. Idle time is a period of time in which no queries are running in the warehouse and can be measured at the warehouse level.

QUERY_ATTRIBUTION_HISTORY view:

SELECT query_id,
       credits_attributed_compute,
       credits_used_query_acceleration
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
WHERE query_id = <query_id>;
Improve this answer

Comments

0

I wrote below query which will find aggregation based on Warehouse, Database, Users ,their Client IP and sum of query they run. Useful to find out who are the users who consuming most of the credits.

show warehouses;

WITH WAREHOUSE_SIZES AS (
SELECT 'X-Small' AS WAREHOUSE_SIZE, 1 AS CREDITS_PER_HOUR UNION ALL
SELECT 'Small' AS WAREHOUSE_SIZE, 2 AS CREDITS_PER_HOUR UNION ALL
SELECT 'Medium' AS WAREHOUSE_SIZE, 4 AS CREDITS_PER_HOUR UNION ALL
SELECT 'Large' AS WAREHOUSE_SIZE, 8 AS CREDITS_PER_HOUR UNION ALL
SELECT 'X-Large' AS WAREHOUSE_SIZE, 16 AS CREDITS_PER_HOUR UNION ALL
SELECT '2X-Large' AS WAREHOUSE_SIZE, 32 AS CREDITS_PER_HOUR UNION ALL
SELECT '3X-Large' AS WAREHOUSE_SIZE, 64 AS CREDITS_PER_HOUR UNION ALL
SELECT '4X-Large' AS WAREHOUSE_SIZE, 128 AS CREDITS_PER_HOUR),
WAREHOUSE_USAGE AS (
SELECT 
    "name" AS WAREHOUSE_NAME,
    "size" AS WAREHOUSE_SIZE
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))  -- Result from SHOW WAREHOUSES
)
SELECT 
qh.WAREHOUSE_NAME,
wu.WAREHOUSE_SIZE,  -- Warehouse size
ws.CREDITS_PER_HOUR,  -- Credits per hour based on warehouse size
qh.DATABASE_NAME,
qh.SCHEMA_NAME,
qh.USER_NAME,
LH.client_ip, 
COUNT(*) AS ACCESS_COUNT,  -- Number of queries executed
SUM(qh.TOTAL_ELAPSED_TIME / 1000) AS TOTAL_ELAPSED_SECONDS,  -- Total elapsed time in seconds
SUM((qh.TOTAL_ELAPSED_TIME / (1000 * 60 * 60)) * ws.CREDITS_PER_HOUR) AS CREDIT_CHARGED  -- Credits charged
FROM 
SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY LH
INNER JOIN 
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY QH
ON QH.USER_NAME = LH.USER_NAME 
INNER JOIN 
SNOWFLAKE.ACCOUNT_USAGE.SESSIONS S 
ON S.SESSION_ID = QH.SESSION_ID 
AND S.LOGIN_EVENT_ID = LH.EVENT_ID
LEFT JOIN 
WAREHOUSE_USAGE wu 
ON QH.WAREHOUSE_NAME = wu.WAREHOUSE_NAME
LEFT JOIN 
WAREHOUSE_SIZES ws 
ON wu.WAREHOUSE_SIZE = ws.WAREHOUSE_SIZE
WHERE 
qh.START_TIME >= DATEADD(DAY, -30, CURRENT_TIMESTAMP)  -- Last 30 days
GROUP BY 
qh.WAREHOUSE_NAME, 
wu.WAREHOUSE_SIZE,
ws.CREDITS_PER_HOUR,
qh.DATABASE_NAME, 
qh.SCHEMA_NAME, 
qh.USER_NAME, 
LH.client_ip
ORDER BY 
CREDIT_CHARGED DESC;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.