Lookup table that changes over time

[palpeti21]

Can you please help me how do I design my model so I can make the below work:

I have sales data in the fact table and I have customer data in the lookup table. The customer data contains customer addresses. The address for the same customer ID could be different today than it was a month ago. 

I need to be able to tell each transactions in city 'A' a month ago, and  today. Also, I need to be able to tell what were the transaction items made to customers who had their address changed between today and a month ago (or between any two points in time).

What is the best way to design the tables and relations to make this model work?

Many Thanks,

[palpeti21]

Thanks, this is very helpful. I will probably need to use Type 2 - Row Versioning. My next issue then is with this method I will no longer be able to use the customer ID, I'll need to use the Surrogate Key to connect to my fact table. I don't have a match for these in the fact table though...

[Shahfaisal · Accepted solution]

---

@palpeti21 wrote:

Thanks, this is very helpful. I will probably need to use Type 2 - Row Versioning. My next issue then is with this method I will no longer be able to use the customer ID, I'll need to use the Surrogate Key to connect to my fact table. I don't have a match for these in the fact table though...

---

You are right. You will need a surrogate key and your fact table ETL (extract, transform, load) process also needs to be modified to populate the right surrogate key in the fact table.

If your question has been answered, please mark the post as answered.

[Shahfaisal]

My advice is to handle this in your Data Warehouse. This scenario is called Slowly Changing Dimension Type 2 and here is an article for you to get up to speed - https://adatis.co.uk/introduction-to-slowly-changing-dimensions-scd-types/