We have several websites from where we download csv files - say 100 such websites. We don't have a control over their schedules, so we download files from them every few hours. Each such file can be assumed to have 20GB worth of data where 1 row should be on an avg. 4KB. So around 5 million rows per file.
The first time this activity was performed, we read, transformed and normalised the data from all these files across 10 tables in a PostgreSQL Database. Now, every time we get a new file from each of these websites, we want to update our database. This would involve adding any new rows available, updating existing rows if corresponding columns changed or marking the row in the tables with a flag if the corresponding row was suddenly absent from the source(s).
Is the most optimal way to do this is to use some diffing tool (maybe a UNIX
diff) to find out what got added, what got removed and what got changed, and then only for those create a bulk
INSERT/UPDATE queries and fire them into the Database? Is there a recommended file format or a tool for this or some sort of out of box solution if say the files are downloaded in
OR are there faster ways like creating temporary normalised tables from new versions of the files and then doing some sort of compare and update into the main customer facing tables?
OR just create temporary but not-normalised table and do the diffing there with the new version of the data and then transform and update the main normalised tables?
I'm new to databases and had also read that we can write sever side function which Databases can execute and do a few things for us - ie., bake transform + normalise logic into such server-side functions and create a trigger to execute them etc.
What is the recommendation?