Hi All, Been searching around for a solution to this but wondering if anyone has similar use cases and any advice on what they are doing to solve this. We use prefect a lot for managing DBT operations, essentially an ECS task that executes a DBT job on snowflake. for the most part it works well - except for a few instances We have cases where a job takes longer to execute, for one reason or another, than the time allotted before the next scheduled run.. in this case, we can have many instances of the same run begin to pile up, and that leads to a lot of manual management we would like to avoid.. so basically cancel a scheduled run if there is an instance of it that still is not in the completed state. In a similar vein of flow management, we may have 1-off dbt runs, and if one of these is executed manually, we want to be able to pause these sets of cron jobs until this 1-off run is finished. This would really tidy up our infra so excited to hear about solutions to this! The only other minor issue we are dealing with is a cancelled DBT run does not actually cancel the underlying snowflake queries, so wondering what strategies people have for more graceful exits in the event of cancels. Right now we have to rely on manually cancelling those queries in snowflake themselves Thanks in advance!

If you are using prefect cloud then perhaps you could pause the schedule (through an automation) at the start of a run and resume it at the end

That is an interesting thought 🤔 thanks, will look into docs surrounding that see if it could work

You might be able to use a hook on cancellation to tell Snowflake to abort all the queries for the user

ALTER USER [ IF EXISTS ] [ <name> ] ABORT ALL QUERIES

@Sean Williams This tends to be the manual operation we use, only issue with this that adds some complexity is a warehouse may be running more than just 1 thing at a time, so there is the potential that aborting all queries also causes runs to fail that are still in a proper running state. I do appreaciate the advice on state change hooks, the ideal solution probably would have to incorporate those in some way

That shouldn't impact other queries using that warehouse, if they've been run by a different user. If they're all being run by the same user, yeah that won't be ideal.

oooh separating it out by user, thats interesting. Our current prefect implementation has the same user but maybe changing that could be part of the solution