Hi,
Currently, I have the following source data regarding counterparties/companies:
The source provides the columns GUID, NAME, and CUSTOMER_ID, along with corresponding descriptive properties. The NAME column and the descriptive properties refer to the level of the GUID.
GUID | NAME | CUSTOMER_ID | ADDRESS | OTHER_DESCR_COLs… |
---|---|---|---|---|
74727e…- | Counterparty A | 100123 | Street No. 1 | … |
6bb74b…- | Counterparty A | Street No. | … | |
edb7e3…- | COUNTterparty A | Street No. 1 | … | |
81cdb3…- | COUNTER PARTY A | Street No. |
(In this example, all counterparty names refer to the same object but with different spellings.)
NAME is seen as the business key and represents any contact that once occurred with the company. It is only when the contact is verified, suitable for business and the spelling is fine that it becomes a customer and receives a CUSTOMER_ID (assigned by business), which is then transferred to all subsequent systems.
Therefore, I currently see 2 business keys:
- Counterparty (NAME)
- Customer (CUSTOMER_ID)
I would also create a link between these keys:
- counterparty_customer_l
The issue I see is more with the satellites: I cannot simply attach the descriptive information to Counterparty or Customer, as they are either ambiguous or at the wrong level (shouldn’t this be GUID instead?).
Currently I have two options in mind:
-
A:
- Counterparty_h (BK: NAME)
- Customer_h (BK: CUSTOMER_ID)
- Counterparty_l
- Counterparty_h_…_sat
- GUID (as subsequence)
- ADDRESS
- OTHER_DESCRIPTIVE_COLs
- Counterparty_sal (to align/match different spellings)
=> is using GUID as sat-subsequence legit?
In this case it is impossible or hard to get the correct address for the customer-entity.
-
B:
Load GUID to counterparty_h and attach NAME as satellite. But this seems not to be legit. GUID is not a BK and only valid inside this source. Other sources also use counterparty-names as key. -
C: Similar to B, As proposed here: Link tables with technical key - #5 by patrickcuba :
Load GUID and NAME to the same hub (counterparty_h).And attach all descriptive data as sat to this hub.