I am in bit of a situation. I have a stubborn client who wants to upload about ~5gb csv file from a web browser. End goal for the file is to reach a SQL Server but I decided that I will first put that in blob and then have a process to read it from Blob and write it to table.
To make matters more complicated, i need to validate whether the file structure (i.e. file format, columncount etc) is accurate before start uploading file to blob. if these are not correct, i must let user know that file upload can't be done.
To achieve these requirements:
Users will upload the file from UI, our code first check if the file format (column name, file extension etc) is correct and then stream the file to blob storage.
once file is uploaded at blob, I am thinking to use azure functions which will read every 10K rows and validate if the data is correct by going to a table A and then inserts the data into the table B. If the some of the data is inaccurate, it will write the data rows in another directory in blob for users to download. I will repeat this process until entire file is stored into table B.
However, I am confused whether to take above mentioned 2 approach or use 3rd approach
- Using Azure Data factory and create a pipeline to get the file from blob and put it in a staging table. once its available at staging table, I need to validate the data by validating it with another table and then insert the valid records into the destination table. In the end, the invalid records will be written into 3rd table (error table) and end point will be given to a user to download the contents of the table.
I am confused whether to use 2nd or 3rd approach. Please suggest if which one may be fast or less likely to have performance issues.
If you can share comments on overall approach, it would great.
Thank you so much
