# Zero key as ghost record hash key? **URL:** \ **Category:** Data Vault 2.0\ **Created:** [January 5, 2024, 10:17am UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103 "2024-01-05T10:17:51Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![pdoortje](https://avatars.discourse-cdn.com/v4/letter/p/96bed5/32.png) [@pdoortje](https://forum.data-community.org/u/pdoortje)\ **Post date:** [January 5, 2024, 10:17am UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/1 "2024-01-05T10:17:51Z")
Hello, If I understand correctly, according to some articles from Scalefree, I should use the hash key of the _unknown_ zero key as the hash key for the ghost record. This approach allows me to perform an EQUI JOIN on an hierarchical link (optional relationship / parent-child relationship) through the hub (optional / unknown zero key) with the satellite. Is this approach correct? Thank you for providing your insights. ---
**Author:** ![FrenkLoonen](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/frenkloonen/32/119_2.png) [@FrenkLoonen](https://forum.data-community.org/u/FrenkLoonen)\ **Post date:** [January 9, 2024, 7:58am UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/2 "2024-01-09T07:58:40Z")
Can you give some example source data, and specify how you loaded it into your Data Vault and for what scenario you want to get the data out, and how? ---
**Author:** ![pdoortje](https://avatars.discourse-cdn.com/v4/letter/p/96bed5/32.png) [@pdoortje](https://forum.data-community.org/u/pdoortje)\ **Post date:** [January 9, 2024, 12:40pm UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/3 "2024-01-09T12:40:03Z")
Scenario: Employer reporting to Manager ## Source Employee\_Id Manager\_id Name Function A X Name\_A Technical B X Name\_B Administrative X NULL Name\_X Manager ## Hub\_Employee HK\_Employee BK\_Employee HK(X) X HK(A) A HK(B) B HK(NoRef) Noref ## Link\_Employee\_Manager HK\_Link HK\_Employee HK\_Manager HK(A|X) HK(A) HK(X) HK(B|X) HK(B) HK(X) HK(X|NoRef) HK(X) HK(NoRef) ## Satellite\_Employee HK Name Function HK(Ghost) NULL NULL HK(X) Name\_X Manager HK(A) Name\_A Technical HK(B) Name\_B Administrative ## Select to get the data out select Hub\_Employee.BK\_Employee as Employee\_id , Satellite\_Employee.Name , Satellite\_Employee.Function , Satellite\_Manager.Name as Manager from Link\_Employee\_Manager inner join Hub\_Employee – Role Employee on Hub\_Employee.HK\_Employee = Link\_Employee\_Manager.HK\_Employee inner join Satellite\_Employee – Role Employee on Hub\_Employee.HK\_Employee = Satellite\_Employee.HK\_Employee inner join Hub\_Employee Hub\_Manager – Role Manager on Hub\_Manager.HK\_Employee = Link\_Employee\_Manager.HK\_Manager inner join Satellite\_Employee Satellite\_Manager – Role Manager on Hub\_Manager.HK\_Employee = Satellite\_Manager.HK\_Employee ; ## Output If HK(Noref) = HK(Ghost) then output will be as desired Employee\_Id Name Function Manager A Name\_A Technical Name\_X B Name\_B Administrative Name\_X X Name\_X Manager If HK(Noref) \<\> HK(Ghost) then output Employee\_Id Name Function Manager A Name\_A Technical Name\_X B Name\_B Administrative Name\_X → Missing record for Employee X Hence initial question! ---
**Author:** ![FrenkLoonen](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/frenkloonen/32/119_2.png) [@FrenkLoonen](https://forum.data-community.org/u/FrenkLoonen)\ **Post date:** [January 9, 2024, 4:42pm UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/4 "2024-01-09T16:42:57Z")
By having zero keys, you’re able to do an equi-join from the link back to the hub. A join to the sat however you’ll most likely always want to do using a left join. Another thing is that you’d need to handle the timelines in your sat when getting the data out. Maybe you omitted it for the sake of simplicity. But what matters in the end, is that ghost records should only get attention as soon as you start implement PIT tables. It’s there where you, for every time slice, for every hub record, get the respective record from every satellite. If a certain hub record doesn’t have a satellite record for a certain time slice, that is where you then insert the reference to the ghost record, so that when getting the data out, you can select from the PIT and inner join to the satellite (that of course must have that ghost record). Patrick Cuba was of course way better in explaining this. [This article](https://medium.com/snowflake/data-vault-mysteries-zero-keys-ghost-records-3be7cb05ee94) should have the information (although it seems to be for Medium members only nowadays) . ---
**Author:** ![FrenkLoonen](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/frenkloonen/32/119_2.png) [@FrenkLoonen](https://forum.data-community.org/u/FrenkLoonen)\ **Post date:** [January 9, 2024, 4:44pm UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/5 "2024-01-09T16:44:19Z")
Also, could you post the link to those Scalefree articles? I’m intrigued 🙂 ---
**Author:** ![pdoortje](https://avatars.discourse-cdn.com/v4/letter/p/96bed5/32.png) [@pdoortje](https://forum.data-community.org/u/pdoortje)\ **Post date:** [January 10, 2024, 8:53am UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/6 "2024-01-10T08:53:28Z")
I’m familiar with Patrick Cuba’s article ‘Data Vault Mysteries… Zero Keys & Ghost Records…’ (full version) and also with the remark that you cannot have descriptive information for a key linked to a null value. But in the scenario mentioned above, I need this information, and I don’t want to use a left-join. That’s where these two Scalefree articles comes in play: [Ghost Records](https://www.scalefree.com/scalefree-newsletter/implementing-dv2-0-ghost-records/) and [Zero Keys](https://www.scalefree.com/scalefree-newsletter/implementing-data-vault-2-0-zero-keys/). What is your opinion? ---
**Author:** ![FrenkLoonen](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/frenkloonen/32/119_2.png) [@FrenkLoonen](https://forum.data-community.org/u/FrenkLoonen)\ **Post date:** [January 10, 2024, 9:23am UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/7 "2024-01-10T09:23:33Z")
My opinion is that I have no problem with doing left joins 😉 ---
**Author:** ![charles](https://avatars.discourse-cdn.com/v4/letter/c/6f9a4e/32.png) [@charles](https://forum.data-community.org/u/charles)\ **Post date:** [January 11, 2024, 1:42pm UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/8 "2024-01-11T13:42:16Z")
Using the hash key of the unknown zero key for the ghost record in a hierarchical link scenario can indeed be a valid approach, especially if you are dealing with optional relationships or parent-child relationships in a database schema. This strategy helps in linking the ghost record to the associated hub (optional/unknown zero key) and subsequently performing an EQUI JOIN with the satellite. ---
**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\ **Post date:** [January 12, 2024, 1:21am UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/9 "2024-01-12T01:21:08Z")
Scalefree seems to obscuring the purpose of Zero Keys and Ghost Records. They must be seperate, [https://www.linkedin.com/pulse/data-vault-mysteries-zero-keys-ghost-records-patrick-cuba](https://www.linkedin.com/pulse/data-vault-mysteries-zero-keys-ghost-records-patrick-cuba) Zero keys around a hub + link should also find a record loaded to the adjacent satellite; this means you do not need left joins anywhere Zero keys can be extended to just -1, you could in theory use the concept to map other nuances around your keys, for example: - -1 is missing, use this for optional portions of the relationship - -2 late arriving, this is a business rule - -3 _etc etc_ See here: [Design Tip #128 Selecting Default Values for Nulls - Kimball Group](https://www.kimballgroup.com/2010/10/design-tip-128-selecting-default-values-for-nulls/) Ghost records should never be mixed with Zero keys; only used for equi joins for sats surrounding a hub or link. ---
**Author:** ![pdoortje](https://avatars.discourse-cdn.com/v4/letter/p/96bed5/32.png) [@pdoortje](https://forum.data-community.org/u/pdoortje)\ **Post date:** [January 15, 2024, 9:27am UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/10 "2024-01-15T09:27:36Z")
> [@patrickcuba](#): > > Zero keys around a hub + link should also find a record loaded to the adjacent satellite; this means you do not need left joins anywhere @patrickcuba: Feeling a bit lost here. How do I create a record for a zero key in the adjacent satellite? Upon table creation? (Naturally is not an option; the presence of the zero key in the link table is a consequence of the link being optional, not due to a null value in the source for that hub). And what about your quote ‘You cannot have descriptive content describing nothing!’? ---
**Author:** ![patrickcuba](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.data-community.org/patrickcuba/32/19_2.png) [@patrickcuba](https://forum.data-community.org/u/patrickcuba)\ **Post date:** [January 15, 2024, 10:47am UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/11 "2024-01-15T10:47:20Z")
> [@pdoortje](#): > > How do I create a record for a zero key in the adjacent satellite? Upon table creation? No, zero record attributes would have been mapped already as a part of the satellite table load from your staged content. > [@pdoortje](#): > > the presence of the zero key in the link table is a consequence of the link being optional, not due to a null value in the source for that hub Also not correct in wording, a zero-key exists in one or more participants in the link table making that participant optional. Which is why a Link as a M:M construct infact supports 1:M, 1:0, 1:1 cardinalities, where you find the relationship is optional it is filled by that zero key. (hashed -1). This means **you will** have a zero key in the hub else how does one make the equi-join work between hub and a link? > [@pdoortje](#): > > ‘You cannot have descriptive content describing nothing!’? Yes, so if you have attributes with valid values off a zero-key then has the DV been mapped correctly? Why are you getting descriptor data with no key? Should this be instead trapped in an Error table and prevented from loading into the vault? ---
**Author:** ![Carl](https://avatars.discourse-cdn.com/v4/letter/c/ee7513/32.png) [@Carl](https://forum.data-community.org/u/Carl)\ **Post date:** [January 22, 2024, 2:17pm UTC](https://forum.data-community.org/t/zero-key-as-ghost-record-hash-key/1103/12 "2024-01-22T14:17:13Z")
Hi Frenk, Most of Patrick’s articles, which were on Medium, are also available on LinkedIn. If you know the title of the article then just add “LinkedIn” into the search and you can read the articles without having to sign up to Medium.