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.

INSERT INTO cm.DimCustomer(
CustomerID,
FirstName,
LastName,
)
SELECT
CustomerID,
FirstName,
LastName,
FROM sf.CustomerData
UNION
SELECT
'' AS CustomerID,
'' AS FirstName,
'' AS LastName,

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.