Data Warehouse Question
What is a Slowly Changing Dimension (SCD)? What are the different types of SCDs?

Question

Data Warehouse Question

What is a Slowly Changing Dimension (SCD)? What are the different types of SCDs?

Accepted Answer

A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over
time in a data warehouse. It is considered and implemented as one of the most critical ETL tasks in tracking the
history of dimension records.

There are three types of SCDs and you can use Warehouse Builder to define, deploy, and load all three types of
SCDs.

Type 1 SCDs - Overwriting :
In a Type 1 SCD the new data overwrites the existing data. Thus the existing data is lost as it is not stored anywhere
else. This is the default type of dimension you create. You do not need to specify any additional information to create
a Type 1 SCD.

Type 2 SCDs - Creating another dimension record :
A Type 2 SCD retains the full history of values. When the value of a chosen attribute changes, the current record is
closed. A new record is created with the changed data values and this new record becomes the current record. Each
record contains the effective time and expiration time to identify the time period between which the record was active.

Type 3 SCDs - Creating a current value field :
A Type 3 SCD stores two versions of values for certain selected level attributes. Each record stores the previous
value and the current value of the selected attribute. When the value of any of the selected attributes changes, the
current value is stored as the old value and the new value becomes the current value.

Top CGI Group Senior Software Engineer interview questions & answers

Popular interview questions of Senior Software Engineer

Top HR questions asked in CGI Group Senior Software Engineer