Operation type DELETE means the record is supposed to disappear. If you were using SCD Type 1, the record would be removed from the silver table. When using SCD Type 2, AUTO CDC only updates the lifecycle metadata columns to make the record inactive; it does nothing to any other business columns.

For your use case, the only option is to convert the DELETE operation into an UPDATE operation before it reaches the AUTO CDC logic. If you have a view between your bronze and silver layers, you can use that view to add custom logic that checks:

• if the business key column is not null, and
• the operation type is DELETE,

then change the operation type to UPDATE. You might also want to add another flag that records that the original operation type was DELETE.