🚧 dbt-synapse 🚧

custom dbt adapter for Azure Synapse. Major credit due to @mikaelene and his sqlserver custom adapter.

major differences b/w dbt-synapse and dbt-sqlserver

• macros use only Azure Synapse T-SQL. Relevant GitHub issue
• use of Create Table as Select (CTAS) means you don't need post-hooks to create indices
• Azure Active Directory Authentication options

status & support

Passing all tests in dbt-integration-tests.

outstanding work:

• test incremental materializations more thoroughly than is done with dbt-integration-tests.
• Add support for ActiveDirectoryMsi
• Publish as package to pypi
• Use CTAS to create seeds?
• staging external tables as sources (in progress)
• officially rename the adapter from sqlserver to synapse

dbt version support

as of now, only support for dbt 0.15.3, support for forthcoming 0.18.0 in development

Easiest install is to use pip (not yet registered on PyPI).

First install ODBC Driver version 17.

pip install git+https://github.com/swanderz/dbt-synapse.git

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

sudo apt install unixodbc-dev

Authentication

SqlPassword is the default connection method, but you can also use the following pyodbc-supported ActiveDirectory methods to authenticate:

• ActiveDirectory Password
• ActiveDirectory Interactive
• ActiveDirectory Integrated
• ActiveDirectory MSI (to be implemented)

boilerplate

this should be in every target definition

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

SQL Server authentication

user: username
password: password

ActiveDirectory Password

Definitely not ideal, but available

authentication: ActiveDirectoryPassword
user: [email protected]
password: i<3opensource?

ActiveDirectory Interactive (Windows only)

brings up the Azure AD prompt so you can MFA if need be.

authentication: ActiveDirectoryInteractive
user: [email protected]

ActiveDirectory Integrated (Windows only)

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

authentication: ActiveDirectoryIntegrated

ActiveDirectory MSI (to be implemented)

authentication: ActiveDirectoryMsi

Table Materializations

CTAS allows you to materialize tables with indices and distributions at creation time, which obviates the need for post-hooks to set indices.

Example

You can also configure index and dist in dbt_project.yml.

`models/stage/absence.sql

{{
    config(
        index='HEAP',
        dist='ROUND_ROBIN'
        )
}}

select *
from ...

is turned into the relative form (minus __dbt's _backup and _tmp tables)

  CREATE TABLE ajs_stg.absence_hours
    WITH(
      DISTRIBUTION = ROUND_ROBIN,
      HEAP
      )
    AS (SELECT * FROM ajs_stg.absence_hours__dbt_tmp_temp_view)

Indices

• CLUSTERED COLUMNSTORE INDEX (default)
• HEAP
• CLUSTERED INDEX ({COLUMN})

Distributions

• ROUND_ROBIN (default)
• HASH({COLUMN})
• REPLICATE

Changelog

v0.15.3

Fixes:

• Fix output of sql in the log files.
• Limited the version of dbt to 0.15, since later versions are unsupported.

v0.15.2

Initial release