# Data Warehouse – Slowly Changing Dimensions SCD (Type 1 vs Type 2)

**URL:** <https://forum.cloverdx.com/t/data-warehouse-slowly-changing-dimensions-scd-type-1-vs-type-2/1643>\
**Category:** CloverDX Platform\
**Created:** [June 17, 2015, 12:00am UTC](https://forum.cloverdx.com/t/data-warehouse-slowly-changing-dimensions-scd-type-1-vs-type-2/1643 "2015-06-17T00:00:00Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![slechtaj](https://avatars.discourse-cdn.com/v4/letter/s/59ef9b/32.png) [@slechtaj](https://forum.cloverdx.com/u/slechtaj)\
**Post date:** [June 17, 2015, 12:00am UTC](https://forum.cloverdx.com/t/data-warehouse-slowly-changing-dimensions-scd-type-1-vs-type-2/1643/1 "2015-06-17T00:00:00Z")

</div>

Data warehouse (DW) structure may differ depending on what Slowly Changing Dimension (SCD) model we choose. Among all SCD approaches there are two that are the most frequent: so called _ **SCD type 1** _ and **_SCD type 2_**. I will discuss how to implement these two SCD types from ETL perspective in CloverETL.

\*\ ***SCD Types**  
The difference between type 1 and type 2 is usually only in history tracking. While in the first type (SCD type 1) DW does not preserve historical states of dimensional data at all, in the second one DW holds the entire history of dimensional data (records are enriched by additional flags, such as date ranges that describes the period when the record is valid for).

For those who are not familiar with SCD types, I recommend reading the [Wikipedia article](https://en.wikipedia.org/wiki/Slowly%5Fchanging%5Fdimension) at first.

\*\ ***Data Warehouse Structure**  
In this example, there are the following tables:

- fact\_item (Fact table with quantitative sales data: Units Sold, Units Paid, Units Shipped)

- dim\_date (Date dimension – Type 0 SCD type used)

- dim\_item (Product dimension that were sold)

- dim\_store (Dimension of places where aforementioned products were sold)

- dim\_customer (Dimension of customers that bought above products)

Fact table that holds information on how many items were sold, paid and shipped is connected with dimensional tables using so called surrogate keys – these are PRIMARY KEYS of dimensional entries.

Depending on what SCD type is used dim tables may contain other fields. This is the case of SCD type 2 and Item, Store and Customer dimensions, which besides the regular data (surrogate keys and dimensional values) contains also two other columns (_start\_date_ and _end\_date_). These two fields describe the period when a particular entry was used.

\*\ ***ETL Process**  
Regular ETL process may usually be divided into three steps:

- Extraction

- Transformation

- Loading

Loading is the last phase that is subject of this example. The previous two phases are not described in this example. I have already prepared source data – the files are located in _data-in_ folder in both projects.

[SCD2-DEMO.zip](https://cdn.cloverdx.com/forum/16494%5Fd2a6f6614879eab030e7a8c051c876b5/SCD2-DEMO.zip)

- [SCD2-DEMO.zip](https://cdn.cloverdx.com/forum/16494%5Fd2a6f6614879eab030e7a8c051c876b5/SCD2-DEMO.zip)
- [SCD1-DEMO.zip](https://cdn.cloverdx.com/forum/16494%5F42ccdb4043aadfa637dfad5a2d11824c/SCD1-DEMO.zip)
- [01-etl-general.png](https://cdn.cloverdx.com/forum/16494%5Fb071226b954d9ed5ef449c2376a02841/01-etl-general.png)
- [02-dim-update.png](https://cdn.cloverdx.com/forum/16494%5F4ed2570c559b6a4742a7f2e555fb618e/02-dim-update.png)
- [03-scd1.png](https://cdn.cloverdx.com/forum/16494%5F647be96449d5a67b174e981227f1d7c8/03-scd1.png)
- [04-scd2.png](https://cdn.cloverdx.com/forum/16494%5F67479deab481af20d45821ec2fd45e28/04-scd2.png)
- [05-fact.png](https://cdn.cloverdx.com/forum/16494%5F553682134d0a026b4241ff26c3d25046/05-fact.png)