DataflowGen2 in a Data Pipeline is intermittently inserting data twice despite uniqueness measures

Thanks Adamk. Right now the results continue to be inconsistent in Fabric via the Pipeline and the Power Query, and when I check my API call it seems to produce the correct # of rows aka the unduplicated count. I am using Supermetrics to create and generate the API query and results.  Here are some screenshots with supoprting documentation. 

• When I run them in Supermetrics Query Manager the results are typically around ~40k rows and ~190 rows respectively (see slides 5&6)
• When I put it into Power Query and run results in Power Query to get a row count it’s the same, around ~40k rows (slide 2)
• In my Power Query I have a left anti-join in the Power Query that checks for dates already in my destination table so that it doesn’t add in any dates that already exist (this is more of a failsafe)
• It’s set to append to a table I have set up in my Microsoft Fabric data warehouse
• I also have a Data Pipeline in place that then deletes the last 31 days worth of data from the table each night and then upon that success, it calls on the Power Query data flow to insert the new latest 31 days of data into the table (slide 7)
• I currently have the Data Pipeline set to run at 1AM Pacific Time each night
• The problem I am experiencing: sometimes (intermittently, not every time) the data added to my table from the Power Query flow is exactly duplicated
• For example, last night the row count added to Power Query was 86,262 – which I suspected was duplicated
• When I re-ran the Data Pipeline manually this morning it successfully deleted those ~86k rows and then inserted the correct amount of rows which is 43,131 – exactly half of what was inserted last night
• I know that my pipeline or flow isn’t running twice or concurrently because I have the left-anti join set up to not insert any dates that already exist into the table
• I also tested having the Power Query run on its own every night independent of the Data Pipeline and still got duplicate results

So far I haven’t been able to recreate this duplication when I manually run the data query /pipeline myself during the day manually. It seems to only happen on my overnight schedules. Is there something about the time at which I am running it that could be impacting the data coming through twice?

Do you have any other hypotheses as to why this could be happening? I have set my Data Pipeline to run again today on a schedule to see if time impacts it. And to see if it’s always when it’s from a schedule or if I can get it to duplicate when I manually trigger it.

I appreciate your help and time and anything you can suggest for me to try and test!