Destination MSSQL - Add Typing and Deduping

[jbfbell]

We removed "normalization" from Destination MSSQL. Normalization was a process which would extract data from the raw json format and cast the data into tables with the appropriate types. For other destinations, we implemented Typing and Deduping to replace normalization and we will bring typing and deduplication to MSSQL if there is sufficient interest

The purpose of this discussion is to gauge interest in bringing this functionality back to MSSQL.

Without typing and deduping, you would need to integrate another tool, such as dbt to accomplish the same result. While integrating another tool gives you greater customization over the data format, it comes with additional operational overhead.

Should Airbyte bring Typing and Deduping to MSSQL?

[JustGitting]

Hi @jbfbell,

1+ vote from me.

I have to use MS SQL server for work and found the normalisation with DBT (Destination V1) to be very very slow as it uses views. It might be due to the sync is based on cursors and not CDC.

I hope that Typing and Deduping with Destination V2 would be much faster, but I'm not an expert.

[tmotyl]

Support for MSSQL is the most important factor why I evaluate Airbyte. In my usecase I'm fetching data from ERP system to ecommerce.

[krutoileshii]

+1 on this.

[lfigueira]

+1 - this would be extremely helpful

[Andrew-InTheBox]

+1 interest for sure

[MigrateData]

+1 from me, it would be awesome to have

[cedstrom]

+1000. Why does this continue to pop up, other than prioritization? The same question was asked for Clickhouse. This is obviously one of the best features of Airbyte and having to orchestrate dbt on top of syncs is a huge pain that airbyte already solves. I can understand that you have limited time to support all the connectors, but there's really no reason to go removing this feature proactively since the end user can turn it off if they want to and it's SUPER CRAZY useful in it's current form.

[mikebaldry-acu]

+1 from me too. This is an incredibly useful feature to have integrated. As others have mentioned above, I think the "typing and deduping by default with optional opt-out" is the way to go for pretty much any RDBMS.

[georgeemr]

It was working for my tables, so +1 with optional opt-out.

[jeffsdata]

I just got through a 7-month project to get Airbyte approved and running in production, and just found out that the final tables are no longer created (we were using an old version of Airbyte and the MSSQL connector, so they were created in all our testing). It's not the end of the world - using JSON directly or writing dbt/Python to convert it is OKAY... just not as excellent/fun/exciting as it was.

[dturner-mmal]

+1. As a leading enterprise RDBMS, many will use MSSQL source and destination. In our evaluation, we found MSSQL as a source was recently certified under #34873, but overall has some limitations:
a) Unfortunately the certified source connector seems does not support modern auth or token auth required for Azure SQL or Dataverse TDS.
b) The destination is still marketplace (not certified), and does not include the Destination V2 features to maintain parity with other RDBMSs.
I hope these may be addressed soon, or may need to consider workarounds via a custom Java connector, such as mentioned in the JDBC for Postgres: https://docs.airbyte.com/connector-development/tutorials/building-a-java-destination#step-5-implement-write or python approaches.