This dbt package contains macros that:

• can be (re)used across dbt projects running on Azure databases
• define implementations of dispatched macros from other packages that can be used on a database that speaks T-SQL: SQL Server, Azure SQL, Azure Synapse, etc.

This package provides "shims" for:

• dbt-utils (partial)
• dbt-date (partial)
• dbt-expectations (limited regex & timeseries functionalities)
• dbt-audit-helper (except compare_relation_columns)

Wherever a custom tsql macro exists, dbt_utils adapter dispatch will pass to tsq_utils. This means you can just do {{dbt_utils.hash('mycolumnname')}} just like your friends with Snowflake.

To make use of these TSQL adaptations in your dbt project, you must do two things:

1. Install both and tsql-utils and any of the compatible packages listed above by them to your packages.yml

   packages:
     # and/or calogica/dbt-date; calogica/dbt-expectations; fishtown-analytics/dbt-audit-helper
     - package: dbt-labs/dbt_utils
       version: {SEE DBT HUB FOR NEWEST VERSION}
     - package: dbt-msft/tsql_utils
       version: {SEE DBT HUB FOR NEWEST VERSION}

2. Tell the supported package to also look for the tsql-utils macros by adding the relevant dispatches to your dbt_project.yml

   dispatch:
     - macro_namespace: dbt_utils
       search_order: ['tsql_utils', 'dbt_utils']
     - macro_namespace: dbt_date
       search_order: ['tsql_utils', 'dbt_date']
     - macro_namespace: dbt_expectations
       search_order: ['tsql_utils', 'dbt_expectations']
     - macro_namespace: audit_helper
       search_order: ['tsql_utils', 'audit_helper']

Check dbt Hub for the latest installation instructions, or read the docs for more information on installing packages.

Some helper macros have been added to simplfy development database cleanup. Usage is as follows:

Drop all schemas for each prefix with the provided prefix list (dev and myschema being a sample prefixes):

dbt run-operation sqlserver__drop_schemas_by_prefixes --args "{prefixes: ['dev', 'myschema']}"

Drop all schemas with the single provided prefix (dev being a sample prefix):

dbt run-operation sqlserver__drop_schemas_by_prefixes --args "{prefixes: myschema}"

Drop a schema with a specific name (myschema_seed being a sample schema name used in the project):

dbt run-operation sqlserver__drop_schema_by_name --args "{schema_name: myschema_seed}"

Drop any models that are no longer included in the project (dependent on the current target):

dbt run-operation sqlserver__drop_old_relations

or for a dry run to preview dropped models:

dbt run-operation sqlserver__drop_old_relations --args "{dry_run: true}"

Macro Support

generally, SQL Server and Azure SQL have a larger scope of SQL commands that are implemented. So sometimes commands are not supported on Synapse. Additionally, some common SQL conventions are not supported in TSQL, so it will never be possible to port a macro that uses it.

• ❇️: dbt-utils's version works without modification
• ✅: works in dbt-synapse and dbt-sqlserver
• ☑️: works only in dbt-sqlserver
• ⭕: still needs to be ported to TSQL
• ❌: will never work in TSQL

Integration test support:

Sometimes, the macros work, but the integration tests used to let us know if they're working when making pull requests do not work. So we disable the test. The takeaway is to be somewhat-leery of building a dependency on this macro.

• ✅: works in dbt-synapse and dbt-sqlserver
• ☑️: works only in dbt-sqlserver
• ⭕: strange bugfix going onL
• ❌: doesn't work

Read more about these macros in the dbt-utils package repo.

Read more about these macros in the dbt-date package repo.

Read more about these macros in the audit-helper package repo.

Read more about these macros in the dbt-expectations package repo.

use at your own risk! it was supported at once point, but the code base has evolved significantly since to include many nested CTEs, which aren't suported today in TSQL. Click here to upvote and get the feature supported!