Pagination in ADF using REST API giving duplicate entries

Question

Hi
I have an API end point that gives 300 records per page and contains an absolute URL for next page
I am using copy data activity with 'AbsoluteURL' pagination rule and it's value $['nextLink'].
The pipeline works for few APIs but for some APIs, it imports same page again and again. This causes it to dump duplicate data in database (my sink).

Is there any solution for this?

Answer 1

Hi @Neeraj Chimwal ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query.

As I understand your ask you are trying to copy the data from REST API to database, however, it is leading to duplicates in the target as even after implementing pagination , it's returning same page records sometimes. Please let me know if my understanding is incorrect.

Generally, this is not expected , implementing $['nextLink'] should return next page records. But as in your case, you are getting duplicate data . To avoid getting duplicate record in the target , you can try following approaches:

• Try loading the data to a temporary /dummy table and then try using a custom stored procedure to delete duplicate records from the target. You can also use dataflow for this matter . Kindly check these resources: Remove Duplicate Rows using Mapping Data Flows in Azure Data Factory
  Stored procedure to delete duplicate rows
• After loading data to a dummy table, you can also try to use copy activity and use query option and select unique records using row_number or rank function and copy the same to the main table.

Hope this will help. Please let us know if any further queries.

------------------------------

• 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