Strictly from a data engineering perspective, transactions should be representative of one unit of work. Now if we take that concept into the Prefect world, where tasks can fail and be retried, I personally think splitting a transaction across multiple retry-able pieces of logic doesn’t make a ton of sense.
Could you refactor your tasks so that each task handles a transaction? You can still get execution order guaranteed by Prefect handling the dependencies between tasks.
I’d think that a new connection per task makes a lot of sense, since tasks are assumed to have isolated execution environments. If you want to share the connection across multiple tasks, I believe you should be able to do this while using the
LocalExecutor
, but that’s more of a side-effect of how that Executor is implemented as opposed to a big feature that it supports.
EDIT: Fixed grammar