Clear all

Modeling a conformed dimension with mutually exclusive records

Posts: 41
Topic starter
Eminent Member
Joined: 1 year ago

We have a table that exists in two different sources and they are linked to each other using IDs. When modeling out the table, I discovered that the tables have  some records are mutually exclusive in each system. The objective is to create a single dim table in the INFO_MART, more of a conformed dimension.



What is the best way to model this, assuming that not all the CUSTOMER records will have a corresponding SALESFORCE_ACCOUNT_ID. And similarly not all the ACCOUNTS records will have the corresponding NS_CUSTOMER_PK.


The composite Business Key is currently defined as the SALESFORCE_ACCOUNT_ID and NS_CUSTOMER_PK, which also poses an issue with duplicate business key when one of the keys in the composite key is NULL.


Please advise

3 Replies