I've also considered this problem. I have not yet used incremental extracts because of this. Here's my idea:
If Tableau enhanced Incremental Extracts to be more intelligent that would be great. The software should intelligently come up with a unique row ID without asking the user to choose a column. It should be basically like a differential backup using disk imaging software (if you're familiar with that). Meaning: Tableau should look at all rows and, if they have changed, update the row. If they're the same, do nothing. So, if you are going by date (in your example) and the date stays the same but another column's value in that row changes, will the incremental extract pick it up?
Does Tableau assume that all data already in the extract will stay the same? Is the new data always "additive", like an append or SQL Insert?
Just a sampling of my ideas here. Any thoughts?
You can actually control an incremental extract in exactly the way you are suggesting by not including the current/most recent day (or hour, or minute, or second if you have a datetime field available, so that you avoid trying to extract a period before it is guaranteed to be complete. Even if you have a datetime field accurate to the second you can still miss rows in the current second if you have a significant transaction rate, unless you do this.
It's explained in the material from one of the 2011 conference sessions - I think it's probably "Understanding Tableau’s Fast Data Engine - Matthew Eldridge & Richard Wesley" (I know it's one of the ones I've watched, I just can't quite remember which one - I think it's that one).
Basically you just need to set an appropriate filter condition to exclude the most recent period when you first create the extract.
Richard, that does exactly what I wanted. Thanks!