How to reliably compare query run times?

Solution 1:

There are two cache's that you have control over. The query result cache can be disabled with the following:

ALTER SESSION SET USE_CACHED_RESULT = FALSE ;

The other cache that you can control is the data cache - sometimes also called the warehouse cache. This can be cleared by suspending and resuming the warehouse:

ALTER WAREHOUSE MY_WAREHOUSE SUSPEND ;
ALTER WAREHOUSE MY_WAREHOUSE RESUME ;

Finally there is the metadata cache, however, this is held in the global services layer and to the best of my knowledge there is no way to clear this or to instruct Snowflake not to use it.

For comparing queries that are supposed to do the same thing it is often a good idea to check the query execution plan since databases in general are quite smart and you might find they compile to exactly the same plan.