Welcome! Here are the series of articles about DBT. In part 1 we kicked things off with DBT on Windows. You have instructions how to install DBT and connect to Azure SQL.

• Part 1 - Getting Familiar with DBT and get started with Azure SQL
• Part 2 - Use DBT with Azure Synapse - Your are here

Connect to Azure Synapse Pool

Azure SQL works well, can we reuse same models without changing any DBT templates and create in Azure Synapse? Sure!

1. Create Azure Synapse Pool and establish connection (all steps combined)

Create SQL Pool instance (DW100 should be enough :) )

Add Synapse entries to profiles.yml file and edit dbt_project.yml file.

Verify dbt connections and run deployment

dbt debug

At first, I’ve received ODBC errors - Client driver version is not supported.

I’ve realized the ODBC driver is too old and does not support Synapse. 

Installing latest driver, changing profile driver from SQL Serve**r to ODBC Driver 17 for SQL Server in the profiles.yml** file solves the issue. I proceed further.

dbt run

Unexpectedly, I get “An insufficient number of arguments were supplied for the procedure or function sp_rename” error.

After a short though, I realize my currently installed DBT connector supports only SQL Server, Azure SQL and Managed Instance. I need DBT Synapse connector.

To avoid conflicts with dbt-sqlserver and dbt-synapse, I remove dbt-sqlserver connector.

pip uninstall dbt-sqlserver

Now, I take a guess and pick the first connector and install it:

pip install dbt-synapse

The new package gets installed quickly. Also, I need to update my profiles.yml file with new credentials.

I run dbt run command again, now get “Could not find adapter type synapse!” error.

Usually such types of issues are linked with version issues. After short debugging I find that my dbt is version 0.18.1. While dbt-synapse package available on pip works with only 0.18.0. I take a prerelease dbt-synapse package which now should support also 0.18.1.

pip install dbt\_synapse-0.18.1-py3-none-any.whl

Unfortunately, it doesn’t help and I still get the same error. So I decide to create a clean python environment, install dbt once again and install dbt-synapse from prerelease library.

Guess what? The same error once again. 

So I decide to try another Synapse package - dbt-azuresynapse

pip install dbt-azuresynapse

Change configuration settings and run dbt debug

Now it’s time to execute dbt run and enjoy tables in my database instance.

Read more about DBT