Situation:
Every day an xlsx file is dropped into a shared folder. It is a snapshot of a database. The snapshots are ranked and have the date they were added as new fields, then the new table and the ever growing QVD are concatenated. When I took this over it had grown to nearly 8 million rows and is growing by 80k a day but that number grows each day and there is the possibility for that number to almost double, to 150k a day, suddenly in the future. I'm worried about how sustainable this is as there has already been performance issues.
Needs:
We want the ability to go through the snapshots to see the state of the database on that day. Data added on Monday might change on Friday, that is why the last person was ingesting the whole snapshot.
Possible Solution:
There is a date modified field and uid for each record, and only about ten fields can actually change per record. So I'm thinking about some sort of table that tracks the changes in order to avoid the large table. I'm still not sure exactly how I would do this, or if this is a bad approach.
I'm looking for advice on how to handle this. What approach would you take?
