ADF session 121 and 122 notes.

SCD type 3 implementation notes

scd3 --> maintains recent n events of history of specific attributes. 

output incremental load job.

Day 1:

day1:

delta3.txt

id,name,sal,company

101,aa,50000,ibm

102,bb,60000,infy

103,cc,70000,wipro

for scd type 3 job input is output of incremental load job (delta3.txt). 

target table :  Azure Sql

data base : mydb

create table scd3(id int, name varchar(100),

p2sal int, p1sal int , sal int , company varchar(100));

insert into scd3(id,name,sal,company) values

(101, 'aa', 20000, ibm),

(102, 'bb', 30000, infy);

After scd3 job:

 target table should contain following data:

scd3

-------------------------------------------------

id name p2sal p1sal sal company

--------------------------------------------------

101 aa null 20000 50000 ibm

102 bb null 30000 60000 infy

103 cc null null 70000 wipro

-----------------------------------------------------------

day2:

delta3.txt

id,name,sal,company

101,aa,80000,ibm

102,bb,90000,infy

103,cc,85000,wipro

104,dd,90000,ibm

after scd3 job: 

scd3

-------------------------------------------------

id name p2sal p1sal sal company

--------------------------------------------------

101 aa 20000 50000 80000 ibm

102 bb 30000 60000 90000  infy

103 cc null 70000 85000  wipro

104 dd null null 90000 ibm

-----------------------------------------------------------

day3:

delta3.txt

id,name,sal,company

101,aa,90000,ibm

102,bb,95000,infy

after scd3 job:

scd3

-------------------------------------------------

id name p2sal p1sal sal company

--------------------------------------------------

101 aa 50000 80000 90000  ibm

102 bb 60000 90000  95000  infy

103 cc null 70000 85000  wipro

104 dd null null 90000 ibm

-----------------------------------------------------------

SCD3 Implementation:

---------------------

1. Source : 

output stream : delta

dataset : ds_delta

projection :

id as integer

sal sal integer

2. Source:

output stream: scd3

dataset : ds_scd3

3. Lookup : 

output stream : lookup1

prime stream (left) : delta

lookup stream (right) : scd3

Lookup condition:

delta@id == scd3@id

4. select :

output stream : select1

Columns:

delta@id as id

delta@name as name

delta@sal as sal1 (rename)

delta@company as company 

scd2@id as id (drop)

scd2@name as name (drop)

scd2@sal as sal2 (rename)

p2sal as p2sal

p1sal as p1sal

scd2@company as company (drop)

5. derived column:

output streamm : derived1

Add columns:

column expression

---------------------------

p2sal p1sal

-----------------------

6. derived column:

output streamm : derived2

Add columns:

column expression

---------------------------

p1sal sal2

-----------------------

7. select :

output stream : select2

Columns:

id as id

name as name

sal1 as sal (rename)

company as company

p2sal as p2sal

p1sal as p1sal

sal2 as sal2 (drop)

8. Alter row:

output stream : alterrow1

Alterrow condition:

Action : upsert if

condition : 1==1

9. sink:

data set : ds_scd3

sink settings:

action : upsert

list of columns: id

------------------------------------------------------