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.

v1.12.0 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). It also reworks where transactions begin and end around a build, so a model no longer blocks other sessions' metadata reads while it loads. Please leave any feedback or insights on the GitHub Discussions page! #788

⚠️ 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: dbt-sqlserver[mssql] (mssql-python) for standard setups, dbt-sqlserver[pyodbc] to keep pyodbc, 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. dbt run-operation commits when the macro finishes cleanly (#862), and unit tests no longer leave a __dbt_tmp table behind (#874).
  • Legacy Option: Set to False in dbt_project.yml to retain legacy autocommit behavior (Deprecated; will be removed in a future release).
• Transaction boundaries moved to stop Sch-M lock blocking (#819):
  On table, incremental and snapshot, the new table is created empty and committed first, then loaded with INSERT ... WITH (TABLOCK), so a build no longer blocks catalog scans, other dbt runs or SSMS for the length of the load. The transaction now covers the in-transaction pre-hooks, the load, the cutover, masks and the in-transaction post-hooks. Index creation, grants, denies and persist_docs run after the commit.
  • Post-hooks now run before index creation. A transaction: true post-hook that needs the indexes should declare transaction: false.
  • Indexes created in post-hooks are dropped in the same run when drop_unmanaged_indexes: true. Move them to the indexes config.
  • New pre_hook_transaction_scope config (load | build, default load): a transaction: true pre-hook that creates an object the model reads now fails with Invalid object name. Declare that hook transaction: false, or set pre_hook_transaction_scope: build (which holds Sch-M for the load, as before).
  • Compiled SQL shows the create and the load as separate batches. A crashed run can leave a __dbt_tmp intermediate, which the next run drops automatically.
  • See docs/transaction_scope.md.
• 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).
• Column widening uses a single ALTER COLUMN (#836):
  prefer_single_alter_column now defaults to unset. Widening a column within its type (e.g. a longer varchar, or varchar(max)) runs one ALTER COLUMN, which is metadata-only on SQL Server 2022 and keeps the column's indexes, default constraint and position. Other type changes still use the four-step rewrite, which now recovers from a failed earlier attempt instead of breaking the model.
  • Legacy Option: Set prefer_single_alter_column: false to keep the rewrite for widening too.
• Clustered columnstore index naming (#578):
  The as_columnstore index is named after the final table rather than the __dbt_tmp build table. Existing tables keep their old index name until rebuilt.
• 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).

✨ New Features

• Ephemeral models are supported, except one whose SQL starts with its own WITH, which T-SQL can't nest inside a CTE (#166).
• denies config: re-applies object-level DENY after each build, so an exception carved out of a schema-level GRANT survives drop-and-recreate.
• Data masks on full_refresh_build: prebuilt: masks are no longer silently lost on that rebuild path.
• sqlserver__openquery macro for pass-through queries against linked servers, plus a SQL Server best-practices guide.
• Cheaper column probes: snapshots and contract-enforced models with a CTE-headed query read the result shape via sys.dm_exec_describe_first_result_set instead of running the query an extra time.

🛠️ Other Compatibility Changes

• sql_header is officially rejected: Use pre_hook or query_options instead.
• Standardized identifier quoting: Adapter-generated identifiers now consistently use adapter.quote(), and an embedded " is escaped by doubling. Review custom macros or downstream packages expecting bracket-style SQL formatting ([schema].[table]). Schema names containing ., " or \ now work (#785, #409).
• 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.

🔁 Also Included from v1.11.2

If you are upgrading from v1.11.1 or earlier, these fixes are new to you as well. See the v1.11.2 release for details.

• Contract constraints are now emitted (#579): primary_key, foreign_key, unique and check constraints previously never reached the database. They now do, so data that violates them fails the build, and a foreign key pointing at a model makes that model's rebuild fail with Msg 3726 unless it uses the drop_fk_constraints() pre-hook (see the README).
• Unchanged view models refresh stale metadata with sp_refreshview (#838).
• Concurrent CREATE SCHEMA no longer fails with Msg 2714 (#839).
• table_refresh_method: dml keeps its columnstore index and constraints across a schema change.
• check snapshots with check_cols work when the SQL starts with WITH (#865).
• sqlserver__array_append added (#861).
• Long-lived processes (dbtRunner, xdist workers) no longer slow down from a growing behavior flag list (#855).

📋 Upgrade Checklist

Before deploying this release to production environments:

1. Backend Selection: Install dbt-sqlserver[mssql], [pyodbc] or [adbc] if you relied on the implicit pyodbc.
2. Transactions: Verify projects/hooks aren't relying on implicit autocommit state.
3. Hooks: Check transaction: true pre-hooks that create objects the model reads, post-hooks that expect indexes to exist, and indexes created from post-hooks when drop_unmanaged_indexes: true.
4. String Columns: Check downstream systems/views affected by VARCHAR(MAX) or NVARCHAR(4000) type shifts.
5. Schemas: Confirm custom schema names align with core concatenation rules or override the schema macro.
6. Constraints (from ≤ v1.11.1): Confirm existing data satisfies contract constraints, and add drop_fk_constraints() where a foreign key targets a model.
7. Custom SQL/Macros: Check for assumptions around SQL bracket quoting, sql_header, or columnstore index names.

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

What's Changed

• feat: dbt-core 1.12 compatibility, Python 3.14 support, and transaction-safety fixes by @axellpadilla in #772
• Add workflow to sync main branch in preparation for default branch migration by @axellpadilla in #781
• Raise compiler error for sql_header config with redirect to pre_hooks/query_options by @axellpadilla in #777
• Add experimental ADBC backend by @axellpadilla in #783
• ci: run unit and integration tests on release branches by @axellpadilla in #797
• fix: concurrent-incremental catalog deadlock, and (nolock) on the remaining catalog lookups by @axellpadilla in #792
• fix: drop the model's outbound foreign keys too (#632) by @axellpadilla in #793
• fix: one identifier quoting style, and the unquoted identifiers underneath it (#785, #409) by @axellpadilla in #795
• Bump version to 1.12.0rc2 by @axellpadilla in #799
• fix: compare view body exactly instead of suffix-matching the whole definition by @Benjamin-Knight in #803
• feat: add denies config so object-level DENY survives a rebuild by @Benjamin-Knight in #802
• feat: apply data masks on the full_refresh_build=prebuilt path by @Benjamin-Knight in #804
• Fix/undrained probe cursor rollback by @Benjamin-Knight in #810
• chore: replace mypy with ty for type checking by @joshmarkovic in #784
• feat: default dbt_sqlserver_use_default_schema_concat to True by @axellpadilla in #811
• feat: add sqlserver__openquery macro and SQL Server best-practices guide by @axellpadilla in #790
• fix: quote openquery's server name via adapter.quote() instead of hand-formatted brackets by @axellpadilla in #816
• chore: prepare 1.12.0rc3 release by @axellpadilla in #817
• fix(view): sp_refreshview when a view rebuild is skipped by @Benjamin-Knight in #821
• test(denies): give each xdist worker its own deny principal by @Benjamin-Knight in #823
• chore: simplify expand_column_types by @joshmarkovic in #826
• chore: simplify render_column_constraint by @joshmarkovic in #825
• fix: rebuild the DML refresh's scratch table so a schema change keeps the columnstore by @lll86789 in #829
• test(openquery): bootstrap the loopback linked server, and stop rebuilding it per worker by @axellpadilla in #831
• test: schedule functional tests by class (--dist loadscope) instead of by xdist_group mark by @axellpadilla in #835
• chore: drop unused dev dependencies, unpin ty, refresh the lockfile by @joshmarkovic in #827
• fix(constraints): emit column and model constraints instead of dropping them by @lll86789 in #828
• fix(table): stop Sch-M locks spanning the load in table builds by @Benjamin-Knight in #822
• fix(full-refresh-marker): resolve the marker object in the model's database by @Benjamin-Knight in #846
• build(deps): bump the github-actions-updates group across 1 directory with 3 updates by @dependabot[bot] in #851
• Update Docker-in-Docker devcontainer feature by @axellpadilla in #852
• docs: add AGENTS.md conventions, a PR template and changelog fragments by @axellpadilla in #853
• fix(describe): keep the query's own error instead of retrying it blind by @Benjamin-Knight in #845
• fix(schema): serialize CREATE SCHEMA so concurrent sessions cannot collide by @axellpadilla in #842
• fix(view): refresh a skipped view's metadata only when it is stale by @axellpadilla in #847
• fix(adapter): stop behavior setter growing the shared flag list by @axellpadilla in #856
• fix(indexes): name the columnstore index after the final table by @mopthe in #850
• chore: prepare 1.12.0rc4 by @axellpadilla in #841
• docs(readme): fix PyPI links and misplaced transaction notes by @axellpadilla in #860
• chore(deps): bump sqlparse from 0.5.5 to 0.6.0 by @dependabot[bot] in #832
• docs(github): add a bug report issue template by @axellpadilla in #863
• feat(ephemeral): enable the ephemeral tests and document the nested WITH limit by @axellpadilla in #864
• fix(connections): commit a run-operation's transaction when it finishes cleanly by @yvaishalirao in #866
• fix(snapshot): read check_cols without nesting the snapshot query by @axellpadilla in #867
• feat(utils): add sqlserver__array_append by @axellpadilla in #868
• fix(columns): make a failed column widening retryable and honor prefer_single_alter_column by @axellpadilla in #873
• fix(unit-tests): commit after the unit materialization drops its fixture table by @axellpadilla in #875
• chore: prepare 1.12.0 release by @axellpadilla in #872

New Contributors

• @lll86789 made their first contribution in #829
• @mopthe made their first contribution in #850
• @yvaishalirao made their first contribution in #866