Hope everyone knows SCD Type-2. In that,
Insert Record if it is coming for first time.
Update Record the record it is already exists in Target.
Here the CDC comes into picture. Based on the primary key, we will check whether the record is present or not in the target. If it is, then update. So why don't we have a scenario, if that the record is coming without any changes in the data. Then also we are simply updating the data with again the same data that the table is having.
So to avoid this unncecessary updates which is not required. We will capture the changed records only. That is called Change Data Capture.
How to Implement ?
Now, we will maintain one more column in the target to. So while loading the data into the target, we have to calculate this. So MD5 Informatica Function will do this. For more on MD5, refer help.
MD5 will give the checksum value, 32 digit hexa-decimal unique value it will generate.
MD5(col1||col2||col3) -- This value we will insert into the target, if the record is coming for first time also. When the record is coming for next time. We will check the MD5 value of incoming record with the MD5 value of the target. So if there is no change then there is not change in the data. If it doesn't matches, then there is some changes happened in the record.
So like this, instead of blindly updating the record even though there are no changes happened for the record. CDC will do it in an efficient way. If you don't use CDC also nothing will happen, the results will not effect. But unnecessarily, you are reading and writing the same records which effects the performance.
