Microsoft Azure Synapse DWH setup
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:
python -m pip install dbt-core dbt-synapse
Configuring dbt-synapse
For Synapse-specific configuration, please refer to Synapse configs.
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.
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:
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.