Snowflake: Create Without Use Warehouse

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.

Default Behaviour
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'.

--Callable SP
--Execute as Caller
create or replace procedure SP_CreateWarehouse(WAREHOUSE String)
    returns string
    language javascript
    strict
    execute as caller
    as
    $$
    var get_wh_sql = `SET CURRENT_WH = (SELECT CURRENT_WAREHOUSE());`;
    var create_wh_sql = `CREATE OR REPLACE WAREHOUSE ` + WAREHOUSE + ` WITH INITIALLY_SUSPENDED = TRUE;`;
    var use_wh_sql = `USE WAREHOUSE IDENTIFIER($CURRENT_WH);`;
    try {
            snowflake.execute ( {sqlText: get_wh_sql} );
            snowflake.execute ( {sqlText: create_wh_sql} );
            snowflake.execute ( {sqlText: use_wh_sql} );
        return "Succeeded.";   // Return a success/error indicator.
        }
    catch (err)  {
        return "Failed: " + err;   // Return a success/error indicator.
        }
    $$;
 
call SP_CreateWarehouse('CURT_WH');
 
select current_warehouse();

Conclusion
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!

Originally published at http://bigdatadave.com on February 26, 2020.