Extracting Salesforce Deleted Records: A Step-by-Step Guide

When working with Salesforce data, it's important to understand how deleted records are handled and how to retrieve them if needed. Salesforce employs a logical deletion mechanism, which offers a safety net for data recovery. Here’s what you need to know:

How Salesforce Handles Deleted Records

When a record is deleted in Salesforce, it isn't immediately and permanently removed. Instead, Salesforce marks the record as "logically deleted" and moves it to the recycle bin. This means the data is still present but hidden from normal data views and reports.

Using The Recycle Bin for Record Recovery

The recycle bin acts as a temporary holding place for deleted records. Records remain recoverable for 15 days, providing a window to restore them if needed. After 15 days, or if the recycle bin is full, Salesforce permanently deletes the records to free up storage space.

Recovering Deleted Salesforce Records

Any normal Salesforce Query will by default automatically filter out items that have been deleted. Using the Matillion Data Productivity Cloud's Salesforce Query component as an example, a standard Salesforce Query component has an implicit filter IsDeleted = false.

To simplify Salesforce data extraction, including deleted records, explore the Matillion Salesforce Connector for seamless integration and automation.

Running Queries with IsDeleted = true

To fetch all records, including those deleted, you'll need to run a second Salesforce Query with an explicit filter IsDeleted = true added.

Here's how it looks in an orchestration pipeline.

Extracting deleted and non-deleted records from Salesforce

Merging Deleted and Non-Deleted Records

After extracting both sets of records, you can unify them into a single table using a transformation pipeline for ease of use downstream.

Here's how it looks in Matillion's designer interface:

Combining deleted and non-deleted records from Salesforce

Logical vs. Physical Deletion in Data Warehousing

It’s important to distinguish between logical and physical deletion. Logical deletion simply marks a record as inactive while retaining the data. In a data warehousing context, this is beneficial for history tracking and understanding past trends.

Physical deletion, on the other hand, completely removes the data from the database, making it unrecoverable and hindering historical analysis. Salesforce employs logical deletion for a limited time before physical deletion.

When it comes to slowly changing dimensions (SCDs) in data warehousing, especially with dimension tables that change over time, the method of deletion affects data currency. Logical deletion keeps historical data intact, showing a complete timeline of changes, and helps understand past states of a dimension. Physical deletion might lead to a loss of historical context, making it difficult to track dimension changes over time. Thus, logical deletion is generally preferred in SCDs to maintain detailed historical data, ensuring accurate reporting and analysis.

Simplifying Salesforce Data Extraction with Matillion

If you're already a Matillion user, you can use the Matillion Exchange to download sample pipelines that demonstrate this technique. 

If you've never tried the Matillion Data Productivity Cloud, head to the Matillion Hub for a free trial to simplify your Salesforce data recovery process.