# Deleted Record Handling
Depending on the [Replication Method](https://help.qlik.com/en-US/stitch/replication/replication-methods) being used and how records are deleted in the source, deletes may not be captured during the replication process.
## Deletion methods
There are two methods that can be used to delete a source record:
* **Soft deletes**, which will leave a record in the source and use a flag to indicate deletion, such as `is_deleted` or `deleted_on`. If the delete event updates the record’s Replication Key value, Stitch will detect and replicate the changes.
* **Hard deletes**, which completely remove records from the source. It’s as if the record never existed. If using Key-based Incremental Replication, this will remove the record’s Replication Key value, which Stitch uses to identify new and updated records. Without a Replication Key value to check, Stitch can’t identify the change and update the record in the destination.
### Delete support overview
In the table below are each of Stitch’s Replication Methods and the level at which each deletion method is supported.
Click the Replication Method name to check out examples of how each deletion method works with that specific Replication Method.
| **Replication Method** | **Soft deletes** | **Hard deletes** |
| --- | --- | --- |
| [Full Table](https://help.qlik.com/en-US/stitch/replication/deleted-record-handling#full-table) | the Success icon
Supported
**Supported**
Record will have an updated deleted flag value, if a flag is available in the source. | the Warning 2 icon
Sometimes supported
**Sometimes supported**
Record may remain or be removed.
Refer to the [Full Table Replication](https://help.qlik.com/en-US/stitch/replication/deleted-record-handling#full-table) section for more info and examples. |
| [Key-based Incremental](https://help.qlik.com/en-US/stitch/replication/deleted-record-handling#key-based-incremental) | the Success icon
Supported
**Supported**
Record will have an updated deleted flag value, if a flag is available in the source. | the Remove icon
Not supported
**Not supported**
Record will have an updated deleted flag value, if a flag is available in the source. |
| [Log-based Incremental](https://help.qlik.com/en-US/stitch/replication/deleted-record-handling#log-based-incremental) | the Success icon
Supported
**Supported**
Record will have an updated deleted flag value, if a flag is available in the source. | the Warning 2 icon
Sometimes supported
**Sometimes supported**
Record will remain in the destination. The `_sdc_deleted_at` column will be populated with a timestamp value and all columns except for the Primary Key will be overwritten.
**Note**: Only deletes completed as part of a [supported event type](https://help.qlik.com/en-US/stitch/replication/replication-methods/log-based-incremental#limitation--database-event-types) will be captured. |
## Full Table Replication
Click the tabs below to view examples for how each deletion method is handled using [Full Table Replication](https://help.qlik.com/en-US/stitch/replication/replication-methods/full-table).
* Soft deletes
* Hard deletes
* Hard deletes with Append-Only loading
Soft deletes with Full Table
In this example, we’ll demonstrate what happens when two records are soft-deleted in the source. This table uses the `is_deleted` column as a deletion flag, which will be `true` if the record is deleted.
Soft delete: Initial job
During the **initial job**:
* **In the source:** A table contains three records: `id: 1`, `id: 2`, and `id: 3`.
* **During the job**: Stitch replicates the entire table.
* **In the destination:** Stitch loads the entire table in full.
| **Source**
| id | is_deleted |
| 1 | false |
| 2 | false |
| 3 | false |
| **Destination**
| id | is_deleted |
| 1 | false |
| 2 | false |
| 3 | false |
|
| --- | --- |
Soft delete: Second job
During the **second job**:
* **In the source:** Records `1` and `3` are soft deleted (`is_deleted: true`).
* **During the job**: Stitch replicates the entire table.
* **In the destination:** Stitch overwrites the table in the destination, resulting in the `is_deleted` values for records `1` and `3` being updated.
| **Source**
| id | is_deleted |
| 1 | true |
| 2 | false |
| 3 | true |
| **Destination**
| id | is_deleted |
| 1 | true |
| 2 | false |
| 3 | true |
|
| --- | --- |
Hard deletes with Full Table
In this example, we’ll demonstrates what happens when a record is hard-deleted - or removed entirely - from the source.
Information note
**Important:** **Append-Only destinations**
[If using a destination that’s configured to use Append-Only loading](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior#reference--destinations-loading-behavior), this example is relevant only to integrations that support configurable Replication Methods.
For other integrations, see the **Hard deletes with Append-Only loading** tab for an example of how hard deletes are handled.
Hard delete: Initial job
During the **initial job**:
* **In the source:** A table contains three records: `id: 1`, `id: 2`, and `id: 3`.
* **During the job**: Stitch replicates the entire table.
* **In the destination:** Stitch loads the table in full.
| **Source**
| id | name |
| 1 | Finn |
| 2 | Jake |
| 3 | Bubblegum |
| **Destination**
| id | name |
| 1 | Finn |
| 2 | Jake |
| 3 | Bubblegum |
|
| --- | --- |
Hard delete: Second job
During the **second job**:
* **In the source:** Before the second job begins, record `2` is hard deleted and removed entirely from the source.
* **During the job**: Stitch replicates the table in full.
* **In the destination:** Stitch deletes and recreates the table in a single transaction, loading the replicated table in full.
| **Source**
| id | name |
| 1 | Finn |
| 3 | Bubblegum |
| **Destination**
| id | name |
| 1 | Finn |
| 3 | Bubblegum |
|
| --- | --- |
Hard deletes with Append-Only loading
In this example, we’ll demonstrates what happens when a record is hard-deleted from the source and loaded into a destination using Append-Only loading.
How hard deletes are handled in an Append-Only destination depends on the **integration**:
* **If you can define Replication Methods for an integration**, Stitch will delete and re-create tables in a single transaction. This is the scenario described in the **Hard deletes** tab for this section.
* **Otherwise**, Stitch will append the table in full to the destination table. **This is the scenario we’ll outline here.**
Hard delete with Append-Only loading: Initial job
During the **initial job**:
* **In the source:** A table contains three records: `id: 1`, `id: 2`, and `id: 3`.
* **During the job**: Stitch replicates the entire table.
* **In the destination:** Stitch loads the table in full.
| **Source**
| id | name |
| 1 | Finn |
| 2 | Jake |
| 3 | Bubblegum |
| **Destination**
| id | name |
| 1 | Finn |
| 2 | Jake |
| 3 | Bubblegum |
|
| --- | --- |
Hard delete with Append-Only loading: Second job
During the **second job**:
* **In the source:** Before the second job begins, record `2` is hard deleted and removed entirely from the source.
* **During the job**: Stitch replicates the table in full.
* **In the destination:** Stitch appends the replicated data to the end of the table in the destination. In this case, records `1` and `3` would be appended.
| **Source**
| id | name |
| 1 | Finn |
| 3 | Bubblegum |
| **Destination**
| id | name |
| 1 | Finn |
| 2 | Jake |
| 3 | Bubblegum |
| 1 | Finn |
| 3 | Bubblegum |
|
| --- | --- |
## Key-based Incremental Replication
Click the tabs below to view examples for how each deletion method is handled using [Key-based Incremental Replication](https://help.qlik.com/en-US/stitch/replication/replication-methods/key-based-incremental).
* Soft deletes
* Hard deletes
Soft deletes with Key-based Incremental
In this example, we’ll demonstrate what happens when two records are soft-deleted in the source. This table uses the `is_deleted` column as a deletion flag, which will be `true` if the record is deleted.
Soft delete: Initial job
During the **initial job**:
* **In the source:** A table contains three records: `id: 1`, `id: 2`, and `id: 3`.
* **During the job**: Depending on the type of integration, the initial extraction will vary slightly:
* **Database integration**: Stitch replicates the table in full.
* **SaaS integration**: Stitch uses the **Start Date** defined in the Integration Settings as the initial Replication Key (`updated_at`) value. Records with an `updated_at` value greater than or equal to the Start Date are replicated.
**For all integrations**, Stitch saves `updated_at: 2018-07-27 12:07:00` as the maximum Replication Key value.
* **In the destination:** Stitch loads the table.
| **Source**
| id | updated_at | is_deleted |
| 1 | 2018-07-27 12:00:00 | false |
| 2 | 2018-07-27 12:05:00 | false |
| 3 | 2018-07-27 12:07:00 | false |
| **Destination**
| id | updated_at | is_deleted |
| 1 | 2018-07-27 12:00:00 | false |
| 2 | 2018-07-27 12:05:00 | false |
| 3 | 2018-07-27 12:07:00 | false |
|
| --- | --- |
Soft delete: Second job
During the **second job**:
* **In the source:** Records `1` and `3` are soft deleted (`is_deleted: true`). The Replication Key value (`updated_at`) is updated.
* **During the job**: Stitch replicates all records with an `updated_at` value `>= 2018-07-27 12:07:00`.
* **In the destination:** The `is_deleted` and `updated_at` values are updated for records `1` and `3`.
| **Source**
| id | updated_at | is_deleted |
| 1 | 2018-07-28 04:00:00 | true |
| 2 | 2018-07-27 12:05:00 | false |
| 3 | 2018-07-28 04:05:00 | true |
| **Destination**
| id | updated_at | is_deleted |
| 1 | 2018-07-28 04:00:00 | true |
| 2 | 2018-07-27 12:05:00 | false |
| 3 | 2018-07-28 04:05:00 | true |
|
| --- | --- |
Hard deletes with Key-based Incremental
In this example, we’ll demonstrates what happens when a record is hard-deleted - or removed entirely - from the source.
**Note**: To remove hard deletes from destination tables replicated in this way, you’ll need to:
1. Temporarily drop the table in the destination
2. Reset the table’s Replication Keys
3. Wait for Stitch to re-create the table during the next replication job
**These steps must be done in this order**, or you may cause issues with replication.
Hard delete: Initial job
During the **initial job**:
* **In the source:** A table contains three records: `id: 1`, `id: 2`, and `id: 3`.
* **During the job**: Depending on the type of integration, the initial extraction will vary slightly:
* **Database integration**: Stitch replicates the table in full.
* **SaaS integration**: Stitch uses the **Start Date** defined in the Integration Settings as the initial Replication Key value. Records with a Replication Key value greater than or equal to the Start Date are replicated.
**For all integrations**, Stitch saves `updated_at: 2018-07-27 12:07:00` as the maximum Replication Key value.
* **In the destination:** Stitch loads the table.
| **Source**
| id | updated_at |
| 1 | 2018-07-27 12:00:00 |
| 2 | 2018-07-27 12:05:00 |
| 3 | 2018-07-27 12:07:00 |
| **Destination**
| id | updated_at |
| 1 | 2018-07-27 12:00:00 |
| 2 | 2018-07-27 12:05:00 |
| 3 | 2018-07-27 12:07:00 |
|
| --- | --- |
Hard delete: Second job
During the **second job**:
* **In the source:** Before the second job begins, record `2` is hard deleted and removed entirely from the source. Records `1` and `3` are updated.
* **During the job**: Stitch replicates all records with an `updated_at` value `>= 2018-07-27 12:07:00`.
* **In the destination:** The `updated_at` values for records `1` and `3` are updated.
Record `2` remains in the destination table.
| **Source**
| id | updated_at |
| 1 | 2018-07-28 04:15:00 |
| 3 | 2018-07-28 04:07:00 |
| **Destination**
| id | updated_at |
| 1 | 2018-07-28 04:15:00 |
| 2 | 2018-07-27 12:05:00 |
| 3 | 2018-07-28 04:07:00 |
|
| --- | --- |
## Log-based Incremental Replication
Click the tabs below to view examples for how each deletion method is handled using [Log-based Incremental Replication](https://help.qlik.com/en-US/stitch/replication/replication-methods/log-based-incremental).
* Soft deletes
* Hard deletes
Soft deletes with Log-based Incremental
In this example, we’ll demonstrate what happens when two records are soft-deleted in a source table using Log-based Incremental.
Soft delete: Initial job
During the **initial job**:
* **In the source:** A table contains three records: `id: 1`, `id: 2`, and `id: 3`.
* **During the job**: Stitch uses `SELECT` replication to replicate the table in full.
* **In the destination:** Stitch loads the table in full.
| **Source**
| id | name | is_deleted |
| 1 | Finn | false |
| 2 | Jake | false |
| 3 | Bubblegum | false |
| **Destination**
| id | name | is_deleted |
| 1 | Finn | false |
| 2 | Jake | false |
| 3 | Bubblegum | false |
|
| --- | --- |
Soft delete: Second job
During the **second job**:
* **In the source:** Records `1` and `3` are soft deleted (`is_deleted: true`) using an `UPDATE` event.
As `UPDATE` is a supported event type for Log-based Replication, log messages describing the modified `is_deleted` values for records `1` and `3` are written to the database’s binary log.
* **During the job**: After the historical replication job, Stitch will read updates for the table from the database’s binary logs.
Stitch reads the log messages for records `1` and `3` and replicates the updated records.
* **In the destination:** The `is_deleted` and `updated_at` values are updated for records records `1` and `3`.
| **Source**
| id | name | is_deleted |
| 1 | Finn | true |
| 2 | Jake | false |
| 3 | Bubblegum | true |
| **Destination**
| id | name | is_deleted |
| 1 | Finn | true |
| 2 | Jake | false |
| 3 | Bubblegum | true |
|
| --- | --- |
Hard deletes with Log-based Incremental
In this example, we’ll demonstrate what happens when a record is hard deleted - or removed entirely - from a source table using Log-based Incremental. In this situation, Stitch will use a `TIMESTAMP` column named `_sdc_deleted_at` to indicate when records have been identified as deleted in the source.
**Note**: Identifying hard deleted records is dependent on how the record is deleted, as not all event types are recorded by databases in binary logs. For example: If a record is deleted using `TRUNCATE` in a MySQL database, Stitch will not detect the deletion.
The following event types will be detected by Log-based Incremental for database integrations that support this Replication Method:
* `INSERT`
* `UPDATE`
* `DELETE`
Hard delete: Initial job
During the **initial job**:
* **In the source:** A table contains three records: `id: 1`, `id: 2`, and `id: 3`.
* **During the job**: Stitch uses `SELECT` replication to replicate the table in full.
* **In the destination:** Stitch loads the table in full.
| **Source**
| id | name |
| 1 | Finn |
| 2 | Jake |
| 3 | Bubblegum |
| **Destination**
| id | name | _sdc_deleted_at |
| 1 | Finn | |
| 2 | Jake | |
| 3 | Bubblegum | |
|
| --- | --- |
Hard delete: Second job
During the **second job**:
* **In the source:** Record `2` is hard deleted using a `DELETE` event.
As `DELETE` is a supported event type for Log-based Replication, log messages describing the delete of record `2` are written to the database’s binary log.
* **During the job**: After the historical replication job, Stitch will read updates for the table from the database’s binary logs.
Stitch reads the log message for record `2` and identifies the record as deleted.
* **In the destination:** Record `2` is overwritten with the exception of its Primary Key value. Stitch will place a timestamp in the `_sdc_deleted_at` column to indicate when the deletion took place.
| **Source**
| id | name |
| 1 | Finn |
| 2 | Jake |
| 3 | Bubblegum |
| **Destination**
| id | name | _sdc_deleted_at |
| 1 | Finn | |
| 2 | | 2018-07-28 04:05:00 |
| 3 | Bubblegum | |
|
| --- | --- |