A soft delete may not move your copy's modified time
Freshness gates keyed on your copy's modified time skip soft deletes.
SymptomRecords deleted in the CRM keep appearing in answers, while in your warehouse copy only the connector's sync column shows they were touched.
Incremental pipelines pick what to reprocess by the CRM's last-modified time. It's the standard freshness gate, and the right clock for ordinary edits.
A soft delete is the exception. In the CRM, a delete may restamp the record itself. But a connector learns about deletes through a separate deleted-records call, so the copy of the modified time in your warehouse never moves; only the connector's sync column does. Fivetran's Salesforce documentation says it captures soft-deleted records through the sObject Get Deleted endpoint.
A gate keyed on that copied stamp then sees nothing new. The delete is skipped, and the record keeps feeding answers as if it were live.
A job that reads the CRM directly with a standard query never sees deleted rows at all; only a query that includes deleted rows returns them.
The answer is two clocks with two jobs.
- The sync stamp decides what gets processed.
- The CRM stamp stays the displayed time, because it says when the business record changed.
In a synthetic test set with 50 soft-deleted records in a warehouse copy, a reprocess gate on the copied CRM modified time left all 50 still served; a gate on the sync stamp left none. The set shows the mechanism, not how common it is.
List records in the CRM's recycle bin from the last 30 days. Compare each record's modified time in your warehouse copy with the connector's sync time. Check whether only the sync time moved. Check whether your pipeline gates on the copied modified time. If both are true, those records never reached you as gone.
Passes ifEvery record in the last 30 days of the recycle bin reached your pipeline as gone. Otherwise, your freshness gate keys on the sync time.
Access needed:crm-readlocal-config
- 01Gate reprocessing on the connector's sync stamp, not the CRM's last-modified time.
- 02Keep the CRM's last-modified time as the "changed at" time you show people and agents.
- 03Read the deleted flag on every record the sync stamp surfaces. Retire the record downstream when the flag is true.
- 04Use the query mode that includes deleted records when reading the CRM directly for deletes.
- 05Apply the same two-clock gate at every layer that keeps its own copy, not only the first load.