Initial date of effective date column for SCD 2 implementation

ozisamur Sun Nov 16, 2014 4:55 am

Hi guys,

Assume that I have a CUSTOMER table. And also in the OLTP system I have createdDate field in the customer table which specifies the creation of the customer.

In the first initial loading to the CUSTOMER table in DW, which value should I set to the EFFECTIVE_DATE column for the CUSTOMER dimension?
. CreateDate or SystemTimestamp?

ngalemmo Sun Nov 16, 2014 9:02 pm

Both. If you have a source that provides a business timestamp as to when a change occurred, you should capture it. You should also capture the system timestamp when the row was created/updated in the DW itself. This gives you the ability to view data using two different points of view… when the business knew and when it became known in the DW.

Dr. Warehouse Wed Nov 19, 2014 6:34 pm

Aside capturing both, I would set the creation timestamp as the first effective date.

Using the load timestamp could render you unable to find an effective SCD row because the first instance of your customer became effective after the first occurrence in the fact table (if using source system timestamps to resolve the surrogate key during ETL).

Sponsored content

Initial date of effective date column for SCD 2 implementation

Initial date of effective date column for SCD 2 implementation

Re: Initial date of effective date column for SCD 2 implementation

Re: Initial date of effective date column for SCD 2 implementation

Re: Initial date of effective date column for SCD 2 implementation