Hey @douwe_maan, wondering if you have any thoughts on this Singer issue that I raised: https://github.com/singer-io/tap-mysql/issues/131#issuecomment-703777662 TLDR is that incremental replication mode intentionally duplicates the last-emitted record to guard against race conditions. I've not seen a lot of discussion around incremental vs. full-table extract around here, but it looks like Singer taps' incremental update mode is fundamentally designed for target DBs where you're handling de-duplication of records, so naively pointing a singer tap at a CSV file or SQL database target in incremental update mode is not in general a sound operation (i.e. you can't update a SQL DB target incrementally because you'll get multiple records emitted with the same primary key). This behavior from Singer was pretty surprising to me, just wanted to see if you had hit this already. Feels like this should be at least documented in https://meltano.com/docs/integration.html#pipeline-state, although I'm wondering whether this behavior actually makes sense for Meltano's case.

We've seen the same behavior in tap-postgres @paul_tiplady . We always deduplicate as an early step in our pipelines, so in practice this hasn't ben a big deal. But it was surprising. Would like to know where you end up going to address it

Hmm yeah the Stitch docs actually go into a lot of detail here: https://www.stitchdata.com/docs/replication/replication-methods/key-based-incremental, so it seems it's just par for the course. I do wonder if it's possible to mark targets as an unsafe sink for duplicated records (i.e. incremental-unsafe); I was able to wire up some tap-target combinations that really should have error'd. Seems like a footgun waiting to happen.

@paul_tiplady As you've found, the limitation is documented explicitly at https://www.stitchdata.com/docs/replication/replication-methods/key-based-incremental#limitation-3--replication-key-inclusivity, so correctly implemented taps should behave this way (and we can expect the Stitch-approved ones to actually do so), even though this is not explicitly documented in the spec. This also means that targets that intend to support taps incremental replication (which ideally would be all of them) should use UPSERT or some other dedup method, even though this is not explicitly documented in the spec either. So I agree that: 1. we should definitely document this ourselves in the https://meltano.com/docs/integration.html#pipeline-state section (which we may want to rename to "Incremental replication" or something like that), and 2. Meltano should warn users when they attempt to use incremental replication with a target that doesn't actually support it correctly, which starts with specifying in our discoverable plugin definitions (https://meltano.com/docs/contributor-guide.html#discoverable-plugins) whether a target supports incremental replication. Meltano would then detect whether incremental replication is used (read: whether we're passing a state file to the tap) and fail hard if the target doesn't support it, and suggest the user use full-table replication instead, or a different target. Meltano could also detect this and simply fall back on full-table replication, although this wouldn't actually be better with append-only formats like CSV and JSONL, or targets that wouldn't wipe the existing data before inserting new records. Some targets support

ACTIVATE_VERSION

(https://meltano.slack.com/archives/CFG3C3C66/p1601148878022700?thread_ts=1600699540.015800&cid=CFG3C3C66) which we may also be able to pay special consideration to. Does that sound reasonable or do you think there's more we can do at this stage? The direction this would ideally move the ecosystem into is proper support for incremental replication in both taps and targets.