Question on Deleting records from dimension tables

I have created a Type 2 Slowly changining dimension and have realized that some of the records that are in the dimension get removed from the source table after a long period of time.

Rather than remove the record, I was thinking of adding a "Deleted" flag since I already have an acitve flag that has to do with an active status. Other than a deleted flag is there a better way via dates or some other process to conider the record deleted without physically deleting it? Or would i be simply better removing it from the dimension table buy using the natural key in the stage and determine if it exists in the source ?

Thanks,

Sure, you can put a flag on the dimension row, but is there any particular reason one needs to know the data was purged from the operational system?

I was going to add the flag in for a record that was in the source system and they no longer want to ever know it existed or report on it. I could have used an active status flag but I already have that column for something else.

Thanks,

Yes. I have to do this all the time when the application deletes data from the system. It's commonly called a logical delete.

Continuing on the previous. If you detect the deletion of a dimension record in the source you would flag the dimension record on the deleted flag and set the deleted date on day of detection. In a SCD Type 2 situation, what would you do with the start / end dates. In particular the end date : should you keep it on 99991231 or should you change it to the deleted date? And what about the active flag?

DP

Treat it like any other change. No special logic is required other than setting the delete flag.