I need some suggestions on how to solve this problem.
I have a workflow that imports a csv file containing the updated database (only users who have approved to be contacted) every week. In this workflow I set 3 activities:
Transfer file (download the file csv from SFTP) -> Load file -> Update data ( operation type: insert or update)
The problem is: every time the database is updated (indicatively once a week), some contacts that were uploaded in previous imports may no longer be present in the new csv (users who have changed the approval to be contacted). How can I delete users who are no longer in the database without deleting and re-importing all users every week?
My idea is to create a new workflow with 2 activities:
- Query that selects the contacts that haven't last modification date or creation date equal to the date of last import
- Update data with operation type Delete
But I think this solution is not good because there may be users that don't change any field between an import and the other and it will not be modified neither "Last modified" or "Created"
Easiest thing would be to be able to get the list of users who changed their approval and to pass that information to Campaign as well. This way, you'd know exactly who to delete. (I am assuming here that you just don't keep users who opted-out in your primary database).
What you can do as well is in your workflow, force a field to be updated. For example, extend your profile resource with a new date field where you'll pass the date of the latest import (updated by your import workflow everyweek). Then, all users referenced in the CSV would have this field updated anyway and you'd be able to finetune your deletion strategy.