Write Batch Size Default

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?

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!

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.

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!

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.