Copy Activity from Rest API Duplicating Results

Hi pace-jp ,
Thanks for using Microsoft Fabric Community for posting your query.

Thanks for sharing the chart. The fact that some IDs are duplicated up to 700 times while others aren’t duplicated at all shows this might not be just a pagination issue. It seems like something is going wrong when the data is written to the Lakehouse table. Here are a few things you can check to figure out what’s happening:

1. Check the Default Schema Mapping:

   Make sure the table schema (columns and data types) matches what’s coming from the API. Pay attention to the unique ID field, if it isn’t handled properly in the mapping, it could lead to duplicates.

2. Parallel Processing:

   Check if your pipeline is  processing or writing data in parallel . If it is, this might cause duplicate rows to be written.

3. How the Data is Written:

   Look at how the pipeline writes data to the table. For example, is it writing in batches or one row at a time? Sometimes, retries or errors in batch writes can result in duplicates.

4. Test with a Smaller Dataset:

   Change your pagination to pull just 1 or 2 pages (e.g., 100 records) and see how the table behaves. This smaller dataset will make it easier to spot what’s going wrong.

5. Clean Up the Table:

    You can add a step to remove duplicates from the table after the data is written. Use the unique ID field to keep only one copy of each row.

6. Test Writing the JSON Data:

   Take the JSON output (which worked fine) and try writing it to the Lakehouse table directly. If that also causes duplicates, it confirms the problem lies in the table write process.
    

If these workarounds don't resolve the issue, it may indicate a bug. Consider reaching out to Microsoft by raising a support ticket.

If this post helps, please accept it as solution to help others benefit and a kudos would be appreciated.
Please reach out for further assistance

Regards,
Vinay.

I observed very similar behavior, and I believe it's a bug in the Fabric Copy Activity, creating cartesian products of all arrays in the JSON file. I did find a workaround and described it in detail here : Fabric : Hidden Collection Reference in Copy Activity - Mattias De Smet

Hope it helps!