Creating Empty Records
Every single dimension table should have an empty record. Empty records should arise naturally as a result of processing the dimension. Some fact records will not have a dimension attached to them and the empty record takes care of that scenario.
However, in certain cases it is acceptable to preload an empty record. Sometimes, you can have an empty record as part of the dimension processing just to make sure it's always there like the example below.
The empty record should be identified by an empty record. By that I mean an empty string value for SourceSystemKey. What you should not do is use the contrived key to identify empty records. While I'm at it, you should not be using the contrived key for anything other than joining to fact records and necessary maintenance.
Pro Tip: Do not use the contrive key to identify empty records.
Last updated