Join us on July 16th as we explore real-world Reinforcement Learning Learn more

Scaling snowflake virtual warehouse as a part of recipe

Level 1
Scaling snowflake virtual warehouse as a part of recipe

Hello,

just a suggestion for some improvement towards Snowflake integration in DSS. It would be nice to have opportunity to dynamically scale up or down the warehouse as a part of the visual recipes. Because there might be some heavy joins or other operations which require more computation power than rest of the flow. 

Currently I do workaround for this and define the scaling as separate steps in scenario but it also means I have to divide the scenario into more smaller steps.

I think it could be part of advanced tab of the recipe where you would just call Snowflake command for ALTER WAREHOUSE and just scale it up for that one recipe. Of course it would be also nice to have an opportunity to call some post recipe command to scale it back down or do it automatically. 

Cheers,

Tomas

 

 

9 Replies
Dataiker
Dataiker

Hi Thomas,

Thanks very much for the suggestion. I'm the product manager looking after our Snowflake integrations. 

Indeed this is something I have on my radar and started to discuss with Snowflake. We do want to eventually help our users optimize the cost of compute although not sure quite yet how. 

If you can spare some time I'd be interested with following up with you directly?

Thanks

JC 

Level 2

Hi, I support this topis as we are facing the same issue. Running a pre-SQL or post-SQL would be very welcome (similar to what is possible when using Impala engine). Currently the only way is to use code/SQL recipe.

Dan

0 Kudos
Dataiker
Dataiker

Thanks @dan_fejtek . Quick question for you both: if you are already changing the DW size with code in your flows. How do you manage concurrent execution of flows and the possible conflict between 2 flows trying to resize? 

For context, Snowflake recommended we do not resize the DW in the flow, but allow to switch to different DW for each recipe. 

Thoughts?

JC

0 Kudos
Level 1

Having some issue with my account and I cannot login to that account I created yesterday so I create a new one 🙂 

Yes, having two flows trying to resize the same warehouse could be an issue. Now It is not a problem for me because I am using a separate connection per project or in case of same connection is used by multiple projects then they run one after another and not side by side. Each connection uses a different warehouse so in case of scaling I scale the warehouse just for that one flow.
 
But we are also preparing another project which contains tens of projects with complex dependencies between them and there might happen that two flows would try to resize the same warehouse at the same time. Here I agree it will be much better to allow to switch to a different DW for each recipe than allow scaling because it could cause problems.
 
As far as I know each recipe is standalone snowflake session so allowing developer to overwrite defined warehouse should work. How to do it? I can see multiple options. As @dan_fejtek stated, it could be pre/post-SQL. It could be also an option on advanced tab of recipe (similarly it is done for defining some options for spark or impala).
I can see another place - where you define the computation engine for the recipe - there is that small gear icon. After opening it there might be possibility to change WH for this session.
 
And because each recipe is standalone session, developers do not have to return it back. Meaning that next recipe will again use default warehouse defined on connection level unless the developer would change it again to something else. But I am not UI/UX specialist 🙂 They will probably know better where to place such option 🙂 
 
Coming back to following up with me directly - no problem with that. You can reach me anytime. Actually I had a call with some of your colleagues (Walter and Daniel) yesterday. It was about this: https://medium.com/@tomas.sobotik/pitfalls-when-using-snowflake-in-dataiku-dss-f26e890c8967
 
There I wrote down some other pitfalls I have faced during last year. They told me that you should already have that article in your slack and you are trying to solve some of them 🙂 They suggested me to use this community to submit other ideas/comments so that's why I have created this topic 🙂
 
Cheers!
Tomas
 
Dataiker
Dataiker

Thanks Tomas. Yes, I've seen your blog 🙂 

Level 2

@JCR One more thought still, which is relevant for using different warehouse sizes. At the moment DSS will not allow you to use "In database (SQL)" engine if all source and target datasets of a recipe do not use the same DSS connection. In a sense DSS forces the developers to use a single DSS connection for the whole Snowflake DB.

Since the warehouse is defined on DSS connection, I can imagine that changing the warehouse might be done also by allowing to use the SQL engine for different DSS connections - if the connections share the same snowflake account and user (which means they have the same privileges in source and target connections).

The warehouse to use could then be picked from any source or target of the recipe connections.

For example: staging connection points to STAGING schema, using S warehouse, analytics connection pointing to ANALYTICS schema using XL warehouse. Now when you have a recipe loading data from staging to analytics you could pick, if you want to use the pre-defined S or XL warehouse.

Dataiker
Dataiker

Absolutely! The concept of connection for Snowflake is quite different from traditional databases. My sense is we need to revisit how we define connections to Snowflake in order to allow the enhancements discussed in this thread. We're looking into this. 

Level 2

That is actually my aim - not to use "alter warehouse" but "use warehouse" - we plan to use pre-defined warehouses of different sizes for different jobs.

Alter warehouse is in my opinion not really usable in concurent flows (using the same WH), only in sequential jobs, which however might also be a valid case. So perhaps leaving it to the developer and just enable pre/post-SQL would work just fine.

Level 2

@JCR just found one very interesting workaround for running a recipe with a specific warehouse (different than the connection default one). When you setup recipe with all sources and target, pick "in database (sql)" engine, you can go to output dataset, settings->advanced tab and there you have "Pre-write statements" and "Post-write statements" fields, where you actually CAN put "use warehouse xxx" and quickly testing this, it seems to be working.