1

On a daily basis, I have to remove some rows from my Customers Data Extension. This cannot be done via the Data Retention Policy since the IDs are provided by a database (and saved in a separate Data Extension) and this does not depend on how old data is.

As far as I know, there is no straightforward way of deleting rows from a Data Extention e.g. running a DELETE FROM query inside a SQL Query Activity. So the suggested solution so far is to copy all the data inside the Customers DE into a temp DE like CustomersTemp and then using another SQL Query Activity to exclude the rows we don't want and "Overwrite" Customers using that query.

E.g.

SELECT ID, Field1, Field2, ..., FieldN
WHERE ID NOT IN (SELECT DISTINCT ID FROM DeletedCustomer)

But for a Data Extension with millions of rows, this looks like too much work. I am wondering how I can make this process easier on the server.

Is the current solution better than overwriting the entire Data Extension every day? Because as of today, I am only using delta files (only the modified rows for the last 30 days) to update my Data Extensions to save time and resource.

If you have a better alternative in place, I would appreciate if you shared with me.

1

If you are looking at deleting Millions of rows then using a staging table may be the most optimized way, however for smaller volumes SSJS Activities may be able to help - for example using WSProxy (https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/ssjs_WSProxy_delete.htm) would allow you to do all the same actions you would be able to do via SOAP - this would mean doing delete with primary keys Alternative would be using the deletedata function (https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/ssjs_platformDataExtensionDeleteData.htm) which is purely value based.

  • When you say "using a staging table", does it mean the same solution I explained above? I will get into the nitty-gritty of SSJS if this does not work. But just to make sure my solution is not terrible. – Disasterkid Jul 8 at 7:12
  • Hey, yep same thing. It's not the worst idea and if you are deleting Millions of rows per day it would likely be the better option than using SSJS – Doug Jul 8 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.