I'm dealing with many years of various ways of bringing in data (there's still a SQL 2k server in use) and a couple of rather unfriendly third party systems that only hold data for 30 days. So there will be and are deleted dimension records.

I really, really, really don't want to have to search all the fact tables and bridge tables that might access the deleted records. We've got plenty of space and since I don't know what tables will incur this problem a deleted flag would be iffy and needed on every ETL.

I think using the ExpirationDt will work because if there's a current record with a date that's less than 2999-12-31 then it's no longer in the source. That also indicates when it was removed and keeps the time slices accurate.

I'll leave the post open without an answer for a while (nothing against you Ron) to give other people a chance to chime in with what they do in their environments.