Interested to Buy Any Domain ? << Click Here >> for more details...
what is difference between scd2 and scd3
- 3 Answers
- 9631 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 are confirmed dimension? We alwys give date as a conformed dimension but if it has different format for different contries say YYMMDD for italy and MM-DD-YYYY for france.Then are they not confirmed.
4 Answers IBM, MAHINDRA, Tech Mahindra,
How to reduce warnings?
What is the data type of the surrogate key?
What is a view as it relates to system modeling?
What is the difference between an ODS and a W/H?
How are the dimension tables designed?
What is operational data store (ods)?
What is data validation strategies for data mart v?
Explain swot analysis?
What are the main steps to build the data warehouse?
Is it possible to have more than one row on the same table with the same foreign key?
What are aggregate tables and aggregate fact tables?
Teradata 
