SQL SERVER

A COMPLETE PROFILES.YML

The most common setup — SQL authentication with a user and password. Copy it, change five values, and you're connected:

# ~/.dbt/profiles.yml
my_project:
  target: mssql_dev
  outputs:
    mssql_dev:
      type: sqlserver
      server: sql-server.internal.com   # host is an accepted alias
      port: 1433
      database: analytics
      schema: dbo
      user: dvt_user
      password: "{{ env_var('MSSQL_PASSWORD') }}"
      encrypt: false                    # dev default — see below
      trust_cert: true
      threads: 4

Then run dvt sync once from your project. It reads this profile, sees a SQL Server output, and installs the adapter and the pyodbcdriver for you. One OS-level prerequisite remains yours: the unixODBC runtime and Microsoft's msodbcsql ODBC driver (on macOS, brew install unixodbcplus Microsoft's driver package). dvt syncchecks for this and tells you the exact command if it's missing — and dvt debug confirms the connection end to end.

WINDOWS INTEGRATED AUTH — WINDOWS_LOGIN

On a domain-joined machine you can skip credentials entirely and connect as the logged-in Windows user. No password ever rides the profile or the wire:

    mssql_win:
      type: sqlserver
      server: sql-server.internal.com
      database: analytics
      schema: dbo
      windows_login: true       # trusted connection — no user, no password

With windows_login: true set, user and password aren't required — or read.

ENTRA ID PASSWORD — ACTIVEDIRECTORYPASSWORD

For Azure SQL and Entra-joined servers where you sign in with your organizational account, set authentication and use your Entra credentials as the user and password:

    mssql_entra:
      type: sqlserver
      server: my-azure-sql.database.windows.net
      database: analytics
      schema: dbo
      authentication: ActiveDirectoryPassword
      user: analyst@yourcompany.com
      password: "{{ env_var('ENTRA_PASSWORD') }}"
      encrypt: true
      trust_cert: false

ENTRA ID SERVICE PRINCIPAL — SERVICEPRINCIPAL

The right shape for CI and scheduled runs: an app registration instead of a person. Three fields identify it — no user or password needed:

    mssql_sp:
      type: sqlserver
      server: my-azure-sql.database.windows.net
      database: analytics
      schema: dbo
      authentication: ServicePrincipal
      client_id: "{{ env_var('AZURE_CLIENT_ID') }}"
      tenant_id: "{{ env_var('AZURE_TENANT_ID') }}"
      client_secret: "{{ env_var('AZURE_CLIENT_SECRET') }}"
      encrypt: true
      trust_cert: false

Both spellings are accepted — ServicePrincipal and ActiveDirectoryServicePrincipal mean the same thing. DVT composes the principal identity the driver expects from these three fields itself; you never write the client_id@tenant_id form by hand.

NAMED INSTANCES — HOST\INSTANCE

A named instance goes in serverexactly the way SQL Server tools write it, backslash and all. DVT splits the host from the instance for its data-movement engine — you don't configure them separately:

    mssql_instance:
      type: sqlserver
      server: 'db-host\SQLEXPRESS'   # quote it — YAML and backslashes
      database: analytics
      user: dvt_user
      password: "{{ env_var('MSSQL_PASSWORD') }}"

Single-quote the value so the backslash survives YAML parsing. Named instances usually sit on dynamic ports resolved by the SQL Browser service — if yours pins a fixed port, plain server + port is the simpler, firewall-friendlier spelling.

HOW SQL SERVER BEHAVES IN DVT

Encryption defaults favor local dev. encrypt defaults to false and trust_cert to true — the combination that connects to a fresh developer container with its self-signed certificate. For production, flip both: encrypt: true, trust_cert: false, so the server's certificate is actually verified.

Schema means dbo unless you say otherwise. The default schema is dbo, which is where most SQL Server databases keep everything anyway. Identifier case follows your database collation — case-insensitive on the common defaults, so GL_Accounts and gl_accounts are the same table.

Reading SQL Server live from a federation model takes one meta.connection line pointing at the output you defined above:

# models/staging/sources.yml
sources:
  - name: erp
    meta:
      connection: mssql_dev    # a profiles.yml output name
    database: ERP
    schema: dbo
    tables:
      - name: GL_Accounts
      - name: AP_Invoices

Local development.Microsoft's official container gets you a server in one line — and it's exactly what the dev defaults above are tuned for:

docker run -d --name mssql -p 1433:1433 \
  -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD='Str0ng!Passw0rd' \
  mcr.microsoft.com/mssql/server:2022-latest

# then in profiles.yml:
#   server: localhost, user: sa, encrypt: false, trust_cert: true

Same family, different cloud. Microsoft Fabric speaks the same TDS protocol but only accepts Entra ID auth — it has its own page: Microsoft Fabric setup.

INCREMENTAL MODELS — MERGE IS REAL, AND THE KEY DECIDES

The SQL Server adapter accepts four strategy names, spelled exactly as they go into config: append, delete+insert, merge, and microbatch. insert_overwrite is not among them. The adapter does ship a helper macro for it, but the name is rejected before that macro can be reached, so treat it as unsupported.

Leave incremental_strategy unset and the adapter runs its default, which branches on the key: with a unique_key it emits a MERGE, without one it appends. T-SQL has a real MERGE statement and the adapter uses it — nothing is emulated here.

{{ config(
    materialized='incremental',
    incremental_strategy='merge',   -- append | delete+insert | merge | microbatch
    unique_key='invoice_id'         -- omit it and merge matches nothing
) }}

select invoice_id, status, updated_at
from {{ source('erp', 'AP_Invoices') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

The key is the part that bites. delete+insert without a unique_key silently degrades to an append: the delete block is skipped, the insert still runs, and nothing errors. merge without a unique_key compiles to a MERGE whose match predicate is FALSE and which carries no update clause — every incoming row is inserted and nothing is ever updated. Both shapes run green and accumulate duplicates. With a key present, merge honours merge_update_columns and merge_exclude_columns, and delete+insert accepts a list of key columns as well as a single one.

microbatch has requirements of its own. It needs an event_time config and the batch boundaries dbt passes alongside it; those boundaries are cast to datetimeoffset in the generated delete. It ignores unique_key entirely — it deletes by time predicate, then inserts.

The multi-statement strategies are not one transaction by default. The behaviour flag dbt_sqlserver_use_dbt_transactions defaults to false, which makes begin/commit no-ops and leaves the driver auto-committing each statement, so an earlier statement is not rolled back when a later one fails. delete+insert and microbatch both emit a delete followed by a separate insert. Nothing on this engine is gated on a SQL Server version, a table format, or a catalog type — the adapter declares no such conditions, and none of these strategies runs at all on a full refresh or against an existing view, where the model is simply rebuilt.

A name the adapter does not accept is refused by dbt's own gate rather than by the adapter, and which message you get depends on the kind of name you wrote:

incremental_strategy='insert_overwrite'
  The incremental strategy 'insert_overwrite' is not valid for this adapter

incremental_strategy='upsert'
  dbt could not find an incremental strategy macro with the name
  "get_incremental_upsert_sql" in <your project>

When SQL Server is the target of a federated model. An f_incremental model is computed by DVT and landed through its data-movement layer, so the vocabulary narrows to append, merge, and delete+insert; insert_overwrite and microbatch are refused as DVT025— "has no Sling equivalent — f_incremental supports append, merge and delete+insert". On that lane merge and delete+insert are the same thing: both become a key-based upsert on unique_key, with no separate delete pass. Either one without a unique_key warns and falls back to a full refresh instead of failing, and so does append without a watermark_column. SQL Server is one of the engines admitted to the keyless-watermark path — a federated incremental that leans on a watermark instead of a key is allowed here because DVT has verified this connector honouring an update-key-only load. Engines it has not verified — StarRocks, Snowflake, Databricks, BigQuery, Fabric and Athena — refuse the same model with DVT026. DVT rewrites no strategy on SQL Server: the one substitution it makes anywhere is ClickHouse's merge to delete+insert.

REFERENCE — EVERY PROFILE FIELD

Everything a type: sqlserver output reads. Exactly one auth shape applies per output: SQL auth (user + password, the default), windows_login, or an authentication variant.