Greetings @AcrobaticMonkey , I put on my researcher hat and dug into our internal docs. Here is what I found:
Short answer: this isnโt configurable today. The connectorโs SCD Type 2 behavior โclosesโ a record by setting __END_AT and does not emit a new tombstone row with isDeleted = true. The is_deleted column is always false by design.
What the connector does today
-
With SCD Type 2 enabled, soft deletes are treated like updates: the last active version is marked inactive by populating __END_AT; no new โdeleteโ row is inserted.
-
The connectorโs is_deleted column is intentionally not used; it always remains false and should be ignored. Use __END_AT and __CURRENT to reason about deletions.
-
Hard deletes arenโt automatically captured; you need a full refresh to reflect them.
Is there a configuration switch to insert a new row with isDeleted = true?
No. Thereโs no connector setting that produces a new tombstone row on delete. The recommended pattern is to base downstream logic on __END_AT and __CURRENT in the SCD Type 2 target table.
Practical ways to model deletes as a new row
If you need an explicit โdeleteโ record for downstream consumers, add it in a transformation step:
-
Create a small downstream table or view that emits a tombstone row when a key transitions from active to inactive (that is, the key has no current version). Use the SCD 2 semantics (__START_AT, __END_AT, __CURRENT) to align the delete rowโs timing with the closed version.
-
Use Lakeflow Pipelines CDC to build a derived target that stores data as SCD Type 2 and applies explicit deletes when your source (or derived logic) indicates deletion. The CDC SQL supports APPLY AS DELETE WHEN โฆ STORED AS SCD TYPE 2, which generates proper history with __START_AT and __END_AT. Youโll need a deletion condition to drive this (for Lakeflow Connect, this is typically derived from the absence of a current version).
Example skeleton using Pipelines SQL (adjust to your source and sequencing columns):
-- Target with explicit delete records stored as SCD Type 2
CREATE OR REFRESH STREAMING TABLE sfdc_accounts_scd2_with_deletes;
CREATE FLOW sfdc_accounts_flow AS
AUTO CDC INTO sfdc_accounts_scd2_with_deletes
FROM stream(main.sf_accounts_changes)
KEYS (Id)
-- Provide a condition that identifies delete events in your ingest (derived or source-provided)
APPLY AS DELETE WHEN delete_condition
SEQUENCE BY sequence_col
COLUMNS * EXCEPT (sequence_col)
STORED AS SCD TYPE 2;
Recommendations
-
If auditability is the goal, keep the connectorโs SCD 2 table as the source of truth and expose a downstream โtombstoneโ view or table that flags or materializes deletes for consumers.
-
If you must persist physical delete rows, implement a downstream CDC flow or batch transform to create and maintain those tombstones alongside the connector output. Note that the connector enables change data feed on target tables, which can simplify CDC-driven transforms.
I hope this provides you with some guidance.
Regars, Lou.
