I had created a flow that reads from SP-API and writes to a single table
I had set the batch and page size set to 3000 and have run this flow with multiple pages without any issues.
Since this is a temp table that will be deleted after I merge it into the final table, I wanted to keep a history of all the exports requested from SP-API, because it is sometimes useful to see what the data looked like on a certain day in the past (I plan to run this job daily).
So, I created a branch so that when a page is read, that same page is written to both the temp table and a history table:
When I ran this new branched flow, it gave me the following results:
Both imports show as canceled.
The temp table contained 907 records (event though the run console shows 0 under the success column)
The history table was empty.
After reading https://connective.celigo.com/t/troubleshooting-why-is-my-job-within-a-flow-that-imports-to-ftp-seemingly-canceled/824 I figured that maybe my page/batch size might be some how affecting this. Maybe the export page is somehow duplicated and causes the 3M size to trigger a cancellation, so I reduced my page/batch size to 1000.
Here are the results:
Once again the run console reports canceled on both tables and that only one page was processed, but when I look at the tables in snowflake both tables contained 1206 rows.
Assuming that I was headed in the right direction, I again modified my page size and batch size from 1000 to 500, and re-ran the flow with the same start date parameters.
Confusingly, this time, the temp table table contained 0 rows and the history table contained 500 rows.
I had previously set the sp-api connection concurrency to 1 in an attempt to throttle my requests to the SP-API, and thought that this might be the problem, so I changed it to 3 thinking that maybe this might be causing a cancellation
This had no effect on the results.
I will continue experimenting, perhaps without branching.