Hello everyone!
Perhaps the question has already been discussed, but unfortunately I couldn’t find it.
There are 5 source systems.
Sources are loaded independently of each other.
In 4 of them the business key is present and it is guid. But in the 5th there is only an alternative key alt_id.
The same alternative key is in source 1.
It can probably be mapped at the time of loading using sat_customer_s1 via left join.
In this regard, questions:
- Is it normal to do a left join to determine pk at the time of loading source 5 (left join stage_source5 with sat_customer_s1 by alt_id)?
- If at the time of left join in sat_customer_s1 there is no alt_id yet, then how to handle this situation?
I have attached a picture that reflects the situation.
G’day
Not sure I’m 100% following what you’re proposing but I’ll give some general do’s and don’ts that might be helpful for your situation.
- DONT Use your vault to create business keys for your vault
- This will end up circular and unmaintainable. Not to mention can be very error prone.
- DO Use the data in your sources to define your entity
- Do you need the guid or is alt_id a business key in its own right?
- DONT Assume that all sources need to use the same business key
- Does the GUID actually match the entity or is it just a convenient PK?
- DO look for business keys that represent the entity in each source
- You can always use same-as-links to join them together in the vault.
I could go on but I think these cover the main points in the post.
Shout if more clarity is needed!
All the best,
Frankie
1 Like
Frankie, Patrick, thank you!
1 Like
