ADF Pipeline Error When Moving Data from one CosmosDB Account to Other

Taranjeet Malik 421

I'm trying to setup a Data Flow to move documents from one CosmosDB collection to another and getting the following error:

Job failed due to reason: at Sink 'TargetdevicemanagementeventsINsitpemssharedcosmos': {"innerErrorMessage":"All retries exhausted for 'UPSERT' bulk operation - statusCode=[400:0] itemId=[7d5ed366-10c3-4860-8374-5b8973609102], partitionKeyValue=[[{}]]"}

I can't find the itemID mentioned in the above error in the source CosmosDB collection. If I re-run the pipeline, it fails with the same error, but for a different itemID every time.

Can someone please share some ideas as to what to look for or if they've seen this before?

Thanks

Taranjeet Singh

phemanth 5,840 Reputation points Microsoft Vendor

2024-03-20T10:06:16.1733333+00:00
@Taranjeet Malik

Thanks for reaching out to Microsoft Q&A.

Here are the consolidated insights and potential solutions based on the information provided and common CosmosDB Data Flow issues:

Potential Causes and Solutions:

Data Validation Issues in Target Collection:

Schema Mismatch: Ensure the data being upserted conforms to the schema defined in the target CosmosDB collection. Any data type mismatches, missing required fields, or invalid values can trigger 400 errors.

Unique Keys: If the target collection has unique keys defined, verify that the documents you're trying to upsert don't violate these constraints.

Solution: Review your data transformation logic in the Data Flow to make sure it's correctly mapping data to the target schema and handling null or missing values appropriately. You might need to add data cleansing or validation steps in the Data Flow.

Missing or Incorrect Partition Key:

Partition Key Definition: If your target collection has a partition key defined, check if your Data Flow is correctly setting the partition key value for each document. Incorrect partitioning can lead to write throttling or errors.

Solution: If you're using a partition key in the target collection, ensure your Data Flow explicitly sets the partition key value for each document based on the source data. You can use a transformation or a script to achieve this.

CosmosDB Throughput Limitations:

Throttling: If the target collection is experiencing high write volume or has insufficient provisioned throughput (RUs), you might encounter throttling errors (similar to a 429 error).

Solution: Monitor CosmosDB throughput and consider increasing it temporarily or permanently if it's consistently reaching the limit. Alternatively, enable automatic scaling in CosmosDB to adjust throughput dynamically based on usage.

Data Flow Configuration Issues:

Write Batch Size: The default write batch size in Data Flow might be too large for CosmosDB requests. You can experiment with reducing the write batch size in the Data Flow settings.

Retry Logic: Review your Data Flow retry configuration. If the current retry settings are causing excessive retries for failing documents, you might need to adjust the number of retries or timeout values.

Solution: If none of the above solutions resolve the issue, explore adjusting the Data Flow's write batch size or retry configuration. Refer to the Azure documentation for details on these options.

Hope this helps. Do let us know if you any further queries.
phemanth 5,840 Reputation points Microsoft Vendor

2024-03-21T12:13:20.3066667+00:00

@Taranjeet Malik We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Taranjeet Malik 421 Reputation points

2024-03-25T00:00:55.76+00:00

Hi @Phemant

Thanks for sharing the details. Will revisit the issue with the team that's responsible for managing data layer and get back.

Thanks