How do you deal with missing dimensions for foreign keys?

In-Depth Discussions
2

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.

1 Like