The pattern is documented and you are right not to full refresh pipeline 2, but as written the deletion detection can never fire, and the full refresh is not why.

spark.readStream.table("raw.contact") makes contact append-only, so no key ever leaves it, and the API closes a record only when a key present in the target is "no longer present in the source." That read also fails outright on any update or delete to the source, which an ordinary sync produces, so skipChangeCommits would only hide the failure and the deletes with it.

1. Give the API a batch snapshot instead of a stream. The documented pattern is a @DP.view returning spark.read.table("raw.contact"), then dp.create_streaming_table("contact_scd2") and dp.create_auto_cdc_from_snapshot_flow(target="contact_scd2", source="<that view>", keys=["Id"], stored_as_scd_type=2). (apply_changes_from_snapshot is the old name for the same function.) With no checkpoint on the source, the weekly refresh is just the next snapshot and deletes close the SCD2 rows. Try it in a dev pipeline first, since changing the source a flow reads can require a full refresh.

2. Run pipeline 2 as a task after pipeline 1 in one job. A refresh that fails partway is documented to leave target tables "in an inconsistent state with partial data," and a snapshot read against a half-loaded table end-dates every key it cannot see. Nothing flags that for you either, since the snapshot API does not emit num_deleted_rows. A row-count expectation on the view is cheap insurance, and pipelines.reset.allowed = false on the target stops anyone refreshing the history away.

3. A removed column never reaches your SCD2 table. The connector marks it inactive rather than dropping it, so raw.contact keeps its schema. Type changes are the ones it cannot handle.

Worth questioning the weekly refresh itself, though: soft deletes land on the next sync and deleted columns are handled automatically, so the only cases documented as needing a full refresh are true hard deletes and records purged from the recycle bin before the next sync.