Dual Customer Keys and Two Conflicting History Requirements
Meridian Retail operates 620 stores and an e-commerce channel. Its warehouse holds a customer dimension of 6.2M rows and an order fact of roughly 900M rows spanning five years. Meridian acquired a competitor 18 months ago, and the CRM has begun issuing new customer numbers for overlapping individuals while legacy numbers remain valid in the acquired brand's order system; about 4% of customer records now carry two natural keys. Marketing wants "customer as of the campaign date" for attribution, while the tax team must restate historical sales by the customer's current province when a customer relocates, because provincial tax treatment follows current registration. Analysts currently join facts to the dimension on the natural key, and they report both duplicate customers and unexplained year-over-year revenue swings. A relocation of 40 high-value accounts last quarter moved 3.8% of reported revenue between provinces.
1. Recommend a dimension design and key strategy that satisfies both the marketing and tax requirements. Justify the choices and state what you would explicitly not do.
Read the worked answer
The two requirements are genuinely opposed, so one column cannot serve both. Marketing needs as-was attribution: the province, segment, and tier that applied when the order was placed. Tax needs as-is: the customer's current registered province, applied to historical sales.
Design: a customer dimension with an integer surrogate key as the primary key, never the natural key. Version Type 2 the attributes used for attribution (province at order time, segment, tier) with effective_start, effective_end, and a current-flag, and enforce uniqueness on (source_system, natural_key, version). Add a separate Type 1 column, current_province, overwritten in place on relocation. Facts store the surrogate key resolved at load time for as-was joins, plus the durable party key for cross-brand identity.
What I would not do: rewrite Type 2 history on relocation. That would make historical revenue move every time someone moves, destroy auditability, and silently change closed periods. It is the most common wrong answer here because it appears to make the tax report easy.
The cost is two deliberate join paths, which must be documented in the semantic layer: fact→surrogate for performance analysis, fact→party→current_province for tax. Add an unknown member (key −1) and a late-arriving dimension lookup by order date, otherwise the first order from a new customer fails the join or gets the wrong version.
2. How would you resolve the duplicate identities created by the acquisition, given that the fact table already contains 900M rows keyed to legacy identifiers?
Read the worked answer
Do not re-key the fact table. A destructive rewrite of 900M rows is expensive, irreversible, and will break any downstream process pinning to legacy IDs. Resolve identity in a mapping layer instead.
Build a cross-reference table: party_id, source_system, natural_key, confidence, effective_from, effective_to, match_method, review_status. Use deterministic rules first (normalized email plus postal code, exact phone plus surname) and probabilistic matching only where no shared key exists. Route matches below a confidence threshold to a steward queue rather than auto-merging, because a false merge is far more damaging than a missed merge in a retail context - it combines two customers' purchase history and can corrupt segmentation and credit decisions.
The mapping must itself be slowly changing: a many-to-one mapping that is later split needs effective dates so historical reports remain reproducible. Expose a resolved view joining fact to xref to dimension; analysts query the view, not raw keys.
Operationalize it: report duplicate rate, merges and splits per month, sampled false-merge rate, and unmatched new records. Keep merges as data changes with an audit trail, never as UPDATE statements against the dimension, so any merge can be reversed when the steward is wrong.
3. A merchandising report shows a 3.8% revenue shift between provinces year over year after 40 accounts relocated. The tax team says the number is correct. How do you adjudicate?
Read the worked answer
First determine which join the report uses. If it joins facts to current_province (Type 1), the shift is mechanically expected: those 40 accounts' entire history now lands in the new province. That is correct for tax and misleading for performance comparison, because it makes the old province look like it lost five years of revenue in one quarter.
Verify magnitude before accepting the explanation: compute the revenue attributable to accounts with a province change in the period. If it is approximately 3.8%, concentrated in 40 accounts, the report is behaving as designed, not buggy. If the shift exceeds that, look for duplicate party resolution changing the join cardinality - a merge can double-count or drop rows, which produces a similar-looking swing.
The adjudication is not to change the number but to change the labeling and the default. Publish two certified views: "revenue by province at time of sale" (Type 2) for trend and performance analysis, and "revenue by current province" (Type 1) for tax and registration reporting. Each labels its semantics on the report and in the semantic layer definition. Then add a monitoring check on revenue reassigned by relocation, so a future 8% shift triggers scrutiny rather than an argument. Fixing the report by reverting to history would break the tax filing.