Which dimension attributes belong under SCD Type 0, where the original value is never changed?
answer
- the type where nothing gets updated
- think signup date, acquisition channel
- 'original' reads correctly in the column name
- enforced by the load, not the engine
- distinct from chasing the current value
basics
~20 sSCD Type 0 suits attributes that describe an origin and are true forever: the durable business key, the signup or first-order date, the original credit band, the branch an account was opened at. The value is written once and later source updates are ignored.
solid answer
~50 sType 0 means **retain original**: the column is populated when the dimension row is first created and is never updated afterwards, regardless of what later source extracts say. It fits attributes whose meaning is "as at the beginning" — the durable business key itself, `date_first_ordered`, `original_credit_score`, `account_opened_branch`, an original contract type. The point is that these are not stale values waiting to be refreshed; the original *is* the fact being reported on, so silently overwriting it under a Type 1 policy would destroy a measure people analyse cohorts by. Type 0 is a **load-time policy**, not a database feature: nothing in the engine stops an update, so the rule lives in the transformation logic and is enforced by a test asserting the column never changes for an existing member. A genuine source correction to a Type 0 column is an exception that goes through a data-quality decision rather than a silent rewrite.
go deeper
Recall that Type 0 means the value is set once and never updated, and be able to name a couple of columns that fit: the business key, a signup date, an acquisition channel.
Explain why it is a distinct policy rather than neglect — the load excludes the column from the update path and a test asserts it never moves — and contrast it with Type 1, which actively chases the current source value.
Show what breaks when Type 0 is applied by accident or omitted by accident: cohort and vintage analysis quietly changes meaning with no error anywhere. Describe how you surface a source revision to a frozen column for review instead of applying it.
Own the classification itself: which attributes the organisation treats as binding at origination, who decides when a frozen value may be corrected, and how that decision is recorded so an audit can reconstruct why a value changed.
## The definition SCD Type 0 is the "do nothing" member of the taxonomy: once the attribute is written into the dimension row, the warehouse never changes it, no matter how many times the source system revises it. It is sometimes described dismissively as "the type where you ignore changes", which undersells it — the interesting version of Type 0 is a *deliberate* declaration that the original value is the value the business reports on. ## Which columns qualify Three families of attribute genuinely earn Type 0. **Identity.** The durable business key — `customer_id`, `product_code` — is Type 0 by definition. If it changed, the row would no longer describe the same member, and every version of that member in the dimension would come apart. **Origin facts.** Attributes that are explicitly "as at the beginning": `date_first_ordered`, `original_list_price`, `acquisition_channel`, `original_credit_band`, `account_opened_branch`, `original_contract_type`. These are the columns cohort analysis lives on. "Retention by acquisition channel" is meaningless if the channel column is overwritten every time marketing re-attributes the customer, and "loss rate by credit band at origination" is meaningless if the band column tracks today's score. **Values whose provenance is contractual.** The terms an agreement was signed under, the tax jurisdiction at the time of registration, the SKU's launch specification — cases where the business, an auditor, or a regulator regards the initial value as binding. A useful test when deciding: read the column name out loud with the word "original" or "first" in front of it. If that reads as the intended meaning, it is Type 0. If it reads wrong, the column is asking for Type 1 or Type 2. ## Type 0 is not the absence of a decision Two things get conflated here and interviewers do probe the difference. The first is **Type 0 versus Type 1**. Both leave you one row per member. But Type 1 says "the current source value is the truth and I will keep chasing it"; Type 0 says "the first value is the truth and later ones are irrelevant". Applying Type 1 by default to a column that meant Type 0 is a quiet data-quality failure: nothing errors, no row count changes, and a cohort report simply stops meaning what its title says. The second is **Type 0 versus laziness**. "We never bothered to update it" is not Type 0; it is an unmaintained column that will eventually be corrected by someone who does not know the rule. Real Type 0 is written down in the model documentation, implemented in the load, and asserted by a test. ## Implementing and enforcing it No relational engine has a "write-once column" concept you can lean on, so the guarantee is procedural. In practice the load simply excludes Type 0 columns from the update path — they are set in the insert branch for a new member and never appear in the update branch — and a test asserts that, for a stable business key, the column's value never differs from what it was on the previous run. Because the protection is convention rather than a constraint, it is worth being explicit in the column comment or the model docs so that the next engineer does not "fix" the omission. ## When the source "corrects" a Type 0 column This is the real interview follow-up, because it is where the policy meets reality. The source system revises the account-opening branch, or the signup date shifts by a day because of a timezone fix upstream. Two cases must be separated: - **A late-arriving truth or a genuine data-quality fix.** The original value was recorded wrongly and never described reality. This deserves a correction — but as an explicit, logged, reviewed change, not as a silent by-product of the nightly load. - **A business change dressed up as a correction.** The customer moved branch; the source updated the field it happens to store the branch in. Type 0 is doing exactly its job by ignoring it. If the business needs the *current* branch too, the answer is a second column with its own policy, not the abandonment of the Type 0 rule. Because both look identical in an extract, a Type 0 column benefits from an alert when the incoming value differs from the stored one: not to apply the change, but to surface it for a human decision.
- How is a Type 0 column different from a Type 1 column that simply happens not to have changed yet?By intent and by enforcement. A Type 1 column will be overwritten the moment the source changes; a Type 0 column is deliberately excluded from the update path so it never will be. The difference is invisible in today's data and decisive tomorrow, which is why the policy belongs in the model documentation and in a test, not just in the loader's behaviour.
- The source system revises a Type 0 attribute. What should the pipeline do?Not silently apply it. Separate a genuine data-quality correction — the original was recorded wrongly and never described reality — from a real-world change that the source stores in the same field. The first justifies an explicit, logged correction; the second is exactly what Type 0 exists to ignore. Raising an alert on the difference lets a human decide rather than the loader.
saying these in an interview costs you the question
- Type 0 just means we forgot to update the column
- Type 0 and Type 1 both track the current source value
- A database constraint makes the column write-once
- Freeze every attribute as Type 0 to be safe
- Type 0 keeps a history of past values