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.
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.