v1.12.0

[axellpadilla]

dbt-sqlserver v1.12.0

Together with @Benjamin-Knight and @joshmarkovic, we used the releases leading up to v1.11.0 to bring the adapter closer to current dbt-core versions while fixing several bugs along the way.

The v1.12.x release series formalizes that work and addresses the deprecations and behavior changes required to align the adapter with upcoming dbt-core v2.0 behavior (including support for the v2 parser). Please leave any feedback or insights on the GitHub Discussions page!

---

⚠️ Important Behavior Changes

Review these items before upgrading, as several default settings have changed:

• pyodbc is no longer installed by default:
  You must explicitly install and configure a backend. Use mssql-python for standard setups or the experimental ADBC backend for future compatibility testing.
• dbt_sqlserver_use_dbt_transactions defaults to True:
  dbt-managed transaction hooks now issue explicit BEGIN TRANSACTION / COMMIT TRANSACTION statements. Failed model executions roll back automatically instead of leaving partial state.
  • Legacy Option: Set to False in dbt_project.yml to retain legacy autocommit behavior (Deprecated; will be removed in a future release).
• dbt_sqlserver_use_native_string_types defaults to True:
  String type mappings are now:
  • STRING → VARCHAR(MAX)
  • NCHAR → NCHAR(1)
  • NVARCHAR → NVARCHAR(4000)
  • Legacy Option: Set to False to preserve legacy VARCHAR(8000) and CHAR(1) mappings (Deprecated; will be removed in a future release).
• dbt_sqlserver_use_default_schema_concat defaults to True:
  Custom schemas now concatenate with target.schema, matching dbt-core's default generate_schema_name macro behavior.
  • Legacy Option: Set to False to retain legacy behavior (Deprecated; will be removed in a future release). Alternatively, override sqlserver__generate_schema_name for custom schema handling (#800).
• Experimental ADBC backend available:
  Configure with backend: adbc. Requires installing the dbt-sqlserver[adbc] extra and the ADBC driver via the dbc CLI. Currently supports SQL Server username/password authentication (#771).

---

🛠️ Other Compatibility Changes

• sql_header is deprecated and rejected: Use pre_hook or query_options instead.
• Standardized identifier quoting: Adapter-generated identifiers now consistently use adapter.quote(). Review custom macros or downstream packages expecting bracket-style SQL formatting ([schema].[table]).
• Dependency requirements updated: Requires dbt-core >= 1.12.0 and dbt-adapters >= 1.24.5.
• Python 3.14 support: Added to the build and testing matrix.

---

📋 Upgrade Checklist

Before deploying this release to production environments:

1. Backend Selection: Choose and install mssql-python or adbc if relying on implicit pyodbc.
2. Transactions: Verify projects/hooks aren't relying on implicit autocommit state.
3. String Columns: Check downstream systems/views affected by VARCHAR(MAX) or NVARCHAR(4000) type shifts.
4. Schemas: Confirm custom schema names align with core concatenation rules or override the schema macro.
5. Custom SQL/Macros: Check for assumptions around SQL bracket quoting or sql_header.

---

What's Changed

• feat: dbt-core 1.12 compatibility, Python 3.14 support, and transaction-safety fixes by @axellpadilla in feat: dbt-core 1.12 compatibility, Python 3.14 support, and transaction-safety fixes #772
• Add workflow to sync main branch in preparation for default branch migration by @axellpadilla in Add workflow to sync main branch in preparation for default branch migration #781
• Raise compiler error for sql_header config with redirect to pre_hooks/query_options by @axellpadilla in Raise compiler error for sql_header config with redirect to pre_hooks/query_options #777
• Add experimental ADBC backend by @axellpadilla in Add experimental ADBC backend #783
• ci: run unit and integration tests on release branches by @axellpadilla in ci: run unit and integration tests on release branches #797
• fix: concurrent-incremental catalog deadlock, and (nolock) on the remaining catalog lookups by @axellpadilla in fix: concurrent-incremental catalog deadlock, and (nolock) on the remaining catalog lookups #792
• fix: drop the model's outbound foreign keys too (Macro drop_fk_constraints() only drops incoming FKs, ignoring outgoing FKs #632) by @axellpadilla in fix: drop the model's outbound foreign keys too (#632) #793
• fix: one identifier quoting style, and the unquoted identifiers underneath it (Inconsistent identifier quoting: a few macros hand-format [brackets] while relation rendering already uses "double quotes" #785, Database error when using slash in schema name #409) by @axellpadilla in fix: one identifier quoting style, and the unquoted identifiers underneath it (#785, #409) #795
• Bump version to 1.12.0rc2 by @axellpadilla in Bump version to 1.12.0rc2 #799
• fix: compare view body exactly instead of suffix-matching the whole definition by @Benjamin-Knight in fix: compare view body exactly instead of suffix-matching the whole definition #803
• feat: add denies config so object-level DENY survives a rebuild by @Benjamin-Knight in feat: add denies config so object-level DENY survives a rebuild #802
• feat: apply data masks on the full_refresh_build=prebuilt path by @Benjamin-Knight in feat: apply data masks on the full_refresh_build=prebuilt path #804
• Fix/undrained probe cursor rollback by @Benjamin-Knight in Fix/undrained probe cursor rollback #810
• chore: replace mypy with ty for type checking by @joshmarkovic in chore: replace mypy with ty for type checking #784
• feat: default dbt_sqlserver_use_default_schema_concat to True by @axellpadilla in feat: default dbt_sqlserver_use_default_schema_concat to True #811
• feat: add sqlserver__openquery macro and SQL Server best-practices guide by @axellpadilla in feat: add sqlserver__openquery macro and SQL Server best-practices guide #790
• fix: quote openquery's server name via adapter.quote() instead of hand-formatted brackets by @axellpadilla in fix: quote openquery's server name via adapter.quote() instead of hand-formatted brackets #816

Full Changelog: v1.11.1...v1.12.0rc3

---

This discussion was created from the release v1.12.0rc1.

[axellpadilla]

One of the serious issues dbt transactions model leaves on sql server is contention when reading the catalog and deadlocks, one of the paths that causes this is delete insert process, does anyone sees a fix to this? using the other model will require more changes on upstream for v2 but I hope there is a solution, like using #temp tables where needed.

[axellpadilla]

#792 should fix most of the issues, also, after deep research, it looks like dbt-core transaction is still in progress for v2, most adapters haven't fix these issues and this will be one of some that can trully support in-transaction hooks

[axellpadilla]

@Benjamin-Knight @joshmarkovic please make a quick review of the transaction and quoting flow fixes, already testing live using branch, way better even with transactions on

[axellpadilla]

Fixes in https://github.com/dbt-msft/dbt-sqlserver/releases/tag/v1.12.0rc2