Trouble loading data into Snowflake using Azure Data Factory

user_612190 0
Viewed 613

I am trying to import a small table of data from Azure SQL into Snowflake using Azure Data Factory.

Normally I do not have any issues using this approach: https://docs.microsoft.com/en-us/azure/data-factory/connector-snowflake?tabs=data-factory#staged-copy-to-snowflake

But now I have an issue, with a source table that looks like this: enter image description here There is two columns SLA_Processing_start_time and SLA_Processing_end_time that have the datatype TIME

Somehow, while writing the data to the staged area, the data is changed to something like 0:08:00:00.0000000,0:17:00:00.0000000 and that causes for an error like:

Time '0:08:00:00.0000000' is not recognized File

The mapping looks like this: enter image description here

I have tried adding a TIME_FORMAT property like 'HH24:MI:SS.FF' but that did not help. Any ideas to why 08:00:00 becomes 0:08:00:00.0000000 and how to avoid it?

azure-data-factory-2 azure-data-factory azure-blob-storage snowflake-cloud-data-platform azure-sql-database
2 Answers

Finally, I was able to recreate your case in my environment. I have the same error, a leading zero appears ahead of time (0: 08:00:00.0000000). I even grabbed the files it creates on BlobStorage and the zeros are already there. This activity creates CSV text files without any error handling (double quotes, escape characters etc.). And on the Snowflake side, it creates a temporary Stage and loads these files. Unfortunately, it does not clean up after itself and leaves empty directories on BlobStorage. Additionally, you can't use ADLS Gen2. :(

This connector in ADF is not very good, I even had problems to use it for AWS environment, I had to set up a Snowflake account in Azure. I've tried a few workarounds, and it seems you have two options:

  1. Simple solution:

    Change the data type on both sides to DateTime and then transform this attribute on the Snowflake side. If you cannot change the type on the source side, you can just use the "query" option and write SELECT using the CAST / CONVERT function.

  2. Recommended solution:

    • Use the Copy data activity to insert your data on BlobStorage / ADLS (this activity did it anyway) preferably in the parquet file format and a self-designed structure (Best practices for using Azure Data Lake Storage).
    • Create a permanent Snowflake Stage for your BlobStorage / ADLS.
    • Add a Lookup activity and do the loading of data into a table from files there, you can use a regular query or write a stored procedure and call it.

    Thanks to this, you will have more control over what is happening and you will build a DataLake solution for your organization.

    enter image description here

user_126071260

My own solution is pretty close to the accepted answer, but I still believe that there is a bug in the build-in direct to Snowflake copy feature.

Since I could not figure out, how to control that intermediate blob file, that is created on a direct to Snowflake copy, I ended up writing a plain file into the blob storage, and reading it again, to load into Snowflake

enter image description here

So instead having it all in one step, I manually split it up in two actions

One action that takes the data from the AzureSQL and saves it as a plain text file on the blob storage

enter image description here

And then the second action, that reads the file, and loads it into Snowflake.

This works, and is supposed to be basically the same thing the direct copy to Snowflake does, hence the bug assumption.

user_6121900
Related