I am trying to find a robust and correct solution for syncing data from different sources to my local relational database.
I have different sources of data for eg: CSV, REST API and probably network shared file system. These sources at any point of time gives the snapshot of the system and there is a script which runs weekly and updates my local database with all the changes. For more detailed example suppose there is an API which provides me the information of all the database names active in a VM, the next time I read data there may be the case that there are more or less number of databases depending on whether they were added or remove from last read. The remote system doesn't maintain that historic information.
In my local system I have to record and maintain these information.
Right I am reading the data from the API and then reading database from my local database make a set of the both the database names and doing set operation to check those information using operations like
difference etc. I don't know if this approach would scale well.
As discussed in first line, what is the correct and robust way to do it?