Daily Incremental Refresh with Delta Capture and Reporting

Yes, it does that very well! But I noticed an interesting bug that if a record changes and then in the future that record changes again, the code doesn't detect at the "Date Level" that a change was detected. But if I expand the Date to show all of the records, it shows that the record was indeed changed.

Welcome to the world of Nyquist and Shannon.  That's why I prefer event based reporting over snapshots.

Nyquist and Shannon - theory of signal detection. I didn't get that far in my psychophysics/acoustics classes 😉 Is there any work around given my constraints of snapshot reporting? I'm still investigating with the DBAs and system admin to see if there is a 'lastupdated' or 'lastmodified' field on my source tables I could use. I assume this is the event based reporting you mean?

Either way, I'd be stuck with daily snapshots but if I had confidence that I could capture only valid changed records each day, it would limit the size of my snapshots and it would make the manual review easier.

Not so much signal detection but more sampling rate discussion. According to Nyquist and Shannon you need to sample at a rate at least twice as high as your change rate.  So let's say you have weekly snapshots - that means you will be blind to any changes that happen to your data within the week.   If you need better quality you need to increase the sampling rate (for example to twice a day)

If you want to be cute you can post-process your snapshots to throw away all rows that have not changed.

This isn't a large scale application. We're talking about less-than 100k records in each table. Some of them only  change once a year, some once a month. Some may change daily but those are definitely  smaller data sets.

It turns out I was able to get Date Created and Last Updated fields exposed in the reporting layer. So I can include those fields now if needed. Do you think that might help improve the code? I could only bring records that changed between Day A and Date B?

Some of them only  change once a year, some once a month. Some may change daily but those are definitely  smaller data sets.

That data is unsuitable for incremental refresh. Incremental refresh only works with immutable data.

You will need to implement your own partition management, up to and including periodic full refreshes across all partitions.

Based on your original message, I assumed I didn't actually need to use incremental refresh in the Service. Rather, because the records are so small, that continuously appending new files to the folder should be okay because PBI will be able to just look at the MAX file date and the next "max" file date (today v. yesterday) to keep the refreshes tidy.

Frankly, if your data volume is low, don't bother with Incremental Refresh.  Do a brute force flush and fill.

Appreciate your patience and time on this 🙂 Yea, brute force refresh is good. Can you think of any way to deal with the problem of a record changing on a given day and then changing again in a few days? Am I simply stuck with this issue in the fantastic code you wrote for me because of the Nyquist/Shannon sampling issue you described?

I'm just wondering if the Last Updated field could be of any help? 

Your only other option would be switch to CDC and event based reporting. In its simplest form that is a reference table with all changes to all fields being recorded one by one.  It's a bit more computation, but requires dramatically less storage, and allows you to recreate the state of each transaction for any point in time you choose. You can then also do things like jitter analysis and sankey flow diagrams etc.