We are extracting data from Legacy to SQL server and in future will move data to SAP.
Initially we are loading Master data from Legacy in this process we are implementing SCD2.
When I am using CUST_ID, CUST_DRI_ID as PK as suggested by our Business analysts, But not declared as PK in database.
I am using below logic to implement SCD2
For this we have created ROW_ID, FLAG, STARTDATE, ENDDATE columns in Target Tables.
Source—QT- TABLECOMPARISION- HP-KEY GEN
TABLE COMPARISON- CUST_ID,CUST_DRI_ID (Input primary key columns) and rest of columns in COMAPRE COLUMNS , I am excluded Date,datetime in compare columns
HP- STARTDATE( system date),ENDATE(12.31.999) and FLAY-Y,N
In compare columns I am taking all columns.
1)When I execute the job for first time it is loading all records i.e. 100 records.
When I run job for second time without changing any source data, records are getting doubled.
100 records-with today date( Aged Records), 100 records with 12.31.999( Valid records).
2) Suppose if I update THRJYJ to ABCD (CUST_NAME) column all the records are being Aged( Today date) where CUST_DRI_ID = 4901
3) I doubt my BA has provided wrong keys, But as per my BA all the above records are valid.
4) when I declare PK job is failing due to PK violation.
Can anyone let me know how to solve this problem?