sql server compatibility

[jgilfillan]

Can someone provide some basic instructions on how to make this package sql server compatible? Is it just a case of adding a sqlserver function to each macro? TIA

[jaypeedevlin]

Welcome. We're excited about the possibility of you making a contribution!

I would recommend waiting until #188 is merged before getting started as it changes some of the fundamentals of how the package works, but once that is done the key things you need to take care of:

• Making sure the adapter you're supporting implements the type helpers used in the source models.
• Adding overwrites for the custom type helpers if required
• If required, creating adapter-specific version of the upload_* macros.
• Adding SQLServer CI support

The last one is perhaps the trickiest, as it may involves setting up some infrastructure. We could potentially use some guidance from the sql server adapter maintainers as to how they've setup testing for their adapter.

[visch]

Welcome. We're excited about the possibility of you making a contribution!

I would recommend waiting until #188 is merged before getting started as it changes some of the fundamentals of how the package works, but once that is done the key things you need to take care of:

• Making sure the adapter you're supporting implements the type helpers used in the source models.
• Adding overwrites for the custom type helpers if required
• If required, creating adapter-specific version of the upload_* macros.
• Adding SQLServer CI support

The last one is perhaps the trickiest, as it may involves setting up some infrastructure. We could potentially use some guidance from the sql server adapter maintainers as to how they've setup testing for their adapter.

For the last point here's a SQL server spun up in CI https://github.com/AutoIDM/autoidm-target-mssql/blob/main/script.sh could tweak this for GH actions

[ataft]

Looks like #188 is merged. I haven't verified, but imagine that the https://github.com/dbt-msft/tsql-utils package will help with the type helpers and upload_* macros. If using that the tsql-utils package, since many dbt utils have been moved to core, you need the following in dbt_project.yml:

dispatch:
  - macro_namespace: dbt
    search_order: ['tsql_utils', 'dbt']
  - macro_namespace: dbt_utils
    search_order: ['tsql_utils', 'dbt_utils']

@jgilfillan I don't have time to implement SQL Server compatibility right now, but can help. One thing to note is that SQL Server does not support nested CTES, but I haven't checked if anything in this project requires nested CTES.

[jgilfillan]

name and schema are keywords in SQL Server that must be quoted in queries, either with double quotes (eg. "schema") or square brackets (eg [schema]). Any thoughts on the best way to implement this db-specific syntax within the model scripts?

[jgilfillan]

Opened PR #248 which will close this issue.