Slowly Changing Dimension (SCD)

1. Overview

A. Definition

A Slowly Changing Dimension (SCD) is a dimensional modeling technique that defines how to store and track a change when an attribute value in a data warehouse dimension table changes slowly over time. It is categorized from Type 0 to Type 6 depending on whether and how change history is preserved.

The core question of this concept, systematized by Ralph Kimball, is simple yet powerful. "When a customer's address changes from Seoul to Busan, should past orders remain recorded as having occurred in Seoul, or should they all be changed to Busan?" The answer to this question governs the accuracy of analytical results. A dimension supplies the "who, what, where, when" context to a fact (a measurement), and how that context is handled when it changes is precisely the history-management strategy.

The key word in SCD is "slowly." The target is attributes that change rarely and irregularly — such as product classification, customer tier, organizational affiliation, and region code — which differ in nature from transactional data (facts) pouring in at thousands of records per second. Rapidly changing attributes are instead best separated into facts or extracted into a dedicated mini-dimension.

B. Background and Necessity

Traditional operational (OLTP) databases manage only the "current state." When a customer changes their address, the existing record is overwritten with an UPDATE, and the past address vanishes without a trace. This is reasonable because operational work needs only the latest value. In the analytical world (OLAP), however, the story is entirely different. If a customer's past address is lost while analyzing "sales trends by region," three-year-old sales would be aggregated under the current address, distorting the result and damaging the historical truth.

This problem is especially fatal in the regulatory and audit domains. Credit-rating change history in finance, insured-party attribute changes in insurance, and patient-state tracking in healthcare must be able to reproduce exactly "what the value was at that point in time." A simple overwrite fundamentally eliminates this point-in-time reconstruction capability. SCD emerged precisely to bridge this gap — between the operational system's "keep only the present" and the analytical system's "demand for history preservation."

Furthermore, as the data warehouse became the Single Source of Truth for enterprise-wide decision-making, handling dimension changes ad hoc without consistent rules causes the chaos of analytical results differing per analyst and per query. SCD standardizes change-handling into types, forcing an explicit decision at design time about which attributes preserve history and which are overwritten. This secures a reproducible and auditable analytical foundation.

2. The Place of SCD in the Dimensional Model and Overall Structure

To understand SCD, one must first see the relationship between dimension and fact in a dimensional model (star schema). The fact table holds measurements (sales amount, quantity) along with foreign keys pointing to each dimension, and the starting point of SCD is that these foreign keys are not operational natural keys (business keys, e.g., customer number) but surrogate keys. A surrogate key is a meaningless serial number generated by the DW itself, enabling "different point-in-time versions of the same customer" to be distinguished as separate rows. Nearly every history-preserving SCD type is built on this surrogate key.

flowchart LR
    subgraph SRC["Operational system (OLTP)"]
      OP[("Customer source table<br/>(only current value, UPDATE)")]
    end
    OP --> ETL["ETL / ELT load<br/>(change detection, per-type handling)"]
    subgraph DW["Data warehouse (star schema)"]
      DIM[("Customer dimension<br/>(surrogate key, history versions)")]
      FACT[("Order fact<br/>(measurements, dimension FK)")]
      FACT -->|"surrogate key reference"| DIM
    end
    ETL --> DIM
    ETL --> FACT
    DIM --> BI["BI / analytics<br/>(point-in-time reconstruction, trend analysis)"]
    FACT --> BI

The key in the structure above is change detection in the ETL stage. The ETL compares the source's current value with the latest version of the dimension already loaded in the DW and (1) inserts if the key is new, (2) handles per-type rules if a tracked attribute changed, and (3) passes it through if there is no change. Here, "which attribute is a tracking target and which Type each attribute is handled with" is the product of design, and it is common to mix different Types per attribute even within a single dimension (this becomes the basis for Type 6, explained later).

To improve the accuracy of change detection, practitioners store a hash value (hash diff) of each attribute and compare it against the source hash, or process change events received from the CDC ([[change-data-capture]]) seen in Section 2. The former suits batch comparison and the latter near-real-time loading. Either way, SCD rules can be applied correctly only when "what changed" is determined reliably.

Two time concepts must be distinguished here. One is valid time — the period during which the value was valid in reality — and the other is transaction/load time — the moment the DW came to know that fact. The two times can diverge due to source delays or retroactive corrections, and a design that tracks both is called bitemporal. In domains where regulatory and audit requirements demand both "when it was valid in reality" and "when we recorded it that way," a bitemporal dimension design that adds a load-time column to the Type 2 validity period is needed.

3. Handling by SCD Type (Type 0 ~ 6)

SCD types are arranged along a single axis: "how the change is stored." Starting from the most conservative Type 0 (immutable), they advance through overwriting (Type 1), full historization via row addition (Type 2), limited history (Type 3), and the combination of these (Type 6). Below, each type is described in order in terms of what situation it was devised for and what price it pays.

flowchart TB
    START["Dimension attribute change occurs"] --> Q0{"Must the change be reflected?"}
    Q0 -- "No (immutable attribute)" --> T0["Type 0<br/>retain original value permanently"]
    Q0 -- "Yes" --> Q1{"Is past history needed for analysis?"}
    Q1 -- "Not needed (latest only)" --> T1["Type 1<br/>overwrite existing value"]
    Q1 -- "Needed (full history)" --> T2["Type 2<br/>add new version row"]
    Q1 -- "Only the prior value" --> T3["Type 3<br/>add previous-value column"]
    T2 --> T6["Type 6<br/>1+2+3 hybrid<br/>(current and history together)"]
    T3 --> T6

A. Type 0 — Retain Original. A method that never changes a value once recorded. It applies to attributes that are immutable by definition or must be treated as such, like a customer's original join date, date of birth, or national unique identifier. Even if the value changes at the source, the DW ignores it or classifies it as a separate error. Implementation is the simplest, but if the judgment of "immutable" is wrong (e.g., an initial data-entry error can never be fixed), it instead entrenches a quality problem, so it is applied only, in a limited way, to attributes that truly must not change.

B. Type 1 — Overwrite. A method that discards the past by UPDATE-ing the existing value with the new one. It is the most intuitive, being identical to operational-system behavior, and has low storage/query cost because the dimension row count does not grow. It suits cases where past history is analytically meaningless or is a simple error correction, such as fixing a phone-number typo or correcting a classification-code spelling. However, overwriting with Type 1 causes a retroactive restatement of past fact aggregation on that attribute's basis. That is, three-year-old sales are re-aggregated under today's classification, making it impossible to reproduce "the actual picture at that time." Whether this retroactive effect is permissible is the decision criterion for applying Type 1.

C. Type 2 — Add New Row (preserve history). The de facto standard and most widely used method of SCD. When an attribute changes, instead of overwriting the existing row, a new row with a new surrogate key is added. Multiple versions sharing the same natural key (customer number) but differing in surrogate key coexist, and each version is distinguished by meta columns such as effective start date (effective_date), effective end date (end_date), and a current-flag (is_current). When a new version is created, the previous version's end_date is closed and its is_current is turned off. The greatest advantage of this method is complete point-in-time reconstruction. Because the fact references the surrogate key as of the moment it occurred, a past order remains forever linked to "that customer's Seoul-address version at the time." The price is a growing dimension row count and the complexity of "query only the current version" (requiring flag/date conditions). It is used essentially in domains where history is itself a regulatory requirement, such as financial credit ratings, insurance contract status, and organizational-affiliation changes.

D. Type 3 — Add New Attribute (previous-value column). A method that adds a column rather than a row, placing current_region and prior_region side by side. It is used when one must "compare the moment just before and after a change simultaneously," as in a sweeping reorganization, but does not need full history. Storage cost is small and queries are simple, but because it preserves only the prior one step (or a fixed N steps), change history beyond two changes is lost. It is therefore confined to special situations where changes are rare and "previous↔current" comparison is central to the analysis.

The differences in character among the four types can be summarized as follows. The table is only a supporting aid for comparison; the actual choice is determined by the balance of "need for history, permissibility of retroactivity, and storage/query cost" explained above.

Category	Change handling	History preservation	Impact on past facts	Row/column growth	Representative use
Type 0	Not reflected	Original fixed	None	None	Date of birth, join date
Type 1	Overwrite	None (current only)	Retroactively changed	None	Typo, code correction
Type 2	Add new row	Fully preserved	Immutable (time-fixed)	Row growth	Credit rating, contract status
Type 3	Add column	Prior value only	Mixed	Column growth	Before/after reorganization
Type 6	1+2+3 combined	Full + current together	Selective	Row + column growth	Current & point-in-time together

E. Type 4 · Type 6 — Combined forms. Type 4 separates the current value into the main dimension and the history into a dedicated history mini-dimension, aiming at both current-query performance and history preservation. Type 6 (the name derives from 1+2+3=6) places in a single dimension Type 2 version rows, a "current value" column that always keeps the latest value via Type 1, and a Type 3-style previous-value column together. This allows both "the value at that time" (the version row) and "this customer's current value" (the current column) to be queried from the same table, enabling, for example, a dual analysis that "groups past orders from the Seoul-resident era also from the perspective of a customer who now lives in Busan." It is flexible but has the drawback of making ETL logic more complex.

4. Implementation Architecture and ETL Handling Techniques

In practice, SCD is implemented as the dimension-load stage of an ETL/ELT pipeline. The most typical pattern is SQL's MERGE (UPSERT) statement, which joins the source current snapshot with the DW dimension, INSERTing new rows and handling changes per type. For Type 2, a single MERGE cannot easily handle "closing the existing version + opening a new version" at once, so it is usually split into two steps: (1) an UPDATE closing the end_date of the changed natural key's current row → (2) INSERTing the new version. If this order is reversed, a consistency error arises where two is_current rows exist for one natural key, so atomicity must be guaranteed by wrapping it in a transaction.

sequenceDiagram
    participant S as Source snapshot
    participant E as ETL engine
    participant D as Dimension table
    S->>E: pass current customer record
    E->>D: look up current version (is_current=Y) by natural key
    alt New natural key
        E->>D: INSERT with a new surrogate key
    else Tracked attribute changed (Type 2)
        E->>D: set existing row end_date, is_current=N
        E->>D: INSERT new version row (is_current=Y)
    else Untracked attribute changed (Type 1)
        E->>D: UPDATE current row (overwrite)
    else No change
        E-->>E: pass (skip)
    end

To improve the reliability of change detection, practitioners concatenate the tracked attributes to create a hash (e.g., SHA-256) fingerprint and store it on the dimension row. Comparing the source hash with the stored hash just once at load time allows change to be detected immediately without collating many columns one by one, greatly reducing the comparison cost for dimensions of tens of millions of rows. Also, setting the validity-period end_date to an infinity marker such as 9999-12-31 instead of an actual end time allows the "currently valid" condition to be expressed simply as a range join.

Surrogate-key design is also an important implementation point. Using an operational natural key directly as a fact FK not only makes it impossible to distinguish history versions but also becomes vulnerable to source-system integration, key reuse, and NULL handling. The surrogate key serves to insulate the DW from such operational-system changes, and reserved keys (e.g., -1) are operated together for special situations such as unknown and late arriving. The late arriving dimension problem, where the fact arrives before the source, is handled by first creating a temporary placeholder row and later filling it with the actual value.

5. Type-Selection Comparison and Application Cases

Even for the same "address change," the correct Type differs by analytical purpose. The fundamental reason this difference arises is that SCD is not a data-storage technique but the implementation of a business rule about "how to interpret the past." The three cases below show this clearly.

First, in the financial credit-assessment case, Type 2 is effectively mandatory. When a customer's credit rating is upgraded from BBB to A, failing to reproduce at which rating-point a past loan was executed cannot satisfy the supervisory authority's risk-reproduction demand (e.g., the rating distribution of the quarter-end portfolio at a specific time). In fact, Basel-compliance DWs load rating, limit, and collateral attributes all as Type 2 to accurately reconstruct exposure at any arbitrary past point in time.

Second, in the e-commerce product catalog case, Types are mixed by attribute. Fixing a product-name typo suffices as Type 1 (overwrite), but product-category reclassification must be kept as Type 2 so that both "quarterly sales on the pre-reclassification basis" and "the post-reclassification basis" can be analyzed. It is common for dozens of attributes within a single product dimension to be mixed as Type 0/1/2, and this design document itself becomes a data-governance deliverable.

Third, in the HR/organization analysis case, Type 3 or Type 6 is useful. When one must compare "performance on the pre-reorganization affiliation basis" and "performance on the post-reorganization affiliation basis" side by side right after a large-scale reorganization, a prior-affiliation column (Type 3) or coexisting current/historical columns (Type 6) greatly simplifies the analyst's query. However, if the reorganization repeats two or more times, Type 3 alone loses intermediate history, so running Type 2 in parallel is unavoidable.

What these three cases jointly imply is that a wrong Type choice leads to silent data errors. If an attribute that needs history is overwritten with Type 1, past reports are retroactively falsified, yet the system raises no error at all, and the analyst may make a wrong decision without even knowing the numbers changed. Conversely, handling a simple-correction attribute as Type 2 piles up meaningless versions and bloats the dimension. This is exactly why SCD design review must be treated as a core activity of data quality management.

6. Deep Dive — SCD in the Lakehouse Era and Recent Trends

Traditional SCD evolved on the premise of RDBMS-based DWs, but the spread of the data lakehouse ([[data-lakehouse]]) and the ELT paradigm is changing its implementation. In the past, ETL tools directly UPDATE/INSERTed rows, but traditional UPDATE on files over object storage was expensive or impossible. What solved this are open table formats like Delta Lake, Apache Iceberg, and Apache Hudi, which support ACID transactions and MERGE INTO, enabling Type 2 loading to be performed declaratively even over a lake. In particular, Iceberg/Delta's time travel assigns versions to the table snapshots themselves, so it can also serve as a complementary means of resolving some point-in-time reconstruction demands at the storage layer without SCD.

In the transformation layer, dbt's snapshot feature has effectively standardized SCD Type 2. When an analytics engineer declares only a tracking strategy (check/timestamp) and the tracked columns, dbt auto-generates validity-period and current-flag management and change detection. This has greatly reduced errors compared with the past of implementing SCD logic by hand, and has become a core component of code-based data pipelines (Analytics Engineering) that can be version-controlled and tested.

Meanwhile, a design task that pairs with SCD is handling the Rapidly Changing Dimension (RCD). If frequently changing attributes such as a customer's age band or purchase-propensity score are kept as Type 2, versions explode. In that case, the rapidly changing attributes are split out of the main dimension into a mini-dimension, and the fact is designed to reference both dimensions separately. Since SCD is a technique for "slow change," the judgment of identifying and separating fast changes outside that boundary is also the dimension designer's responsibility.

On the database-standard side, the system-versioned temporal table introduced by SQL:2011 draws attention. Supported by PostgreSQL, SQL Server, MariaDB, and others, this feature provides past-point-in-time queries at the language level with the FOR SYSTEM_TIME AS OF syntax, so that from the application's perspective the DBMS transparently handles history preservation similar to Type 2. However, because this is a system-time history of "the whole row," it differs in aim from the "per-attribute tracking-strategy mix" or surrogate-key-based fact linkage required in analytical modeling, so it tends to complement rather than fully replace the analytical DW's SCD.

7. Considerations and Implications (Professional Engineer's Perspective)

Because SCD is not a mere loading technique but a design decision that governs the reliability, auditability, and analytical accuracy of the data warehouse, from a Professional Engineer's perspective the following must be considered comprehensively.

• Application strategy (making per-attribute Type mapping explicit): Decide the Type per attribute rather than for the whole dimension, and codify it in a data dictionary and governance document. Because "which change is historical truth and which change is a simple correction" is a question the business, not IT, must answer, the consensus process with the business must be included in the design process.
• Trade-off (history completeness ↔ storage/query cost): Type 2 perfectly preserves history but entails row explosion and the complexity of "current-version query." Setting high-change-frequency attributes to Type 2 indiscriminately bloats the dimension, so high-frequency attributes must be balanced by adjusting the design through mini-dimension separation or factification.
• Guaranteeing consistency/atomicity: Type 2's "close existing version + open new version" must be wrapped in a transaction to prevent is_current duplication, period overlap, and gaps, and post-load validity-period continuity verification must be automated as a data-quality test. Securing idempotency ([[idempotency]]) to prevent duplicate version creation on reload is also essential.
• Outlook and related technologies: Combined with CDC ([[change-data-capture]]), near-real-time SCD rather than batch becomes possible, and the lakehouse's open table formats, dbt snapshot, and system-versioned tables are elevating SCD implementation to be declarative and standardized. However, whatever technology is used, the business rule itself of "how to interpret the past" is not automated, so clear definition of the history policy must precede technology choice.

References

• Kimball Group, "Slowly Changing Dimension (SCD)" — https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/
• dbt Labs, "Snapshots" — https://docs.getdbt.com/docs/build/snapshots
• Delta Lake Documentation, "Slowly changing data (SCD) Type 2 operation into Delta tables" — https://docs.delta.io/latest/delta-update.html

---

In one line: SCD is a technique defining how to store and track slow attribute changes in a data warehouse dimension, implementing the business rule of "how to interpret the past" by choosing — per attribute — among overwrite (Type 1), full-history row addition (Type 2), previous-value column (Type 3), and hybrid (Type 6).