.png){kind=small}
Zero Keys in Data Vault: What to Do When a Business Key Is Missing
Your Data Vault loads every night.
Hubs fill with business keys, links tie them together, and satellites keep the history. Then one night an order arrives with no ship-to address. The key you need is empty.
That moment drives one of the most viewed threads on the Data Community forum. "Zero Key in Hub" has been viewed more than 3,300 times, and a follow-up thread on ghost records picks up where it left off.
The problem: a business key arrives empty
When would a hub ever hold a row for a key that does not exist?
It matters because a link needs a hub row on each side. If the address key is empty, the link row has nothing to join to. Your joins either drop the order or need outer joins. Ghost records add to the muddle, since some guides treat them as the same thing as zero keys.
What members recommend
Swap the empty key for a zero key in staging
Replace the missing key in staging, before you hash it. Members use two values:
when the key is required but missing, such as the item on an order line.
when the key is optional, such as a ship-to address.
Both are hashed like any other key. One member uses text labels instead, such as "#null_req" and "#null_opt". The rule is the same either way. Pick values no source will ever send.
Let the zero key land in the hub
The zero key needs a row in the hub, so the link can join to it with a plain inner join. Members disagree on how it gets there. Some pre-load -1 and -2 into each hub on day one. One long-time member never loads them in advance. The staging step swaps in the zero key, and the normal hub loader inserts it the first time it appears.
The load date splits views too. Some use a fixed early date, such as 1900-01-01. Others keep the date from staging, which shows when the missing value first turned up.
Keep ghost records separate
A ghost record is one empty row you add to each satellite up front. A PIT table can then point to it for any time slice with no real satellite row, and inner joins still work.
One member put it plainly. Ghost records exist so satellites can be joined. Zero keys mark a key that is missing or optional. He said the two should never be mixed.
Don't describe "nothing"
A link with a zero key marks an optional side of a relationship. It should not carry descriptive data. If a row with an empty key arrives with real attributes, members read that as a mapping problem.
Stop the load, or send the row to an error table for review.
Composite keys with one empty part
This one is less settled. One member treats a partly empty key like any other and uses -1 for the empty part. Another asks if a key with empty parts is a valid business key at all.
A third suggested adding a code for the source system, known as a business key collision code. That keeps zero keys unique across systems.
A simple way to apply it
Pick your zero key values once. For example, -1 for required and -2 for optional. Check no source sends them as real data.
Swap empty keys in staging. Do it before hashing, so every table gets the same hash.
Let the hub loader insert them. Decide as a team whether you also pre-load them, and write it down.
Add one ghost record per satellite. Use it so PIT tables can inner join to satellites.
Trap descriptive data on empty keys. Route those rows to an error table.
Choose how users see zero keys. In the information mart, show them as "Unknown" or "Not applicable", whichever your reports need.
Using AutomateDV? It skips rows with empty keys in hubs and links by default. You can switch on null key replacement in staging. By default it uses -1 for required keys and -2 for optional keys. It keeps the original null in an extra column. See the null handling guide.
Frequently asked questions
What is a zero key in Data Vault?
A zero key is a stand-in value for a business key that arrives empty. A common choice is -1 for a required key and -2 for an optional one. It is hashed like any other key, so links can still join to the hub.
What is the difference between a zero key and a ghost record?
A zero key stands in for a missing business key in hubs and links. A ghost record is one empty row added to each satellite. PIT tables point to it when no real row exists for a point in time. Keep the two separate.
Should zero keys be pre-loaded into hubs?
Members disagree. Some pre-load them on day one. Others let the normal hub load insert them the first time staging produces one. Either works if the hub has the row before a link needs it.
Join the conversation
This post sums up two real threads on the Data Community forum. Read the full thread to see every reply.
Data Community has more than 4,000 members. It has run since 2018, with monthly meetups and a forum where members trade answers like these.
Join Data Community and join the forum to ask your own modeling questions and share how your team handles empty keys.
