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.