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.
Azure SQL works well, can we reuse same models without changing any DBT templates and create in Azure Synapse? Sure!
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
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.
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.