> ## Documentation Index
> Fetch the complete documentation index at: https://www.propeldata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Deleting data

> Real-time and batch deletes in ClickHouse.

<div />

Propel offers both [real-time](#real-time-deletes) and [batch](#batch-deletes) delete capabilities.

## Real-time deletes

Real-time deletes allow you to delete existing rows in a table, which are immediately excluded when querying.

### Use cases

* Delete rows in a table and immediately exclude them from queries
* Delete entities (users, accounts, etc.) or dimensions

### How it works

Real-time deletes in Propel are supported by ClickHouse's ReplacingMergeTree table engine. Deletes do not happen transactionally. Instead, they happen asynchronously, where Propel uses a combination of filter-on-read and background jobs to perform the deletes.

When a row is deleted, a new record is written with an internal `_propel_is_deleted` column set to `true`. Propel deletes these records in the background, asynchronously. To ensure that deleted data is never returned in queries, Propel automatically performs a filter-on-read when querying ReplacingMergeTree tables. This ensures that no deleted record is returned, even if the background delete operation has not yet occurred.

However, it's important to note that there are two implications:

1. The row count in ReplacingMergeTree tables may be larger than the logical number of rows. This is because there may be duplicate rows in the table that haven't been deleted yet. These rows will be de-duplicated at query time.
2. Filtering deleted records at query time has performance implications on read operations. This can be compensated using a larger Propeller.

### Create a Data Pool to handle real-time deletes

In this section, we will guide you through the process of creating a Data Pool that can handle real-time deletes. We will provide step-by-step instructions on how to create it via the Console, API, and Terraform.

<Tabs>
  <Tab title="Console">
    To create a Data Pool that can handle real-time deletes in Console, first go to the **"Data Pools"** section, then click **"Create Data Pool"**.

    <Frame caption="Propel's Data Pools Console page">
      <img src="https://mintcdn.com/propeldocs/G8bGSg66eBpnYaHB/images/docs/2024-07-22-updating-data-01.png?fit=max&auto=format&n=G8bGSg66eBpnYaHB&q=85&s=cd545c27666d2fbc2746810c94bf8d68" alt="Propel's Data Pools Console page" width="1393" height="634" data-path="images/docs/2024-07-22-updating-data-01.png" />
    </Frame>

    Select the Data Pool type you need to create.

    <Frame caption="Create new Data Pool">
      <img src="https://mintcdn.com/propeldocs/G8bGSg66eBpnYaHB/images/docs/2024-07-22-updating-data-02.png?fit=max&auto=format&n=G8bGSg66eBpnYaHB&q=85&s=80f4070ce08619699dc8dc076d4761a9" alt="Create new Data Pool" width="1396" height="726" data-path="images/docs/2024-07-22-updating-data-02.png" />
    </Frame>

    In the **"Table Settings"** step, select **"Mutable Data"**.

    <Frame caption="ClickHouse table settings configuration">
      <img src="https://mintcdn.com/propeldocs/G8bGSg66eBpnYaHB/images/docs/2024-07-22-updating-data-03.png?fit=max&auto=format&n=G8bGSg66eBpnYaHB&q=85&s=0fa8b83a89fede6711870e2137cd6257" alt="ClickHouse table settings configuration" width="1466" height="373" data-path="images/docs/2024-07-22-updating-data-03.png" />
    </Frame>

    Answer the questions on how to uniquely identify records and your query patterns. For more information, read the "Mutable records" section of the [How to select a table engine and sorting key](https://www.propeldata.com/docs/guides/table-engine-and-sorting-key#mutable-records) guide.

    <Frame caption="ClickHouse table settings configuration questions">
      <img src="https://mintcdn.com/propeldocs/G8bGSg66eBpnYaHB/images/docs/2024-07-22-updating-data-04.png?fit=max&auto=format&n=G8bGSg66eBpnYaHB&q=85&s=ee411e277d3f78522f40a6a41708d21c" alt="ClickHouse table settings configuration questions" width="1466" height="694" data-path="images/docs/2024-07-22-updating-data-04.png" />
    </Frame>

    Next, you will get the suggested table settings. Note that the table engine is ReplacingMergeTree, and the sorting key determines the uniqueness of the row.

    <Frame caption="Suggested ClickHouse table settings">
      <img src="https://mintcdn.com/propeldocs/G8bGSg66eBpnYaHB/images/docs/2024-07-22-updating-data-05.png?fit=max&auto=format&n=G8bGSg66eBpnYaHB&q=85&s=cb4330a36ab5c5c1dfb76e6ae76cc5a7" alt="Suggested ClickHouse table settings" width="1465" height="744" data-path="images/docs/2024-07-22-updating-data-05.png" />
    </Frame>

    Click **"Next"** to finish setting up the Data Pool. Once it is created, you can go to the **"Details"** tab and verify the settings.

    <Frame caption="Data Pool details">
      <img src="https://mintcdn.com/propeldocs/G8bGSg66eBpnYaHB/images/docs/2024-07-22-updating-data-06.png?fit=max&auto=format&n=G8bGSg66eBpnYaHB&q=85&s=1cc44db00e8cb50cf931d2c1ae23b159" alt="Data Pool details" width="1072" height="502" data-path="images/docs/2024-07-22-updating-data-06.png" />
    </Frame>
  </Tab>

  <Tab title="API">
    The API request below creates a Data Pool that can handle real-time deletes. It uses the ReplacingMergeTree table engine, the timestamp, and order\_item\_id columns to sort and determine uniqueness.

    ```graphql theme={"system"}
    mutation {
      createDataPoolV2(input: {
        uniqueName: "TacoSoft Order Items"
        description: "Data Pool supporting real-time deletes for TacoSoft Order Items"
        timestamp: {
          columnName: "timestamp"
        }
        columns: [
          { columnName: "quantity", type: INT32, isNullable: false },
          { columnName: "taco_name", type: STRING, isNullable: false },
          { columnName: "sauce_name", type: STRING, isNullable: false },
          { columnName: "restaurant_id", type: STRING, isNullable: false },
          { columnName: "restaurant_name", type: STRING, isNullable: false },
          { columnName: "taco_total_price", type: FLOAT, isNullable: false },
          { columnName: "order_item_id", type: STRING, isNullable: false },
          { columnName: "tortilla_id", type: STRING, isNullable: false },
          { columnName: "toppings", type: JSON, isNullable: false },
          { columnName: "sauce_id", type: STRING, isNullable: false },
          { columnName: "taco_unit_price", type: FLOAT, isNullable: false },
          { columnName: "order_id", type: STRING, isNullable: false },
          { columnName: "order_item_generated_at", type: TIMESTAMP, isNullable: false },
          { columnName: "taco_id", type: STRING, isNullable: false },
          { columnName: "timestamp", type: TIMESTAMP, isNullable: false },
          { columnName: "tortilla_name", type: STRING, isNullable: false }
        ],
        tableSettings: {
          engine: {
            replacingMergeTree: {
              type: REPLACING_MERGE_TREE
            }
          }
          partitionBy: ["toYYYYMM(timestamp)"]
          orderBy: ["timestamp", "order_item_id"]
        }
      }) {
        dataPool {
          id
          uniqueName
          description
          tableSettings {
            orderBy
            partitionBy
          }
        }
      }
    }
    ```
  </Tab>

  <Tab title="Terraform">
    The following Terraform creates a Data Pool that can handle real-time deletes. It uses the ReplacingMergeTree table engine, the timestamp, and order\_item\_id columns to sort and determine uniqueness.

    ```hcl theme={"system"}
    resource "propel_data_pool" "updatable_tacosoft_order_items" {
      unique_name = "TacoSoft Order Items"
      description = "Data Pool supporting real-time deletes for TacoSoft Order Items"
      timestamp   = "timestamp"

      table_settings {
        engine {
          type = "REPLACING_MERGE_TREE"
        }
        partition_by = ["toYYYYMM(timestamp)"]
        order_by     = ["timestamp", "order_item_id"]
      }

      column {
        name     = "quantity"
        type     = "INT32"
        nullable = false
      }

      column {
        name     = "taco_name"
        type     = "STRING"
        nullable = false
      }

      column {
        name     = "sauce_name"
        type     = "STRING"
        nullable = false
      }

      column {
        name     = "restaurant_id"
        type     = "STRING"
        nullable = false
      }

      column {
        name     = "restaurant_name"
        type     = "STRING"
        nullable = false
      }

      column {
        name     = "taco_total_price"
        type     = "FLOAT"
        nullable = false
      }

      column {
        name     = "order_item_id"
        type     = "STRING"
        nullable = false
      }

      column {
        name     = "tortilla_id"
        type     = "STRING"
        nullable = false
      }

      column {
        name     = "toppings"
        type     = "JSON"
        nullable = false
      }

      column {
        name     = "sauce_id"
        type     = "STRING"
        nullable = false
      }

      column {
        name     = "taco_unit_price"
        type     = "FLOAT"
        nullable = false
      }

      column {
        name     = "order_id"
        type     = "STRING"
        nullable = false
      }

      column {
        name     = "order_item_generated_at"
        type     = "TIMESTAMP"
        nullable = false
      }

      column {
        name     = "taco_id"
        type     = "STRING"
        nullable = false
      }

      column {
        name     = "timestamp"
        type     = "TIMESTAMP"
        nullable = false
      }

      column {
        name     = "tortilla_name"
        type     = "STRING"
        nullable = false
      }
    }
    ```
  </Tab>
</Tabs>

***

## Batch deletes

Batch deletes are useful for one-off or automated jobs where you need to delete many rows in a table.

### Use cases

* Deletes for GDPR compliance
* One-off deletes of incorrect data

### How it works

Batch deletes in Propel are supported by table mutations in ClickHouse. When you perform a batch delete, Propel issues an `ALTER TABLE … DELETE` statement to ClickHouse and monitors its progress. The mutation proceeds part-by-part, partition-by-partition, deleting rows in the table.

Propel provides a simple way to data data asynchronously using the Console or the [`createDeletionJob`](/docs/management-api/mutations#createdeletionjob) API.

### Create a batch delete job

In this section, we guide you through the process of creating a batch delete job via the Console and API.

<Tabs>
  <Tab title="Console">
    You can initiate a delete job in the Console by navigating to the Data Pool from which you need to delete data, clicking on the **"Operations"** tab, and then clicking **"Delete data"**.

    <Frame caption="Deleting data">
      <img src="https://mintcdn.com/propeldocs/ovcvzBlD2PZqaQX3/images/docs/2024-01-04-data-pool-operations-menu.png?fit=max&auto=format&n=ovcvzBlD2PZqaQX3&q=85&s=ddfb3b3c6d58608afc7bbdbf74055394" alt="Deleting data" width="1262" height="267" data-path="images/docs/2024-01-04-data-pool-operations-menu.png" />
    </Frame>

    Here, you can specify the filters of the data to delete.

    <Frame caption="Deleting data in Console">
      <img src="https://mintcdn.com/propeldocs/G8bGSg66eBpnYaHB/images/docs/2024-07-22-deleting-data-01.png?fit=max&auto=format&n=G8bGSg66eBpnYaHB&q=85&s=10b59273b3e9930b918d55c4eaacbb8f" alt="Deleting data in Console" width="1221" height="514" data-path="images/docs/2024-07-22-deleting-data-01.png" />
    </Frame>

    Lastly, you can see the progress of the delete job and when it is completed.

    <Frame caption="Monitoring a delete job in Console">
      <img src="https://mintcdn.com/propeldocs/G8bGSg66eBpnYaHB/images/docs/2024-07-22-deleting-data-02.png?fit=max&auto=format&n=G8bGSg66eBpnYaHB&q=85&s=63789ab32ae5e6a0f49105ada5168ff9" alt="Monitoring a delete job in Console" width="1111" height="377" data-path="images/docs/2024-07-22-deleting-data-02.png" />
    </Frame>
  </Tab>

  <Tab title="API">
    Here's an example of how to delete data using the API (read the docs for more details):

    ```graphql theme={"system"}
    mutation {
      createDeletionJob(input: {
        dataPool: "DPO00000000000000000000000000"
        filterSql: "restaurant_name='Farolito' AND taco_name='Veggie'"
      }) {
        job {
          id
          status
          progress
          error {
            message
          }
        }
      }
    }
    ```
  </Tab>
</Tabs>

***

Remember, deleting data is permanent and cannot be undone, so use this feature cautiously.