# Capture Deletes

When loading data incrementally, you may want to handle records that exist in the target but are missing from the source. The `delete_missing` option supports two modes:

* `hard`: Physically deletes records from the target table that no longer exist in the source
* `soft`: Marks records as deleted in the target table by setting a deletion timestamp

{% hint style="warning" %}
Be careful when enabling this feature on massive tables. The primary key column(s) is fully selected from the source stream each run in order to determine which records don't exist anymore. For large tables, consider using `source_where` and `target_where` to scope the delete detection to a subset of data (see [Scoped Delete with WHERE Clauses](#scoped-delete-with-where-clauses) below).
{% endhint %}

{% hint style="success" %}
For true incremental delete capture without scanning the source table, consider using [Change Capture (CDC)](/concepts/change-capture.md) mode. CDC reads deletes directly from the database transaction log, making it efficient even on massive tables.
{% endhint %}

## Hard Delete

Physically removes records from the target table that no longer exist in the source.

```yaml
source: MY_POSTGRES
target: MY_SNOWFLAKE

defaults:
  mode: incremental
  update_key: updated_at
  primary_key: id  # primary key is required for delete_missing

streams:
  finance.accounts:
    object: finance.accounts_target
    target_options:
      delete_missing: hard  # will remove records that don't exist in source
```

## Soft Delete

Marks records as deleted in the target table by setting a `_sling_deleted_at` timestamp column.

```yaml
source: MY_POSTGRES
target: MY_SNOWFLAKE

defaults:
  mode: incremental
  update_key: updated_at
  primary_key: id  # primary key is required for delete_missing
  target_options:
    delete_missing: soft  # will mark records as deleted with timestamp

streams:
  finance.accounts:
    object: finance.accounts_target
```

When using `soft` delete mode, Sling will add a `_sling_deleted_at` timestamp column to track when records were marked as deleted.

## Scoped Delete with WHERE Clauses

For large tables with years of historical data, comparing all primary keys can be expensive. You can scope the delete detection to a subset of data using `source_where` and `target_where` clauses. This is useful when you only need to detect deletes in recent data (e.g., records from the last 30 days). This feature is added in *v1.5.6*.

```yaml
source: MY_SQLSERVER
target: MY_POSTGRES

defaults:
  mode: incremental
  update_key: updated_at
  primary_key: id

streams:
  sales.orders:
    object: sales.orders_target
    target_options:
      delete_missing:
        type: soft
        # Only check for deletes in records from the last 30 days
        source_where: created_at >= DATEADD(day, -30, GETDATE())  # SQL Server syntax
        target_where: created_at >= NOW() - INTERVAL '30 days'   # PostgreSQL syntax
```

The extended `delete_missing` configuration accepts:

* `type` **(required)**: Either `soft` or `hard`
* `where`: WHERE clause applied to both source and target (use when both databases share the same SQL syntax)
* `source_where`: WHERE clause for the source query (uses source database SQL syntax)
* `target_where`: WHERE clause for the target query (uses target database SQL syntax)

{% hint style="info" %}
When using different database types (e.g., SQL Server to PostgreSQL), use `source_where` and `target_where` with the appropriate SQL syntax for each database. When source and target use the same SQL dialect, you can use the simpler `where` option.
{% endhint %}

**Example with single `where` clause (same database types):**

```yaml
target_options:
  delete_missing:
    type: hard
    where: created_at >= '2024-01-01'  # works when source and target use same syntax
```

## Important Notes

* The `delete_missing` option requires that you specify a `primary_key` to uniquely identify records
* For incremental loads, an `update_key` is also required to determine which records to process
* The comparison is done using a temporary table to efficiently identify missing records
* When using `source_where`/`target_where`, only records matching the WHERE clause are considered for delete detection
* **Schema configuration**: Ensure your target database connection has the correct default schema configured. For databases that support schema search paths:

  * **PostgreSQL**: Set `search_path` in your connection string (e.g., `?search_path=my_schema`) or configure it at the database/role level
  * **SQL Server**: The default schema is determined by the user's default schema setting
  * **Oracle**: Set the `current_schema` or ensure the user has appropriate schema permissions

  This is particularly important when running multiple streams concurrently, as the delete operation uses temporary tables that need to resolve correctly within the target schema.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.slingdata.io/examples/database-to-database/capture_deletes.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.