DEV Community

Sospeter Mong'are
Sospeter Mong'are

Posted on

How to Install dbt with SQL Server on WSL (Windows Subsystem for Linux)

#ai #database #data #science

Note: A beginner-friendly, step-by-step guide for data engineers on Windows

What is dbt?

dbt (data build tool) is an open-source transformation tool that lets you write SQL-based data models, test them, and document them — all from the command line. It sits on top of your database and transforms raw data into analytics-ready tables and views.

If you're working with data pipelines, ELT workflows, or data warehouses, dbt is a tool you'll encounter regularly.

Prerequisites

Before starting, make sure you have:

  • WSL 2 with Ubuntu 24.04 installed on your Windows machine
  • Python 3.12 available (python3 --version to check)
  • A virtual environment already created and activated (see the Airflow installation guide if you need to set one up)
  • Access to a SQL Server instance — remote, Azure SQL, or local

Step 1: Activate Your Virtual Environment

Always work inside a virtual environment to keep your dependencies isolated:

source ~/your-project-folder/env/bin/activate
Enter fullscreen mode Exit fullscreen mode

Your prompt should show (env) at the beginning confirming it's active.

Step 2: Install dbt with the SQL Server Adapter

The SQL Server adapter for dbt is maintained as dbt-sqlserver. Installing it also pulls in dbt-core automatically:

pip install dbt-sqlserver
Enter fullscreen mode Exit fullscreen mode

Verify the installation:

dbt --version
Enter fullscreen mode Exit fullscreen mode

Confirm the adapter is installed:

pip show dbt-sqlserver
Enter fullscreen mode Exit fullscreen mode

You should see:

Name: dbt-sqlserver
Version: 1.x.x
Summary: A Microsoft SQL Server adapter plugin for dbt
Requires: dbt-adapters, dbt-common, dbt-core, pyodbc
Enter fullscreen mode Exit fullscreen mode

Note: dbt --version may not always list plugins explicitly in some environments — pip show dbt-sqlserver is the reliable way to confirm the adapter is present.

Step 3: Install the Microsoft ODBC Driver for SQL Server

dbt-sqlserver uses pyodbc under the hood, which requires Microsoft's ODBC driver to physically connect to SQL Server. Run the following commands one at a time:

Command 1 — Download and register Microsoft's GPG signing key:

curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg
Enter fullscreen mode Exit fullscreen mode

Command 2 — Add Microsoft's package repository:

curl https://packages.microsoft.com/config/ubuntu/24.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
Enter fullscreen mode Exit fullscreen mode

Command 3 — Refresh apt to pick up the new repository:

sudo apt update
Enter fullscreen mode Exit fullscreen mode

Command 4 — Install the ODBC driver:

sudo ACCEPT_EULA=Y apt install -y msodbcsql18 unixodbc-dev
Enter fullscreen mode Exit fullscreen mode

Verify the driver is registered:

odbcinst -q -d -n "ODBC Driver 18 for SQL Server"
Enter fullscreen mode Exit fullscreen mode

You should see:

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.6.so.2.1
UsageCount=1
Enter fullscreen mode Exit fullscreen mode

This confirms the driver is installed and ready.

Step 4: Clone Your dbt Project Repository

If you have an existing dbt project in a Git repository, clone it:

cd ~
git clone <YOUR_REPO_URL_HERE>
cd <cloned-folder-name>
Enter fullscreen mode Exit fullscreen mode

If the repository is private, authenticate using a GitHub Personal Access Token (PAT):

git clone https://<your-github-username>:<your-PAT>@github.com/org/repo.git
Enter fullscreen mode Exit fullscreen mode

Step 5: Install dbt Project Dependencies

Most dbt projects have a packages.yml file that lists external dbt packages. Install them with:

dbt deps
Enter fullscreen mode Exit fullscreen mode

Step 6: Configure Your profiles.yml

dbt connects to your database using a profiles.yml file. By default it lives at ~/.dbt/profiles.yml, but some projects include it locally inside the repo folder.

Create the default profiles directory and file:

mkdir -p ~/.dbt
nano ~/.dbt/profiles.yml
Enter fullscreen mode Exit fullscreen mode

Paste the following template and fill in your actual credentials:

your_project_name:
  target: dev
  outputs:
    dev:
      type: sqlserver
      driver: "ODBC Driver 18 for SQL Server"
      server: your_server_address        # e.g. 192.168.1.10 or myserver.database.windows.net
      port: 1433
      database: your_database_name
      schema: your_schema_name
      user: your_username
      password: your_password
      encrypt: true
      trust_cert: true                   # set to false in production with a valid SSL cert
      login_timeout: 60                  # seconds before connection attempt times out

    prod:
      type: sqlserver
      driver: "ODBC Driver 18 for SQL Server"
      server: your_prod_server_address
      port: 1433
      database: your_prod_database
      schema: your_prod_schema
      user: your_prod_username
      password: your_prod_password
      encrypt: true
      trust_cert: true
      login_timeout: 60
Enter fullscreen mode Exit fullscreen mode

Important: The top-level key (your_project_name) must exactly match the name: field inside the project's dbt_project.yml file.

Save and exit (Ctrl+O, Enter, Ctrl+X).

Step 7: Test the Connection

Run dbt's built-in connection test:

dbt debug --target dev
Enter fullscreen mode Exit fullscreen mode

A successful output looks like:

All checks passed!
Enter fullscreen mode Exit fullscreen mode

If you see a Login timeout expired error, check:

  • Your server address is correct and reachable
  • Port 1433 is open on the server's firewall
  • Your credentials are correct in profiles.yml
  • You're on the right network (VPN may be required for remote servers)

Test if the server port is reachable from WSL:

nc -zv your_server_address 1433
Enter fullscreen mode Exit fullscreen mode

Step 8: Run Your dbt Models

Once the connection test passes, run a specific model:

dbt run --select model_name --target dev
Enter fullscreen mode Exit fullscreen mode

Run all models:

dbt run --target dev
Enter fullscreen mode Exit fullscreen mode

Run models against production:

dbt run --select model_name --target prod
Enter fullscreen mode Exit fullscreen mode

List all available models in the project:

dbt ls --target dev
Enter fullscreen mode Exit fullscreen mode

Other Database Adapters

dbt supports many databases. Here are the install commands for the most common ones:

Database Install Command
SQL Server / Azure SQL pip install dbt-sqlserver
PostgreSQL pip install dbt-postgres
BigQuery pip install dbt-bigquery
Snowflake pip install dbt-snowflake
MySQL pip install dbt-mysql
Redshift pip install dbt-redshift
DuckDB pip install dbt-duckdb
Databricks pip install dbt-databricks
Trino pip install dbt-trino

Each adapter has its own profiles.yml format. The type: field changes per adapter. For example:

PostgreSQL:

my_project:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      port: 5432
      database: my_database
      schema: public
      user: my_user
      password: my_password
Enter fullscreen mode Exit fullscreen mode

BigQuery:

my_project:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: service-account
      project: my_gcp_project
      dataset: my_dataset
      keyfile: /path/to/service-account.json
Enter fullscreen mode Exit fullscreen mode

Snowflake:

my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: my_account
      user: my_user
      password: my_password
      database: my_database
      schema: my_schema
      warehouse: my_warehouse
      role: my_role
Enter fullscreen mode Exit fullscreen mode

Quick Reference: Most Used dbt Commands 

dbt debug              # Test database connection
dbt deps               # Install project package dependencies
dbt run                # Run all models
dbt run --select model_name         # Run a specific model
dbt run --target prod               # Run against a specific target
dbt test                            # Run data tests
dbt ls                              # List all models
dbt compile                         # Compile SQL without running
dbt docs generate && dbt docs serve # Generate and view documentation
Enter fullscreen mode Exit fullscreen mode

Summary of Installation Commands 

# Activate virtual environment
source ~/your-project/env/bin/activate

# Install dbt with SQL Server adapter
pip install dbt-sqlserver

# Install ODBC Driver (run one at a time)
curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg
curl https://packages.microsoft.com/config/ubuntu/24.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt update
sudo ACCEPT_EULA=Y apt install -y msodbcsql18 unixodbc-dev

# Verify ODBC driver
odbcinst -q -d -n "ODBC Driver 18 for SQL Server"

# Clone project repo
git clone <REPO_URL>
cd <project-folder>

# Install dbt packages
dbt deps

# Configure connection
mkdir -p ~/.dbt && nano ~/.dbt/profiles.yml

# Test connection
dbt debug --target dev

# Run models
dbt run --target dev
Enter fullscreen mode Exit fullscreen mode

Written based on a real dbt + SQL Server setup on WSL2 Ubuntu 24.04 (Noble) with ODBC Driver 18.

Top comments (0)