Change Capture Stage(CCD)

CDC -- Change Capture 


Sample Source Data You can take like below 

Before_Data 
e_id,e_name,e_Add 
11,kim,bris 
22,jim,mel 
33,pim,syd 
44,lim,canb 
55,pom,pth 

After_Data 
e_id,e_name,e_add 
11,kim,syd 
22,jim,canb 
33,pim,syd 
44,lim,canb 
55,pom,pth 

Read the data in two different files. And 

Go to Change Capture Stage 

Select Key_id as e_id 

and click on change values. 

Select Values in Available Properties to add 

And select e_add as value 

Click Ok 

Go to Output --- Drag and Drop All the required columns required. 

You can drag and drop every thing here. And Click Ok 


Give file name in the Target File 

Compile and Run the Job 

You will get the output as 



e_id e_name e_add change_code 

11 kim syd 3 
22 jim canb 3 


3 means edited data 

Implementation:

1. Two input datasets are required for change data caputure 
stage.
One is Old dataset
Second is New or updated dataset

2. Give in the 2 inputs to the change capture stage and the 
target as a dataset.

3. Let the incoming data be sorted based on a key column(s) 
for performance purpose in the change Caputure stage.

4. Upon executing the job, the data when viewed from the 
dataset shows a new column added apart from the output 
data. A change code column would be generated in the change 
capture stage having values as 0, 1, 2, 3 which depicts the 
changes on comparing the 2 input datasets such as copy(0), 
Insert(1), Delete(2), Edit(3).

5. See what kind of data you need in the output target like 
copy, insert, delete, edit.

6. To apply SCD Type 2 we require Start date and End date 
columns. 

7. The Change Capture Stage output is given to a 
Transformer Stage, where 2 new columns are generated with 
Effective Start Date and End Date.

8. If you need all Inserted or new data to be passed in to 
a particular dataset then you need to specify an 
appropriate condition in the Transformer Stage to the 
outgoing link. Ex. Drop Output For insert=true 

9. In the similar way other data can also be captured or a 
Filter can also be used after the Transformer Stage to 
filter the data into the targets based on the requirement.