Snowflake: Is there a way to create a warehouse without using it?

Question

We're using DBT to run automated CI/CD to provision all our resources in Snowflake, including databases, schemas, users, roles, warehouses, etc.

The issue comes up when we're creating warehouses -- the active warehouse automatically switches over to the newly created one. And this happens whether or not the warehouse already exists (we're using CREATE WAREHOUSE IF NOT EXISTS commands).

This basically resumes/turns on all our warehouses for no reason (even though we're using INITIALLY_SUSPENDED = TRUE), because snowflake is then using that warehouse to execute the subsequent queries. And then our CI/CD continues on the wrong warehouse (whichever one was the last one to execute). We have a dedicated warehouse for CI/CD, and we'd like the execution to remain on that one (so we can monitor the costs).

We're aware that this is the default behavior specified in the documentation, but is there any way to create a warehouse without using it?

I wish the CREATE WAREHOUSE command had a parameter like USE_WAREHOUSE = TRUE|FALSE.

As a workaround, we're exploring ways to skip the CREATE WAREHOUSE commands entirely if the warehouse already exists, but that doesn't solve the issue for warehouses that do need to be created.

Otherwise, we might just add a USE WAREHOUSE command after every CREATE WAREHOUSE, in order to return to the original CI/CD warehouse.

Answer 1

The idea is to store current warehouse in a variable and restore it:

SET warehouse_name = (SELECT CURRENT_WAREHOUSE());

CREATE WAREHOUSE TEST WAREHOUSE_SIZE=XSMALL, INITIALLY_SUSPENDED=TRUE;

USE WAREHOUSE IDENTIFIER($warehouse_name);

---

Alternatively wrapping it with stored procedure(simplified version - no error handling and only warehouse name provided as parameter):

CREATE OR REPLACE PROCEDURE create_warehouse(CURRENT_WAREHOUSE_NAME STRING
                                            ,WAREHOUSE_NAME STRING)
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
 var rs = snowflake.execute({sqlText: `CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER(?) WAREHOUSE_SIZE=MEDIUM, INITIALLY_SUSPENDED=TRUE`, binds:[WAREHOUSE_NAME]});   
 // restore original warehouse, USE WAREHOUSE cannot be used inside SP
 var rs2 = snowflake.execute({sqlText:`CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER(?)`, binds:[CURRENT_WAREHOUSE_NAME]});   
 return 'Done.';
$$;

CALL create_warehouse(CURRENT_WAREHOUSE(), 'TEST');