Late arriving master data/reference data is a common scenario when loading facts. E.g We receive a transaction carrying a store number (master data) or currency code (reference data) that has not yet been received in the dimension load from the same source.
The data engineering team at the current site has taken the approach of looking up the surrogate key during the transactional load and substituting the failed lookup with the surrogate and natural key of the default unknown member record in the corresponding dimension. They have not developed any sophisticated techniques for logging the failed lookup’s and then re-processing the fact record so that the reference to the unknown record is substituted with correct dimensional record when it becomes available from the true source.
A popular technique using traditional tools such as SSIS, data stage, talend was to create an inferred member row by calling a stored procedure and passing the natural/business key of the reference/master data code on detecting a failed dimensional lookup. They had somewhat out-of-the-box support for dealing with a failed lookup, creating the inferred member record and using the surrogate key returned by the sp call.
Is there a best practice/recommend design pattern for handling handling late arriving dimensions when loading facts?