Should HUB be loaded only from actual source and how to define- biz. key if 2 source have diff. keys

Data Vault 2.0
, , , , ,
1

I have a system , I have mimicked using Customer Product analogy as in diagram below

Source Systems
Source Systems1555×666 233 KB

Based on this System -C always has subset of customers from System-A and System-B and produces some data sets (50+ in number) for customer_product relationship(s)
The Data Vault model would look like below :

Data Vault Raw Model
Data Vault Raw Model1088×647 119 KB

TO pouplate DV we are using the workflow as below:

Data Vault Load Sequence
Data Vault Load Sequence1301×513 93.4 KB

I have follow question

  1. The SYSTEM-B has only CUST_ID and SYSTEM-A has CUST_ID and CUST_SRC as biz.keys. I am thinking to design one single HUB_CUSTOMER with some default value in CUST_SRC for SYSTEM-B, is this approach correct?
  2. AS the source for HUB_CUSTOMER and HUB_PRODUCT are SYSTEM-A and SYSTEM-B ., do I really need to populate CUSTOMER_HUB from SYSTEM-C as it only has Biz.Keys for CUSTOMER_HUB and PRODUCT_HUB for all 50 data sets as that would lead to duplicates if I ran 50+ data sets in parallel in case of DBT workflows with storage as DELTA (no unique key constraint in DB ) , shouldn’t data from SYSTEM-C only populate LINK tables and individual satellites?
    ANy help is greatly appreciated :slight_smile:
2

Only Cust_ID is a biz key

yes

what if some keys don’t exist in the other systems? Can you guarantee that?

3

Thanks @patrickcuba , Here the SYSTEM- C is always a subset from A & B so it is kind of guaranteed.
The issue is if I use all 50 data sets from SYSTEM -C in parallel to populate HUB_CUSTOMER
(they arrive at same point in time) I might see duplicate HK in it( although grim chances) , as my tables are all DELTA tables with no constraints

4

NEVER, if you do your table locking properly

5

If I have different Databricks workflows populating using DBT and automateDV , it create a temporary table every time , so parallel executions might erase the other data and hence cause issues.
Only option I have is I might have to SQL here instead of automate DV

Also there is only one way to do locking in Databricks is using “merge” , is that recommended for data vault 2.0 to use merge ? If there is any other strategy to lock if you can point to

6

Yes, I did it here