What is the difference between SCD Type 1 and Type 2?

Type 1 overwrites the old value with the new one and leaves no trace: the history is lost for good. Type 2 adds a new row with a validity date, keeping both the old and the new value: the history stays reconstructible row by row. Type 1 is chosen when the change is a correction, Type 2 when it is an event a historical report needs to be able to find again.

What is the difference between SCD Type 2 and Type 4?

Both preserve full history, but organize it differently: Type 2 accumulates it as extra rows in the same dimension table, Type 4 moves it into a separate mini-dimension. Type 4 is used when an attribute changes so often that Type 2 would blow the main table up to millions of useless rows.

Which SCD type should I use?

It depends on the question the attribute's history has to answer, not on a technical preference: for a correction (a typo), Type 1; when the exact history matters and the attribute changes rarely, Type 2; when the attribute changes very often, Type 4; when both the current value and the value at the time of each historical fact are needed in the same schema, Type 6 or Type 7. The choice is made dimension by dimension, and sometimes attribute by attribute, not once for the whole data warehouse.

What are Slowly Changing Dimensions (SCD)?

Techniques for handling how a dimension's attributes change over time (e.g. a customer's address) in a data warehouse.

A dimension, in a star schema, describes the context of a fact: the customer who bought, the product sold, the store where the transaction happened. The problem is that these entities change over time: a customer moves and changes address, a product gets reclassified into a different category, a store changes its banner. Slowly Changing Dimensions (SCD) are the set of techniques that answer a precise question: when you generate a historical report on a sale from two years ago, should you see the address the customer had back then, or the current one? There is no universally correct answer: it depends on what the business needs, and that choice, made dimension by dimension and attribute by attribute, is what determines which SCD type to apply. Classic data warehousing literature, starting with Kimball, distinguishes eight types numbered 0 through 7, each with different behavior regarding how history is preserved.

The main types (0-7)

The eight Kimball types and their mechanism for preserving the attribute's history.
Type	Mechanism	History kept
Type 0	The attribute never changes once written, by definition	Not applicable (immutable)
Type 1	Overwrites the old value with the new one	None
Type 2	Adds a new row with `valid_from`/`valid_to`	Complete, row by row
Type 3	Adds a column for the previous value next to the current one	One level back
Type 4	Moves fast-changing attributes into a separate mini-dimension	Complete, in the mini-dimension
Type 5	Adds a Type 1 reference to the Type 4 mini-dimension in the main dimension	Like Type 4, plus a direct read of the current value
Type 6	Hybrid "1+2+3": combines all three approaches in the same row	Current, complete and immediate-previous together
Type 7	Dual key: natural (always current) and surrogate (tied to the historical row)	Both, for "as is" and "as was" reporting

A data engineer proposed a "Type 8" in 2025 for very high-cardinality, high-churn dimensions, replacing validity dates with one event per observed combination and leaning on date partitioning: the author himself states that "the name and number are not yet established". It is not a type recognized by the Kimball methodology, only an individual proposal not yet vetted by the community: worth knowing about, not worth applying as if it were a standard.

Rapidly Changing Dimension (RCD) / Fast Changing Dimension (FCD)

A dimension is called "rapidly changing" or "fast changing" when one or more of its attributes change very often and across a large number of rows, typically attributes of a near-transactional nature (ordered quantity, applied price, order status). Applying a classic Type 2 to these attributes becomes impractical: every small change would generate a new row, blowing the dimension up to millions of useless records. The standard technique, codified by Kimball, is precisely Type 4: move the fast-changing attributes into a dedicated, smaller and more manageable mini-dimension, keeping the main dimension (the customer, the product) stable and lean.

Type 2 under scrutiny: "functional data engineering"

A practical critique of Type 2, from Zach Wilson (ex Facebook, Netflix, Airbnb) and Sahar Massachi in "SCD-2 considered harmful" (November 2025): valid_from/valid_to logic was born when disk was expensive, but now every run depends on the previous one, so a bug on one date must be rerun in sequence across every day after it. The alternative, "functional data engineering", appends a complete, immutable snapshot every day with a datestamp column (ds): the backfill becomes parallelizable, historical queries a simple WHERE ds='...' filter. This does not invalidate Type 2 where row-by-row history is needed: it shifts the trade-off between parallelizability and simplicity.

Why it matters for a business

Choosing the right SCD type for each dimension is not a technical detail to leave to whoever writes the pipelines: it is a business decision. Getting it wrong one way, using Type 1 where Type 2 was needed, means silently losing history: historical reporting becomes unreliable because every past fact ends up showing the current attribute instead of the true one at the time of the event. Getting it wrong the other way, using Type 2 on a rapidly changing dimension instead of the Type 4 it calls for, blows the schema up into dimensions with millions of rows nobody needs, weighing down queries and costs. This discipline sits directly on top of the star schema, and it is the same tension between model integrity and delivery speed that runs through the Kimball vs Inmon debate: the right SCD choice is what keeps reliable, over time, the gold layer both philosophies are trying to build.