All Forums Database
Raj_k 4 posts Joined 01/14
18 Mar 2014
CDC

Hi all,
What is ment by CDC(Change Data Capture) ?? How it is used ??
 
Thanks
Raj

Wait for your time .. You will reach to success ..
VandeBergB 182 posts Joined 09/06
18 Mar 2014

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

Some drink from the fountain of knowledge, others just gargle.

Raja_KT 1246 posts Joined 07/09
18 Mar 2014

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,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

sgarlapa 88 posts Joined 03/13
18 Mar 2014
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)
 
 

Raj_k 4 posts Joined 01/14
03 Apr 2014

Hi,
 
Thanks a lot to all ..
Raj

Wait for your time .. You will reach to success ..

Adeel Chaudhry 773 posts Joined 04/08
09 Apr 2014

Very nice piece of code shared by sgarlapa.

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.