Skip to main content

Microsoft Azure Synapse DWH setup

Overview of dbt-synapse

  • Maintained by: Microsoft
  • Authors: Microsoft (https://github.com/Microsoft)
  • GitHub repo: Microsoft/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: Microsoft/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.

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
Edit this page