Bring your towel and join one of the fastest growing data communities. Welcome to our second-generation open source orchestration platform, a completely rethought approach to dataflow automation.

Prefect Community

hello team prefect! We are reviewing snowflake task and are finding a number of limitations. Having to connect each time creates quite a bit of overhead and we are unable to run multisql statements as per <https://docs.snowflake.com/en/user-guide/python-connector-api.html#>

Hi Arkady, thank you for the feedback! That's a solid point-- do you mind creating a Github issue for the improvements you'd like? That way we can track and prioritize enhancements.

keep in mind that creating a connection each time does fall inline with tasks being idempotent units of execution.
However you can certainly look into just using the task decorator in a function to get what you need.

We have a roadmap item that we are hoping to address this quarter which will make it possible to share objects like connections among different tasks, as long as you use an executor that supports a shared memory space. Hope to update with more details soon!

<@UM6AJ5V7G> thanks - agree, we are working on reducing overhead for tasks and connect/disconnect times. As per <@UKTUC906M> it would help to have shared object for connection among different tasks.

<@UKTUC906M> second part of this is more important as it allows multiple sql statements to be executed on a single call. Current implementation uses snowflake *`executed = cursor.execute(query, params=data`)* line 101 in task code. Based on that call only a single sql statement will execute. Snowflake API allows to pass multiple sql statements as part of a single call.

Hi <@UURBM3DQF> - we don’t use Snowflake at Prefect, so I’m not too familiar with their Python API (these tasks were written by members of the Prefect community!). However, it sounds like what you’re describing would certainly be an improvement. To make the change immediately, you can just copy / paste this code into your own custom task class and adjust that line as necessary. If it turns out to be straightforward, we’d definitely welcome that change as a PR so others could benefit as well!