Delete data from Cassandra with part of the partition key

Question

Let's say that I have the following table in Cassandra:

customer_bought_product (
    store_id uuid,
    product_id text,
    order_time timestamp,
    email text,
    first_name text,
    last_name text,
    PRIMARY KEY ((store_id, product_id), order_time, email)

The partition keys are store_id and order_id and it is used in order to store time series data.

The data does not have a TTL, as it should be accessible at all times.

In some cases we may require to delete all of the data for a given store_id. What is the best practice to do it?

So far I have thought of the following solutions:

1. Write a program, that will select all of the data from the table and delete the records with the given store_id. - The downside is that this will take more and more time as we insert more data in the table.
2. Leave the data in the table. - The only problem with doing this, is that we will have useless data.
3. Store the table name with the available partition keys in a different table, that can be queried by store_id, get the keys from it and create a delete statement for each or those keys. - I do not like this concept, because I have to maintain the records.

Has anyone encountered this problem? What is the best practice to clear unused records from Cassandra (excluding TTL)?

Answer 1

Create a materialized view to store the product_ids that belong to a corresponding store_ids. This way you can query the MV for a given store_id and then delete the corresponding rows from the main table. This way additional application code could be avoided to maintain two different tables.

create materialized view mv_customer_bought_product 
as select product_id, store_id, order_time, email 
from customer_bought_product 
where order_time is not null 
and email is not null 
and product_id is not null 
and store_id is not null 
primary key (store_id, product_id, order_time, email) ;