Have you ever wanted to CREATE a warehouse in Snowflake, but not immediately use it? This can be frustrating default behaviour if you are setting up an account for hundreds of warehouses based on a permission and chargeback model. This will turn on a warehouse for a while that you do not intend to immediately use, thus consuming credits as well. My favourite customer had this problem too, and I needed to solve it for them.
CREATE warehouse in Snowflake default behaviour immediately uses the newly created warehouse for the next SQL statement. I am currently using the ‘Analytics_WH’ in my session context and this can be checked by running some SQL as well as in the UI.
--Doing this with SQL select current_warehouse();
Now if I create a new warehouse named ‘New_WH’ I will immediately start using it in my session context.
--Create a New Warehouse create warehouse new_wh with initially_suspended=true; select current_warehouse();
SQL for Create Not Use
We can set a session variable called ‘current_wh’ to preserve the original warehouse context before running the create command.
--Setting Context Back with a Session Variable set current_wh = (select current_warehouse()); select $current_wh;
Now if we set back the context to $current_wh we will first see an error: ‘SQL compilation error: syntax error line 1 at position 14 unexpected’. To get around this error we must use the identifier literal function https://docs.snowflake.net/manuals/sql-reference/identifier-literal.html. With this level of protection in Snowflake willy-nilly strings will not be confused for object names, and we can leverage our session variables with ‘$’.
--Create a New Warehouse create or replace warehouse New_WH with initially_suspended=true; select current_warehouse(); --SQL compilation error: syntax error line 1 at position 14 unexpected '$current_wh'. use warehouse $current_wh; --https://docs.snowflake.net/manuals/sql-reference/identifier-literal.html use warehouse identifier($current_wh); select current_warehouse();
Stored Procedure Time
I don’t always want to write all this SQL to CREATE a warehouse without USE. Now let’s create a stored procedure that will take a string and create a warehouse for it. The most important line here is ‘Execute as Caller’, if you execute as owner (default) you will get this error: ‘Execution error in stored procedure SP_CREATEWAREHOUSE: Stored procedure execution error: Unsupported statement type ‘USE’. At Statement.execute, line 6 position 8’.
Now you can leverage this stored procedure to create warehouses without turning them on. Of course the caller needs permission to create warehouses. Create those warehouses without using them…yet. Happy Querying!