Introduction
One of the fundamental requirements of a data warehouse is the ability to preserve and manage historical data for analytical and decision-making purposes. In real-world business environments, dimensional attributes frequently change over time, creating challenges in maintaining both current and historical information efficiently. Slowly Changing Dimensions (SCDs) are widely used in data warehousing to address this issue by managing changes in dimensional data. Depending on business requirements and the nature of data changes, different SCD approaches such as Type 1, Type 2, Type 3, Type 4, and Type 6 are used to store and track historical information in different ways.
Solution
There are a few common types of SCDs and they differ based on how the attributes of dimensions behave when changes occur. These options are:
- Type 0 – Only the first version is retained. No further modifications are made.
- Type 1 – Only the last version is retained. Previous versions will be overwritten.
- Type 2 – When there is a change, a new record will be added per the current version. Previous versions will be set to previous versions and end-dated. This SCD is very common because it offers greater flexibility than other SCDs.
- Type 3 – Only the previous version is maintained in the same row version as another column. The challenge in this technique is that only the previous version is kept.
- Type 6 - hybrid approach that combines the features of Type 1, Type 2, and Type 3 SCDs to maintain full historical records while also supporting overwriting and tracking of previous attribute values within the same dimension table.
What is Type 4 SCD?
A Type 4 Slowly Changing Dimension (SCD) separates current and historical data into two distinct tables: one table stores the latest version of the dimension data, while another maintains the complete history of changes.
This approach is similar to the way operational systems often manage historical records separately from active data. Type 4 SCD is particularly useful when attribute values change frequently, as it helps improve query performance on current data while still preserving historical information for analysis and auditing purposes.
Sample Scenarios – Customer Loan Repayment
Consider a bank that monitors customer loan repayments every month. Each customer can have one of the following statuses as shown in the below figure. A customer’s payment status may change every month based on their repayment behavior, such as On-Time Payment, Minor Delay, Major Delay, or No Payment.
If these frequent changes are handled using a Type 2 Slowly Changing Dimension, a new version of the customer record must be inserted into the dimension table each time the status changes. Over time, this can cause the dimension table to grow rapidly with a very large number of records, leading to scalability and performance issues. In such situations, the original purpose of a “slowly” changing dimension becomes less meaningful because the attribute changes occur too frequently. For example, in a banking system with 50,000 customers, even if only 40,000 customers experience a status change each month, the warehouse would need to store at least 40,000 additional records monthly, resulting in significant storage and maintenance overhead.
The bank needs to maintain the latest customer payment status for operational and day-to-day reporting while also preserving historical payment behavior for purposes such as risk analysis, customer credit evaluation, compliance monitoring, and auditing. Since customer payment statuses may change frequently over time, the system must efficiently support both current and historical data management. This requirement makes Type 4 Slowly Changing Dimension (SCD) an appropriate solution, as it separates current records from historical records using dedicated current and history tables.
Implementation of Type 4 SCD
The following figure shows the high-level flow of the Type 4 SCD. This workflow illustrates the ETL process for implementing a Type 4 Slowly Changing Dimension (SCD). After extracting data from the source system, the incoming record is compared with the current dimension record to detect any changes. If no changes are found, the current record is simply updated or retained. If a change is detected, the existing record is first archived in the history table to preserve historical information, and then the current dimension table is updated with the latest attribute values. This approach ensures fast access to current data while maintaining a complete audit trail of historical changes.
The implementation requires two tables:
- Current Dimension Table
- History Table
The current dimension table stores the latest customer payment information. We can implement this as follows:
CREATE TABLE DimCustomerPaymentStatus ( CustomerKey INT IDENTITY(1,1) PRIMARY KEY, CustomerID INT NOT NULL, CustomerName VARCHAR(100), PaymentStatus VARCHAR(50), StatusUpdatedDate DATE );
The history table stores previous payment statuses. This would look like this:
CREATE TABLE DimCustomerPaymentStatusHistory ( HistoryKey INT IDENTITY(1,1) PRIMARY KEY, CustomerKey INT, CustomerID INT, CustomerName VARCHAR(100), PaymentStatus VARCHAR(50), EffectiveDate DATE, EndDate DATE, ChangeDate DATETIME DEFAULT GETDATE() )
Let us insert the first record for the Customer Dinesh Asanka with the payment status “On Time Payment” as shown from the below query.
INSERT INTO DimCustomerPaymentStatus
(
CustomerID
, CustomerName
, PaymentStatus
, StatusUpdatedDate
)
VALUES
(101, 'Dinesh Asanka', 'On Time Payment', '2026-01-01');Since this is the first entry, there won’t be historical records against the above customer.
Let us assume that the customer payment status changed to “Major Delay” from “On Time Payment”. There should be couple of changes.
- Insert the previous record into the historical table
- Update the existing dimension record with the new value.
Since this is the first entry, there won’t be historical records against the above customer.
When a customer's payment status changes, the existing record in the DimCustomerPaymentStatus table should not be discarded because it represents the customer's previous payment behavior. Instead, the current record is first copied to the DimCustomerPaymentStatusHistory table, where the original StatusUpdatedDate is stored as the EffectiveDate and the current date is recorded as the EndDate, indicating when that version ceased to be current. This preserves the complete payment history before the latest payment status is written to the current dimension table. The following SQL statement copies the existing record for CustomerID = 101 into the history table.
INSERT INTO DimCustomerPaymentStatusHistory
(
CustomerKey
, CustomerID
, CustomerName
, PaymentStatus
, EffectiveDate
, EndDate
)
SELECT CustomerKey
, CustomerID
, CustomerName
, PaymentStatus
, StatusUpdatedDate
, getdate()
FROM DimCustomerPaymentStatus
WHERE CustomerID = 101;
Since there only one record exists for this customer in the current dimension table, the above query will transfer that record into the historical table with the above query. After preserving the previous version in the history table, the current dimension record can be updated with the customer's latest payment status. This ensures that the DimCustomerPaymentStatus table always contains only the most recent payment information, while historical versions remain available in the history table.
UPDATE DimCustomerPaymentStatus SET PaymentStatus = 'Major Delay' , StatusUpdatedDate = getdate() WHERE CustomerID = 101;
The following figure shows the how the data is updated for Customer 101 when Type 4 SCDs is implemented. This architecture demonstrates the implementation of a Type 4 Slowly Changing Dimension (SCD) using Customer 101 as an example. During the ETL process, incoming customer data is compared with the current dimension record to detect changes. The latest payment status of Customer 101 (e.g., On Time Payment) is maintained in the Current Dimension to support fast operational reporting, while previous statuses, such as Minor Delay and Major Delay, are archived in a dedicated History Table. By separating current and historical records, Type 4 SCD provides efficient access to the most recent data while preserving a complete history for auditing, compliance, and trend analysis.
Here is the data for the DimCustomerPaymentStatusHistory and DimCustomerPaymentStatus tables after the update.
Advantages of Using Type 4 SCD
Type 4 SCD offers several advantages for banking applications, particularly when managing frequently changing customer payment statuses. Since the current dimension table stores only the latest customer records, operational queries and reports can be executed more efficiently without scanning large volumes of historical data. At the same time, historical payment behaviors are preserved in a separate history table, enabling comprehensive analyses such as credit risk assessment, loan recovery evaluation, regulatory compliance auditing, and fraud investigations.
This separation of current and historical data also simplifies reporting, as operational users can easily access the most recent customer payment status without applying complex filters to distinguish current records from historical ones.
Disadvantages of Using Type 4 SCD
Despite its advantages, Type 4 SCD has several limitations that should be considered during implementation. The approach requires additional ETL logic to identify changes, archive historical records, and update current records correctly. Since both a current dimension table and the history table must be maintained, the overall data management process becomes more complex. Historical analysis may also require queries across multiple tables, increasing query complexity compared to approaches where all versions are stored in a single table. Furthermore, ensuring data consistency and integrity between the current and history tables is critical, as any synchronization issues can lead to inaccurate reporting and historical analysis.
Alternative Solution
An alternative approach to maintaining a separate historical table, which can lead to more complex analytical queries, is to model the frequently changing attribute as a separate dimension. In the banking example, the payment status can be moved to a small, independent dimension table containing the possible status values (e.g., On-Time Payment, Minor Delay, Major Delay, and No Payment). The corresponding surrogate key from this payment status dimension can then be stored in the fact table.
This approach, often referred to as a mini-dimension strategy, reduces the need to create multiple versions of the customer dimension record while still allowing changes in payment status to be tracked efficiently. It also helps keep the main customer dimension stable and compact, improving both storage efficiency and query performance.
The following schema demonstrates an alternative implementation using a mini-dimension rather than maintaining a separate history table. The DimCustomer table stores relatively stable customer attributes such as CustomerName, CustomerType, and BranchName, while the frequently changing payment status is moved to a separate DimPaymentStatus table. Instead of creating multiple versions of customer records, the FactCustomerPayment table references both CustomerKey and PaymentStatusKey, allowing each payment transaction (identified by PaymentMonth) to be associated with the customer's payment status at that point in time. This design keeps the DimCustomer table compact while enabling historical payment status analysis through the relationship between the fact table and the mini-dimension.
-- Customer Dimension Table CREATE TABLE DimCustomer ( CustomerKey INT IDENTITY(1,1) PRIMARY KEY, CustomerID VARCHAR(20) NOT NULL, CustomerName VARCHAR(100), CustomerType VARCHAR(50), BranchName VARCHAR(100) ); -- Payment Status Mini Dimension Table CREATE TABLE DimPaymentStatus ( PaymentStatusKey INT IDENTITY(1,1) PRIMARY KEY, PaymentStatus VARCHAR(50) NOT NULL, StatusDescription VARCHAR(255) ); -- Fact Table CREATE TABLE FactCustomerPayment ( PaymentFactKey INT IDENTITY(1,1) PRIMARY KEY, CustomerKey INT NOT NULL, PaymentStatusKey INT NOT NULL, PaymentMonth DATE NOT NULL, AmountDue DECIMAL(12,2), AmountPaid DECIMAL(12,2), FOREIGN KEY (CustomerKey) REFERENCES DimCustomer(CustomerKey), FOREIGN KEY (PaymentStatusKey) REFERENCES DimPaymentStatus(PaymentStatusKey) );
When Should Type 4 SCD Be Used?
Type 4 SCD is most appropriate when:
- Historical data must be preserved.
- Current records are accessed significantly more often than historical records.
- Frequently changing attributes would cause excessive growth in a Type 2 dimension.
- Operational reporting requires fast access to the latest data.
The following flowchart will allow you to choose your best SCD.
This decision diagram helps identify the most appropriate Slowly Changing Dimension (SCD) strategy based on business requirements. If historical data is not required, Type 1 is sufficient. When history must be preserved, the choice depends on how frequently the data changes and how often current records are queried. Type 2 is ideal for maintaining complete historical records, while Type 4 is better suited for frequently changing attributes by separating current and historical data into dedicated tables, improving query performance for operational reporting.
Typical use cases include banking payment statuses, customer loyalty levels, account risk ratings, product availability statuses, and employee assignment histories.
