DWH SCD2 update

[dhruvakumar]

Dears,

As part of SCDTYPE2 implementation, we requried to update and delete the records.

For instance given attachement, :

Need to delete highlight one , please can you help on this request.

Thanks 

[PeterClemmensen]

I don't see any attachment? 

[dhruvakumar]

sorry jst attached. Thanks

[dhruvakumar]

Dears,

As part of SCDTYPE2 implementation, we requried to update and delete the records.

For instance given attachement, :

id	name	age	sex	O_obs	C_obs	DFINOBS
1	John	10	M	01Feb2019	01Jan2019	31Jan2019
1	John	10	M	01Feb2019	01Feb2019	31Dec9999
2	Mary	10	F	01Feb2018	01Jan2018	31Jan2018
2	Mary	10	F	01Feb2018	01Feb2018	31Dec9999
2	Mary	10	F	01Mar2018	01Feb2018	28Feb2018
2	Mary	10	F	01Mar2018	01Mar2018	31Dec9999

Need to delete highlight one , please can you help on this request.

Thanks 

[PeterClemmensen]

What is logic behind highlighting this obs? And what do you mean by 'highlight'? Is this for reporting purposes? 

Please be more specific 

[dhruvakumar]

The  highlighting  obs should be deleted.

1-Feb-18

1-Feb-18

31-Dec-99

because we the same ID updated in 1-Mar -18.

id	name	age	sex	O_obs	C_obs	DFINOBS
1	John	10	M	1-Feb-19	1-Jan-19	31-Jan-19
1	John	10	M	1-Feb-19	1-Feb-19	31-Dec-99
2	Mary	10	F	1-Feb-18	1-Jan-18	31-Jan-18
2	Mary	10	F	1-Feb-18	1-Feb-18	31-Dec-99
2	Mary	10	F	1-Mar-18	1-Feb-18	28-Feb-18
2	Mary	10	F	1-Mar-18	1-Mar-18	31-Dec-99

[Kurt_Bremser]

---

@dhruvakumar wrote:

The  highlighting  obs should be deleted.

1-Feb-18

1-Feb-18

31-Dec-99

because we the same ID updated in 1-Mar -18.

id	name	age	sex	O_obs	C_obs	DFINOBS
1	John	10	M	1-Feb-19	1-Jan-19	31-Jan-19
1	John	10	M	1-Feb-19	1-Feb-19	31-Dec-99
2	Mary	10	F	1-Feb-18	1-Jan-18	31-Jan-18
2	Mary	10	F	1-Feb-18	1-Feb-18	31-Dec-99
2	Mary	10	F	1-Mar-18	1-Feb-18	28-Feb-18
2	Mary	10	F	1-Mar-18	1-Mar-18	31-Dec-99

---

And what is the logical rule for that?

[Patrick]

@dhruvakumar 

With SCD2 you NEVER delete a record but you expire it.

Assuming ID is your generated key and Name is your business key: I can't even see a reason in the sample data you've posted why there should be two records. All the other columns which potentially could be under change tracking are the same. With SCD2 there should be only a single row in such a case.

With SCD2 you need to have either a VERSION column and for every single row with a change to columns under change tracking for the same business key you add a new version - or you have start and end date columns and if you add a new row (an update) you also change the end date of the previous version to the start date of the new version (or current start date -1 day if you wish).

So: You first need to fix your table and clearly define the cases for your load logic (new business key, change for existing business key, expiration of current record for a business key, reopening an previously expired record for a business key) before even asking HOW to technically implement things.