CDC

Database
Fan

CDC

Hi all,

What is ment by CDC(Change Data Capture) ?? How it is used ??

Thanks

Raj

5 REPLIES
Enthusiast

Re: CDC

CDC is a broad term that applies to any of several methods of detecting only the data that changed for loading to avoid row collisions, maintain history and make ELT/ETL more efficient

Cheers

Enthusiast

Re: CDC

CDC is change data capture. It is essential in DWH/BI project to see changed data.Different techniques are available like change in timestamps, versioning of rows, indicators, or both timestamps and indicators, messages and queues,logs, events.

Imagine a case when  when you pull or push huge source data that have been processed before into downstream edw or applications not neccesary by business.It simply eats up time and effort(example). So keep track only data that is changed and proceed further.

There are many CDC tools available in  the market.

Cheers,

Enthusiast

Re: CDC

create volatile table delta 
as(
sel
case
when a.custnum is null then 'D'
when b.custnum is null then 'I'
when (a.custname=b.custname and a.dob=b.dob and
a.gender=b.gender and a.mstatus=b.mstatus and a.addr=b.addr and a.contact=b.contact )
then 'N'
else 'U'  end as indic,
coalesce (a.custnum,b.custnum) custnum,
coalesce (a.custname,b.custname) custname,
coalesce (a.dob,b.dob) dob,
coalesce (a.gender,b.gender) gender,
coalesce (a.mstatus,b.mstatus) mstatus,
coalesce (a.addr,b.addr) addr,
coalesce (a.contact,b.contact) contact
from
new_cust a
full outer join
old_cust b
on 
a.custnum=b.custnum
) with data
on commit preserve rows;

Please see the refernce code above where new_cust is new data, old_cust is yesterday data full outer joined based on cust_num and deriving the chane indicator (i - new record to insert, D - logical delete , U - update the changed details, N - no change)

Fan

Re: CDC

Hi,

Thanks a lot to all ..

Raj

Teradata Employee

Re: CDC

Very nice piece of code shared by sgarlapa.