dbt-synapse
custom dbt adapter for Azure Synapse. Major credit due to @mikaelene and his dbt-sqlserver
custom adapter.
#
related packagesTo get additional functionality, check out:
- fishtown-analytics/dbt-external-tables which allows for easy staging of blob sources defined in
YAML
, and - dbt-msft/tsql-utils enables
dbt-synapse
to use dbt-utils: the much-loved, extremely-useful collection of dbt macros.
dbt-synapse
and dbt-sqlserver
#
major differences b/w - 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 (see Table Materializations section below for more info)
- rewrite of snapshots because Synapse doesn't support
MERGE
.
#
status & supportas of now, only support for dbt 0.18.0
Passing all tests in dbt-adapter-tests, except test_dbt_ephemeral_data_tests
#
outstanding work:ephemeral
materializations (workaround for non-recursive CTEs) see #25- officially rename the adapter from
sqlserver
tosynapse
see #40 - Make seed creation more fault-tolerant #36
#
InstallationEasiest install is to use pip (not yet registered on PyPI).
First install ODBC Driver version 17.
pip install dbt-synapse
#
AuthenticationPlease see the Authentication section of dbt-sqlserver's README.md
#
Table MaterializationsCTAS allows you to materialize tables with indices and distributions at creation time, which obviates the need for post-hooks to set indices.
#
ExampleYou 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)
#
IndicesCLUSTERED COLUMNSTORE INDEX
(default)HEAP
CLUSTERED INDEX ({COLUMN})
CLUSTERED COLUMNSTORE INDEX ORDER({{COLUMN}})
# see docs for performance suggestions
#
DistributionsROUND_ROBIN
(default)HASH({COLUMN})
REPLICATE
Changelog
See CHANGELOG.md