From JSON to Snowflake Azure Data Factory - More than 1 column

Question

Azure Data Factory allows you through COPY INTO activity to copy a JSON file to Snowflake in one column table. (VARIANT TYPE)

According to the documentation:

For JSON format, direct copy only supports the case that source Snowflake table or query result only has single column and the data type of this column is VARIANT, OBJECT, or ARRAY.

I would like to have instead of one column, two columns, being the first one the JSON file and the second one the load_date which would come from the pipeline activity date.
With parquet files I had some issues.

Does someone have a solution for this ?

Answer 1

I found a workaround:

I used the normal copy activity in ADF and copy only one column. Then I create a script in ADF that copies the previous table and adds a new column.

BEst!

Answer 2

Hello @kosmos ,

Thanks for the question and using MS Q&A platform.

I doubt it is possible using single ADF copy activity, as the document clearly states that the ADF service checks the settings and fails the Copy activity run if the following criteria is not met:

But as a workaround I would recommend giving a try by adding an additional column to your source section and do a mapping and see if the copy work.
Sorry I wasn't able to test this as I don't have a snowflake instance, but you can simply create an additional column in your copy activity source and add mapping and see how it goes.

In case if it fails, then as mentioned in the documentation, the criteria have to be met to run a successful copy.

Do let us know how it goes.

Thank you