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.
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?
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.
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.
Having some issue with my account and I cannot login to that account I created yesterday so I create a new one 🙂
@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.
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.
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.
@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.