Bulk loading CSVs regularly with row-level status indicators and/or reconciliation

[mhomol]

I have a need for end-users to be able to import CSVs of data regularly into my Dataverse tables.  The files could easily have thousands of rows (even tens of thousands) and may be uploaded/imported a few times a week.  There's a chance that the data uploaded will have issues.  For rows where no issues are encountered, I'd like to perform an upsert into the appropriate table(s) and for the rows where issues are detected, I'd like to place into a holding list for reconciliation.  I'd like to be able to provide the end user with a status of the import and then alert them when completed, where they can reconcile any issues.  The end users will normally be leveraging a model-driven app for their day-to-day use of all this data, and I expect will leverage it for reconciliation as well.

I see the following 2 main options, but I would love to get some input on any alternatives that I'm not considering:

• A dataflow approach
  • End user would need to be trained in where to navigate outside of the model-driven app they normall use
  • Transform splits the extracted information into 2 queries: no issues and issues
  • No issues list is automatically loaded into main destination table
  • Issues list is loaded into the reconciliation destination table
  • Can I alert the user when the dataflow finishes, no matter what the outcome?
  • This seems like the fastest approach.  Am I correct?
• A cloud flow approach
  • I create a Uploads table with SharePoint attachments enabled
  • End user uploads the file within the model-driven app, which then triggers a cloud flow
  • The flow loops through the rows
    • Any row that has no issues is inserted into the main table immediately
    • Any row that has issues is inserted into a reconciliation table immediately
  • Alerts are sent upon completion
  • I know this will run slower, but just HOW slow do you guys think?
  • This feels more user-friendly and seems like I could even do more intricate status indicators if I wanted, as well

I would love anyone's input on what I have written above, even if just to let me know if I'm assessing my options correctly or if I'm not taking some important things into account.

[Devvj · Accepted solution]

Hi @mhomol 

as it seems to be fairly often this is done, I personally would perfer something like Azure Data Factory or KingswaySoft on SSIS, since they have great functonality and ability to run upserts directly while configurable to not break API-limits etc, but then again that might be to big of a stretch and fairly advanced to get your users trained on.
Of the 2 alternatives in the Post I would try the Dataflow approach.

[mhomol]

Thank you so much for the input.  I will absolutely review Azure Data Factory as well to see if there's a place for it.  I think the big thing is trying to make it feel simple for end users while also maintaining some level of speed.

[Devvj]

Ye, as said it is probably kinda over the top to let everyone on the the ADF Studio to run things, but if you got some "developers" hangning around you could set up some cool stuff where you build a simple canvas app where users upload the CSV and then call the ADF Pipeline via REST.
Just brainstorming here ;D

[mhomol]

I love it, especially the brainstorming 😀.  Yes, that's exactly the kind of thing I'm interested in.  I don't mind seeing if the end user can handle the dataflow interface, but it's pretty rigid in its interface, so I love the idea of trying to get it down to a single button click for the end user and let the back-end do it's thing.