what is difference between scd2 and scd3
- 3 Answers
- 8286 Views
- I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / rashmirekha panigrahy
In SCD Type2 we keep the entire historical data in the dimension table whereas in SCD Type3 we keep partial historical data in the dimension table.
For Ex : Suppose we are having a Customer Table. Acustomer 1st staying at Los Angels. Then he is shifted to DEllas. again he is shifted to Texas.
In case of Type 2 if we are using date method it will show the location and how many days he stayed on that particular location.
Sk_C Cust_ID Cust_NM Location Start_Dt End_DT
101 200345 Williams Los Angels 01-Mar-2011 23-Jun-2012
678 200345 Williams Dellas 24-Jun-2012 12-Nov-2014
1562 200345 Williams Texas 13-Nov-2014 NULL
But In case of Type 3 we will keep only current location and previous location. Not more than that.
Sk_C Cust_ID Cust_NM Cur_Location Pre_Location
101 200345 Williams Los Angels NULL
He shifted to Dellas.
Sk_C Cust_ID Cust_NM Cur_Location Pre_Location
101 200345 Williams Dellas Los Angels
Again he shifted to Texas.
Sk_C Cust_ID Cust_NM Cur_Location Pre_Location
101 200345 Williams Texas Dellas
| Is This Answer Correct ? | 3 Yes | 0 No |
According to my findings, the key difference between SCD type 2 and type 3 is in their ability to maintain historical records. SCD type 2 is capable of storing multiple versions of a record, while SCD type 3 may only retain the current version and one previous version. However, there is some ambiguity regarding how many records SCD type 3 can hold.
To clarify, a good rule of thumb is to use SCD type 3 when the number of historic records that need to be retained is known in advance for e.g. If you need to store 5 records per user_id then the data structure is going to remain consistent over time i.e. 5 records hence implementing type 3 SCD will be a better choice. Conversely, if the number of records per user_id is uncertain, SCD type 2 may be a better choice.
| Is This Answer Correct ? | 0 Yes | 0 No |
What is the difference between dependent data warehouse and independent data warehouse?
How to know the time taken for particular report execution?
What is is a star schema?
What is is the guidelines to build universe with better performance? R performance tuning issues of universes?
What is index awareness in universe?
which automation tool is used in data warehouse testing?
What are the primary ways to store data in OLAP?
how do you use commit frequencies? how does it affect loading performance?
Why is dimensional normalization not required?
What are templates in which way it is help full?
How to answer the question how many fakt table in your datawarehouse and what size it have?
what is the synchonus? wht is the diff between synchronous and asynchronous what is the diff between v1 and v2 v3 delta modes in sap ecc
Teradata 
