Bump to version 1.9 to track Fabric Adapter

[Benjamin-Knight]

The upstream fabric adapter has been bumped to version 1.9 to support micro batching, looking at the code there appears to be no reason we cannot just track this update as all the requirements have been implemented in a way that this adapter will just run.

I did note that they have removed the merge incremental model as a supported type. This could be a breaking change if someone was using this type.

[Benjamin-Knight]

After some more testing whilst it does work out of the box its only with datetime2 data types as that's what Fabric supports.

I've added in a change to the upstream implementation so that it should handle datetime columns as well and a test to cover it.

[cody-scott]

Did they add a reason about why they dropped the merge type? That feels like a big change for anyone who has adopted it currently.

My preference would be that the merge statement remains unless there is a really good reason to drop it.

[cody-scott]

I also think that one piece that is going to create a significant diversion from fabric is their support for nested CTEs.
https://learn.microsoft.com/en-us/sql/t-sql/queries/nested-common-table-expression?view=sql-server-ver16

Since SQL Server will likely never implement this, any of their logic that depends on the nested CTE is going to be problematic for this adapter.

At some point it will probably make sense to branch this back out unfortunately, or at least rely on them for some of the auth/connection logic, but the actual transformation logic coming from here.

[Benjamin-Knight]

Did they add a reason about why they dropped the merge type? That feels like a big change for anyone who has adopted it currently.

My preference would be that the merge statement remains unless there is a really good reason to drop it.

I agree with the merge type drop, there was no documentation about it I just noted the change in the commits.

microsoft/dbt-fabric@2ac5e23#diff-529f3242d3cb0dbb13da6e1544cc0579af3346c5dfa161a037fa061b928aeb30R169

I believe all fabric did was push the implementation back to the base DBT adapter, I can't see any specific macros for it. I can update the supported types to change it back.

[Benjamin-Knight]

Updated the adapter to override the Fabric adapter and add merge back as a supported type. The functionality is derived from the base DBT adapter but it works again.

I've left the insert_overwrite strategy as removed as it doesn't work on SQL server anyway.

[Benjamin-Knight]

There have started to be macro changes that cause nested CTE queries to be executed. I've reimplemented two macros that were causing test failures due to this.

They got pulled in automatically as I've set a dependency of dbt-fabric>=1.9.0,<1.10.0 however this may no longer be safe as it appears that any patch in the upstream adapter may now contain what to this project is a breaking change.