Incremental refresh issues

[Anonymous]

I'm trying to implement Incremental Refresh on a large dataset.

We have the dataset pre processed elsewhere because it's simply massive and we're only looking to use the most current record of each item.

My issue is that i have incremental refresh set to store 3 years of data and update data in the past year only.

If the most current record in our system was between 1 and 3 years old, then it's in the historical dataset and not the one being refreshed.

If this old record is newly modified, it will a current date and fall into my incremental refresh policy criteria, but when the refresh data is paired with the historical data, i now have two records of a single item and it invalidates my one-to-many table relationship and the data refresh fails.

I've tried implementing table.buffer(), but because you need to load the table into memory before you can sort the column by date and then remove duplicates, it results in the incremental refresh failing due to memory.

Any recommendations or solutions to this issue?

[john_ach]

It sounds like your issue might be in the date field that you are using for the incremental refresh.

If you use a date field which can change on a given record, then on successive data refreshes after that field has changed the record could appear in different incremental refresh partitions. Partitions are only cleared and reloaded ('refreshed') if they are within the refresh window. So if your record had made it into the historic partition then it would remain there, as well as appearing in the refreshed partitions - as you described.

The fix would be to use a date field from the table which does not change.

You could then consider using your 'last modified date' field as the Detect Data Changes field to further minimse your data refreshes.