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.

  • In SQL server you can use change tracking or temporal tables to track down changes. Take a look at those solutions.
    – MBuschi
    Jul 15 '21 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.