Modelling Counterparty / GUID / BK

Data Vault 2.0
,
1

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.

2

I wonder what value there is in modelling unassigned (to a business object) data?

Name can be anything and even introduce the same name for a different business object (it seems). This column likely fails the test for a reliable business key. Ig the GUID is unique it might be a better key than name — although not ideal since it is a GUID.

3

GUID is only valid and known to this particular source. Other sources use Counterparty (company-names; also with variations in spelling). Once a counterparty/company name is valid, a customer_id is assigned by business and communicated to downstream systems. Downstream systems also rely on counterparty names (in one of them its the primary key) and when the customer_id is assigned its just an addiitional freeform attribute.

Is it valid to consider customer_id as business key? But how to handle the assigment to a counterparty afterwards?
I would use GUID to handle mappings in SAL.

In reports counterparty (with or without customer_id) is relevant. Some counterparties are customers, some not but still a kind of counterparty.

4

“If all you have a hammer, everything looks like a nail.”

You need a strong identifier, those records that do not have a strong identifier probably shouldn’t belong in a data vault.