Cassandra Data Cleanup in Practice

We designed a data cleanup plan in five steps:

1. Export All Product IDs: Export all product IDs from the Cassandra products table to create a complete ID list.
2. Fetch Active Product IDs: Obtain the current list of active (in-stock, valid) product IDs from the business system as the authoritative source.
3. Filter for Inactive IDs: Compare the full ID list against the active list to identify inactive product IDs—those present in Cassandra but no longer active.
4. Bulk Delete Inactive Data: Use a script to delete data records corresponding to these inactive product IDs.
5. Verify the Cleanup: After deletion, re-check data volumes or spot-check specific product IDs to confirm removal.

This is the workflow: Export → Fetch Active IDs → Filter Inactive IDs → Bulk Delete → Verify.

Data Export: Exporting Cassandra Product IDs

The first step is to extract all product IDs currently stored in Cassandra. Assuming the product table is named products, we export only the primary key id column.

Use Cassandra's cqlsh tool with the COPY command. For tens of millions of rows, increase the timeout before exporting:

$ cqlsh --request-timeout=600000 <cassandra_host>
cqlsh> USE product_keyspace;
cqlsh:product_keyspace> COPY products(id) TO '/tmp/products_ids.csv' WITH HEADER = false;

This command exports the id column from the products table to a CSV file in the server's temporary directory (without headers). Exporting tens of millions of IDs typically takes ten or more minutes. After export, use scp to copy the file to a local machine:

$ scp user@<cassandra_server>:/tmp/products_ids.csv ./products_ids.csv

You now have products_ids.csv containing all product IDs.

Fetch Active Product IDs

Next, obtain the list of currently active product IDs from the business system. This serves as your ground truth for determining which IDs to delete. Typically, this comes from an e-commerce product service or similar authority. For example, if an internal API returns a JSON array of product IDs, call it with curl and save the result:

$ curl "http://internal.api.company/active_products" -H "Content-Type: application/json" -d '{"pageSize":10000,"currentPage":1}' -o active_products.json

The API response may need basic processing—extract the product ID field and save it to valid_ids.txt with one ID per line.

Filter for Inactive Product IDs

Now compare the full Cassandra export against the active ID list. An inactive ID exists in Cassandra but not in the active list.

Use this Python script to filter:

# load valid ids into a set for fast lookup
valid_ids = set()
with open('valid_ids.txt', 'r') as f:
    for line in f:
        vid = line.strip()
        if vid:
            valid_ids.add(vid)

# iterate over all exported IDs and filter
count = 0
with open('products_ids.csv', 'r') as f_all, open('garbage_ids.txt', 'w') as f_out:
    for line in f_all:
        pid = line.strip()
        if not pid:
            continue
        if pid not in valid_ids:
            f_out.write(pid + "\n")
            count += 1

f_out.close()
print("无效商品ID数量:", count)

The script reads products_ids.csv line by line and checks membership in the valid_ids set. IDs not in the set are written to garbage_ids.txt. The final count of inactive IDs is printed.

Note: With large files, always read line by line to avoid memory exhaustion. This example assumes the active ID count fits in memory.

Delete Inactive Data

With the list of IDs to delete in hand, bulk delete them from Cassandra using the Python Cassandra driver:

from cassandra.cluster import Cluster

# 连接Cassandra集群
cluster = Cluster(['<cassandra_host_ip>'], port=9042)
session = cluster.connect('product_keyspace')

# 准备参数化删除语句
delete_stmt = session.prepare("DELETE FROM products WHERE id = ?")

# 逐行读取无效ID文件，执行删除
count = 0
with open('garbage_ids.txt', 'r') as f:
    for line in f:
        pid = line.strip()
        if not pid:
            continue
        session.execute(delete_stmt, [pid])
        count += 1

print("删除完成，删除总条数:", count)

The script reads garbage_ids.txt and executes a DELETE statement for each ID. Prepared statements improve efficiency. Deleting millions of records takes time—run this during off-peak hours.

Note: In Cassandra, DELETE does not immediately remove data. Instead, it marks rows with a "tombstone" for later purge during compaction. Large-scale deletions can accumulate tombstones that degrade read performance. Monitor cluster health and run maintenance (compaction) as needed.

Verify the Cleanup Results

After deletion completes, verify success in two ways:

• Re-export and Compare: Repeat the initial export to get a new ID list from products. The row count should be lower; the difference should match the count in garbage_ids.txt. Our cleanup reduced the data volume by approximately one million records.
• Spot-Check Queries: Randomly select IDs from garbage_ids.txt and query Cassandra for them (e.g., with SELECT). Confirm no records are returned.

Once verified, the cleanup is complete.

Practical Summary

This cleanup project yielded several lessons:

• Clear Standards and Preparation: Before cleanup, establish firm criteria for what is inactive. Validate that your active ID list is complete and accurate—a stale or incomplete source of truth will cause data loss. Preserve critical logs, including the ID deletion list, before any large operation.
• Scripting for Scale: Manual operations cannot handle massive datasets. Scripts reduce errors and improve efficiency. Cassandra's COPY utility paired with Python scripts handles this cleanly.
• Performance and Monitoring: Exporting and deleting tens of millions of records stresses the database. Schedule for off-peak hours and set reasonable batch sizes and concurrency. Tombstones from deletions can harm read performance—monitor the cluster and compact as needed.
• Validation and Documentation: Verify results immediately. Document the scripts, process, and outcomes for future reference.

By this process, we removed millions of inactive product records from Cassandra, reducing load on the search system while preserving data accuracy.