RonKyle: That's a very good point...this technique is perhaps most appropriate for an accumulating snapshot table, where by definition each record is changing with time. The boiled-down example I've given here instead is a transaction fact table that's changing, yet not accumulating over time. I've put this technique to use in financial applications, where there are lots of adjustment entries to past data, e.g. late revisions.

Also, the count issue can be accounted for if there a unique identifying attribute added to the incoming records. For example the natural key from the source system plus an identifier of the source system combined would be a unique key candidate. This field wouldn't change over time, so you could run a COUNT DISTINCT on it to get the correct count of unique records.

Peterzeke: marking one record as active defeats the purpose of effective dating -- because if you queried for a partial audit range, the sums wouldn't necessarily equal 1. Again, a correct count is achievable with this technique only if a unique identifying column is sourced from the original feed.