Skip to main content

Microsoft Azure Synapse DWH setup

Community plugin

Some core functionality may be limited. If you're interested in contributing, check out the source code for each repository listed below.

Overview of dbt-synapse

  • Maintained by: Community
  • Authors: dbt-msft community (https://github.com/dbt-msft)
  • GitHub repo: dbt-msft/dbt-synapse
  • PyPI package: dbt-synapse
  • Slack channel: #db-synapse
  • Supported dbt Core version: v0.18.0 and newer
  • dbt Cloud support: Not Supported
  • Minimum data platform version: Azure Synapse 10

Installing dbt-synapse

pip is the easiest way to install the adapter:

pip install dbt-synapse

Installing dbt-synapse will also install dbt-core and any other dependencies.

Configuring dbt-synapse

For Synapse-specifc configuration please refer to Synapse Configuration

For further info, refer to the GitHub repository: dbt-msft/dbt-synapse

Dedicated SQL only

Azure Synapse offers both Dedicated SQL Pools and Serverless SQL Pools. Only Dedicated SQL Pools are supported by this adapter. If you really insist on using serverless pools, check out the neglected, experimental project: dbt-synapse-serverless

Prerequisites

On Debian/Ubuntu make sure you have the ODBC header files before installing

sudo apt install unixodbc-dev

Download and install the Microsoft ODBC Driver 18 for SQL Server. If you already have ODBC Driver 17 installed, then that one will work as well.

Default settings change in dbt-synapse v1.2 / ODBC Driver 18

Microsoft made several changes related to connection encryption. Read more about the changes here.

Authentication methods

This adapter is based on the adapter for Microsoft SQL Server. Therefor, the same authentication methods are supported.

The configuration is the same except for 1 major difference: instead of specifying type: sqlserver, you specify type: synapse.

Example:

profiles.yml
your_profile_name:
target: dev
outputs:
dev:
type: synapse
driver: 'ODBC Driver 17 for SQL Server' # (The ODBC Driver installed on your system)
server: workspacename.sql.azuresynapse.net # (Dedicated SQL endpoint of your workspace here)
port: 1433
database: exampledb
schema: schema_name
user: username
password: password

You can find all the available options and the documentation and how to configure them on the documentation page for the dbt-sqlserver adapter.

0