I am building a platform to analyse and delete some data matching a set of rules from our data extensions in marketing cloud and I was planning to use two different types of queries to perform the job.
To analyse the data I was thinking to perform a query like the following one
SELECT key FROM de1 LEFT JOIN rulesde ON ( de1.key = rulesde.key ) WHERE ISNULL(rulesde.key)
To delete the rows I was thinking to create & fill a Data Extension with a set of fields that identify the rows to delete (ie. in this example it is the same key extracted before) and to use a query activity to fill a second Data Extension with the data to do not delete to be able to drop the previous data extension.
SELECT de1.column1, de1.column2, de1.column3 ... FROM de1 LEFT JOIN rowstodeletede ON ( de1.key = rowstodeletede.key ) WHERE ISNULL(rowstodeletede.key)
To create a clone of the target data extension and extract the columns I would use the SOAP APIs.
But I have the following questions:
- it looks like that it's not possible to query data older than 6 months in the system tables (https://help.salesforce.com/articleView?id=mc_as_using_the_query_activity.htm&type=5), does this apply to all the other data extension too?
- Do all the activities interacting with the data extensions be affected by the drop / recreation of the data extension even if the name will be the same? If yes, Does keeping the same customer key help? After my platform will drop the target data extension and rename the new data extension to match the name I can potentially update the Customer Key.
- If I decide to rely only on the SOAP APIs to delete the rows, can I face rate limiting / timeout issues? Of course I am aware it would take ages but I need a fall back solution