Hi, we are building a custom connector that runs once a day and pulls in data for yesterday from an API. We are sinking to postgres into a single table, so
Full Refresh - Append would seem appropriate. Sometimes, however, data for a given date (e.g. 7 days ago) is updated by the remote server, and we need to re-ingest for that date. which would yield records with duplicate primary keys in the destination table, for that date. The only other sync method that seems close is
Incremental - Dedupe with History , However, this requires a cursor, and because the re-ingested date could be well before the value of the current cursor, it is not clear to me whether the dedupe would work, as it looks like the dedupe just works for records more recent than last cursor value. How can we achieve this behaviour?
What we kinda need is the dedupe to work only for the records with the given date (i.e. the newly ingested date e.g. 7 days ago).