How can I retain null values for decimal type columns when copying data from Snowflake source to Azure Blob Store Excel sink?

Kumar 20 Reputation points
2024-06-19T13:52:16.7766667+00:00

I am using the ADF copy activity to transfer decimal data type columns with null values from Snowflake source to Azure Blob Storage CSV/Parquet file sinks. However, the null values are getting converted to empty values. Is it possible to retain these null values in the Parquet/CSV files sink the same way it is in the source?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,070 questions
{count} votes

Accepted answer
  1. Nandan Hegde 30,951 Reputation points MVP
    2024-06-20T11:28:24.9866667+00:00

    when you say within the CSV file null value is getting converted to empty, what are you expecting? Do you want those values to be preserved as null?

    Below is the CSV dataset configuration :

    User's image

    wherein by default the null is replaced with empty string. You can specify the value you want for null values within the file

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. phemanth 8,480 Reputation points Microsoft Vendor
    2024-06-19T16:42:55.2733333+00:00

    @Ananthakumar Annamalai Selvakumar

    Thanks for posting your question in the Microsoft Q&A forum

    it is possible to handle null values when transferring data using Azure Data Factory (ADF) copy activity. However, it might require some additional steps or configurations. Here are a few methods you could consider:

    1. Script Activity: After the copy activity, you can use a script activity in ADF to update values with ’ ’ to null1.
    2. Dataset Configuration: In the dataset configuration, you can specify what value you want to be considered as null2.
    3. Derived Column: You can create a derived column and use the iifNull({ColumnName}, 'Unknown') expression to handle null values3.
    4. SQL Query in Source Section: In the Source section of the COPY activity, you can write a SQL query and use NULLIF for the column for which you need to replace NULL values

    please refer:https://stackoverflow.com/questions/76559063/how-to-make-adf-copy-data-activity-convert-into-null

    Hope this helps. Do let us know if you any further queries.