what is difference between scd2 and scd3

Answers were Sorted based on User's Feedback



what is difference between scd2 and scd3..

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

what is difference between scd2 and scd3..

Answer / mahesh225

SCD Type2 maintains the complete history and SCD Type3 will maintains one time history.

www.futuresoftworld.com
www.futureitem.com

Is This Answer Correct ?    1 Yes 0 No

what is difference between scd2 and scd3..

Answer / shubhamw

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

Post New Answer

More Data Warehouse General Interview Questions

What is execution plan?

0 Answers  


Explain what are the various methods of getting incremental records or delta records from the source systems?

0 Answers  


Explain the definition of normalized and denormalized view and what are the differences between them?

0 Answers  


What are the steps to build the datawarehouse?

0 Answers  


Explain what is difference between query studio and report studio?

0 Answers  






What are the types of dimensional modeling?

0 Answers  


Can any one explain the hierarchies level data warehousing.

0 Answers  


Difference between ODS and Staging

4 Answers   HeadStrong, TCS,


Explain what are the modules in power mart?

0 Answers  


Advantages of de normalized data?

2 Answers  


Explain which columns go to the fact table and which columns go the dimension table?

0 Answers  


Hi Friends, Can anyone tell me. is there any certification in Database testing or BI testing or ETL testing international certification? Thanks ArunKumar

0 Answers  


Categories