@mwegener thanks I understand things partly. Can you confirm below reasoning and answer some of the questions within?
Let's say this is a query:
Step 1: Source
Step 2: Filtering source with RangeStart and RangeEnd
Step 3-8: All sorts of transformations
Step 9: Result table
This is the table at step 2:
| Index | Created | Modified |
| 1 | 1-1-2020 | 5-1-2020 |
| 2 | 2-1-2020 | 2-1-2020 |
| 3 | 3-1-2020 | 4-1-2020 |
| 4 | 4-1-2020 | 4-1-2020 |
| 5 | 5-1-2020 | 5-1-2020 |
Using RangeStart (4-1-2020) and RangeEnd (5-1-2020) only on the Created column anything after step 2 will look like this in PQ Editor at step 9:
| Index | Created | Modified |
| 4 | 4-1-2020 | 4-1-2020 |
| 5 | 5-1-2020 | 5-1-2020 |
At the same time it will still look like this in Dataflow (Dataflow removes the RangeStart and RangeEnd filters):
| Index | Created | Modified |
| 1 | 1-1-2020 | 5-1-2020 |
| 2 | 2-1-2020 | 2-1-2020 |
| 3 | 3-1-2020 | 4-1-2020 |
| 4 | 4-1-2020 | 4-1-2020 |
| 5 | 5-1-2020 | 5-1-2020 |
- It is required to have the Created and Modified columns available in the result table (step 9), otherwise you can't select them in incremental refresh setup. Meaning that any other tables like dimensions, referencing this data, also need to have these columns. A problem that I foresee is that in these dimension tables duplicates will be removed which renders these columns useless. Please tell me there is a solution for this? Or tell me this assumption correct:
- When there are 5 tables in a Dataflow, and 1 table is the 'mother table' (the other 4 tables reference this table) you only need to setup incremental refresh for the mother table and incremental refresh will do it's magic on the 'children tables', meaning that the children tables do not need to have the Created and Modified columns.
- Incremental refresh for Dataflows looks at the Created and Modified columns in the result table after step 9. It does not impact at step 2, other than removing the RangeStart and RangeEnd filters.
- For the Created column:
- The part 'from the past' in 'Store rows from the past' and 'Refresh rows from the past' means it is relative to the last date in the Created column and not relative to the actual date the Dataflow refresh happens.
- Setting 'Store rows from the past' to 4 days, only Indexes 2, 3, 4 and 5 will be in the result table from the Dataflow. Index 1 will be removed.
- Setting 'Refresh rows from the past' to 2 days, only Indexes 4 and 5 will be refreshed. Meaning that Index 3 will not be updated. Conclusion: setting 'Refresh rows from the past' to anything less than 'Store rows from the past' is risky if you don't know how far changes will go back. Meaning that Index 2, 3, 4 and 5 will be in the result table and only Indexes 4 and 5 are refreshed.
- For the Modified Column:
- Setting 'Only refresh data if the maximum value in this field changes' means that nothing will be refreshed because the maximum value of this column is evaluated for each of the periods in the incremental range. I assume a period in the incremental refresh is equal to the setting 'Refresh rows from the past' and is not equal to the setting 'Store rows from the past'. Meaning that Index 3, even though it has changed in the last 2 days, will not be refreshed and thus have outdated data. Same conclusion as before: setting 'Refresh rows from the past' to anything less than 'Store rows from the past' is risky if you don't know how far changes will go back.
- With these settings Index 1 will never be included even when it changes.
