Snowflake compute costs by QUERY TYPE

user_16813269 0
Viewed 268

I could find a valid list of query types that generate compute costs on Snowflake. Would you happen to know accurately which ones do generate cost please ?

Here is a list that i was able to extract from our execution logs : (table QUERY_HISTORY)

  • ALTER
  • ALTER_ACCOUNT
  • ALTER_NETWORK_POLICY
  • ALTER_PIPE ALTER_SESSION
  • ALTER_TABLE
  • ALTER_TABLE_ADD_COLUMN
  • ALTER_TABLE_DROP_COLUMN
  • ALTER_TABLE_MODIFY_COLUMN
  • ALTER_USER
  • ALTER_USER_RESET_PASSWORD
  • ALTER_VIEW_MODIFY_SECURITY
  • ALTER_WAREHOUSE_RESUME
  • ALTER_WAREHOUSE_SUSPEND
  • BEGIN_TRANSACTION
  • COMMIT
  • COPY
  • CREATE
  • CREATE_CONSTRAINT
  • CREATE_EXTERNAL_TABLE
  • CREATE_NETWORK_POLICY
  • CREATE_ROLE
  • CREATE_SEQUENCE
  • CREATE_STREAM
  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • CREATE_TASK
  • CREATE_USER
  • CREATE_VIEW
  • DELETE
  • DESCRIBE
  • DESCRIBE_QUERY
  • DROP
  • DROP_CONSTRAINT
  • DROP_NETWORK_POLICY
  • DROP_ROLE
  • DROP_STREAM
  • DROP_TASK
  • DROP_USER
  • EXPLAIN
  • EXTERNAL_TABLE_REFRESH
  • GET_FILES
  • GRANT
  • INSERT
  • LIST_FILES
  • MERGE
  • PUT_FILES
  • REMOVE_FILES
  • RENAME_COLUMN
  • RENAME_SCHEMA
  • RENAME_TABLE
  • RENAME_VIEW
  • RESTORE
  • REVOKE
  • ROLLBACK
  • SELECT
  • SET
  • SHOW
  • TRUNCATE_TABLE
  • UNKNOWN
  • UNLOAD
  • UNSET
  • UPDATE
  • USE

thanks

snowflake-cloud-data-platform sql
3 Answers

All operations generate a cost in some way. Operations that do not use a warehouse are usually run under the cloud services layer

Snowflake give you free CSL cost up to 10% of your compute costs. Over that and they start charging you for it

user_141005830

Metadata operations such as ALTER TABLE do not consume "compute costs", but I do not have a full list. I think you can use the same approach to find the query types to consume WH credits by checking the warehouse size:

select distinct query_type from snowflake.account_usage.query_history
where WAREHOUSE_SIZE is not null;
user_125509650

Here's a SF query that tells you compute costs for the top 15 query types over a date range.

select query_type
    , count(*) freq
    , sum(CREDITS_USED_CLOUD_SERVICES) as total_CS_CREDITS
from snowflake.account_usage.query_history
where 1=1
and start_time = :daterange
group by 1
order by 3 desc
fetch 15 rows;
user_6018550
Related