Conditional data retention policy in Azure Data Explorer (Kusto)

Question

The current Kusto data retention policy is mainly based on ingestion time. I am wondering if there is a way to define a data retention policy that is based on some other condition, or any way to mimic the behavior of a conditional data retention policy would do.

For example, I want to remove an item in the database only if there is a newer version of the same (defined by an ID column, say) item got ingested; Otherwise, I'd want the item to be kept in the database regardless its ingestion time. How to achieve that?

Answer 1

I want to remove an item in the database only if there is a newer version of the same (defined by an ID column, say) item got ingested

You could consider creating a materialized view that uses summarize arg_max(version_column, *) by id_column - older records won't be dropped, but if you query the view instead of the table that has the raw data, they will not be visible in your queries.