How to get highest watermark on a sharepoint list with more then 5k records?

Question

I was following below in order to implement sharepoint list data pulling from ADF, https://learn.microsoft.com/en-us/answers/questions/528638/azure-data-factory-lookup-filter-query-for-sharepo?page=1&orderby=Helpful&comment=answer-529617#newest-answer-comment

so far it works for lists which are less then 5k, but if the list somehow grow beyond 5k say like 10k or 13k the query stops working and throwin server returned an error.. am simpy writing an orderby query with modified data with desc, that is giving some issue and filter query is not able to work properly in ADF, is there any workaround for this, we can take a look at logic apps and power automate, but that defeats the purpose of what we exactly have in ADF. Any help would be appreciated, thanks!

Answer 1

Hello @Anmol Ganju Sorry to hear that the Sharepoint's limitation is blocking your ADF capabilities!

Did you check SharePoint's list view threshold, which limits operations on lists with more than 5,000 items.

More Info:

This issue occurs because SharePoint Online uses the Large List Resource Throttling feature. By default, the list view threshold is configured at 5,000 items. When the defined item limit in a list is exceeded, the message is displayed.

It is not possible to modify throttling for SharePoint Online. Additionally, while it is possible to modify throttling for Sharepoint on-premises, it is not recommended.

If you encounter this error, it is recommended to configure indexed fields and/or adjust your queries to maintain performance, which I think you have already implemented!!!,

Answer 2

Anmol Ganju Are you saying that you are getting throttling error intermittently with 13K+ records? If that's the case then you can probably create a support ticket so that someone can look into your environment. Please let us know if you need any help creating a support ticket.

Additionally, can you try changing your logic to fetch only 5000 records with HTTP Linked service instead of using SharePoint connector and use For loop to fetch more records?