Slowly Changing Dimensions (SCD) are used in data warehousing to manage and track changes to dimension data over time. Here is the breakdown of SCD Type 1, Type 2, and Type 3, along with real-world scenarios and examples for each.
1. SCD Type 1: Overwrite (No History)
How it works: The existing data is overwritten with the new data. No history is kept of what the value used to be.
When to use: When you only care about the current state of the data and corrections to data entry errors, or when historical changes are completely irrelevant for reporting.
Example: Customer's Marital Status or Correction OF CUSTOMER NAME
WHICH does not need to track the dates
2. SCD Type 2: Add New Row (Full History)
How it works: A completely new row is inserted into the dimension table to capture the change. It uses effective start/end dates, a version number, or a
current_flagto distinguish the active record from historical ones.When to use: When you need to keep a complete historical record and attribute past transactions accurately to the attributes as they existed at the time the transaction occurred. This is the most commonly used SCD type.
Example: Customer's address change State/Region Relocation.
sales order status like booked , ordered , paid , shipped ,delivered
address changes of customer
3. SCD Type 3: Add New Column (Limited History)
How it works: A new column is added to the existing record to store the previous value alongside the current value.
When to use: When you want to track only one level of historical change (the immediate past) without bloating the table with multiple rows. It is rarely used in practice compared to Types 1 and 2 because of its limited scope.
Example: to track Employee's performance Previous Department vs current dept.
Scenario: 1 Employee's performance Previous Department vs current dept.
scenatio : 2 - current designation previous designation
No comments:
Post a Comment