I’d need more details about your data source and the pipeline you’ve planned, but here’s a scenario based on a few best practices to help frame the discussion. When the source doesn’t send a delete flag, you still need a reliable way to reflect deletions in your Delta tables. Below are practical patterns that work well on Databricks.

WHAT TO PRIORITIZE

Real CDC (best case)
Propagate op = 'D' from the source and apply it downstream.

-- DLT example
APPLY CHANGES INTO LIVE.silver_customers
FROM STREAM(LIVE.bronze_cdc)
KEYS (id) SEQUENCE BY op_ts
APPLY AS DELETE WHEN op IN ('D');

Full snapshot (no CDC)
Use MERGE with NOT MATCHED BY SOURCE. Prefer soft delete.

MERGE INTO silver.customers t
USING staging.curr_snapshot s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET t.is_deleted = true, t.deleted_at = current_timestamp();

Incremental without a delete flag (classic case)
Generate synthetic tombstones via an anti-join between the current set of keys and the target’s active keys.

-- Current keys from the latest batch
CREATE OR REPLACE TEMP VIEW curr_keys AS
SELECT DISTINCT id FROM bronze_latest_customers;

-- Active keys in the target
CREATE OR REPLACE TEMP VIEW tgt_keys AS
SELECT id FROM silver.customers WHERE is_deleted = false;

-- Keys that disappeared => tombstones
CREATE OR REPLACE TEMP VIEW tombstones AS
SELECT t.id, true AS is_deleted, current_timestamp() AS deleted_at
FROM tgt_keys t LEFT ANTI JOIN curr_keys c ON t.id = c.id;

-- Apply real changes + tombstones
MERGE INTO silver.customers tgt
USING (
SELECT * FROM staging.upserts -- real inserts/updates
UNION ALL
SELECT id, /* nulls for other cols */, true, current_timestamp() FROM tombstones
) chg ON tgt.id = chg.id
WHEN MATCHED AND chg.is_deleted THEN
UPDATE SET tgt.is_deleted = true, tgt.deleted_at = chg.deleted_at
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED AND COALESCE(chg.is_deleted,false)=false THEN INSERT *;

Note: In DLT, you can materialize tombstones and use APPLY AS DELETE WHEN is_deleted to keep everything within DLT.

DELTA BEST PRACTICES

• Default to soft deletes in Silver/Gold (is_deleted, deleted_at); do physical deletes only when required.
• Add a grace period/watermark to avoid false deletes from late-arriving data.
• Data quality: expectations for unique id, caps on delete percentage per batch; log metrics.
• Change Data Feed (CDF) for downstream propagation/auditing:
ALTER TABLE silver.customers SET TBLPROPERTIES (delta.enableChangeDataFeed = true);
• Performance: OPTIMIZE ... ZORDER BY (id) and Deletion Vectors (if enabled):
ALTER TABLE silver.customers SET TBLPROPERTIES (delta.enableDeletionVectors = true);
• Observability & governance: record batch counts; use DLT event_log / system tables.
• Idempotency: every batch should be safely re-runnable.

TL;DR

• If you have CDC, use APPLY AS DELETE.
• If the source delivers a full snapshot, use MERGE with NOT MATCHED BY SOURCE (soft delete).
• If it’s incremental without native deletes, create tombstones via anti-join and apply via MERGE/DLT.

I hope this was helpful.