Document unofficial Singer extension: ACTIVATE_VERSION

[MeltyBot]

Migrated from GitLab: https://gitlab.com/meltano/meltano/-/issues/2508

Originally created by @DouweM on 2021-01-12 22:46:35

---

As explained by Jeff Huth in Slack:

Most taps are upsert only; either FULL_TABLE (all records) or INCREMENTAL (only records on/after the bookemark). But there are a few taps out there that use ACTIVATE_VERSION . This is an undocumented feature and only works if the target is set up to deal with this... basically, it loads ALL records with a version, then (logically?) deletes earlier version(s) of the records with the same Primary Key. These allow you to upsert + delete (to logically delete the "delta" records).

Imagine you load 100 records from a table today; then you delete 5 records and load 95 records tomorrow. This feature "deletes" the delta 5 records.

Most database targets allow for this. But some file-based targets and even singer-check-tap target fail when you try to run these taps.

tap-google-sheets is a tap that uses this.

The ACTIVATE_VERSION message is not currently documented in the the Singer spec (issue to change that: singer-io/getting-started#71), but it is included in the singer-python library (where it's marked as experimental): https://github.com/singer-io/singer-python/blob/master/singer/messages.py#L137

While this feature may be experimental from the Singer spec's perspective, it is implemented by at least one tap/target pair that's supported out of the box by Meltano, so it's effectively standardized and worth documenting:

• https://meltano.com/plugins/extractors/salesforce.html
  • Feature/activate version messages singer-io/tap-salesforce#6
  • https://gitlab.com/meltano/tap-salesforce/-/blob/a241d97714aaae19a094bfa0a522420f599b23ce/tap_salesforce/__init__.py#L351
  • https://gitlab.com/meltano/tap-salesforce/-/blob/a241d97714aaae19a094bfa0a522420f599b23ce/tap_salesforce/sync.py#L162
• https://meltano.com/plugins/loaders/postgres.html
  • https://github.com/datamill-co/target-postgres/blob/9c095d91e215f932caa897a6587c6dc6278db8cf/target_postgres/target_tools.py#L142-L153

We are also planning to support this feature in taps and targets built using the Singer SDK: https://gitlab.com/meltano/singer-sdk/-/issues/18

[MeltyBot]

View 4 previous comments from the original issue on GitLab

[pnadolny13]

I'm not sure this resolves this issue enough to close it but theres https://hub.meltano.com/singer/docs#activate-version which describes the feature.

cc @tayloramurphy will you close this if you think those docs resolve this issue.

[tayloramurphy]

@pnadolny13 we can close. It's a start but I think we'll want to do more if/when we bring that to the SDK.