Delete rows using a date field inside a data extension

Question

We have a data extension which has around 90 million records and we are trying to filter them out using a date field in the D.E itself for anything older than 6 months. Sp, we can't use data retention policy. I have done it using a Query with using another data extension. But that takes a very long time. Can I accomplish through AMPScript inside SSJS or only SSJS in an automation. I have also considered another type where we can do it by creating a D.E with the keys of the rows to be deleted. Can you please help me out. Thank you

Answer 1

I would suggest having a data extension responsible for archiving data older than X months into. The idea here would make something similar to data retention where you maintained a data extension/table that you kept your data of interest in and also silo'd your historical data. Ideally (depending on the age of the data extension) this would be somewhere around 6 months. If minimal Field lengths/field count is maintained this will also assist in making this process more scale-able as well.

There is documentation for the points where things begin to get a little bit more hairy here:

https://developer.salesforce.com/docs/atlas.en-us.mc-apis.meta/mc-apis/optimizing_api_calls_and_data_structures_to_improve_performance_in_the_salesforce_marketing_cloud.htm

If you intend to utilize AMPscript/SSJS and want it to be performant doing the heavy lifting initially by query activity as mentioned above then having the AMPscript reference your data extension that only contained 6 months of data would be an ideal way to do it. The smaller the amount of data in the data extension the more performant your AMPscript/SSJS would be.

There would not be an easy option here that allowed you to do this performantly/scalably without creating data extensions in a way that allowed for it via SSJS/AMPscript.

Answer 2

I would recommend to stick with queries. Keep in mind that you can increase dramatically the performance of your queries by using non clustered indexes. Even though that is not something you can do on you own on the data extension properties, you just send a support ticket asking "please add a non clustered index to the column Date of the data extension X" and they typically do this quite fast.