How does incremental refresh work on staging tables

[RobertSlattery]

I need to add an index column to a table in a dataset but, incremental refresh breaks this.  I created a staging table and configured it for incremental refresh to load three years of data then refresh one day.  I reference that table in a second query and add the index column there.  I set the RangeStart and RangeEnd parameters to one year so it is manageable for testing in the desktop.

I publish the report and refresh the dataset in the service.

In the service, I can see the full three years of data in the staging table but only one year in the referencing table.  There is no filter on the referencing table.  Why is this?  I don't see any query folding in the referencing query, folding stops in subsequent steps in the staging query after the filter step for IR.

[lbendlin]

Your index must be a composite key including the partition name.

[RobertSlattery]

Interesting!

How can I get the partition name in a column?  I couldn't find any other way so, I just added a hash column based on the RangFrom and RangeTo parameters.

[lbendlin · Accepted solution]

RangeStart and RangeEnd.

Depends on the partition size. When you refresh individual partitions it must not result in duplicate indexes across all partitions.