dbt-sqlserver

dbt-sqlserver is the community-maintained dbt adapter plugin that lets dbt-core projects target Microsoft SQL Server and Azure SQL Database/Managed Instance. It implements dbt’s adapter interface (relations, connections, materializations, catalog) against T-SQL, translating dbt’s incremental, snapshot, seed, ephemeral, and Python model workflows into SQL Server-native DDL/DML, including clustered columnstore index creation, safe column-type expansion, and transaction-aware hook execution.

The adapter is backend-pluggable: the default pyodbc path uses the Microsoft ODBC Driver, an alternative mssql-python backend drops the ODBC driver requirement entirely, and an experimental Arrow-native adbc backend bypasses the row-based DB-API bridge. Authentication covers SQL auth, Windows/Kerberos, and Azure Active Directory (interactive, service principal, managed identity, CLI, and access-token passthrough), and the project ships an extensive unit, functional, and integration test suite that runs against real SQL Server instances in CI.

What You Get

• An adapter plugin registered with dbt-core’s plugin system, installed via pip install dbt-sqlserver and selected as type: sqlserver in profiles.yml
• Three interchangeable connection backends (pyodbc, mssql-python, adbc) selectable per profile target without changing project code
• Built-in support for dbt’s incremental, snapshot, seed, ephemeral, and Python model materializations translated into SQL Server-specific DDL/DML
• Azure Active Directory authentication paths (interactive, service principal, managed identity, CLI, access-token passthrough) alongside SQL and Windows auth
• Behavior flags for safe column-type widening, dbt-native transaction hooks, columnstore index creation, and legacy schema-naming compatibility

Common Use Cases

• Running a dbt project against an on-prem SQL Server or Azure SQL Database warehouse instead of a cloud-native warehouse
• Migrating an existing dbt project from autocommit-only legacy transaction handling to dbt-managed BEGIN/COMMIT semantics via dbt_sqlserver_use_dbt_transactions
• Connecting to Azure SQL with managed identity or service principal credentials in a CI/CD pipeline, without embedding SQL credentials
• Building incremental models on tables that need safe, low-risk column-type expansion instead of manual ALTER TABLE migrations

Under The Hood

Architecture The adapter is a thin translation layer between dbt-core’s SQLAdapter/SQLConnectionManager base classes and T-SQL: sqlserver_adapter.py (SQLServerAdapter, ~960 lines) implements column-schema introspection, index diffing (relation_configs/index.py), and constraint/materialization overrides, while sqlserver_connections.py (SQLServerConnectionManager, ~870 lines) owns cursor lifecycle, retryable-exception classification per backend, and query execution. Backend selection is isolated behind a small set of pure functions in sqlserver_backend.py (_connect_pyodbc, _connect_mssql_python, _connect_adbc) and sqlserver_auth.py (is_adbc_backend, is_mssql_python_backend), so the adapter, connection manager, and macros never branch on backend identity directly. Cross-cutting SQL Server concerns (column masking in sqlserver_mask.py, GRANT/DENY translation in sqlserver_deny.py, credential coercion in sqlserver_credentials.py) are each isolated into single-purpose modules rather than folded into the adapter class. The riskiest coupling point is sqlserver_runtime.py, which lazily imports optional driver packages (pyodbc, mssql_python, adbc_driver_manager) at call time so the package installs cleanly without any of them present.

Tech Stack The project targets dbt-core 1.12+, dbt-adapters 1.24+, and dbt-common 1.22+, built with setuptools and dynamic versioning pulled from a dedicated version module. Three optional driver backends are declared as extras: pyodbc>=5.2.0 (default, requires the Microsoft ODBC Driver plus unixodbc-dev on Linux), mssql-python>=1.7.1 (a no-ODBC alternative needing only a handful of system libraries), and an experimental adbc-driver-manager plus pyarrow combination for Arrow-native access; azure-identity is a separate extra for AAD token acquisition. Tooling is uv-managed, linted with ruff (import ordering, banned relative imports), type-checked with ty pinned to an exact version and scoped to the adapter package, and tested with pytest plus dbt-tests-adapter for the shared dbt adapter conformance suite. CI runs unit tests, a dedicated type-check job, integration tests against a live SQL Server container, and a Docker image publish workflow.

Code Quality Test coverage is extensive and stratified: dozens of files split across unit tests (pure-function tests for schema naming, index diffing, masking, DENY translation, connection logic, ADBC backend behavior) and functional tests (materializations, snapshots, seeds, Python models, transactions, grants, catalog, and provisioning run against a real SQL Server instance). Error handling favors explicit, typed exceptions and backend-specific retryable-exception classification rather than bare excepts. Code carries unusually dense inline rationale comments explaining why a given code path exists, full type hints throughout, and a pinned static type checker scoped to the adapter package with an explicit suppression justified in a comment about dbt’s split-namespace-package import mechanics.

API Design As a dbt adapter, the ‘API’ surface is a profiles.yml target block and a handful of dbt_project.yml behavior flags rather than an importable Python API. Getting started is a two-line install-and-configure with sensible defaults (columnstore indexes, dbt-managed transactions, safe type-widening) that require no extra configuration for the common case, while advanced behavior is opt-in via named flags with documented default values and migration notes. The three-backend abstraction is the adapter’s most distinctive ergonomic choice — it lets a project switch drivers, including to a fully ODBC-driver-free path, by changing one profile field, which is unusual among dbt adapters typically tied to a single client library.