When the real record for an inferred dimension member arrives, why overwrite it instead of adding a Type 2 version?

## Corrections versus changes The whole question turns on one distinction that dimensional modelling makes constantly and that weak candidates blur: - A **change** is the world becoming different. The customer moved from Berlin to Hamburg on 1 April. Both values were true, each during its own window. Type 2 exists precisely to keep both. - A **correction** is the warehouse becoming less wrong. The customer was always called "Acme Foods"; we simply did not know it yet, so we wrote "Unknown" as a stand-in. Only one value was ever true. An inferred member is a correction case by construction. Its placeholder attributes were never facts about the customer — they were an admission of ignorance, deliberately chosen to look like one. Versioning them promotes an admission of ignorance into recorded history. ## What the wrong answer produces Suppose the placeholder was created on 1 May and the master record arrives on 4 May. Split it Type 2 and the dimension now says: this customer was named "Unknown", in segment "Unknown", from the beginning of time until 3 May, and became "Acme Foods" in segment "Retail" on 4 May. Every consequence of that is wrong: - A segment-mix report for the first days of May shows an "Unknown" segment that has no business meaning. - Any "how many customers changed segment this month?" metric counts a change that did not happen. - Facts loaded on 2 and 3 May stay pointed at the old surrogate key and therefore stay attributed to "Unknown" forever, unless you *also* restate them — work you created for yourself out of nothing. ## What the right answer does Overwrite the attributes on the existing row: ```sql UPDATE dim_customer SET customer_name = 'Acme Foods', segment = 'Retail', is_inferred = FALSE WHERE customer_id = 88123 AND is_inferred = TRUE; ``` The surrogate key does not move. Facts already loaded against it are now attributed to the correct customer name and segment without a single fact row being touched. This is exactly why the inferred member was given the natural key in the first place — the natural key is the matching handle, and the stable surrogate key is the payoff. ## Keep the effective window intact If the dimension carries Type 2 history, the inferred row was created with `effective_from` at a low sentinel (`1900-01-01` or similar) so that facts with older event dates still fell inside its window. Leave that alone during the repair. Moving `effective_from` forward to the repair date would push any earlier fact outside every version's window, and the point-in-time key assignment would then find nothing. ## The one case where you do both Sometimes the arriving master record contains not only the true original attributes but also a genuine change that occurred after the placeholder was created. Then you perform two distinct operations in order: 1. **Repair** the inferred row to the values that were true during the window it already covers. 2. **Version** the later change normally, end-dating the repaired row at the change date and inserting a new version from it. The result is two rows that are distinguishable and both truthful, rather than one blended row that conflates "we learned something" with "something happened". ## Do not reissue the surrogate key A recurring bad instinct is to delete the placeholder and reload the dimension from the master, which mints a fresh surrogate key. Every fact loaded during the gap is then orphaned — pointing at a key that no longer exists, in a warehouse where nothing enforces referential integrity, so nothing complains. The join silently drops those facts. The surrogate key is meaningless by design; there is no reason for it to change just because the attributes did, and every reason for it not to. ## Leave an audit trail After a clean repair, the row is indistinguishable from one that was always correct — which is convenient for reporting and inconvenient for auditing. Keep the marker: repurpose the inferred flag into a resolved marker, or add a repair timestamp column. When somebody asks why last week's segment report changed between two runs, the answer is either in that column or it is nowhere. ## Contrast with a genuine Type 1 overwrite Mechanically this is a Type 1 overwrite, and it is worth saying so explicitly in an interview: the repair uses Type 1 semantics *inside* a Type 2 dimension, which is not a contradiction. Type 2 governs how business changes are recorded; corrections to data the warehouse never knew are a separate category and are overwritten. Being able to say that cleanly is the signal an interviewer is listening for.