tsql-utils

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.

Compatibility

This package provides "shims" for:

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

Usage

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.

Installation Instructions

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.

tsql-utils specific macros

Cleanup Macros

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

Legend

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

dbt-utils

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

category	name	supported	integration test
schema test	equal_rowcount	❇️	✅
schema test	equality	❇️	✅
schema test	expression_is_true	❇️	✅
schema test	recency	❇️	✅
schema test	at_least_one	❇️	✅
schema test	not_constant	❇️	✅
schema test	cardinality_equality	❇️	✅
schema test	unique_where	✅	❌
schema test	not_null_where	✅	❌
schema test	not_null_proportion	❇️	✅
schema test	relationships_where	✅	✅
schema test	mutually_exclusive_ranges	⭕	❌
schema test	unique_combination_of_columns	❇️	✅
schema test	accepted_range	❇️	✅
introspective macros	get_column_values	❌	❌
introspective macros	get_relations_by_pattern	❌	❌
introspective macros	get_relations_by_prefix	❌	❌
introspective macros	get_query_results_as_dict	✅	✅
SQL generators	date_spine	✅	✅
SQL generators	haversine_distance	✅	✅
SQL generators	group_by	❌	❌
SQL generators	star	❇️	✅
SQL generators	union_relations	❇️	❌
SQL generators	generate_series	✅	❌
SQL generators	hash	☑️	☑️
SQL generators	surrogate_key	✅	✅
SQL generators	safe_add	❇️	✅
SQL generators	pivot	❇️	✅
SQL generators	unpivot	❌	❌
SQL generators	unpivot_bool	❌	❌
web	get_url_parameter	❌	❌
web	get_url_host	❌	❌
web	get_url_path	❌	❌
cross database	current_timestamp	✅	✅
cross database	dateadd	☑️	☑️
cross database	datediff	☑️	☑️
cross database	split_part	☑️	☑️
cross database	last_day	✅	✅
cross database	width_bucket	✅	✅
jinja helpers	pretty_time	✅	❌
jinja helpers	pretty_log_format	✅	❌
jinja helpers	log_info	✅	❌
materializations	insert_by_period	✅	❌

dbt-date

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

category	name	supported	integration test
Date Dimension	get_date_dimension	✅	✅
Fiscal Periods	get_fiscal_periods	✅	❌
Date	convert_timezone	✅	✅
Date	date_part	✅	✅
Date	day_name	✅	✅
Inner	day_of_month	✅	✅
Inner	day_of_week	✅	✅
Inner	day_of_year	✅	✅
Inner	week_start	✅	✅
Inner	week_end	✅	✅
Inner	week_of_year	✅	✅
Inner	iso_week_start	✅	✅
Inner	iso_week_end	✅	✅
Inner	iso_week_of_year	✅	✅
Date	last_week	✅	❌
Date	month_name	✅	❌
Date	n_days_ago	✅	❌
Date	n_days_away	✅	❌
Date	n_months_ago	✅	❌
Date	n_months_away	✅	❌
Date	n_weeks_ago	✅	❌
Date	n_weeks_away	✅	❌
Date	now	✅	❌
Date	periods_since	✅	❌
Date	this_week	✅	❌
Date	from_unixtimestamp	✅	✅
Date	to_unixtimestamp	✅	✅
Date	today	✅	✅
Date	tomorrow	✅	✅
Date	yesterday	✅	✅

dbt-audit-helper

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

name	supported	integration test
compare_relations	✅	✅
compare_queries	✅	✅
compare_column_values	✅	✅
compare_relation_columns	❌	❌

dbt-expectations

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!