Forum Discussion
Dataflow to store snapshots
Hi Everyone,
I'm in the need to use a dataflow solution to store snapshots of a table from our db. I know it's not ideal - IT resources limited. I've searched the community, found a blog by Chris Webb which offers how to create a dataset to store snapshots which I've used and was successful. Problem with this is it's a dataset now that I can build off, but any necessary changes could result in losing the data - that's why I'd like to create a dataflow to store the snapshots of just the particular table I need point-in-time history from, then bring that into a dataset where I can build my full model.
Blog I've used from C Webb to build a dataset to capture snapshots:
I tried to use the same logic but with a dataflow, however it seems the Power Service creates an auto-generated line as the last step in the logic. When I connect to the dataflow, this results in no data. If I remove my last step, #"Filtered Rows", so that the auto-generated line references the #"Changed Type" step then it loads the current days data, but doesn't retain historical snapshots when pulled into a desktop file.
Here are the Incremental Refresh Settings I applied to the dataflow:
Any help is greatly appreciated!
3 Replies
That 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.
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.
