Incremental Refresh - Duplicates or Missing Updates

Hi - An update on the solution to my issue....

My mistake was in the columns I used to configure Incremental Refresh. This was due to my misunderstanding of how incremental refresh works - which I think could be improved across Microsoft documentation and others' tutorials/guides.

The correct combination is:

1. Filter by record created date
2. Detect changes on last modified date

This is because incremental refresh creates partitions (groups) of data using the value in the Filter (1) column (i.e. when that record was created, grouped into month long partitions). It is this Filter (1) column which is used as the 'key' for incremental refresh (not the actual table primary key). The maximum value of the detect changes column (2) is calculated for each group; if that changes the entire group is discarded and reloaded (by running the query on FIlter (1) column). What it does not do, is track specific records using their primary key.

My understanding is: when the data is refreshed:

1. The whole Filter (1) column is loaded from the data source.
2. The grouping is then applied, and a new maximum value of the detect changes column (2) is calculated per partition.
3. For each partition, if the maximum value of the detect changes column (2) is different to the currently loaded data - then that partition is flagged to require a full refresh.
4. Each flagged partition is then discarded.
5. Each flagged partition is loaded from the data source using the Filter (1) column to load just the subset of data which meets that filter (e.g. data for July 2022).

This means that the value in Filter (1) column should not change for a given record, through its lifetime. If it does, then that record will fall under more than one partitition query through its lifetime.  If/when partitions are refreshed, that record could be duplicated in your loaded data. 

Detect changes column (2) must always change whenever any field of the record changes through its lifetime. If it does not, then that record (the partition that record falls in) will not be flagged as changed, and won't be refreshed. It makes sense that you could use a Detect changes column (2) which is only updated when (for example) columns A, B or C are changed if those are the only columns you load into your data model.

Hope that helps!