Dbt-sqlserver

Huge thanks to GitHub users **cody-scott** and **prescode** for help with this long-awaited update to enable `dbt-core` 1.7.2 compatibility!

Updated to use dbt-fabric as the upstream adapter (https://github.com/dbt-msft/dbt-sqlserver/issues/441#issuecomment-1815837171)[https://github.com/dbt-msft/dbt-sqlserver/issues/441#issuecomment-1815837171] and (https://github.com/microsoft/dbt-fabric/issues/105)[https://github.com/microsoft/dbt-fabric/issues/105]

As the fabric adapter implements the majority of auth and required t-sql, this adapter delegates primarily to SQL auth and SQL Server specific
adaptations (using `SELECT INTO` vs `CREATE TABLE AS`).

Additional major changes pulled from fabric adapter:

- `TIMESTAMP` changing from `DATETIMEOFFSET` to `DATETIME2(6)`
- `STRING` changing from `VARCHAR(MAX)` to `VARCHAR(8000)`

Future work to be validated

- Fabric specific items that need further over-rides (clone for example needed overriding)
- Azure Auth elements to be deferred to Fabric, but should be validated
- T-SQL Package to be updated and validated with these changes.
- Integration of newer dbt-core features.

Another minor release to follow up on the 1.4 releases.

Replacing the usage of the `dm_sql_referencing_entities` stored procedure with a query to `sys.sql_expression_dependencies` for better compatibility with child adapters.

Minor release to follow up on 1.4.1 and 1.4.0.

Adding `nolock` to information_schema and sys tables/views can be overriden with the dispatched `information_schema_hints` macro. This is required for adapters inheriting from this one.

This is a minor release following up on 1.4.0 with fixes for long outstanding issues.
Contributors to this release are [cbini](https://github.com/cbini), [rlshuhart](https://github.com/rlshuhart), [jacobm001](https://github.com/jacobm001), [baldwicc](https://github.com/baldwicc) and [sdebruyn](https://github.com/sdebruyn).

Features

- Added support for a custom schema owner. You can now add `schema_authorization` (or `schema_auth`) to your profile.
If you do so, dbt will create schemas with the `authorization` option suffixed by this value.
If you are authorizing dbt users or service principals on Azure SQL based on an Azure AD group,
it's recommended to set this value to the name of the group. [153](https://github.com/dbt-msft/dbt-sqlserver/issues/153) [#382](https://github.com/dbt-msft/dbt-sqlserver/issues/382)
- Documentation: added more information about the permissions which you'll need to grant to run dbt.
- Support for `DATETIMEOFFSET` as type to be used in dbt source freshness tests. [254](https://github.com/dbt-msft/dbt-sqlserver/issues/254) [#346](https://github.com/dbt-msft/dbt-sqlserver/issues/346)
- Added 2 options related to timeouts to the profile: `login_timeout` and `query_timeout`.
The default values are `0` (no timeout). [162](https://github.com/dbt-msft/dbt-sqlserver/issues/162) [#395](https://github.com/dbt-msft/dbt-sqlserver/issues/395)

Bugfixes

- Fixed issues with databases with a case-sensitive collation
and added automated testing for it so that we won't break it again. [212](https://github.com/dbt-msft/dbt-sqlserver/issues/212) [#391](https://github.com/dbt-msft/dbt-sqlserver/issues/391)
- Index names are now MD5 hashed to avoid running into the maximum amount of characters in index names
with index with lots of columns with long names. [317](https://github.com/dbt-msft/dbt-sqlserver/issues/317) [#386](https://github.com/dbt-msft/dbt-sqlserver/issues/386)
- Fixed the batch size calculation for seeds. Seeds will run more efficiently now. [396](https://github.com/dbt-msft/dbt-sqlserver/issues/396) [#179](https://github.com/dbt-msft/dbt-sqlserver/issues/179) [#210](https://github.com/dbt-msft/dbt-sqlserver/issues/210) [#211](https://github.com/dbt-msft/dbt-sqlserver/issues/211)
- Added `nolock` to queries for all information_schema/sys tables and views.
dbt runs a lot of queries on these metadata schemas.
This can often lead to deadlock issues if you are using a high number of threads or dbt processes.
Adding `nolock` to these queries avoids the deadlocks. [379](https://github.com/dbt-msft/dbt-sqlserver/issues/379) [#381](https://github.com/dbt-msft/dbt-sqlserver/issues/381)
- Fixed implementation of `{{ hash(...) }}` for null values. [392](https://github.com/dbt-msft/dbt-sqlserver/issues/392)

Under the hood

- Fixed more concurrency issues with automated Azure integration testing.
- Removed extra `__init__.py` files. [171](https://github.com/dbt-msft/dbt-sqlserver/issues/171) [#202](https://github.com/dbt-msft/dbt-sqlserver/issues/202)
- Added commits to be ignored in git blame for easier blaming. [385](https://github.com/dbt-msft/dbt-sqlserver/issues/385)

- [Elliot2718](https://github.com/Elliot2718) made their first contribution in https://github.com/dbt-msft/dbt-sqlserver/pull/204
- [i-j](https://github.com/i-j) made their first contribution in https://github.com/dbt-msft/dbt-sqlserver/pull/345

Features

- Support for [dbt-core 1.4](https://github.com/dbt-labs/dbt-core/releases/tag/v1.4.0)
- [Incremental predicates](https://docs.getdbt.com/docs/build/incremental-models#about-incremental_predicates)
- Add support for Python 3.11
- Replace deprecated exception functions
- Consolidate timestamp macros

Bugfixes

- Add `nolock` query hint to several metadata queries to avoid deadlocks by [Elliot2718](https://github.com/Elliot2718) in https://github.com/dbt-msft/dbt-sqlserver/pull/204
- Rework column metadata retrieval to avoid duplicate results and deadlocks by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/368
- Model removal will now cascade and also drop related views so that views are no longer in a broken state by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/366
- Fixed handling of on_schema_change for incremental models by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/376

Under the hood

- Fixed lots of testing concurrency issues
- Added all available tests as of dbt 1.4.6

**Full Changelog**: https://github.com/dbt-msft/dbt-sqlserver/compare/v1.3.1...v1.4.0

<details><summary>PR changelog</summary>
<p>

- Bump pre-commit from 2.20.0 to 3.2.0 by [dependabot](https://github.com/dependabot) in https://github.com/dbt-msft/dbt-sqlserver/pull/344
- Bump docker/build-push-action from 3.2.0 to 4.0.0 by [dependabot](https://github.com/dependabot) in https://github.com/dbt-msft/dbt-sqlserver/pull/331
- [pre-commit.ci] pre-commit autoupdate by [pre-commit-ci](https://github.com/pre-commit-ci) in https://github.com/dbt-msft/dbt-sqlserver/pull/316
- Bump wheel from 0.38.4 to 0.40.0 by [dependabot](https://github.com/dependabot) in https://github.com/dbt-msft/dbt-sqlserver/pull/343
- Copy for workflow schtuff by [dataders](https://github.com/dataders) in https://github.com/dbt-msft/dbt-sqlserver/pull/350
- avoid publishing docker from other branches than master by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/351
- bump pre-commit by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/353
- fix pre-commit for python 3.7 by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/354
- use 127.0.0.1 to avoid issues with local testing by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/358
- allow for more flexible local testing with azure auth by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/359
- credit where due by [dataders](https://github.com/dataders) in https://github.com/dbt-msft/dbt-sqlserver/pull/355
- remove condition for azure testing by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/360
- ignore owner when testing docs in azure by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/365
- impl of information_schema name closer to default by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/367
- Add nolock by [Elliot2718](https://github.com/Elliot2718) in https://github.com/dbt-msft/dbt-sqlserver/pull/204
- Fix concurrency issues and document create as by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/368
- add debug tests by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/363
- add concurrency test by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/362
- add aliases tests by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/361
- add ephemeral error handling test by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/364
- mark db-wide tests as flaky by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/369
- remove azure max parallel test runs by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/370
- add nolock to more metadata calls to avoid deadlocks by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/374
- add query comment tests by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/375
- add seed tests and add cascade to drop relation by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/366
- make testing faster by running multithreaded by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/372
- add tests for changing relation type by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/373
- [incremental models] add tests, various bugfixes and support for incremental predicates by [sdebruyn](https://github.com/sdebruyn) in https://github.com/dbt-msft/dbt-sqlserver/pull/376

</p>
</details>

Minor release to loosen dependency on dbt-core and pyodbc