BigDataDave
Published in

BigDataDave

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.

Now if I create a new warehouse named ‘New_WH’ I will immediately start using it in my session context.

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.

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 ‘$’.

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

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
David A Spezia

David spends his time working with Web-Scale Data Applications built on Snowflake with the largest tenants on the Snowflake Data Cloud.