Keep Supernatural Keys Up to Date

The supernatural key feature is fantastic, but it does have an issue.  During loading, it's possible for a transaction record to arrive in a fact table that corresponds dimension value that doesn't exist yet.  This can happen if the value hasn't been added in the source, or due to a record that was missed in the load because it was created in the time window between the data transfer of the dimension and the fact table.  

In cases like this, the client may want to not populate the supernatural key field in the fact, and use a placeholder key like -1 instead.  This allows them to control what an "unknown" member of a dimension looks like, and is a common requirement for error handling.  

You can get this functionality easily by using the "Only read from the store" supernatural key option, along with an "Is Empty" transformation.  However, if the key is later populated in the dimension, the supernatural key field in the fact will not be updated.  Instead, it will remain -1 or some other placeholder value until the next full load.  

Would it be possible to expand "Keep lookup fields up to date" to include read-only supernatural keys?  They essentially function like a lookup as it is. 


Please sign in to leave a comment.