tsql-utils
This dbt package contains macros that:
This package provides "shims" for:
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.
To make use of these T-SQL adaptations in your dbt project, you must do two things:
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}
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.
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(...) }}
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}"