Setting up the dbt SQL Server Adapter

Introduction

I am currently researching dbt, a data transformation tool that allows you to transform data in your warehouse more effectively. One of the things that is really interesting about it, is that dbt has a set of adapters that allow you to interchange between different database engines.

It is also really easy to get started with local development. There are a few hoops to jump through to get the sql server adapter working with macOS, so I thought I would document them here. Beyond the driver pre-requisites, you can pretty much follow these same steps for Windows.

Adapters?

dbt uses dedicated adapters to connect to different databases. The adapter is responsible for translating dbt’s SQL into the SQL dialect of the database you are using. dbt seems to interchange the words adapters and plugins. Adapters are built as python modules and will be discovered by dbt if installed. There are two types of adapters, trusted and community.

Trusted adapter

The trusted adapters are managed through a Trusted Adapter Program, it suggests that there is a commitment to maintaining the state of the adapter and adhering to a contractual requirement for development, documentation, user experience, and maintenance.

Community adapter

The community adapters are open-source and community maintained, what this does mean is that the adapters might not be fully functional or may have usage inconsistencies.

SQL Server adapter is a community adapter

The SQL Server adapter is a community adapter, so it may not be as complete, or maintained as well as the trusted adapters. Just downloading the quickstart project and running dbt run I have run into errors in syntax. It’s likely to be that a) the project hasn’t been designed to cater for the sql adapter or b) the adapter isn’t fully functional or probably a combination of both. So whilst I’m finding my way with dbt, I’ll be focusing on a trusted adapter and exploring how to handle the community adapters once I have a bit more knowledge.

Postgres for the win

Whilst I’m exploring dbt, I’ll be using the Postgres adapter. It’s a trusted adapter and I can have it spin up in a docker container locally in a few minutes, meaning I can get on with learning dbt without signing up for any cloud services. You can read about that here. Nevertheless, here are the issues I encountered getting the SQL Server adapter up and running on macOS and how I fixed them…

Pre-requisites

Docker and SQL Server

First things first, you will need to have SQL Server running locally. I have written about that here.

Install Microsoft ODBC driver

The dbt SQL adapter requires the Microsoft ODBC Driver 18 for SQL Server. Windows users can download it here. You can install on macOS using homebrew:

1brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
2brew update
3HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

Install supporting drivers

From here you should be able to just run pip install dbt-sqlserver and be good to go. However, there are some issues with the supporting drivers on macOS and they need to be installed first. If you have already run the command, you may have seen an error like:

1ImportError: dlopen(/Users/justin/python/dbt/lib/python3.12/site-packages/pyodbc.cpython-312-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

If you’re not interested in the details, run this command, that should do the trick:

1pip install --no-binary :all: pyodbc==5.0.1 --force-reinstall

You can now move on to installing the dbt-sqlserver adapter. For those interested in the issues, read on…

Troubleshooting the supporting drivers

So a bit of a rabbit hole here, I started uninstalling unixodbc and pyodbc trying a few suggestions I found online. I did end up causing myself a secondary issue at this point:

1ERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.
2dbt-sqlserver 1.7.4 requires pyodbc<5.1.0,>=4.0.35, but you have pyodbc 5.1.0 which is incompatible.

By reinstalling pyodbc I installed a version not compatible with dbt-sqlserver, so pip install pyodbc==5.0.1 fixed that however, I still had the original issue. The error message was:

1ImportError: dlopen(/Users/justin/python/dbt/lib/python3.12/site-packages/pyodbc.cpython-312-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

The suggestion is that the package is not compiled properly for ARM as suggested here and sure enough, using the --no-binary flag along with the version number fixed the issue. Therefore the command to install the supporting drivers is as follows:

1pip install --no-binary :all: pyodbc==5.0.1 --force-reinstall

Install and configure dbt-sqlserver

Just as a summary, the complete set of commands is:

 1# installs the Microsoft ODBC driver and unixodbc
 2brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
 3brew update
 4HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18
 5
 6# installs pyodbc
 7pip install --no-binary :all: pyodbc==5.0.1 --force-reinstall
 8
 9# installs dbt adapter
10pip install dbt-sqlserver

With everything in place, I just need to set up my profile in the profiles.yml file. This is the file that dbt uses to connect to your database. Using the container I have talked through here, my profile would look like this:

 1quickstart-sql-local:
 2  target: dev
 3  outputs:
 4    dev:
 5      database: db1
 6      driver: ODBC Driver 18 for SQL Server
 7      server: localhost
 8      port: 2022
 9      schema: dbo
10      type: sqlserver
11      trust_cert: true
12      user: justin
13      password: 'ADB@5XRB7k$$N#!9EUDQ!o808*OPQ6AO*deQ0!P$17U2Lw%iEQ'

I can now run dbt commands against my SQL Server database. If I run dbt seed in my project directory, dbt will connect to my SQL Server database and run the seed files I have defined. Based on the jaffle-shop sandbox project, here are the tables present in the database:

Want to connect to an Azure SQL Database?

In the above example I am using the sql login that was created by the container, but if using an Azure SQL Database it’s preferable to use Microsoft Entra authentication. There are multiple methods within the scope of Entra which you can see here. To repoint my project to an Azure SQL Database, I would simply create a new profile in the profiles.yml file:

 1jaffle_shop_sql_azure:
 2  target: dev
 3  outputs:
 4    dev:
 5      database: jjb-dev-scratch-db
 6      driver: ODBC Driver 18 for SQL Server
 7      server: jjb-dev-sql.database.windows.net
 8      schema: dbo
 9      type: sqlserver
10      authentication: CLI

I have picked the CLI authentication method, which is the easiest to set up because I just need to run az login and pass in my azure credentials, dbt will then be able to authenticate using my credentials (assuming I have access to the database). Running the dbt seed command will now create the tables in the Azure SQL Database:

To uninstall

To fully uninstall the dbt-sqlserver adapter and the related drivers you would run the following commands in this order to avoid any dependency issues:

1brew uninstall mssql-tools18
2brew uninstall msodbcsql18
3brew uninstall unixodbc
4pip uninstall dbt-sqlserver
5pip uninstall pyodbc

Conclusion

Getting dbt set up with SQL Server on a silicon Mac is a bit of a pain, but once you have the correct drivers installed everything else is pretty straightforward. Because this is a community adapter and I just want to focus on learning dbt, I’m going to work with the postgres adapter for now. I’ll come back to the SQL Server adapter once I have a bit more knowledge.

References

• What is DBT?
• Microsoft SQL Server setup