skip to content

Three teams already ship marts with their own customer dimensions - how do you conform them?

level: principalimportance: should knowfreq 33%

answer

  1. it is an ownership problem with a modelling deliverable
  2. start from what already exists, on one page
  3. the densest column goes first
  4. they may not be modelling the same entity
  5. both keys, reconcile, then drop the old one

basics

~20 s

Map the existing marts onto a bus matrix, pick the highest-leverage dimension, agree one durable business key and a master attribute set with a single owning team, then retrofit mart by mart - adding the conformed key alongside the local one before retiring the local one.

solid answer

~50 s

Retrofitting conformance is an organisational problem with a modelling deliverable, so I sequence it that way. First I build a bus matrix over what already exists to see how many processes each disputed dimension touches, and conform only the densest column first - trying to conform everything at once fails. Then the hard part: getting the teams to agree a **durable business key** for a customer and a master attribute set, which usually surfaces that they genuinely mean different things - account versus person versus billing entity - and sometimes the honest answer is two conformed dimensions with different names. One team owns the resulting dimension and its load. I retrofit incrementally: every new fact adopts the conformed key immediately, and existing facts carry both the conformed key and their legacy key for a period, with a reconciliation query proving the drill-across agrees before the legacy key is dropped. Success is measurable - a cross-mart query that reconciles - not a diagram.

code

sql · 20 lines
sql
-- 1. Add the conformed key beside the legacy one, backfill by business key
ALTER TABLE fact_orders ADD COLUMN customer_key BIGINT;

UPDATE fact_orders f
SET customer_key = (
  SELECT c.customer_key
  FROM dim_customer c
  JOIN legacy_dim_customer l
    ON l.customer_business_key = c.customer_business_key
  WHERE l.legacy_customer_key = f.legacy_customer_key
);

-- 2. Gate the cutover: totals must agree under both dimensions
SELECT l.region AS legacy_region, c.region AS conformed_region,
       SUM(f.order_amount) AS amount
FROM fact_orders f
JOIN legacy_dim_customer l ON l.legacy_customer_key = f.legacy_customer_key
JOIN dim_customer        c ON c.customer_key        = f.customer_key
GROUP BY l.region, c.region
HAVING l.region <> c.region;   -- must return zero rows

go deeper

for a junior

Know that separate marts with their own versions of the same dimension cannot be compared, and that fixing it means agreeing one shared definition rather than writing a clever join.

for a middle

Be able to describe the mechanics: agree a durable business key, add the conformed key alongside the legacy one, backfill via that key, reconcile, then drop the old column.

for a senior

Show the migration discipline - new facts adopt the conformed key immediately, existing facts dual-key and reconcile before cutover - and treat unmatched business keys as a data quality finding rather than an obstacle.

for a principal

Own the sequencing and the politics: rank with a bus matrix, conform one dimension at a time, name a single owner, and pitch the work as business questions currently unanswerable rather than as architectural tidiness.

## Frame the problem correctly The visible symptom is three `dim_customer` tables that disagree. The actual problem is that three teams were allowed to define a shared business entity independently, and no owner exists. If you only fix the tables, they diverge again within two quarters. So the work has a modelling deliverable and an organisational one, and skipping the second guarantees a repeat. ## Step 1: inventory with a bus matrix Before negotiating anything, draw the enterprise bus matrix over the estate as it actually is: rows for the business processes the three marts already model, columns for the dimensions they use, ticks where used. This is cheap, takes a workshop, and produces two things. It ranks the conformance work by column density - the dimension touched by the most processes is where agreement pays back most - and it makes the collision visible to stakeholders who cannot read a schema. Very often the matrix reveals the estate has four disputed dimensions, not one, and that only two are worth the negotiation. ## Step 2: conform one dimension, not all of them Pick the densest column and do only that. A programme that promises to conform every dimension across every mart is a multi-year integration project with no user-visible output, and it will be cancelled. One conformed dimension that unlocks one previously impossible cross-mart report is a demonstrated win that funds the next one. ## Step 3: agree the durable business key first The key is where the disagreement is real. Ask each team what identifies a customer, and you typically discover they are not modelling the same entity at all: the sales mart's customer is a signed account, the support mart's is an individual person, the finance mart's is a billing entity that may span several accounts. This is the single most important discovery in the exercise, and the honest resolution is sometimes **not** one dimension. Two well-named conformed dimensions - `dim_account` and `dim_person` - with an explicit relationship between them is a far better outcome than one forced dimension that satisfies nobody and quietly loses rows. Where they *are* the same entity, agree the durable business key: the identifier that survives source-system changes and that every mart can produce or map to. Then agree the master attribute set - the attributes that will be conformed - and accept that mart-specific attributes may remain, provided they never collide in name with a conformed one. ## Step 4: name an owner One team owns the conformed dimension: one load process, one change path, one place a new attribute is added. Without this the dimension is conformed on the day it ships and drifts the week after, because a downstream team will need a column and add it locally. The owner does not have to be a central platform team - it can be the team closest to the source - but there must be exactly one. ## Step 5: retrofit incrementally, never big-bang The migration pattern that works: - **New facts adopt the conformed key from day one.** No new star is allowed to introduce a private customer dimension. This stops the bleeding immediately and costs nothing. - **Existing facts carry both keys for a period.** Add the conformed key alongside the legacy key and backfill it via the business-key mapping. Both are populated, and consumers can migrate query by query. - **Prove agreement before cutting over.** Run a reconciliation: the same measure aggregated by the legacy dimension's attributes and by the conformed dimension's attributes must match to an agreed tolerance, and every unmatched business key must be explained. Unmatchable rows are the real finding - they are usually a data quality problem the marts were hiding. - **Then retire the legacy key and dimension.** Because this is retrofit inside one organisation with known consumers, the old column goes rather than lingering deprecated. A dual-key state kept indefinitely is the failure mode, not the design. ## Step 6: make regression detectable Conformance has no database constraint behind it, so it needs standing tests: the mapping from business key to dimension row must be unique and stable; each conformed attribute's distinct value set must match across any replicated copies; a scheduled cross-mart reconciliation must stay green. Wire these into the load so a break is an alert rather than a discovery made by a stakeholder in a meeting. ## What to tell leadership Express the payoff as questions the business currently cannot answer, not as architectural tidiness. "We cannot report support cost per revenue customer" funds the work; "our dimensions are not conformed" does not. And be honest about the cost profile: the modelling is days, the key agreement is weeks of meetings, and the retrofit is proportional to the number of existing consumers - which is exactly the argument for conforming the next dimension *before* a fourth mart is built on top of it.

  • The three teams turn out to mean different things by customer. Do you still force one dimension?
    No. If one team means a signed account, another an individual person and a third a billing entity, forcing one dimension satisfies nobody and quietly drops or duplicates rows. Ship two or three clearly named conformed dimensions with an explicit relationship between them. Conformance means never disguising different entities as one, and that applies to dimensions as much as to measures.
  • Why not conform every disputed dimension in one programme?
    Because it becomes a long integration project with no user-visible output and gets cancelled before delivering anything. Conforming the densest column first unlocks one previously impossible cross-mart report, which demonstrates value and funds the next one. It also lets you learn the negotiation and migration mechanics on one dimension before repeating them at scale.
  • How do you keep the retrofitted dimension from drifting again six months later?
    Name a single owning team with one load process and one change path, so a consumer needing a new attribute asks for it rather than adding it locally. Back that with standing tests: business key to dimension row must be unique and stable, replicated copies must agree on every conformed attribute's value set, and a scheduled cross-mart reconciliation must stay green.
  • What do you do with rows whose business keys cannot be matched during the backfill?
    Treat them as the most valuable output of the exercise rather than an obstacle. Unmatched keys are usually a data quality problem the separate marts were concealing - duplicate customers, test accounts, entities created in one system only. Quantify them, get them explained by the owning team, and do not cut over the legacy key until the residue is understood and bounded.

saying these in an interview costs you the question

  • Proposes conforming every dimension at once as one programme
  • Forces one dimension when the teams model genuinely different entities
  • Skips naming an owner, so the dimension drifts again
  • Cuts over without reconciling the old and new keys
  • Leaves both keys in place indefinitely instead of retiring the legacy one

context

resolving links…