General Question on CDC

[wittyme]

I want to know how are deletes performed in CDC on target database?

The reason to ask is in my current project we only consider change code 1 and 3 (Insert and Update).

In my previous project in some jobs we do take delete code and perform delete on database. But that is not a real SCD right? So how are deletes exactly tackled?

[pandeesh]

Your question is not clear.
The change codes are actually for telling the difference between the before and after datasets.
Accordingly, we need to maintain our table.

[premupdate]

@Witty,

You are right..Delete is not a part of SCD.But an unwanted dimensional value need not be necessasary for an organization.For ex: a customer from a bank gets detached.Moreover,the bank doesnt need any info about him.
Now the delete operation helps.

[pandeesh]

But as far as i aware, many sites will not delte the old data. rather they will mark it as Expired which is one of the feature in SCD.

[chulett]

I'm still a little unclear on what your 'general question' is attempting to find out. CDC does not 'perform' deletes, it detects them just as it detects new records and changes. What you do with them is completely up to you and the nature of the associated target.

For a none 'warehouse' type target, it may be perfectly acceptable to physically delete target records. Sometimes that may also include a need to check to see if has been 'used' first or not.

However, I've never been anywhere that physically deleted records from a dimensional table, Type2 or otherwise. As noted, they are logically deleted by marking them as 'non-current' and their effective date range ended as of the delete date. Some other status may be involved as well to note that it was a delete that got it into that state.

[ray.wurlod]

Are we talking about CDC here (capturing of changed records from databases) or change detection (that is, Change Capture, Difference or Compare stage types)?