We have a webapp that will rely on large CSVs from external vendors every month. When I say large, we are looking at around 6gb so a few million rows. Probably, 2-5 CSVs. This webapp will also allow users to enter, correct, and flag/delete data. The number of columns and cleanliness of the data is not guaranteed from the vendor perspective. Also, the data may overlap (we can't show overlapping data in our webapp).
I have been thinking of multiple ways to approach this:
- Load these CSVs into a table per CSV matching the column headers and then create "views" of what we need.
This solution seems like it's the easiest to implement and maintain because we can just import the CSV every month to the CSV tables and things just work. It seems the least efficient and would have the most problems with data integrity. Also, the views would be hella complex.
- Load these CSVs into our own schema each month and build our webapp off of our own schema.
This solution seems harder to implement and maintain because when the import comes in every month, you're going to have to run your import and this might break your existing data. It would be the best that your webapp is your own schema and you can just "map" to it. Furthermore, the CSVs will contain data that invalidates older data so it will be hard to update. If you were to go this route, would you do the import in Java/C# or SQL? It seems Java/C# would make sense so that we can process our business rules but it's slower...
- Load CSVs into a table per CSV and just run SQL queries against it for the webapp to create models that match what the webapp needs.
This solution runs into the same problem as the view solution except now your SQL queries are hella complex and you have the headache of maintaining them. If the external vendor changes there schema, it could break your SQL queries, although you'd problem just change the CSV before importing to match your importer.
Then we have these questions:
If a user updates a row that the CSV populated, say we're going with #2, which is what I'm leaning to, how do we preserve that update when the CSVs come in?
- How do we have the ability to rollback a CSV import? What if we thought the CSV import was good for 1 month and now we have a ton of user generated data to that CSV?
It seems like there isn't a trivial solution and either way I have to "take it". Am I missing anything? Can anyone find or think of a trivial solution?