Why doesn't a warehouse dimension that copies region names from a source lookup suffer update anomalies?
answer
- how many writers touch this table?
- anomalies assume ad-hoc partial updates
- the copy is derived, not authoritative
- what does re-running the load fix?
basics
~20 sBecause nothing updates those copies ad hoc. One scheduled load owns the table, re-derives the region name from the source on each run, and rewrites the affected rows, so the redundancy is recomputable derived data rather than an independent authority.
solid answer
~50 sAn update anomaly presupposes **many independent writers issuing partial updates** — someone changes one copy of a repeated fact and misses the others, so rows start disagreeing. A warehouse dimension has none of that. It has a single writer: one transformation, run on a schedule, that derives every copied attribute from the source of record and rewrites whatever changed. The source lookup table is still the authority; the dimension holds a derived snapshot of it. That means correctness comes from the load being **deterministic and re-runnable**, not from a constraint. Re-running the transformation after a source rename brings every row back into agreement. The failure modes therefore move: they are a partially applied load, a non-idempotent merge, a source row that arrived late, or somebody hand-patching a dimension row in production — which is the one thing that genuinely does reintroduce the anomaly. Pipelines guard against those with idempotent loads and reconciliation against the source, not with foreign keys.
code
sql · 17 lines-- The flattened attribute is re-derived from the source on every run,
-- so a source rename converges across all affected rows.
MERGE INTO dim_customer d
USING (
SELECT c.customer_id,
c.name,
r.region_name
FROM src_customer c
JOIN src_city ci ON ci.city_id = c.city_id
JOIN src_region r ON r.region_id = ci.region_id
) s
ON d.customer_id = s.customer_id
WHEN MATCHED THEN
UPDATE SET d.name = s.name, d.region_name = s.region_name
WHEN NOT MATCHED THEN
INSERT (customer_id, name, region_name)
VALUES (s.customer_id, s.name, s.region_name);go deeper
Know that a dimension's repeated attributes are written by one scheduled job, not by application code, and that re-running the job recomputes them from the source.
Explain the precondition that disappears: anomalies need several uncoordinated writers making partial updates. Then explain what replaces the constraint — a deterministic, idempotent load that re-derives the value.
Show the real failure modes you have hit: half-applied runs, non-idempotent merges, unresolved placeholders for late source rows, and manual patches. Say how you detect them after the load rather than preventing them at write time.
Own the policy: the load is the only writer, manual production edits are incidents, and every published table declares a grain that a post-load assertion verifies. That is what makes derived redundancy an acceptable platform-wide default.
## What an update anomaly actually requires The classic anomaly story is: the same fact is stored in many rows, an application updates some of them, and now the database contradicts itself. Three conditions have to hold for that story to run — the fact is repeated, **more than one uncoordinated writer can change it**, and each write touches an arbitrary subset of the copies. Normalization removes the first condition, which is the only one an OLTP schema can control. (The anomaly taxonomy itself belongs to the transactional schema-design topic; what matters here is which of its preconditions survive in a warehouse.) A warehouse dimension repeats plenty of facts — that is the point of flattening a lookup chain into `dim_customer.region_name`. But it fails the second condition completely, and that is why the redundancy is safe. ## One writer, on a schedule, deriving from a source of record A dimension is populated by a single transformation job. It reads the source tables, joins the lookup chain, and writes the result. Nothing else writes to it: there is no application code path, no user-facing form, no service issuing a targeted `UPDATE` on one row. So the copies cannot drift apart with respect to each other — they are all produced by the same expression in the same run. Just as important, the dimension is not the authority for the region name. The source lookup table is. The dimension holds a **derived** value, which means the question "which copy is right?" always has an answer available from outside the dimension. In a normalized OLTP table, the copy *is* the data and there is nothing to reconcile against. ## The property that replaces the constraint: idempotent re-derivation If a source region is renamed from `EMEA` to `Europe, Middle East & Africa`, the next load recomputes `region_name` for every customer in that region. There is no fan-out risk and no missed row, because the load does not enumerate rows by hand — it re-evaluates a join. This is why analytics engineering cares so much about a property that OLTP hardly discusses: **a load must produce the same result whether it runs once or five times.** Idempotence is the warehouse's substitute for the constraint. It lets you re-run after a failure, backfill a window, or repair a bad deployment without inventing duplicate rows. A load that appends unconditionally, or that merges on a key it does not actually uniquely identify rows by, breaks this property — and that, not redundancy, is where warehouse dimensions genuinely go wrong. ## Where drift really comes from Being precise about the real failure modes is what separates a middle answer from a repeated slogan: - **A partially applied load.** The job died halfway, so some rows carry the new derivation and some the old. The fix is re-running it, which is only possible if it is idempotent. - **A non-deterministic transformation.** The load picks "one" row from a source that has duplicates, and picks a different one each run. - **Late-arriving source data.** The dimension row did not exist when the fact was loaded, so a placeholder was used and never resolved. - **A manual hotfix.** Someone runs an `UPDATE` against the dimension to correct a value in a hurry. Now the table disagrees with what the load would produce, and the next run silently reverts it — or worse, does not. Notice that only the last one is an update anomaly in the classical sense, and it is caused by breaking the single-writer rule that made the design safe. ## Deliberate staleness is not drift One clarification interviewers listen for: a dimension that keeps an *old* attribute value on purpose — because the model versions rows so that historical facts stay attached to the attributes that were true when they happened — is not suffering an anomaly. That is a history requirement being met, and the choice of whether an attribute overwrites or versions is an explicit modelling decision. Drift is when two rows disagree with no rule explaining why; retained history is when they disagree for a documented reason recorded in the row itself. ## What you should still verify Because no engine is checking anything, the load's output is asserted rather than enforced: the dimension has one row per key at its declared grain, keys are not null, coded attributes take known values, and counts and sums reconcile against the source. These assertions are the warehouse's answer to "how do you know it's right?", and the honest version of this answer says so rather than claiming redundancy is simply harmless. ## Answering this in an interview Name the precondition that disappears — uncoordinated writers — then name the property that takes over from the constraint: deterministic, idempotent re-derivation from a source of record. Finish with the real failure modes. That structure shows you understand *why* the trade is safe rather than merely that people make it.
- What actually goes wrong with a dimension load, if not anomalies?Partial runs that leave half the rows re-derived, non-idempotent merges that duplicate rows, non-deterministic picks from a source that has duplicate keys, unresolved placeholders for late-arriving source rows, and manual hotfix updates that break the single-writer assumption. Each is a pipeline defect, not a schema defect.
- Why is idempotence such a big deal for a warehouse load but rarely discussed in OLTP?Because warehouse loads fail and get re-run routinely — a source was late, a job crashed, a window needs backfilling. Idempotence means a re-run converges on the same result instead of duplicating or double-counting. OLTP writes are individually transactional and driven by user actions, so replay is the exception, not the operating model.
- Someone hand-patches a dimension row in production to fix a wrong region. What is wrong with that?It breaks the single-writer property the design depends on. The table now holds a value the load would not produce, so the next run either reverts the fix or, if the load skips unchanged keys, leaves an untraceable divergence. The correct fix is in the source or in the transformation.
The dimension is a printed directory, not a set of sticky notes: nobody scribbles corrections on individual pages, the whole thing is reprinted from the master list whenever the master list changes.
saying these in an interview costs you the question
- Claims warehouses avoid anomalies by enforcing foreign keys
- Says redundancy in a warehouse is simply harmless
- Cannot explain what makes a load re-runnable
- Treats hand-editing dimension rows as normal practice
- Confuses a deliberately retained historical value with drift