Forum Discussion
Premium Incremental Refresh Detect data changes (How does it identify the correct row?)
Fascinating conversation. Coworker and myself have been beating our heads against the wall trying to figure out how this works - its way more complicated than necessary in my opinion. But....I think the short answer to the original question (which never got directly addressed),= is it doesn't need to know what the unique key is to find the same record. And that's because it just reloads the entire partition involved in a change. Just deletes by partition, not by key value.
But then lot of this conversation is making assumptions based on documentation rather than actual data tests. It's unfortunate that Microsoft isn't very revealing of the internals on how this works. And note...if you use the "Get the latest data in real time with DirectQuery" option then it won't load anything for the current partition - instead it's going against your source for the most recent data.
Our tests did this...our data goes back 50 years and can change at any time point in history. The reason is we merge customer records and re-arrange households and golden records on a daily basis so all of history can get a new key value in our fact tables. So our refresh and archive settings are 50 years. Detect data changes is turned on and Get latest data in real time and only refresh complete years is turned off. We set the refresh column to modified timestamp column and setup the parm/filter to business date timestamp such as an order date (converted to time via DAX function) which of course becomes the basis for the partition ranges.
We then updated the modified timestamp to getdate() for specific orders in history. We then profiled the SQL during refresh after waiting a couple of minutes. What we observed was first a query to get max(modifiedtime) for every partition. Then it does a partition range query for every partition where there was a newer modified timestamp. So how it behaves like what was said here.
The one exception was...when I also modified the date which is the basis for the partition so it should move partitions (such as order date) then nothing happened during refresh. I was hoping it was smart enough to rebuild both partitions involved but instead it did nothing at all. My assumption is it gets confused if the partitioning date changes (which they say isn't supposed to). The solution to this will be just knowing on a daily basis the data will leak these kind of changes and say every weekend do complete refresh to get these leaks resolved.