Skip to main content

Microsoft Azure Synapse DWH setup

info

The following is a guide to using Azure Synapse Analytics Dedicated SQL Pools, formerly SQL DW. For more info, refer to What is dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics? for more info.

Refer to Microsoft Fabric Synapse Data Warehouse to set it up with dbt.

  • 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: Supported
  • Minimum data platform version: Azure Synapse 10

Installing dbt-synapse

Use pip to install the adapter. Before 1.8, installing the adapter would automatically install dbt-core and any additional dependencies. Beginning in 1.8, installing an adapter does not automatically install dbt-core. This is because adapters and dbt Core versions have been decoupled from each other so we no longer want to overwrite existing dbt-core installations. Use the following command for installation:

Configuring dbt-synapse

For Synapse-specific configuration, please refer to Synapse configs.

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