[CT-2624] [Spike] Scrutinize and clarify our approach and interface for transactions

[dataders]

Is this your first time submitting a feature request?

• [X] I have read the expectations for open source contributors
• [X] I have searched the existing issues, and I could not find an existing issue for this feature
• [X] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

https://github.com/dbt-labs/dbt-redshift/pull/475 and related issues have shaken my confidence that I could explain how dbt issues and handles transactions in ideal circumstances.

I propose that we refine the interface by which we create and issue transactions to make their usage clearer. As the result of this work may very well introduce breaking changes, it feels v2.0 might be an appropriate milestone for this spike and work.

two opportunities:

• test explicitly for transaction behavior that we and users expect to see
• pivot away from using the SQL API to begin and commit transactions as is done today in the run_hooks macro, and instead rely on the doing this in the python ConnectionManager class.

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

[VersusFacit]

Great followup item! Right now Snowflake and Redshift are set autocommit=on by default. As for default behavior of dbs and their profile options, it's unclear as to what's supported.

[github-actions[bot]]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions[bot]]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.