I have a data source that doesn't do incremental changes. I need to pull the self-same 100 million row record set each day, whereby maybe 1% of the data is new, and 0.5% is updated. There sadly is no column that indicates or hints where this data is. There is a unique identifier that is a random alphanumeric key (not numerically increasing, which would have been nice).
What is the most efficient way to keep this data up to date? I can't just do a replace either, because about 0.5% of the data falls off each day and needs to be retained.
My initial thought is to load the 100 million (new day) records into a staging area and do a compare at that point rather than the typical "update or insert/ upsert" logic which would entail comparing 100 million records against 100 million records, although perhaps that's inevitable.
This data doesn't need to be real time/ live either ... in the past, I've found that for data in reporting databases (as opposed to production/ operations) --- typically it was faster to just insert it all then delete duplicates (esp since there is no row locking in this case, so multiple threads could do this) vs. a traditional upsert. I mean in theory an update is like an insert/ delete but for some reason, updates were always slower ....
Maybe there are no real options, or the best option is to desperately hunt for any way I can ascertain readily where the 1% changed in the 100 million might be.
I'm using Postgres but can also use SQL Server, if that matters.