Forum Discussion
Dataflow to store snapshots
Super UserThat last line is how dataflows implements incremental refresh and will always be re-inserted if you try removing, reordering, or otherwise modifying. Thus, you have to work around it.
To do this, simply add a custom datetime column and specify the date so that if falls into the incremental refresh partition range. This would be today's date normally, but should be yesterday's date if you have the Only refresh complete days option turned on.
Finally, make sure you change the settings so the incremental refresh is looking at your new custom datetime column.
MarkLaf Thanks for the reply, I believe I understand what you're saying but I think I've already tried that and mentioned that above when I said if I remove the #Filtered Rows step all I get is the current date's data, none of the previous. Here's what I have based on what you said, I'd appreciate any additional help you could offer.
- MarkLaf
Any time you change the query, it will do a full refresh of all the incremental refresh partitions, and because your date is hard entered to midnight yesterday, the only partition that would filter in data would be yesterday's. Not entirely sure why you would be seeing no data at all, though. Did you forget to set a daily scheduled refresh or not manually refreshed after updating the query?
I would try turning on the only refresh complete days option and instead of using UtcNow - 1 in your added column, use the RangeStart parameter which will always be inside the parition's filter. On initial refresh, this should load today's data in each of your 46 partitions. However, with each daily scheduled refresh, 46th partition will fall off, and the new 1st partition will have today's data - i.e. after 45 days you'll have complete 45-day historical data.
Keeping your added date as midnight yesterday (which should equal the RangeStart of the first complete day relative to when you are refreshing) should also work without duplicating today's data in all partitions, but with the RangeStart method you can at least verify all the partitions are behaving as expected.