Sometimes a fact (an order or event) arrives referencing a customer or product that the dimension table does not have yet. You cannot just drop the fact, or you lose data. There are three common handling patterns, and the choice is a trade-off between freshness and accuracy.
Option 1: insert an inferred (placeholder) dimension row
When the fact arrives with natural key C-901 and no matching dimension row, create a minimal dimension row for C-901, with unknown values for the attributes (name Unknown, city Unknown), a new surrogate key, and a flag such as is_inferred = true. Load the fact pointing at that surrogate key. When the real dimension record arrives later, update the placeholder row with the real attributes (an overwrite, as in Type 1) and clear the flag. The fact needs no change, because it already points to the right surrogate key.
INSERT INTO dim_customer (customer_key, customer_id, name, city, is_inferred) SELECT NEXT_KEY(), f.customer_id, 'Unknown', 'Unknown', TRUE FROM staged_orders f LEFT JOIN dim_customer d ON d.customer_id = f.customer_id WHERE d.customer_id IS NULL GROUP BY f.customer_id;
This keeps data fresh and complete, at the cost of reports that briefly show "Unknown" for some attributes. If the dimension is Type 2 (history), the real data's validity dates may need adjustment.
Option 2: hold the fact back
Put facts with missing dimensions into a pending or error table, and retry them on later runs, once the dimension has arrived. Reports stay accurate, with no unknowns, but those facts are missing from the numbers until the dimension shows up, so totals are temporarily low. A timeout rule should decide what happens if the dimension never arrives.
Option 3: use a default "unknown" member
Point the fact at a shared -1 / Unknown row, and reprocess later to repair the key when the dimension arrives. It is simple, but the repair step (updating the fact's key) means rewriting fact rows, which is expensive on large tables.
Choosing
If freshness matters and some temporary "Unknown" is acceptable (dashboards, near-real-time reporting), use inferred members. If the numbers feed finance or compliance reports, holding back and flagging is safer. Many teams use inferred members, with a monitor on how many remain inferred after some time.
Prevent it
Load dimensions before facts in the orchestration order, and ask whether the source can guarantee the order. Count orphan facts after each load, and alert on a rise.