Microsoft Azure Synapse DW Profile

Community contributed plugin

These are Community Contributed plugins for dbt. If you're interested in contributing, check out the source code for each repository:

Overview of dbt-synapse

Status: Community Contributed

Author: Nandan Hegde and Anders Swanson

Source Code: https://github.com/swanderz/dbt-synapse

dbt support: version 0.18.0 and newer

The pacakge can be installed from PyPI with:

pip install dbt-synapse

For further (and more likely up-to-date) info, see the README

Connecting to Azure Synapse with dbt-synapse

First download and install the MSFT ODBC Driver 17 for SQL Server

standard SQL Server authentication

SQL Server credentials are supported for on-prem as well as cloud, and it is the default authentication method for dbt-sqlsever

profiles.yml
type: sqlserver
driver: 'ODBC Driver 17 for SQL Server' (The ODBC Driver installed on your system)
server: server-host-name or ip
port: 1433
schema: schemaname
user: username
password: password

Active Directory Authentication

The following pyodbc-supported ActiveDirectory methods are available to authenticate to Azure SQL products:

  • ActiveDirectory Password
  • ActiveDirectory Interactive (Windows only)
  • ActiveDirectory Integrated (Windows only)
  • Service Principal (a.k.a. AAD Application)
  • ActiveDirectory MSI (not implemented)

Windows Only uses your machine's credentials (might be disabled by your AAD admins)

profiles.yml
type: sqlserver
driver: 'ODBC Driver 17 for SQL Server' (The ODBC Driver installed on your system)
server: server-host-name or ip
port: 1433
schema: schemaname
authentication: ActiveDirectoryIntegrated

Overview of dbt-azuresynapse

Status: Community Contributed

Author: Ernesto Barajas and Matt Berns

Source Code: https://github.com/embold-health/dbt-azuresynapse

dbt-azuresynapse Only supports dbt 0.17 and newer!

Easiest install is to use pip:

pip install dbt-azuresynapse

On Ubuntu make sure you have the ODBC header files as well as the appropriate ODBC adapter before installing

sudo apt install unixodbc-dev
sudo apt-get install msodbcsql17
sudo apt-get install mssql-tools

Connecting to Azure Synapse with dbt-azuresynapse

User / password authentication

Configure your dbt profile for using SQL Server authentication or Integrated Security:

SQL Server authentication
dbt-azuresynapse:
target: dev
outputs:
dev:
type: azuresynapse
driver: 'ODBC Driver 17 for SQL Server' (The ODBC Driver installed on your system)
server: server-host-name or ip
port: 1433
user: [username]
password: [password]
database: [databasename]
authentication: SqlPassword
schema: [schema]