Create durable key in dimension

Question

My Customer dimension has columns customer_key (surrogate key int), customer_number (ID from source system) and customer_name. I need to implement SCD 2 to track changes. Business case is that customer_number can change for customer in time. So, I need durable key to track SCD 2 for the correct customer.

I guess, I need this structure of dimension:

How to generate customer_durableID correctly?
Should id be generated in Staging? It is periodic snapshot load.

Answer 1

The business will have to decide with your guidance what the 'Business Key' will be for the Customer entity.

That is not good that the Customer Number changes for a Customer. The question I would ask is 'Does the Customer Name change?' If no, then use Customer Name.

If yes, then you have to get the business to commit to the Customer Number.

Another option would be to investigate the Application that manages the Customer data and see if there is another way to track when the Customer Number changes - maybe it has its own surrogate key for the Customer row with a DateChanged/Updated column.

Then, your ETL can have logic to know the Customer Number changed and your Dimension table can have an 'OriginalCustomerNumber' column to store the original value.