Data Platform School
HomeLandscapeAbout me

DBT with Azure Synapse - Part 2

By Valdas Maksimavicius
Published in Data Engineering
January 26, 2021
1 min read
DBT with Azure Synapse - Part 2

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 :) )

d1f371974a38cd1b11eea35f8873a55b3cc0527b

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

321bedbbbae93b02cf5ec9ba0ae5934b4da82a17

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. 

3f6746ad294d8c091e47e137b8314758f067202b

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.

20ece0085a9a7f9fc2c67afa27ee0c7e85186f6f

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

057caa5777582cbff7c12d212ece2ec8fe0aab27

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.

db5d4233c0e23b958332d75bc25d6a8cea9aca7b

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

72b0bb4aef0bc166c0a18e98f38722cd55c045ac

 pip install dbt-azuresynapse

Change configuration settings and run dbt debug

71c302cb0fe55fd93ded0d26b9ae5078a329c370

6fec6786403ba37a2feca6f865d3d89e0a75debc

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

1179d8bcb7bbd1134fa8c8e76c52f46189bd4c01

910fddd18d6070799c677ca914d272ef2baf6e8b

Read more about DBT


Tags

#data ops#engineering#ELT

The latest set of Azure Data Platform best practices - April 2021

A simple blog post evolved to 25+ page guide with 75+ different recommendations.
Download Document
Previous Article
Install DBT on Windows to target Azure SQL and Synapse - Part 1
Valdas Maksimavicius

Valdas Maksimavicius

IT Architect | Microsoft Data Platform MVP

Resources

ADVERTISE WITH US

Topics

Data Architecture
Data Engineering
Data Governance
Miscellaneous

Related Posts

Data Engineering Patterns and Principles
March 21, 2021
1 min
© 2021, All Rights Reserved.

Quick Links

About mePrivacyContactLandscape

Social Media