Blob datatype conversion

Question

We have migrated few of our tables from on premise oracle to Dedicated SQL Pool via ADF. In that we have a table with blob datatype column. The records in that on-premise (oracle) table are in the format as shown below:-

But when we migrated to dedicated pool, we could seethe column datatype has changed to nvarchar and records are in the format as shown below:-

So is there any way to validate the data

Thanks

Answer 1

Hi @Anonymous ,

Thanks for posting query in Microsoft Q&A Platform.

Azure Synapse SQL not supports BLOB data type. Hence you are seeing BLOB converted as you mentioned above. Click here to know more about data types supported in Azure Synapse SQL.

Also, When we use Copy activity and Oracle as source then BLOB data type will be converted as interim data type and there by loads to Sink. Click here to see interim datatypes related to oracle data types.

If you would like to validate data, then we can consider validating other columns and see all of them loaded or not. OR we can consider validating that column data alone between stage(blob storage) and sink.

Between, below video helps with validating data logic in ADF.
https://www.youtube.com/watch?v=i2PkwNqxj1E

We can also use Assert Transformation to data quality and validation checks.
https://www.youtube.com/watch?v=_NzWpTRxt0s

Hope this helps. Please let us know if any further queries.

--------------------

Please consider hitting Accept Answer button. Accepted answers help community as well.