Find credits used per database and schema in Snowflake

user_19571313 0
Viewed 72

I am trying to find a way to see how many credits a query from account_usage.access_history or account_usage.query_history has used. I see there's a table warehouse_metering_history, but it shows credits used per warehouse and I would like to go further than that.

Is there any way to achieve this?

snowflake-schema snowflake-cloud-data-platform sql
1 Answers

the warehouse metering history is providing information on how many credits a warehouse consumed in an hour. Together with the Query History account usage view you could do the following: Create a CTE querying the Query_History and use the start_time of a query and extract the date and hour portion out of it (e.g. START_HOUR). For each START_HOUR and warehouse calculate how much all queries took in terms of milliseconds to run (e.g. Duration_Seconds_Total_Warehouse_Hour). You then divide each queries runtime for that START_HOUR and warehouse by the Duration_Seconds_Total_Warehouse_Hour time and get the proportion each query used this warehouse for a specific date and hour. (e.g. Query_Consumption_Portion) You can then join this CTE-result with the warehouse_metering_history by warehouse_id and Start_Time and multiply the Query_Consumption_Portion of the CTE with the CREDITS_USED used column of the warehouse_metering_history. You would not be 100% accurate, especially if queries span over multiple hours, but the result would still be pretty accurate.

Here is my sample code:

WITH PRE_QUERY_PARTIAL_TIMES AS
    (SELECT
        START_TIME,
        END_TIME,
        TIMESTAMPDIFF('millisecond', START_TIME, END_TIME) AS Duration_Query,
        SUM(TIMESTAMPDIFF('millisecond', START_TIME, END_TIME)) OVER (PARTITION BY TO_TIMESTAMP(TO_VARCHAR(START_TIME, 'YYYY-MM-DD hh') || ':00:00'), WAREHOUSE_ID ORDER BY TO_TIMESTAMP(TO_VARCHAR(START_TIME, 'YYYY-MM-DD hh') || ':00:00')) AS Duration_Seconds_Total_Warehouse_Hour,
        TO_TIMESTAMP(TO_VARCHAR(START_TIME, 'YYYY-MM-DD hh') || ':00:00') AS START_HOUR,
        Duration_Query/Duration_Seconds_Total_Warehouse_Hour AS Query_Consumption_Portion,
        QUERY_ID,
        WAREHOUSE_ID
    FROM "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY"
    WHERE WAREHOUSE_ID IS NOT NULL)
SELECT QPT.START_TIME,
    QPT.END_TIME,
    QPT.Duration_Query,
    QPT.Duration_Seconds_Total_Warehouse_Hour,
    QPT.START_HOUR,
    QPT.Query_Consumption_Portion,
    QPT.QUERY_ID,
    WHM.WAREHOUSE_NAME,
    WHM.CREDITS_USED,
    WHM.START_TIME AS START_TIME_WH,
    WHM.END_TIME AS END_TIME_WH,
    WHM.CREDITS_USED*QPT.Query_Consumption_Portion AS Credits_Consumed_By_Query
FROM PRE_QUERY_PARTIAL_TIMES AS QPT
INNER JOIN "SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY" AS WHM
    ON QPT.WAREHOUSE_ID = WHM.WAREHOUSE_ID
    AND QPT.START_HOUR = WHM.START_TIME;

Best regards,

TK

user_18664730
Related