Copy Data Activity with Upsert Operation to MongoDB Not Updating Records

Question

I am trying to use Azure Data Factory's Copy Data Activity to copy data from a CSV file into a MongoDB collection using the upsert operation. However, I am facing an issue where the pipeline run indicates that 4 rows were written, but no records are being updated in MongoDB.

Here is my scenario:

Input CSV:

_id,name,gender,age
67c88f641b4dd8cef2342e9d,Sam,M,25
67c88f641b4dd8cef2342e9d,Tam,F,23
,Dam,M,45
,Cam,F,54

Observations:

1. When I map _id as required for upsert, I expect the record with _id = 67c88f641b4dd8cef2342e9d to be updated (based on the second row of the CSV). However, this does not happen.
2. If I leave _id unmapped, it creates new records for each row in the CSV, which is essentially an insert operation and not an upsert.
3. I enabled data consistency verification and logging, but I do not see any useful logs or errors indicating what went wrong.

Sink Mapping Configuration:

Below is the mapping configuration I used for the sink:

{
"mappings": [
    {
        "source": {
            "name": "_id",
            "type": "String"
        },
        "sink": {
            "path": "$['_id']"
        }
    },
    {
        "source": {
            "name": "Name",
            "type": "String"
        },
        "sink": {
            "path": "$['Name']"
        }
    },
    {
        "source": {
            "name": "gender",
            "type": "String"
        },
        "sink": {
            "path": "$['gender']"
        }
    },
    {
        "source": {
            "name": "age",
            "type": "int16"
        },
        "sink": {
            "path": "$['age']"
        }
    }
]
}

Questions:

1. Why is the upsert operation not updating the existing record in MongoDB?
2. Are there any additional configurations required to make the upsert work correctly in the Copy Data Activity?
3. How can I debug this further since enabling logging and data consistency verification did not provide meaningful insights?

Any guidance or suggestions would be greatly appreciated!

Answer 1

@Ramakrishna Vaitla

Thanks for reaching out to Microsoft Q&A.

It seems like you're encountering a common issue with the upsert operation in Azure Data Factory (ADF) when working with MongoDB.

Please Check below steps

• Double-check that your mapping configuration is correct. The _id field should be mapped properly to ensure that the upsert operation can identify existing records. Your current mapping looks correct, but it's worth verifying.
• MongoDB is case-sensitive, so ensure that the field names in your CSV and the MongoDB collection match exactly, including the case.
• Ensure that the data types in your CSV match those expected by MongoDB. For example, if _id is expected to be an ObjectId in MongoDB, but it's a string in your CSV, this could cause issues.
• Make sure that the upsert operation is correctly configured in your ADF pipeline. The upsert operation should be set to update existing records based on the _id field.
• Since enabling logging and data consistency verification didn't provide useful insights, you might want to add additional logging or use a different method to verify the data being sent to MongoDB. For example, you could temporarily write the data to a different sink (like a SQL database) to inspect it more easily.
• Inspect the MongoDB logs to see if there are any errors or warnings that might indicate why the upsert operation is not working as expected.
• Try running the pipeline with a minimal dataset (e.g., just one or two rows) to simplify debugging and isolate the issue.

Please refer the links

:Upsert Option in ADF Copy Activity

:Azure Data Factory - Copy Data Task - Upsert is not updating

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.