I have a Snaplogic pipeline that queries a web application every night and loads the data into a SQL Server table. I use the 'Update' snap to add new records or add any changes. The goal is that the database should mirror the web application (with a one day lag).
The Update seems to be working fine. The issue is, when a record is deleted in the web application, it isn't being deleted in the database. Does anyone have any ideas as to how to either delete a record in the target database when it's been deleted in the source application or flag the record in the database as 'deleted' in the source?
I thought perhaps I could write the daily data to a temp table and then do some form of merge or insert operation in the database. Or I could truncate the table in the database and rebuild it each day with the new data but as the data grows that's seems very inefficient.