Schema drift not working on Unpivot activity/transformation on Azure Data Factory

Question

Hi,

I'm trying to use the "Unpivot" transformation on a Source that doesn't have schema pre-defined (Schema Drift is activated).
The "unpivot" transformation generates the following error:

at Unpivot 'UnpivotForPlaysOrViews': web | LG_VOD_Plays (#) is not of the same unpivot datatype

Due to the fact that my source is set up as schema drift, the Type of the columns is decided at runtime (usually smallint / boolean based on my input data). However, my UnPivot Type is an integer, which is why I get this error. I even tried to set the Type to Any but still got the error.

Is it possible to force my source input columns to always use a particular datatype? This is achievable if the columns are fixed, but in my case columns keep changing so I'm unable to predefine the type.

Answer 1

Hi @Vivek Komarla Bhaskar ,

Thanks for using Microsoft Q&A forum and posting your query.

As per my understanding you have enabled schema drift for your source and the source column types change between short and integer and you have defined the unpivot column type to Integer and hence you are seeing error when the source column type is short. From your source data screenshot, I have also noticed that except Timestamp column, all other columns are of type short. Please correct if my understanding is wrong.

If that is the case, in order to convert all columns of type short to Integer to overcome the error, you can have a derived column after source and using column pattern, you can convert the data type of those drifted columns from type short to integer

Below is a sample I tried with my test data to dynamically convert the column types using Derived column transformation.

Below is how I am using the column pattern condition to convert the data type of source column as per my requirement.

Column data type changed when you do a data preview:

Here is the script from sample for derived column for converting the data type from short to integer . You can use the same and just replace filter1 with your source stream name and it will add the derived column automatically.

filter1 derive(each(match(type == 'short'), $$ = toInteger($$))) ~> convertAllSourceShortTypeColumnsToIntegerType

Hope this helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.