SCD2 on the basis of months

ke.rstin_ · Post by **ke.rstin_** » Mon Oct 28, 2013 9:23 am

Hi everybody,
I have a little tricky case:

Data can be valid on the basis of a month. So a record can be valid from 01.03.2013 to 31.12.2999 for example.

CASE 1: changes in the same month of date_from
If the record is created in March for example (date_from: 01.03.2013) and changes are made in the SAME month (March), then there should only be an update of the record (like SCD1).

CASE 2: changes in another month than date_from
If the record is created in March for example (date_from: 01.03.2013) and changes are made in April, then
* the old record is updated to date_to 31.03.2013
* a new record is created with date_from 01.04.2013
(like SCD2)

Do you have any ideas to solve this in an "appropriate way"?

Best regards,
Kerstin

ray.wurlod · Post by **ray.wurlod** » Mon Oct 28, 2013 1:19 pm

Split the job into two streams based on the date rule, run the data through SCD stage with properties appropriately set on each stream.

ke.rstin_ · Post by **ke.rstin_** » Tue Oct 29, 2013 3:25 pm

So that I have a lookup to check the date_from and then I have my two streams...a really good idea, I thought about corrective steps after the SCD2 steps, but I think your idea is the proper one. Thanks a lot!