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.