Execute these commands to set parameters for your warehouses plus additional settings on the account level. It will reduce credit usage and prevent from unexpected spikes in consumption:
ALTER WAREHOUSE xxxx SET AUTO_SUSPEND = 30;
ALTER WAREHOUSE xxxx SET STATEMENT_TIMEOUT_IN_SECONDS = 600;
ALTER ACCOUNT aaaa SET ABORT_DETACHED_QUERY = TRUE;AUTO_SUSPEND: 30 seconds instead of the default of 600 seconds (10 minutes). Will save you money instantly from the moment you execute this statement, as the warehouse will suspend earlier and not burn credits while waiting for the whole 10 minutes. A small disadvantage might be that you will lose the result cache once the warehouse is suspended.
STATEMENT_TIMEOUT_IN_SECONDS: queries that run for a long time will be cancelled, 600 means after 10 minutes. Reason why you want to set this to a value is to prevent the worst queries to run forever. If you have multiple tabs open in Snowsight, you might lose sight on tabs that have queries running. When users start complaining about queries being cancelled and optimizations are not possible, this parameter can also be set at the USER or SESSION level to create an exception.
ABORT_DETACHED_QUERY: this setting might not really be necessary if you have set your time-out at the account level, but unpleasant surprises might arise when you find out that queries have been running for 48 hours, which is the default, and users have not got a clue. Have seen this happening in PowerBI development cycles on really large datasets where PowerBI was shut down and the queries kept on running.
Any other tips for low-hanging-fruit cost savings ? Please leave a comment.