Marketing Cloud - Select (and delete) data in Data Extensions (joins / older than 6 months / related activities / apis)

Question

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

Thanks!

Answer 1

1. Data Extensions have a separate mechanism for data retention (point 8 of this doc). So if it's not set up - data in DE's are stored infinitely
   2. It will break the mechanism. Internally MC uses internal ID's to recognize proper elements, so even recreating DE with old external ID will not make it work again.
   3. Haven't heard about hard limits, as (If I remember correctly) API calls are one element of the agreement, so going over defined yearly quota means more money for MC team. For API throttling, please check this question: https://salesforce.stackexchange.com/a/205901/38692

As for deleting unnecessary records from Data Extension, I would suggest a bit different approach. It's more focused on moving the data around than on deleting the data extension.

The solution requires additional Data Extension, which is identical to one on which you want to make such operation. Then in the automation, you can do something like that:

• First SQL that will select only records that need to be left in source Data Extension to the mirror Data Extension
• Second SQL to copy all records from that mirror Data Extension to the source Data Extension, with the overwrite setting