# How do you deal with missing dimensions for foreign keys?

**URL:** <https://discourse.getdbt.com/t/how-do-you-deal-with-missing-dimensions-for-foreign-keys/1274>\
**Category:** In-Depth Discussions\
**Created:** [June 15, 2020, 5:57pm UTC](https://discourse.getdbt.com/t/how-do-you-deal-with-missing-dimensions-for-foreign-keys/1274 "2020-06-15T17:57:15Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kimcha](https://avatars.discourse-cdn.com/v4/letter/k/94ad74/32.png) [@Kimcha](https://discourse.getdbt.com/u/Kimcha)\
**Post date:** [June 15, 2020, 5:57pm UTC](https://discourse.getdbt.com/t/how-do-you-deal-with-missing-dimensions-for-foreign-keys/1274/1 "2020-06-15T17:57:15Z")

</div>

Hi,

What’s the best practice in DBT for dealing with missing dimensions for foreign keys?

For example, I have a fact\_orders that contains a foreign key to a dim\_funnel. The rows in dim\_funnel have to be maintained manually, so it can frequently happen that orders happen on a funnel, but the dimension row for the funnel hasn’t been added yet.

In a traditional DWH model we would include a special row like “Missing Funnel” with a -1 key for example. And then in the fact table query we would check if the correct foreign key exists in the dimension table and if not set it it to -1.

A similar situation can happen when dimension records are deleted, but the facts referencing it are kept.

I guess when generating the fact table, I could do a join on the dimension to check if the key is there, but this prevents one of primary benefits of using hash keys-- being able to load dimensions and facts in parallel.

So I am just wondering how you guys are handling such a situation.

Thanks, Kim

---

<div class="post-metadata">

**Author:** ![josh](https://avatars.discourse-cdn.com/v4/letter/j/45deac/32.png) [@josh](https://discourse.getdbt.com/u/josh)\
**Post date:** [June 15, 2020, 6:14pm UTC](https://discourse.getdbt.com/t/how-do-you-deal-with-missing-dimensions-for-foreign-keys/1274/2 "2020-06-15T18:14:02Z")

</div>

We still create a “-1” “Unknown” row in our dimensions. Although it’s not actually “-1” literally, it’s the hash of “-1”. And then we have a variable in our dbt\_project.yml file like this:

` unknown_key: "CAST(FARM_FINGERPRINT('-1') AS INT64)"`

Then when we build our fact tables and need to populate the keys to join to the dimension tables, we do something like this:

```
COALESCE(pers.person_key, {{ var('unknown_key') }} ) AS person_key,

```

I never really thought of one of the primary benefits of hash keys as being able to load dimensions and facts in parallel per se.

---

<div class="post-metadata">

**Author:** ![Kimcha](https://avatars.discourse-cdn.com/v4/letter/k/94ad74/32.png) [@Kimcha](https://discourse.getdbt.com/u/Kimcha)\
**Post date:** [June 15, 2020, 6:28pm UTC](https://discourse.getdbt.com/t/how-do-you-deal-with-missing-dimensions-for-foreign-keys/1274/3 "2020-06-15T18:28:35Z")

</div>

Fantastic, that makes a lot of sense. Thank you for sharing.

And in this example `pers` is the joined dimension table, right?

And how do you “append” your -1 dimension row in the dimension?

Do you just add a `union` at the beginning of the dim table?

---

<div class="post-metadata">

**Author:** ![josh](https://avatars.discourse-cdn.com/v4/letter/j/45deac/32.png) [@josh](https://discourse.getdbt.com/u/josh)\
**Post date:** [June 15, 2020, 11:33pm UTC](https://discourse.getdbt.com/t/how-do-you-deal-with-missing-dimensions-for-foreign-keys/1274/4 "2020-06-15T23:33:30Z")

</div>

Yes, in this case `pers` is the joined dimension table. And yes we just do a `UNION` with the query that is producing the dimension to append this row.

---

<div class="post-metadata">

**Author:** ![fabrice.etanchaud](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/fabrice.etanchaud/32/595_2.png) [@fabrice.etanchaud](https://discourse.getdbt.com/u/fabrice.etanchaud)\
**Post date:** [June 22, 2020, 4:38pm UTC](https://discourse.getdbt.com/t/how-do-you-deal-with-missing-dimensions-for-foreign-keys/1274/5 "2020-06-22T16:38:19Z")

</div>

Hi @Kimcha, there is an interesting article from Roleant Vos on the subject (in the data vault realm) : [unknown-keys-zero-keys-or-ghost-keys-in-hubs-for-dv2-0](http://roelantvos.com/blog/unknown-keys-zero-keys-or-ghost-keys-in-hubs-for-dv2-0/), with a taxonomy on missing values. In my case, we use two distinct missing key values : one when the key is missing but mandatory (problem in the loading or in source data), and another when the key is missing but optional (loading and data ok but data is missing for ‘business’ reason).

Best,  
Fabrice