SCD Type 2 Update - CRTE_TSMP and UPDT_TSMP

Data Loaded as below (CMPNY_ID & EFF_DT are PK)

CMPNY_ID EFF_DT CMPNY_NM STAT_C UPDT_TSMP CRTE_TSMP
2 2008-11-07 ABC D 2010-08-26-00.17.34.189507 2010-06-30-01.32.08.000000
34 2009-11-15 PQR D 2010-08-13-07.05.54.563109 2010-07-25-01.55.17.000000
993 2010-02-28 MNO A 2010-08-12-09.03.51.000000 2010-08-12-09.03.51.000000
993 2010-08-25 MNO1 A 2010-08-25-02.02.27.000000 2010-08-25-02.02.27.000000
2664 2009-10-07 XYZ A 2010-08-12-09.03.51.000000 2010-08-12-09.03.51.000000
2664 2010-08-25 XYZ1 A 2010-08-25-02.02.27.000000 2010-08-25-02.02.27.000000

Current Rules: following SCD type 2
RUn Date: 2010-08-25
Rule1: When first time record will come , will have EFF_DT same as value from soruce.
and CRTE_TSMP and UPDT_TSMP will be current job run time stamp.
Rule2: When CMPNY_NM changes(Updated record) , new record will be inserted with EFF_DT as 2010-08-25 and UPDT_TSMP & CRTE_TSMP will be value of current job run time stamp.
Rule3: For Delete Case, existing record STAT_C will be updated as 'D' and only UPDT_TSMP will update as Current time stamp. CRTE_TSMP will remain same as existing value.


My Query:
1) When update record will come, a new record should be inserted by updating only updt_TSMP as Current time stamp and CRTE_TSMP remain same as existing records value?
means :

CMPNY_ID EFF_DT CMPNY_NM STAT_C UPDT_TSMP CRTE_TSMP
2664 2009-10-07 XYZ A 2010-08-12-09.03.51.000000 2010-08-12-09.03.51.000000
2664 2010-08-25 XYZ1 A 2010-08-25-02.02.27.000000 2010-08-12-09.03.51.000000

OR above process is correct ?

Kindly adivce


Thanks in advance.
Sachin H

This is not the way a type 2 dimension is implemented. The PK is a single unique surrogate key value, not a compound key.

As for how to update the dates, that depends on what the dates are supposed to represent. For example, if UPDT_TMSP is the timestamp when the row is updated, then whenever you update the row, you set the value...

Thanks for Reply.

In My case CUST_ID and EFF_DT , both are part of primary key(Compund Key).
Requerst for advice on
1) CRTE_TSMP and UPDT_TSMP values when record comes first time to warehouse.
2) CRTE_TSMP and UPDT_TSMP values when udpated record comes to warehouse

Thanks in advance

As ngalemmo said, the primary key of your dimension should be a surrogate key.

I'm assuming Cust_ID and EFF_DT are the primary key from the source system?

As for the date question:

CRTE_TSMP : Contains the timestamp when the dimension record is created by the ETL process (either new or type-2 change).
UPDT_TSMP : Contains the timestamp when the dimension record is updated by the ETL process (e.g. type 1 change)

Thanks for extened information
In this case. We are using Type2. Concern is about only CRTE_TSMP and UPDT_TSMP
Current scenario.
1) When first time record comes to warehouse, CRTE_TSMP and UPDT_TSMP will have same value.
2) When Update record comes to warehouse, CRTE_TSMP and UPDT_TSMP will have same value.
(No difference between CRTE_TSMP adn UPDT_TSMP value in updated record)
Ex:
CMPNY_ID EFF_DT |CMPNY_NM |STAT_C |UPDT_TSMP| CRTE_TSMP
993|2010-02-28| MNO |A |2010-08-12-09.03.51.000000| 2010-08-12-09.03.51.000000
993|2010-08-25|MNO1| A |2010-08-25-02.02.27.000000| 2010-08-25-02.02.27.000000

My point of view is
When record comes as updated record to warehouse. Only UPDT_TSMP need to be changed as Job run time stamp but CRTE_TSMP should remain same as old value(when record come very first time to warehouse)
Ex:
CMPNY_ID| EFF_DT| CMPNY_NM |STAT_C |UPDT_TSMP| CRTE_TSMP
993 |2010-02-28 |MNO |A |2010-08-12-09.03.51.000000 |2010-08-12-09.03.51.000000
993 |2010-08-25 |MNO1 |A |2010-08-25-02.02.27.000000 |2010-08-12-09.03.51.000000
993 |2010-08-30 |MNO2 |A |2010-08-30-12.12.10.000000 |2010-08-12-09.03.51.000000

According to me
CRTE_TS represents time stamp when record is created in warehosue(functionally, not db perspective).
UPDT_TS represents time stamp when update record entered in warehouse.

kindly advice me, my openion is correct or wrong.

Thansk in advance
Sachin

I guess you could use the create timestamp that way, but it doesn't give you much information.

But the point LAndrews and myself are trying to make is, what you propose is not how you implement a type 2 dimension. You do not implement dimensions with compound primary keys. Also, all you have is an effective date... how are you supposed to efficiently join to the dimension? Are you carrying the both columns as FKs in the fact? And if you want the current dimension row, how do you find it? With a correlated sub-query in the WHERE clause?