skip to content

How do you set SCD tracking policy per attribute on a widely used customer dimension?

level: principalimportance: should knowfreq 38%

answer

  1. the unit of decision is not the table
  2. one question, asked per column
  3. which mistake can you never undo?
  4. raw history upstream buys you options
  5. a policy nobody wrote down is not a policy

basics

~20 s

Decide the SCD type per column, not per dimension: Type 2 only where a report must read a value as it was, Type 1 for corrections and cosmetic labels, Type 0 for origin facts, Type 4 for volatile ones. Then document and test the policy.

solid answer

~50 s

The unit of decision is the **attribute**, not the table. For each column I ask one question: does any report need this value as it was at the time of the fact? Yes and it changes rarely — Type 2. It is a correction or a cosmetic relabel — Type 1. It describes the member's origin — Type 0. It changes weekly on a large dimension — Type 4, into a mini-dimension. Both views needed over the same facts — Type 6. Over-tracking costs row growth, heavier loads and analyst error, because a member with many versions breaks naive counting. Under-tracking costs history that cannot be recovered once the source moves on. The asymmetry is the strategic point: I keep raw source history in an untransformed ingest layer so a Type 1 decision stays reversible, then publish the per-column policy in the model documentation and enforce it with tests.

code

yaml · 8 lines
yaml
dim_customer:
  customer_id:      { scd_type: 0 }  # durable business key, never rewritten
  signup_date:      { scd_type: 0 }  # cohort analysis depends on the original
  acquisition_channel: { scd_type: 0 }
  customer_name:    { scd_type: 1 }  # corrections only, no reporting history
  sales_region:     { scd_type: 2 }  # finance reports commissions as-was
  loyalty_tier:     { scd_type: 6 }  # as-was and as-is both required
  score_band:       { scd_type: 4 }  # weekly churn, moved to a mini-dimension

go deeper

for a junior

Know that different columns in one dimension can be treated differently, and that the choice comes from whether a report needs the old value rather than from a preference for one type.

for a middle

Be able to run the classification: for each attribute ask whether any report needs the value as it was, and map the answer onto overwrite, version, freeze or split. Explain what over-versioning does to loads and to counting.

for a senior

Show the asymmetry between over- and under-tracking, and the operational hedge of keeping raw source history upstream so a Type 1 decision stays reversible. Describe the tests that keep the declared policy true over time.

for a principal

Own the policy as a published contract: who decides, how it is documented and tested, how a promotion from overwrite to versioning is communicated to consumers whose counts will move, and where the organisation buys optionality cheaply instead of paying for it in the mart.

## Why the decision is per column A customer dimension might carry sixty attributes: identifiers, names, addresses, segment codes, contact preferences, scores, dates. Applying one Slowly Changing Dimension policy to all of them is always wrong in at least one direction — either you version a marketing flag that nobody reports historically, or you overwrite the sales region that finance's commission report depends on. Mature dimensional models assign a type per column and say so in the documentation. Interviewers at a lead level are checking whether you know that, and whether you can run the classification as a process rather than a hunch. ## The classification question One question does most of the work: **does any report need this value as it was at the time of the fact?** Everything else follows. - **Yes, and the attribute changes rarely** → **Type 2**. Sales region, customer segment, product category, employee department. - **No — the old value was never true, or the label is cosmetic** → **Type 1**. Spelling corrections, formatting normalisation, display names, a re-keyed reference code. - **The value means "as at the beginning"** → **Type 0**. Business key, signup date, acquisition channel, original credit band. Overwriting these silently changes what cohort reports mean. - **It changes weekly and the dimension is large** → **Type 4**, into a mini-dimension of banded value combinations that facts reference directly, so the base dimension stops versioning. - **Both the historical and the present-day view are needed over the same facts** → **Type 6**, versions plus a current-value column. - **A planned realignment where both maps must be reportable side by side** → a **Type 3** alternate column. A practical discipline: run this with the people who own the reports, not alone. "Does anyone report on this historically?" is a business question, and the answer is often "we don't today, but we will when we can". ## The two costs, and why they are asymmetric **Over-tracking** is expensive but visible. The dimension grows with change volume, loads get slower, and — the failure that actually hurts — analysts count rows and get versions. A dimension with a chatty Type 2 attribute quietly turns every `COUNT(*)`, every naive join and every distinct-count into a defect factory. Over-tracking also degrades the browsing experience: nobody wants a member appearing eleven times in a filter list. **Under-tracking** is cheap and invisible, right up until it is unrecoverable. The moment you overwrite, both the warehouse and (usually) the source have lost the old value. Someone asks in eighteen months for last year's numbers under last year's territories and the honest answer is that the data no longer exists. Switching a column to Type 2 later only starts history from the cutover. That asymmetry drives the single most useful strategic move: **keep raw, untransformed source history upstream of the mart**. If the ingest layer retains what each extract said, a Type 1 decision is reversible — you can rebuild a versioned dimension retroactively when the requirement appears. This decouples "what do we publish today" from "what could we ever answer", and it lets you default to the simpler policy without betting the company's analytics on it. ## Making the policy real A policy that lives in someone's head is not a policy. Three things make it operational. **Write it down next to the model.** A per-column table of type, rationale and the report that justifies it. When someone later asks why the loyalty tier is versioned, the rationale is there and the debate does not restart. **Test it.** A Type 0 column should be asserted never to change for an existing business key; a Type 2 attribute should be asserted to produce exactly one current version per member; a Type 1 column simply should not be creating versions. These assertions catch the common regression where a change to the load quietly promotes or demotes a column's behaviour. **Name for the semantics.** Where a dimension exposes both an as-was and an as-is view of the same attribute, the column names must make misuse unlikely. Two columns differing by the word "current" in a self-service field list will be mixed up, and no error will ever fire. ## Governing change to the policy Once consumers depend on the dimension, the policy itself becomes a contract. Promoting a column from Type 1 to Type 2 is not a neutral technical change: reports that grouped by it start splitting a member across versions, distinct counts move, and the history before cutover is missing in a way that looks like a data bug. Communicate it, state the cutover date, and where possible backfill from raw history so the discontinuity does not surprise anyone. Demoting from Type 2 to Type 1 is worse — it destroys existing history and should be treated as an irreversible decision requiring the report owners' explicit agreement. The summary a lead should be able to give: type per attribute, driven by a reporting question rather than a preference; the simplest type that satisfies it; reversibility bought cheaply upstream rather than expensively in the mart; and the whole thing written down, tested, and changed deliberately.

  • What is the strongest argument for defaulting to Type 1 rather than Type 2 when the requirement is unclear?
    Simplicity and correct counting: no version explosion, no distinct-count traps, faster loads, a cleaner browsing experience. The argument only holds if the loss is reversible — which means raw source history is retained upstream so a versioned dimension can be rebuilt when someone finally asks. Without that safety net, defaulting to Type 1 is betting that no one will ever need the past.
  • How do you communicate promoting a published attribute from Type 1 to Type 2?
    Treat it as a contract change, not a load tweak. State the cutover date, warn that a member now appears as multiple rows so naive counts and joins will shift, and say plainly that history before the cutover is unavailable unless you can backfill it from retained raw extracts. Give consumers the correct counting pattern — distinct on the durable business key, or filter to the current version — before the change lands.
  • Which signals tell you a Type 2 attribute should be moved out to a mini-dimension instead?
    Version counts per member climbing into double digits, load times dominated by dimension writes, and an attribute that is analytical rather than identifying — nobody looks a member up by it. If its values are, or can be, a small closed set of bands, moving it to a Type 4 mini-dimension referenced by the fact converts unbounded growth into a bounded lookup while keeping the state at event time.
  • How do you handle a stakeholder who asks for Type 2 on every column, just in case?
    Price it honestly: show the projected version count and load time, and the counting errors that follow when members appear many times. Then offer the alternative that meets the underlying fear — retained raw history upstream, which preserves the ability to reconstruct any column later without paying for it in the published mart today. That reframes the ask from insurance to a specific reporting requirement you can evaluate.

saying these in an interview costs you the question

  • Pick one SCD type and apply it to the whole dimension
  • Track everything as Type 2 because storage is cheap
  • A Type 1 column can be given history retroactively from the source
  • The SCD policy is an ETL detail, not a published contract
  • Changing an attribute from Type 1 to Type 2 is invisible to consumers

context

resolving links…