Support opt-in dbt-managed SQL Server transactions

[axellpadilla]

Problem

dbt-sqlserver currently opens SQL Server connections with driver autocommit=True, and adapter-level add_begin_query() / add_commit_query() are no-ops.

This has been the adapter behavior for a long time. There is historical commit context showing autocommit was enabled to mitigate “weird behavior,” and later BEGIN TRANSACTION / COMMIT TRANSACTION were removed for autocommit. However, I could not find a more detailed design note explaining whether the current no-op transaction model is still required.

This means dbt’s transaction flow is mostly logical for this adapter. “Inside transaction” hooks and adapter.commit() do not necessarily map to a real SQL Server transaction unless a macro explicitly emits BEGIN TRANSACTION / COMMIT TRANSACTION.

Proposal

Add an opt-in behavior flag option for dbt-managed SQL Server transactions to go back to dbt standard en enable correct transaction handling, for example:

dbt_sqlserver_use_dbt_transactions: false

Default should remain false, preserving current behavior.

When disabled:

• keep add_begin_query() / add_commit_query() as SQL no-ops
• preserve existing adapter behavior

When enabled:

• make add_begin_query() emit BEGIN TRANSACTION
• make add_commit_query() emit COMMIT TRANSACTION
• add/verify rollback behavior, preferably guarded with IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

Why

Autocommit itself is not the only problem. The bigger issue is that dbt’s begin/commit hooks are currently fictitious at the SQL Server level.

An opt-in explicit transaction mode would let us evaluate real dbt transaction semantics without changing behavior for existing projects.

Suggested scope

• Add a new option, e.g. dbt_sqlserver_use_dbt_transactions, defaulting to false.

• When dbt_sqlserver_use_dbt_transactions: true, implement:

  • add_begin_query() -> BEGIN TRANSACTION
  • add_commit_query() -> COMMIT TRANSACTION
  • rollback behavior -> IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

• Add tests for:

  • default mode unchanged
  • no duplicate BEGIN TRANSACTION in normal materialization flow
  • regular pre/post hooks inside a transaction
  • table materialization success/failure
  • incremental materialization success/failure
  • rollback/cleanup after failed model builds
  • subsequent model can run after a failed model
  • explicit transaction SQL in user hooks/macros is either supported or clearly documented as unsupported in this mode

Acceptance criteria

• Existing behavior remains unchanged by default.
• New opt-in mode emits real SQL Server transaction statements through dbt’s adapter transaction hooks.
• Functional tests cover success, failure, rollback/cleanup, and hook behavior.