T-SQL support

[sdebruyn]

I would like to have support for dbt-sqlserver and dbt-synapse. What would I have to do to make this happen? I am one of the maintainers of those 2 adapters :)

[dave-connors-3]

hey @sdebruyn! what a wonderful question! apologies for the delay on the response.

I honestly am not sure what the answer is! Given that the team who built this has really limited experience with either of those adapters, i'm not sure how well we'd be able to maintain it. Would hate to not be able to let other users use the package though!

what are your thoughts here?

[b-per]

Hi @sdebruyn ! We have not tested the package on T-SQL and I don't know if it would work or not (feel free to try! 😄 )

The first steps will be to make the dispatched macros work with T-SQL. The main ones are:

• select_from_values()
• recursive_dag()
• cross_db_shims

Saying so, it is not sure that once you get a TSQL version of all those macros the package will work out of the box. I believe that we are using a lof of group by 1, 2 etc... in our models, which to my knowledge is still not supported by Microsoft.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[b-per]

Hi @sdebruyn . We are adding support to more adapters and are keen to revisit this issue. I searched a bit online and kind of saw that group by 1, 2 might be supported in T-SQL now?

Would you still be interested in adding support for TSQL?

To see what would need to be changed you could start with running the integration tests with a profile pointing to a TSQL DW

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[jj12000]

+1 to this feature, I would be interested in this package having support for T-SQL, thanks!

[joshrodgersKA]

+1 as well. We are on dbt-fabric now and this would be super useful.

[b-per]

It looks like Fabric now supports nested CTEs (finally). So we might be able to do a bit more testing on that front.

[alittlesliceoftom]

FYI, this is the error set on attempting build on a synapse installation:

So as well as the CTE work listed above, it looks like there are some minor data type corrections where VARCHAR is needed instead of STRING etc.

[b-per]

Thanks for running this first test!

Yes, it looks like there are some types to fix to get it working. Some things around the boolean values we have and other about varchar/string as you mentioned.

I don't have easy access to a Fabric DW to test but if I do I will try to explore further.

[b-per]

As a quick update, I managed to get access to a Fabric DW and I have been working on this for a bit but the changes are far from being trivial. The lack of support for boolean and arrays in T-SQL doesn't make it very easy to adapt the package and it requires significant refactor. I hope that I can get a PR ready in the next couple of weeks.