Soft Delete With Tracking Columns
By default a row deleted at the source is deleted in Snowflake, and your tables hold your columns and nothing else. Turn this option on for a pipeline and deleted rows stay in the destination table, marked by two columns Elglide adds.
The two columns
| Column | Type | Meaning |
|---|---|---|
_ELGLIDE_DELETED | BOOLEAN | TRUE when Elglide saw the source row deleted. |
_ELGLIDE_SYNCED | TIMESTAMP_NTZ | UTC time this row was last inserted, updated, or flagged as deleted. |
Every table in the pipeline gets both columns. To query current data, filter on the flag:
SELECT * FROM SALES.ORDERS WHERE _ELGLIDE_DELETED = FALSE;
A row that existed before the columns were added reads FALSE with an empty
_ELGLIDE_SYNCED until a sync next touches it.
One row per key
The table records each row's last state, not its history. A delete flags the existing row. If the
same key comes back, the same row goes back to FALSE. A later delete flags it again with
the new time. A row deleted, re-inserted, and deleted again is one row, flagged, with the time of the
last delete.
Where deletes can't be flagged
Whether a delete can be seen depends on how the table syncs, and the columns are there either way.
| How the table syncs | _ELGLIDE_DELETED |
|---|---|
| Change Tracking, CDC, logical replication, binlog, LogMiner | Set when the source row is deleted. |
| RowHash | Set when the source row is deleted. |
| Watermark | Always FALSE on this table: watermark sync is used only where the source has no deletes. |
| Full, including keyless tables, keyless views, and custom SQL | Always FALSE on this table: it reloads in full each sync, so deleted rows are not kept. |
What clears flagged rows
Any full reload rebuilds the table from the source, so flagged rows are gone afterwards, and Elglide can't restore them. A full reload is not always something you start yourself:
- A manual Full Sync, which warns you first
- A task whose execution mode is Full
- A column added at the source
- A lost replication slot, a stale change tracking or CDC position, or a fallback from a change mechanism to RowHash
- A missing RowHash snapshot, or an empty or new destination table
- An audit failure that escalates to a full reload, or a Watermark table demoted to RowHash
The run log says how many flagged rows a full reload removed.
Row counts and audits
Audits compare live rows only: flagged rows are rows the source no longer has, so they are left out of the destination side and never reported as extra. A deleted row still counts as a deleted row in your sync figures, the same as a hard delete.
Set it before the first run
The option is off for an account until an administrator turns on Show the soft delete option on pipelines under Organization. That only decides whether pipeline pages show the option: turning it off later changes nothing on a pipeline that already uses soft delete.
The option is on the pipeline page, under the schema map. It can be changed freely until any task in the pipeline is triggered, and then it locks, whichever value it has, because the first run is what creates the destination tables. To use a different setting after that:
- Create a new pipeline with another target connection, or
- Delete all tasks in this pipeline and add them again.
Deleting a task does not drop its Snowflake table, so the second route reuses the old tables. Turning
the option on adds the two columns to them. Turning it off removes _ELGLIDE_DELETED and
_ELGLIDE_SYNCED on the next run, and deletes go back to removing the row. These are the
only columns Elglide ever drops, because it created them; your own columns are never dropped.