System Design: Very Large CSV Imports Every Month

Question

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:

1. 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.

2. 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...

3. 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:

1. 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?

   2. 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?

Answer 1

"CSV" is not the right word for what they deliver you. It's the format they deliver it to you in, and with very weak guarantees. Most people refer to a 6 GB CSV file with unreliable and missing data simply as "the data," and I think that's more accurate here. You have a supplier who makes some data available to you, and you are responsible for serving it to your customers in some way.

Load these CSVs into our own schema each month and build our webapp off of our own schema.

This looks best. You are applying business logic to the supplied data to maintain its integrity and performance. Your supplier is offering you very weak guarantees, and you are offering your customers strong guarantees. The format they give you is a long string. In any tech interview, if they ask to build persistent access to a long string, the answer is to preprocess it, which is what this proposal of yours is.

You should assume your users will ask for more features regarding how to interface with the data. Maybe they want an undo buttonn or audit log, both of which require you to have your own proprietary (meta)-data.

Contrast this with a case where they make the data available to you in a cleaned or functional format e.g. a database. Then you would make the same decision about owning the data yourself or using their format, only now their format has much stronger guarantees.

Then we have these questions:

Those are good, difficult questions, and like I said it sounds like your company is in the business of solving them for your users, and yes, these use cases certainly encourage using your own schema.

Off the top of my head, this looks like a version control problem, so research something like "version control large data." One Google hit: https://datascience.stackexchange.com/questions/5178/how-to-deal-with-version-control-of-large-amounts-of-binary-data

Answer 2

This depends heavily on the requirements, what "enter, correct, and flag/delete data" exactly means, and your further requirements for preprocessing and postprocessing. Is the editing done on a per-row basis? Each row in full, or are there blocks in that row which don't need to be processed? Completely manual, or are there also some automatic cleanup/preprocessing steps? How is "overlapping" detected? There must be some criterion to detect the identity of a row.

You also didn't state what comes afterwards in your data flow. I guess correcting the data is no end in itself, so there is also to consider what happens after the editing was done: is there a requirement to write the data back into CSV files again, using the original format? Will it be stored/archived somewhere? Or will the data be processed in a completely different way?

So once you got these overall requirements, you should create a data model/schema which supports exactly the editing and processing steps you need, no less, no more. This may be to some degree your approach #2, but

• since you mentioned mixing up data from different months as a problem, integrating them is no requirement - so keep the data separated per month. Have a master table Datapool with a primary key "date" or "month", maybe the CSV file name as a unique attribute, and create the other tables as detail tables of the master table

• model only the part of the data your users and processes really need to display and edit; the remaining data can be kept, for example, in a string column. Don't invest time in modeling details when you don't know (yet) if you will ever need them.

• make sure you can support all the steps afterwards. Knowing those requirements should help you to decide which data which must be kept, and which can be ignored.

When creating the data model, keep in mind that the data probably is not of the quality you like to have it. For example, it may not be possible to have all "unique" constraints in place for every attribute which should be unique after the editing.

Answer 3

A solution we adopted was to create mappings. The tool would grab the first n rows of data skipping anything that looked like boilerplate so that the person handling the import would be able to see a sampling of the data and the row headers to control the mapping.

The "CSV" schema was driven by the header row if available and the number of columns. We had a way to map the data from the CSV schema to the application schema. We did encounter a couple things we needed special handling for:

• Default values for required columns (we had cases where the data was known, but not provided since it was the same for all records)
• Custom date format recognition (note: when your date samples include 1/2/2018 and 1/3/2018 it can be difficult to detect which is the month and which is the day)

There's more I'm sure, but I'm a couple years removed from working on that project.

This mapping was stored in the database so we could look it up based on the header signature for future data dumps. It helped a lot with handling the formatting of the data.

Processing consisted of reading in data and writing to the database in chunks. That meant read in 100 rows or so, prepare them in a collection so we could eliminate noise a bit easier, and then write them in that chunk. It speeds up data import when writing to the database without needing to have everything in memory at once.

Any errors were written to a CSV held in the database record that referenced the file so that we could figure out what was wrong with our mapping (i.e. month and day were reversed, or a required column was sparsely filled). The CSV, had the line number, the specific error, the data that caused the problem, and the expected target column.

The more difficult issue for you is detecting duplicate data. As long as you have efficient queries/search for your application's data store, you have a couple options:

• Write the raw input to a prepared area, then post-process as a separate step
• Query the database as you go along.

The trade-off is that in terms of preparing the data into a known schema, the first option is going to ingest the data much more quickly. Allowing you to triage the mapping before it is actually ready for consumption. The second option will have your data ready in one pass, but it can take as much as 10x the processing time.