The Salesforce CDC Retention Challenge

Salesforce Change Data Capture (CDC) via the Pub/Sub API is one of the most effective ways to stream real-time data to your data warehouse (Snowflake, BigQuery, Databricks, etc.). However, Salesforce retains CDC event messages for a maximum of 72 hours (3 days). If your downstream pipeline goes down or stalls longer than this retention window, your stored ReplayId becomes invalid.

Falling back to a standard Bulk API query like WHERE SystemModstamp >= last_timestamp introduces two critical integration bugs:

  • Race Conditions: Records modified during the backfill process can be overwritten with stale data or processed out of order.
  • Lost Deletions: Standard queries miss soft-deleted records (in the Recycle Bin) and completely skip hard-deleted records.

Here is the industry-recommended architecture to reliably recover from CDC replay expiration without missing updates or deletes.

The 4-Step Zero-Data-Loss Recovery Pattern

The core problem with stopping CDC, running a backfill, and restarting CDC is that any events generated during the backfill window are lost. Instead, use an overlapping buffer pattern.

Step 1: Restart CDC Immediately Using ReplayPreset.LATEST

Before running your catch-up query, start consuming the CDC stream again using ReplayPreset.LATEST. Instead of writing directly to your primary warehouse table, push these streaming events into a temporary staging queue or buffer (like Amazon SQS, Apache Kafka, or a raw staging table).

Record the exact timestamp when this stream was reconnected: T_stream_start.

Step 2: Catch Up Soft Deletes and Updates via Bulk API queryAll

To capture both updated records and soft-deleted records, use the Bulk API 2.0 with the queryAll operation (equivalent to the SOQL ALL ROWS clause).

SELECT Id, SystemModstamp, IsDeleted, Name, Custom_Field__c 
FROM Account 
WHERE SystemModstamp >= 2023-10-01T00:00:00Z 
  AND SystemModstamp <= 2023-10-04T12:00:00Z
ALL ROWS

Key Rules for the Query:

  • Set the upper boundary to T_stream_start to define an explicit catch-up window.
  • Always query IsDeleted and SystemModstamp.
  • In your warehouse, update your target table. If IsDeleted = true, mark the warehouse record as deleted.

Step 3: Recover Hard-Deleted Records with the getDeleted() API

If your org uses "Hard Delete" (which bypasses the Recycle Bin), ALL ROWS will not return them. Fortunately, Salesforce provides the Replication API (specifically the getDeleted() SOAP/REST endpoint), which maintains a log of all deletions—including hard deletes—for up to 30 days.

GET /services/data/v60.0/sobjects/Account/deleted/?start=2023-10-01T00:00:00Z&end=2023-10-04T12:00:00Z
Host: yourInstance.salesforce.com
Authorization: Bearer YOUR_ACCESS_TOKEN

The response returns an array of deletedRecords containing the id and deletedDate. Apply these deletions to your target warehouse tables.

Step 4: Drain the Buffer and Apply Idempotent Upserts

Now that your warehouse is brought up to T_stream_start, begin processing the CDC events from your staging buffer.

Because CDC events generated during the backfill overlap with your query results, you must ensure your warehouse writes are idempotent. Never blindly overwrite records based on arrival time. Instead, enforce a version check using SystemModstamp or the CDC header commit timestamp (commitTimestamp):

-- Example: Idempotent Merge in Snowflake / Databricks
MERGE INTO target_table AS target
USING staging_cdc_stream AS source
ON target.Id = source.Id
WHEN MATCHED AND source.SystemModstamp >= target.SystemModstamp THEN
  UPDATE SET 
    target.Name = source.Name,
    target.SystemModstamp = source.SystemModstamp,
    target.IsDeleted = source.IsDeleted
WHEN NOT MATCHED THEN
  INSERT (Id, Name, SystemModstamp, IsDeleted)
  VALUES (source.Id, source.Name, source.SystemModstamp, source.IsDeleted);

Summary Best Practices

  • Never stop CDC before backfilling: Always re-subscribe to LATEST to buffer new changes while backfilling past data.
  • Use queryAll / ALL ROWS: Standard queries miss records in the Recycle Bin.
  • Leverage getDeleted() for 30-day coverage: It bridges the gap between CDC's 72-hour limit and Salesforce's 30-day hard-delete log.
  • Rely on SystemModstamp for concurrency: Ensure your target storage resolves conflicts using record timestamps rather than ingestion timestamps.