Write Batch Size Default

Alex 996 21

I cannot find any documentation on the Copy Data activity for the Write Batch Size Default. If it is not specified, does it assume the default size for the source?

1 answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-29T11:35:44.183+00:00

Hi @Alex 996 ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

The value of "Write Batch Size" will be determined dynamically based on the row size by the Azure Data Factory as per documentation.

Kindly let us know for any specific questions on this and we will be glad to assist further.
Please sign in to rate this answer.

1 person found this answer helpful.
Alex 996 21 Reputation points

2020-09-29T14:05:12.053+00:00

Thanks Haritha. I have a couple of follow up questions for you.

We are inserting into SQL Server database, using a stored procedure, without incremental commits. The writeBatchSize definition says "Data Factory dynamically determines the appropriate batch size based on the row size." Will this perform incremental commits when loading the table? If so, how will this affect retry attempts? Will duplicate data be inserted if there was a failure after a commit, and the copy activity is reran?

HarithaMaddi-MSFT 10,136 Reputation points

2020-09-30T08:31:10.29+00:00

Thanks @Alex 996 for the questions. I tried reproducing a scenario but could not accurately confirm but as per my understanding, it is not incrementally committing the data to the table. I reached out to Product team to check if understanding is correct and also to get more insights. I will update you once I hear back from them.

Thanks for your patience!

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-01T12:43:16.543+00:00

Thanks @Alex 996 for your patience. I reproduced it with batch size of 5000 and max concurrent connections property as 1 in ADF sink and was able to observe that it incrementally commits the data. When there was failure at 10001 record, it failed the pipeline by loading first 5000 records. When I reran the pipeline, it loaded all the 10001 records again, so the table count is now 15001. Yes, there is a possibility of duplicate data load in such scenarios and I tested with retry scenario as well where this also inserts duplicates. I got the above understanding confirmed from Product team and without turning on fault tolerant feature, it will fail at the row causing the issue and corresponding batch will fall back to row by row insert. I have attached the GIF in the answer above showing the tests.

Please let us know for further queries and we will be glad to assist.

Alex 996 21 Reputation points

2020-10-01T13:46:28.587+00:00

Very helpful, thank you!

Last set of questions.
Could you define how this works: "Dynamically determines the appropriate batch size based on the row size". Does that mean if there were 1,000,000 records and the batch size was not specified that it would set the batch size to accommodate the 1,000,000 records, or would it set the batch size as a subset of the total number of records?

And finally if, there is a pre-copy script to truncate the target before loading, if there was a commit, and then a failure, then would the pre-copy script execute again on the retry?

Thank you for all your help!

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-06T11:30:04.743+00:00

Thanks @Alex 996 for further set of queries and for the patience again! I was on vacation so there is a delay in responding to the post.

When it dynamically determines the appropriate batch size, my understanding is it will look at the server load and lot of other factors along with row size. In some cases, it may not match with optimal batch size value and hence customers are suggested to adjust batch size in certain scenarios to improve the pipeline performance.

And I tested the pre-copy script to truncate the target before loading, it truncated during retry attempts as well where I have seen records loaded in second run are not duplicated with first run.

Hope this helps! Please let us know for further queries and we will be glad to assist.

Alex Paskevic 6 Reputation points

2020-10-06T14:06:17.177+00:00

Thank you for all your help! I hope you had a nice vacation.

You have answered all my questions!

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-06T14:09:11.997+00:00

@Alex Paskevic - Thank you, Glad that we were able to answer your queries.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-07T06:46:48.687+00:00

@Alex Paskevic - We are happy that the answers were helpful to you.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members
Sign in to comment

Share via

Write Batch Size Default

1 answer