How to apply retention policy on specific data in azure data explorer?

Question

I need to remove specific data from azure data explorer automatically after some conditions are met, but currently what I understood from retention policy document we only can specify a retention policy on the whole table or database but not each row.

To provide more details, I have an asp.net core application which ingests data continuously to adx. Each data item should be removed automatically based on their type and retention days specified for that type.

So I'm wondering if this is possible and if not would you please recommend a solution for this?

Many thanks

Answer 1

You're correct that a retention policy can be applied at the database or at the table level, but not at the record level.

If your data divides into different "types" for which you have different desired retention periods - you could split the data into multiple tables and apply an appropriate retention policy for each if the tables.

At query time, if you need to process multiple "types", you can have a stored function that unions the set of tables.