Koen Verbeeck (1/13/2015)
ps_vbdev (1/13/2015)
Koen Verbeeck (1/13/2015)
ps_vbdev (1/13/2015)
Ok so your doing the comparison in your staging area. but you would still have to do a full extract from source db (bulk Insert?) to get the source tables in your staging area up todate, yes?
The first time yes
when you say the first time is this every time the etl runs to update the DW weather it be nightly, hourly or 5 minutes etc?
I mean the actual very first time - the initial load - when your staging area and DW are empty.
where I have worked on DWs before we did not have the staging tables backed up routinely as the functionality to show what had changed was based on a created/modified datetime field in the source data, so we just cleared down staging each run and then extracted into staging since last extract datetime.
The method proposed above using CDC from source into staging requires that the staging area is kept under backup control so that you can restore into staging as well as the DW. Our staging tables etc were in source control so could be redeployed.
Just a thought.