A data engineer is preparing cleansed loan-tracking data for a dimensional model that will report on objects loaned between museum branches. Each branch dimension row will be joined into the loan fact table, and branch names occasionally get renamed after reorganizations. Which practice should the engineer follow when preparing the branch dimension for loading?
Select an answer to reveal the explanation.
Short Explanation
A branch's name can change, but a stable stand-in number never has to. Assigning a surrogate key to each dimension row means the fact table's joins keep working even after a rename, a merge, or a reorg.
Full Explanation
Preparing data for a dimensional model means decoupling the fact table's joins from anything in the source that can mutate, and a generated surrogate key, an integer or GUID with no business meaning, is the standard mechanism for that. It stays stable regardless of what happens to the branch's name, address, or other descriptive attributes, and it also gives the model room to track history if the dimension later needs slowly changing behavior. Using the natural name as a join key breaks the moment a branch is renamed, because every historical fact row referencing the old name either orphans or must be rewritten, which defeats the purpose of a dimensional model built for stable reporting. Skipping a dimension key and joining to the live source table at query time reintroduces a dependency on the operational system's availability and schema for every report, which is precisely what a warehouse or dimensional model is meant to avoid, and it prevents keeping any historical view of a branch's past attributes. Reusing the source system's internal row identifier ties the model to a value the source team owns and can change or recycle for reasons entirely outside the analytics team's control. Before finalizing the design, confirm the surrogate key generation is deterministic and idempotent across reloads, so the same source branch never receives two different keys.