A new setting meant to ignore ACTIVATE_VERSION messages entirely

[jeff73511]

Feature scope

Targets (data type handling, batching, SQL object generation, tests, etc.)

Description

I just wanted to post an issue that I had with Meltano that I still cannot figure it out. It is about the add_record_metadata configuration.
Basically when I set add_record_metadata=True, I do see all the following columns showing in this link (https://sdk.meltano.com/en/latest/implementation/record_metadata.html) appear. However when I set it to False, I still see the column, _sdc_table_version in my output which is a mssql table. I started to think whether this is a bug. I Just want to ask how I can remove this column from my output.

Here is my meltano.yml:

version: 1
default_environment: dev
project_id: 99290a45-4a7e-42ae-8208-0b2ca725d652
environments:
- name: dev
  config:
    plugins:
      extractors:
      - name: tap-mssql
        config:
          database: LiftStaging
          host: localhost
          port: '1434'
          user: sa
        select:
        - crm-contact_extract.id
        - crm-contact_extract.country_code
      loaders:
      - name: target-mssql
        config:
          database: LiftStaging
          default_target_schema: crm
          host: localhost
          port: '1434'
          username: sa
          add_record_metadata: false
- name: staging
- name: prod
plugins:
  extractors:
  - name: tap-mssql
    variant: wintersrd
    pip_url: tap-mssql
  loaders:
  - name: target-mssql
    variant: meltanolabs
    pip_url: git+https://github.com/MeltanoLabs/target-mssql.git
  mappers:
  - name: meltano-map-transformer
    variant: meltano
    pip_url: git+https://github.com/MeltanoLabs/meltano-map-transform.git
    mappings:
    - name: table-map
      config:
        stream_maps:
          crm-contact_extract:
            __alias__: contact_loaded (edited) 

We tried meltano invoke tap-mssql > output.json and the output looks like this:

{"type":"STATE","value":{"currently_syncing":"crm-contact"}}
{"type":"SCHEMA","stream":"crm-contact","schema":{"properties":{"country_code":{"inclusion":"available","maxLength":2,"type":["null","string"]},"id":{"inclusion":"available","minimum":-2147483648,"maximum":2147483647,"type":["null","integer"]}},"type":"object"},"key_properties":[]}
{"type":"ACTIVATE_VERSION","stream":"crm-contact","version":1732695221437}
{"type":"RECORD","stream":"crm-contact","record":{"country_code":"US","id":1},"version":1732695221437,"time_extracted":"2024-11-27T08:13:41.445465Z"}
{"type":"RECORD","stream":"crm-contact","record":{"country_code":"US","id":2},"version":1732695221437,"time_extracted":"2024-11-27T08:13:41.445465Z"}
{"type":"RECORD","stream":"crm-contact","record":{"country_code":"US","id":3},"version":1732695221437,"time_extracted":"2024-11-27T08:13:41.445465Z"}
{"type":"RECORD","stream":"crm-contact","record":{"country_code":"US","id":4},"version":1732695221437,"time_extracted":"2024-11-27T08:13:41.445465Z"}
{"type":"RECORD","stream":"crm-contact","record":{"country_code":"US","id":5},"version":1732695221437,"time_extracted":"2024-11-27T08:13:41.445465Z"}
{"type":"STATE","value":{"currently_syncing":"crm-contact"}}
{"type":"ACTIVATE_VERSION","stream":"crm-contact","version":1732695221437}
{"type":"STATE","value":{"currently_syncing":"crm-contact","bookmarks":{"crm-contact":{"initial_full_table_complete":true}}}}
{"type":"STATE","value":{"currently_syncing":null,"bookmarks":{"crm-contact":{"initial_full_table_complete":true}}}}

So even if add_record_metadata is set to false, _sdc_table_version will still appear in the table and only its value becomes NULL because of "type":"ACTIVATE_VERSION",...

Also see:

sdk/singer_sdk/sinks/sql.py

Lines 379 to 387 in 5ed3be1

	if not self.connector.column_exists(
	full_table_name=self.full_table_name,
	column_name=self.version_column_name,
	):
	self.connector.prepare_column(
	self.full_table_name,
	self.version_column_name,
	sql_type=sa.types.Integer(),
	)

[edgarrmondragon]

Here's what target-redshift does, which I think makes sense:

https://github.com/TicketSwap/target-redshift/blob/eb7a814012b70abd7f774b39d76f2c4801b86882/target_redshift/target.py#L44-L52

[edgarrmondragon]

Thanks for logging this issue @jeff73511!