Data Bits : Thoughts on tuning SQL in Snowflake

Overview

Tuning SQL is a necessary part of providing a performant system. Snowflake's Query History UI is a powerful tool to assist with this, especially the Query Profile. However, sometimes it is helpful to run different versions of the SQL, or perhaps with different data volumes, and compare execution results. Then you can utilize account_usage.query_history to easily do a side-by-side comparison of the results.

The below reminders may make the results easier to work with:

Do not use cached results

While the utilization of cached results from an earlier execution of the same SQL is a strength of Snowflake, when you are trying to test different scenarios you want to make sure you are not using this. Just execute the following SQL to not leverage this functionality during testing:

ALTER SESSION SET USE_CACHED_RESULT = FALSE;        

Tag your queries

Identifying the SQL you want to compare in account_usage.query_history is easier if you tag the queries. Depending on your testing scenario you may want to use multiple tags to identify different SQL or different data volumes. To utilize this functionality execute this following SQL before your test query:

ALTER SESSION SET QUERY_TAG='YourQueryTagHere';        

Compare results

Finally, you need to compare the results from account_usage.query_history (Do not forget about the lag that usually occurs for SQL to appear here!). Below is a starting query for research but you may tweak it to meet you own needs:

SELECT query_id
     , total_elapsed_time/1000 AS elapsed_time_seconds
     , partitions_scanned
     , partitions_total
     , warehouse_size 
     , warehouse_type
     , query_tag
     , rows_produced
     , execution_time
     , bytes_spilled_to_local_storage
     , bytes_spilled_to_remote_storage
FROM snowflake.account_usage.query_history qh 
WHERE 1=1 
AND error_code IS NULL 
AND query_tag LIKE 'YourQueryTagHere' 
ORDER BY query_tag, total_elapsed_time DESC 
LIMIT 100;        

Hopefully this is useful to get the maximum performance out of your Snowflake queries!