Choosing Between SCD Types 1, 2, 3 and 6

A dimension attribute can be handled in exactly four ways, and the choice is per attribute rather than per dimension. A customer dimension can overwrite the phone number, version the region, keep the previous credit band in a column, and do all three for the segment. The taxonomy that names these choices is old and slightly awkward, but it survives because the distinctions are real: each type answers a different question, and each costs a different amount of work.

Overwriting: type 1

Type 1 replaces the old value in place. The dimension row keeps its surrogate key, the fact table’s keys stay valid, and history is gone.

The cost is zero beyond the update itself: no new row, no new key, no change to any fact. The consequence is that every historical fact now reports the new value. If a customer’s region is corrected from a mistyped “Bergne” to “Bergen”, that is exactly right — the old value was never true. If a customer genuinely moved, type 1 is a deliberate decision to answer only “where is this customer now” and to abandon “where were they when they bought”.

Type 1 is correct for attribute corrections, for attributes whose history has no analytical value, and for attributes that are derived from another system and should simply reflect it. It is also the right choice when the dimension is large and the attribute changes constantly, because it is the only type whose load cost does not scale with change frequency.

The subtlety is that type 1 destroys the ability to reconcile against a source that retains history. If the source can produce a customer’s region as of any date and the dimension cannot, a comparison between them will disagree on historical rows, and the disagreement is not a bug.

Versioning: type 2

Type 2 adds a row per version, with a new surrogate key and a validity interval, as described in the mechanics of versioned dimensions. It answers both questions — as-of and current — and it is the only type that does so for an unlimited number of changes.

The costs are three. Storage grows with the number of versions rather than the number of entities, which for an attribute that changes monthly is a factor of twelve per year over the entity count. Every fact load must resolve a natural key to a version, which is a range lookup rather than an equality. And every consumer must decide which reading it wants, which is a specification burden that propagates to every report.

The version count is the number that decides whether type 2 is affordable. A dimension of a million customers with an average of one change per year reaches two million rows after a year, which is small. The same dimension with a daily-changing attribute reaches three hundred and sixty-five million, which is not a dimension any more. The pattern that rescues the second case is to version only the slowly changing attributes and move the fast ones to a separate table keyed by entity and date — the mini-dimension arrangement covered in narrow dimensions for fast-changing attributes.

Previous value in a column: type 3

Type 3 keeps a second column holding the prior value. A customer row gains region_current and region_previous, and the load shifts the old value into the second column before writing the new one.

This answers “what was it before” for exactly one step back, and nothing further. It costs one extra column per tracked attribute and one extra assignment per load. It keeps the surrogate key stable, so no fact table changes.

Type 3 is the cheapest way to support a specific comparison — before-and-after a reorganisation, previous and current price band — when the business question is genuinely one step deep. It fails the moment a second change arrives, because the “previous” value is overwritten and the first value is gone.

The variant that works better is a pair of date columns rather than a pair of value columns: the current value plus the date it took effect. That answers “how long has it been this way” without pretending to hold a history, and it composes with type 2 if the attribute later needs full versioning.

A second variant appears in dimensions that track a hierarchy change, where the previous and current parent keys are both stored so that a report can show what moved under what. The limitation is the same: two levels, no more.

Both readings from one table: type 6

Type 6 combines 1, 2 and 3: the table is versioned, every version row also carries the current value of the attribute, and the current row is flagged. The name comes from adding the three types.

The purpose is to let a query answer either question without a join or a subquery. A fact joined to its version’s row can group by the version’s own attribute — the as-of reading — or by the replicated current attribute — the present-day reading — using the same join. That removes the need for two paths to the same dimension, which is a real simplification for a reporting layer that cannot easily join facts to the dimension twice.

The cost is the maintenance of the replicated column. When an attribute changes, the new version row gets the new value, and every prior version row of that entity must be updated to carry it as well. That is an update over the entity’s whole version history per change, which is unbounded work per event: an entity with fifty versions costs fifty row updates per change. The alternative is to leave the replicated column stale and refresh it in a batch, which reintroduces the possibility of two readings disagreeing.

Type 6 also makes the dimension’s rows no longer independent. A row’s meaning depends on other rows, so any process that loads one version in isolation produces an inconsistent table. That is acceptable when a single loader owns the whole dimension and unacceptable when several pipelines write to it.

The decision is per attribute and per question

The four types are not a progression from worse to better, and the choice belongs to the attribute inside a dimension that is otherwise a single wide table. The decision procedure is to name the question the attribute must answer and check which types answer it:

Question	1	2	3	6
What is the value now?	yes	yes, with current filter	yes	yes
What was the value when the fact occurred?	no	yes	one step	yes
What was the value before the last change?	no	yes	one step	yes
How many distinct values has it had?	no	yes	no	yes
No new keys in the fact table	yes	no	yes	no

Two rows in that table do most of the work. The as-of question requires type 2, because only a versioned row carries the interval. The no-new-keys requirement forces type 1 or 3, because the surrogate key must remain stable across a change. Those two requirements are incompatible, and a dimension that is asked for both has to be split: versioned attributes in one table, stable attributes in another, joined by the natural key.

Mixed types within one dimension are normal and correct. A customer dimension typically overwrites the email address, versions the region, keeps the previous segment in a column, and leaves the date of birth alone entirely. The dimension’s documentation has to record which type applies to which column, because the type is not visible from the schema — a region column in a versioned table and a region column in an overwritten table look identical.

What goes wrong when the type is chosen late

The most expensive sequence is to ship type 1 and then discover that history was needed. The old values are gone from the dimension, and they exist only if the source retained them or if the load’s staging area still holds them. Recovering them means replaying history through the dimension and rewriting every fact table’s foreign keys to point at the reconstructed versions, which is a full rebuild of the warehouse’s historical layer.

The reverse sequence is cheaper but not free: shipping type 2 and discovering that nobody wanted history leaves a dimension with many more rows than entities, every fact load paying a range lookup, and every report needing a filter it did not previously need. Coarsening a type 2 dimension to type 1 is a matter of keeping the current version per entity and re-pointing the facts, which is the same key rewrite in the other direction, but with the advantage that the answer is knowable from the table itself.

The asymmetry is why the default should lean toward versioning the attributes that describe the entity’s state in the world — region, segment, ownership, status — and overwriting the attributes that are identifiers or contact details. A rule of thumb that holds: if the attribute appears as a filter in a report that spans time, it needs type 2. If it appears only as a display column, it does not.

There is a third failure, distinct from both: choosing type 2 for an attribute whose source has no effective date. Then the version’s valid_from can only be the load time, and the dimension records when the warehouse learned of a change rather than when the change happened. Facts before the load resolve to the previous version even though the change had already occurred in the world, and the error is invisible in the table because the intervals are well formed. No type choice repairs a source that cannot say when something changed; that limitation has to be documented against the attribute, and the reported history should be understood as load-time history rather than event-time history.