Inferred/Early-Arriving Dimension Members and Safe Completion When Source Details Arrive Later

Learning outcomes

At 09:00 on 22 September, AtlasMart receives an order event for durable customer D-CUST-005, but the CRM profile has not arrived yet. Rejecting the fact loses timely activity; inserting a NULL customer key breaks the dimension contract; inventing a full customer record fabricates knowledge. The safe pattern is an inferred member: create a placeholder with the durable/natural identity you do know, post the fact to its surrogate key, and complete that same member when the source context arrives.

1. Create a specific placeholder, not a generic unknown

The incoming order carries D-CUST-005/C005, so AtlasMart knows which business entity the fact belongs to. The descriptive attributes are unknown, therefore the dimension row carries explicit placeholder labels and is_inferred=1. This is different from routing the fact to the single global D-UNKNOWN member.

-- Before CRM details arriveINSERT INTO dim_customer_history (  customer_sk,durable_customer_id,source_customer_id,customer_name,segment,  geography_code,lifecycle_status,original_signup_channel,effective_from,effective_to,  is_current,is_inferred,change_hash) VALUES (  :new_sk,'D-CUST-005','C005','__INFERRED__','Unknown','__UNKNOWN__',  'active','unknown','2026-09-22','9999-12-31',1,1,:placeholder_hash);INSERT INTO fact_early_order_demoVALUES ('O-EARLY-1006','2026-09-22','D-CUST-005',:new_sk,40);-- When CRM context arrives for the same effective member, complete in place.UPDATE dim_customer_historySET customer_name='Eli Works',    segment='SMB',    geography_code='G-NORTH',    original_signup_channel='web',    is_inferred=0,    change_hash=:completed_hashWHERE customer_sk=:same_sk AND is_inferred=1;

2. Why completion is Type 1 here

The placeholder values never represented a genuine historical business state such as “the customer was really named __INFERRED__.” They represented missing descriptive context for the same effective entity. Replacing placeholders in the same row preserves the fact's surrogate key and corrects incomplete context.

If CRM later reports that D-CUST-005 was in SMB until 25 September and Enterprise from 25 September, that is a real Type 2 business change. Likewise, a retroactive correction to an earlier interval can require splitting history and re-keying affected facts. Do not use “inferred member” as an excuse to overwrite genuine history.

3. Controlled failure: delete the placeholder and insert a full row

A naive loader may delete the placeholder and insert a new complete row, generating a different surrogate key. With foreign keys enforced, the delete fails because fact_early_order_demo references the placeholder key. With constraints disabled, the fact becomes an orphan or must be rewritten, creating unnecessary restatement and a race window.

Repair: complete the same inferred row in place when it is the same effective member. Preserve the surrogate key; update only placeholder descriptive attributes and the inference flag.

4. End-to-end acceptance script

conn=build_conn()# 1. Chapter 07 sales controls remain unchanged.control=conn.execute('''SELECT COUNT(*),COUNT(DISTINCT order_id),SUM(quantity),                               SUM(extended_amount),SUM(extended_cost),                               SUM(extended_amount-extended_cost)                        FROM fact_sales_history''').fetchone()assert control == (7,4,9,625,380,245), control# 2. Create inferred customer and prove creation replay is key-stable.sk1=ensure_inferred_customer(conn)sk2=ensure_inferred_customer(conn)assert sk1 == sk2conn.execute("INSERT INTO fact_early_order_demo VALUES ('O-EARLY-1006','2026-09-22','D-CUST-005',?,40)",(sk1,))# 3. Foreign key blocks delete-and-reinsert replacement.try:    conn.execute('DELETE FROM dim_customer_history WHERE customer_sk=?',(sk1,))    raise AssertionError('FK should have blocked deletion')except sqlite3.IntegrityError:    pass# 4. Complete same row; surrogate key remains unchanged.sk3=complete_inferred_customer(conn,'D-CUST-005','Eli Works','SMB','G-NORTH','web')assert sk3 == sk1row=conn.execute('''SELECT customer_sk,customer_name,segment,geography_code,is_inferred                    FROM dim_customer_history WHERE durable_customer_id='D-CUST-005' AND is_current=1''').fetchone()assert row == (sk1,'Eli Works','SMB','G-NORTH',0), row# 5. Dependent fact still references same surrogate key and amount.fact=conn.execute("SELECT customer_sk,amount FROM fact_early_order_demo WHERE order_id='O-EARLY-1006'").fetchone()assert fact == (sk1,40), fact# 6. Role-playing date and junk/profile fixtures still reconcile.orders=conn.execute('SELECT COUNT(*),SUM(order_amount) FROM fact_order_milestone').fetchone()assert orders == (4,625), orderspending=conn.execute('SELECT COUNT(*) FROM fact_order_milestone WHERE ship_date_sk=0').fetchone()[0]assert pending == 1print('sales_control=',control)print('order_control=',orders)print('inferred_customer_sk=',sk1)print('Chapter 08 acceptance: PASS')

Expected final line: Chapter 08 acceptance: PASS. The test proves creation replay safety, foreign-key protection against surrogate-key replacement, in-place completion, unchanged dependent fact keys, and unchanged Chapter 07 sales controls.

5. Operational and governance surface

Track inferred-member count and age. A placeholder that remains unresolved for days is a data-quality incident, not normal steady state. Record source identity, first-seen time, responsible source owner, and completion state. Alert on aged inferred members and reconcile them after source recovery.

Security and privacy still apply. “Unknown description” does not mean the durable customer identifier is non-sensitive. Synthetic course identities are safe for this lab; production identifiers require the same access, retention, and erasure policy as completed customer rows.

6. Cleanup/reset and what the lab proves

Because the lab runs in an in-memory SQLite database, closing the process is the cleanup/reset path. Re-running build_conn() returns the deterministic Chapter 08 baseline. This proves local serial referential/key semantics; it does not prove distributed source ordering, production CDC delivery, concurrent merge behavior, or legal erasure.

Chapter 09 next turns to multivalued relationships and bridge tables, where one fact can legitimately relate to multiple dimension members and careless joins can multiply measures.

Knowledge check

Summary and next step

Chapter 08 now has executable special-dimension semantics: governed calendar rows, independent date roles, compact transaction flags, degenerate identifiers, profile mini-dimensions, and surrogate-key-safe inferred members. The next chapter addresses many-to-many relationships and measure multiplication.

Authoritative references

• Kimball Group — Dimensional Modeling Techniques — Primary technique index for date, role-playing, junk, degenerate, mini-, and late-arriving dimension patterns.
• Kimball Group — Calendar Date Dimensions — Date attributes, fiscal periods, special dates, and explicit unknown/to-be-determined rows.
• Kimball Group — Role-Playing Dimensions — One physical dimension reused through logically distinct roles.
• Kimball Group — Junk Dimensions — Combining miscellaneous low-cardinality flags into one dimension.
• Kimball Group — Degenerate Dimensions — Transaction identifiers kept directly in fact tables when no descriptive dimension row exists.
• Kimball Group — Type 4 Mini-Dimension — Separating rapidly changing profile attributes and linking both base and mini-dimension keys to facts.
• Kimball Group — Late Arriving Dimension — Placeholder/inferred members and later completion of descriptive context.
• SQLite — CREATE TABLE — Constraint semantics used by the local deterministic lab.
• SQLite — Foreign Key Support — Local referential-integrity behavior used in the inferred-member failure test.