tsql-utils

This dbt package contains macros that:

• can be (re)used across dbt projects running on T-SQL based database engines
• 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, Microsoft Fabric, etc.

Compatibility

This package provides "shims" for:

• dbt-utils (partial)
• dbt-date
• dbt-expectations (partial)
• dbt-audit-helper (partial)

Note that in 2024 we refactored all the T-SQL adapters to have dbt-fabric as their base. This means that you need to be using version 1.7 or newer of your adapter to use version 1.0.0 or newer of this package.

Installation Instructions

To make use of these T-SQL 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; dbt-labs/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.

Usage

You can use the macros as documented in the original packages, but with the following caveat:

dbt_date does not properly dispatch to the T-SQL implementation of the get_fiscal_periods macro. To use the T-SQL implementation, you must use the tsql_utils namespace:

{{ tsql_utils.get_fiscal_periods(...) }}

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 fabric__drop_schemas_by_prefixes --args "{prefixes: ['dev', 'myschema']}"

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

dbt run-operation fabric__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 fabric__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 fabric__drop_old_relations

or for a dry run to preview dropped models:

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