ADF Copy Activity retry behavior with Cosmos DB

Rares POP 1

Having an ADF Copy Activity that copies data (possibly skipping over some fields) between two Cosmos DB containers, what is the behavior if things are failing and retried? I see the 'retry' and 'retryTimeout' options.

I mean, if half the data is copied from the first container into the second, then some connection fails (not data format failure, but other types of failures happen), when the activity is retried, does the Copy Activity know where it left off and continues, or it starts from the beginning and it will end-up with duplicate records?

Thanks!

1 answer

HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2022-03-16T04:59:59.853+00:00
Hello @Rares POP ,
Thanks for the question and using MS Q&A platform.

As we understand the ask here is how copy activity behaves if we have issues while copying in flight with the retry option as "ON" , please do let us know if its not accurate.
When we copy data the process happens in batches , so lets say that while copy is in progress and you have an issues , the activity will try to copy that last batch again .
Regarding duplication of records , since you must have a partition key defined on the sink side if the same set of records are pumped in again , it is going to error out anyways .

Please do let me if you have any queries.
Thanks
Himanshu

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Please sign in to rate this answer.
Rares POP 1 Reputation point

2022-03-16T10:43:28.49+00:00

I'm using ADF to summarize some records I have in one Cosmos DB container to a second Cosmos DB container.

The source maps the first container, and the sink maps the second.

source dataset, I use a query in the form of 'SELECT VALUE root FROM (SELECT SUBSTRING(e.timestamp, 0, 10) AS eventDate, count(1) AS eventCount, e.name FROM e WHERE SUBSTRING(e.timestamp, 0, 10) > "2020-01-01" AND SUBSTRING(e.timestamp, 0, 10) <= "2020-01-02" GROUP BY SUBSTRING(e.timestamp, 0, 10), e.name) AS root

The partition key for the sink is an autogenerated guid, which is not provided by the query.

My question is - in case of a failure, can I end up with wrong results in the sink? I operate on data that's not changing and basically my operation is supposed to be atomic and can have unintended consequences if it's run multiple times. So, if there are some network glitches or other temporary failures that trigger a 'retry' on the Copy Activity, it will re-run the same query again and insert duplicate data?

Thanks!

HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2022-03-23T19:14:40.107+00:00

Hello @Rares POP ,
Apologies for the delay in reply from our side .I am unable to repro the scenario but i am confident that you shoule be good .
On a side note I am assuming that you are using the INSERT opration , just let to let you know if have though of exploring the UPSERT operation ? May be thats what you are looking for .
https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-cosmos-db?tabs=data-factory#azure-cosmos-db-sql-api-as-sink

Thanks
Himanshu

HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2022-03-24T18:54:41.603+00:00

Hello @Rares POP ,
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 back with the more details and we will try to help .
Thanks
Himanshu

HimanshuSinha-msft 19,381 Reputation points Microsoft Employee

2022-03-28T19:07:31.06+00:00

Hello @Rares POP ,
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 .
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread .
Thanks
Himanshu

Rares POP 1 Reputation point

2022-03-29T08:22:02.85+00:00

Unfortunately it's still unclear to me how the retry mechanism in ADF Copy Activity works, so it's really hard to accept the answer :(.

In case of an error, is the 'Copy Activity' block just called again? Is there any state maintained between retries (since it works on batches, does it know where it failed and continue from there)?

I cannot reproduce a failure, but Cosmos DB says it offers 99.99999 reliability, so it can go down or have glitches. What happens if that happens mid copy?

As of yet, I did work around by using a temporary SQL table where I copy things from Cosmos DB and if things are failing, I start over. And then I use a stored procedure to summarize things in a permanent SQL table, in a transactional way. But this seems a bit too complicated.
Sign in to comment