Building Customer Conformed Dimension

Hi guys,

I have two source system which has customer information. But in dwh I should create a just one dimension ( conformed dimension ) which holds the all customer for that company.
Some of the customers can be existed in both source system. Some of them are existed in just one system.
What are the best practices of combining two customer source tables into the one dimension table ?

Thanks.

1. Decide on a mechanism that can reliably deduplicate your customer records - assuming that your customers are individuals then duplicating on first and last name would obviously not be a reliable mechanism.

2. There are other approaches to this, but I would make up a business key for my customer dim and then create a cross reference table that links my source business keys to my generated business key. Having decided that a source customer record matches a record in the customer dim (or it doesn't and you create a new customer dim record), you enter this data in the cross-reference table and then in subsequent data loads you can check this table first and if a record exists for your source record then you can just pick up the Dim business key and you don't have to go through the de-duplication process each time.

3. For every customer attribute, decide which of your source systems is the master (or possibly that all of them are) and then design your ETL so that an attribute in your customer dim only gets updated if it is changed in the master source.