Hi,
looking for some advice on which column(s) I should be using when setting up incremental refreshes for my data set.
I am currently pulling data from a claims database and i have a column for a claim called InsertDate, which is when the claim was created in the system. I also have a LastUpdateDate, which is when the claim was last updated. Each claim has 1 row in the system and only the LastUpdateDate gets changed when the claim is updated.
Now, users can go in and update a claim from 30 or 40 years ago. We have claims that go back to 1970 and sometimes these old claims are still being updated due to subrogration and other reasons. Therefore, i would need to make sure that i capture all these changes in my incremental refresh period in order to not miss anything.
I was originally thinking of using the InsertDate as my incremental refresh date for historical (first time load) and incremental load and then using the LastUpdateDate for the Detect Data changes field. However, in order to capture all of the changes in teh system that could happen, i would need to go back 50 years everytime i do a refresh. For example a claim from 1970 could get updated today and the incremental refresh would have to check all years going back to 1970 in order to capture just the change from today. I feel like this is not really helping me much since all years will have to be queried in the source database which seems to defeat the purpose of the incremental refresh. Am i correct in my thinking or am i missing something?
