customdbt adapter forAzure Synapse. Major credit due to@mikaelene andhisdbt-sqlserver custom adapter.

related packages#

To get additional functionality, check out:

• fishtown-analytics/dbt-external-tables which allows for easy staging of blob sources defined inYAML, and
• dbt-msft/tsql-utils enablesdbt-synapse to usedbt-utils: the much-loved, extremely-useful collection of dbt macros.

major differences b/wdbt-synapse anddbt-sqlserver#

• macros use only Azure SynapseT-SQL.Relevant GitHub issue
• use ofCreate Table as Select (CTAS) means you don't need post-hooks to create indices (see Table Materializations section below for more info)
• rewrite of snapshots because Synapse doesn't supportMERGE.

status & support#

as of now, only support for dbt0.18.0

Passing all tests indbt-adapter-tests, excepttest_dbt_ephemeral_data_tests

outstanding work:#

• ephemeral materializations (workaround for non-recursive CTEs) see#25
• officially rename the adapter fromsqlserver tosynapse see#40
• Make seed creation more fault-tolerant#36

Installation#

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

First installODBC Driver version 17.

pipinstall dbt-synapse

Authentication#

Please see theAuthentication section of dbt-sqlserver's README.md

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 configureindex anddist indbt_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})
• CLUSTERED COLUMNSTORE INDEX ORDER({{COLUMN}}) # seedocs for performance suggestions

Distributions#

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

SeeCHANGELOG.md