I have a Postgres to Snowflake pipeline that's failing on a table where I've specified a column should be of type

variant

, in the postgres database the

object

and

object_changes

columns contains either

null

or a

JSONB

value. here's my tap configuration:

- name: tap-postgres-arc-full-refresh
    inherit_from: tap-postgres
    variant: meltanolabs
    pip_url: git+<https://github.com/MeltanoLabs/tap-postgres.git@v0.0.9>
    config:
      host: ${ARC_DB_HOST}
      user: ${ARC_DB_USER}
      port: ${ARC_DB_PORT}
      password: ${ARC_DB_PASSWORD}
      database: ${ARC_DB_NAME}
      default_replication_method: FULL_TABLE
      filter_schemas:
      - public
      ssl_enable: true
      ssl_mode: require
      ssl_certificate_authority: .secrets/root.crl
      dates_as_string: true

    select:
    - public-versions.*

    schema:
      public-versions:
        object:
          type: [variant, {}]
          format: variant
        object_changes:
          type: [variant, {}]
          format: variant

And I'm getting this error:

2024-04-09T09:21:29.650895Z [info     ] sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 002108 (22000): 01b38cf1-0002-4b95-0042-68070031e832: SQL compilation error: cannot change column OBJECT from type VARIANT to VARCHAR(16777216) cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T09:21:29.650986Z [info     ]                                cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T09:21:29.651127Z [info     ] [SQL: ALTER TABLE SOURCE_MELTANO_DEV.ARC_POSTGRES.VERSIONS ALTER COLUMN object SET DATA TYPE VARCHAR] cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres

This pipeline loads about 5k row and correctly creates the columns in Snowflake as VARIANTs before failing with the above error message. Why is Meltano trying to convert this column to

VARCHAR(16777216)

?

The column

object

seems to be changing types? Or at least snowflake is interpreting that. I'd take a peak at the output of tap-postgres 's raw output ie

meltano invoke tap-postgres-arc-full-refresh

and see if there's multiple schema messages for the same table, and is the schema for that table changing (it shouldn't be but it seems like it is here?) My best guess is that snowflake is interpreting something as a column type change that it shouldn't be, but I'd have to dive into those messages and send them to target-snowflake to be sure

I had a similar hunch that somehow multiple schema outputs could be getting created. when I run

meltano invoke tap-postgres-arc-full-refresh > versions.json

locally the versions.json file ends up being 438k lines long with only this 1 schema output:

{
  "type": "SCHEMA",
  "stream": "public-versions",
  "schema": {
    "properties": {
      "item_type": {
        "type": [
          "string",
          "null"
        ]
      },
      "event": {
        "type": [
          "string",
          "null"
        ]
      },
      "whodunnit": {
        "type": [
          "string",
          "null"
        ]
      },
      "object": {
        "format": "VARIANT",
        "type": [
          "VARIANT",
          {}
        ]
      },
      "object_changes": {
        "format": "VARIANT",
        "type": [
          "VARIANT",
          {}
        ]
      },
      "created_at": {
        "format": "date-time",
        "type": [
          "string",
          "null"
        ]
      },
      "id": {
        "type": [
          "string"
        ]
      },
      "item_id": {
        "type": [
          "string",
          "null"
        ]
      }
    },
    "type": "object",
    "required": [
      "id"
    ]
  },
  "key_properties": [
    "id"
  ]
}

Then there's something in target-snowflake triggering a column type change based on the values of the data instead of schema messages

Hmm. Here's the loader definition

loaders:
  - name: target-snowflake-arc-postgres
    inherit_from: target-snowflake
    variant: meltanolabs
    pip_url: meltanolabs-target-snowflake
    config:
      account: ${DBT_SNOWFLAKE_ACCOUNT}
      user: ${MELTANO_SNOWFLAKE_USER}
      password: ${MELTANO_SNOWFLAKE_PASSWORD}
      warehouse: MELTANO
      default_target_schema: arc_postgres

not sure if this helps, but here's the Snowflake table definition

Again, that's my best guess but I'd dive in and figure out what causes it and then it'd be clear 🤷

@visch correct if I'm wrong but this override doesn't look like a valid json schema:

type: [variant, {}]

Should it rather be something like

type: [object, {}]

unless I'm missing how target-snowflake interprets those types

You're right @Edgar Ramírez (Arch.dev), I didn't notice until I dropped the table in Snowflake entirely then reran the sync and got some error about how "variant" is an unrecognized type. I now have this in my meltano.yml file:

schema:
      public-versions:
        object:
          type: [object, {}]
        object_changes:
          type: [object, {}]

but am unfortunately getting this error message now:

2024-04-09T18:26:53.243221Z [info     ] Traceback (most recent call last): cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.243494Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/bin/target-snowflake", line 8, in <module> cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.243749Z [info     ]     sys.exit(TargetSnowflake.cli()) cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.243907Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/click/core.py", line 1157, in __call__ cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.244263Z [info     ]     return self.main(*args, **kwargs) cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.244470Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/click/core.py", line 1078, in main cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.244710Z [info     ]     rv = self.invoke(ctx)      cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.244862Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/click/core.py", line 1434, in invoke cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.245089Z [info     ]     return ctx.invoke(self.callback, **ctx.params) cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.245248Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/click/core.py", line 783, in invoke cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.245387Z [info     ]     return __callback(*args, **kwargs) cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.245527Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/singer_sdk/target_base.py", line 550, in invoke cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.245737Z [info     ]     target.listen(file_input)  cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.245879Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/singer_sdk/io_base.py", line 34, in listen cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.246072Z [info     ]     self._process_lines(file_input) cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.246224Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/singer_sdk/target_base.py", line 291, in _process_lines cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.246346Z [info     ]     counter = super()._process_lines(file_input) cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.246463Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/singer_sdk/io_base.py", line 93, in _process_lines cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.246580Z [info     ]     self._process_record_message(line_dict) cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.246695Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/singer_sdk/target_base.py", line 341, in _process_record_message cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.246893Z [info     ]     sink._validate_and_parse(transformed_record) cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.247276Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/singer_sdk/sinks/core.py", line 317, in _validate_and_parse cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.247494Z [info     ]     self._validator.validate(record) cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.247658Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/jsonschema/validators.py", line 437, in validate cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.247840Z [info     ]     for error in self.iter_errors(*args, **kwargs): cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.248137Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/jsonschema/validators.py", line 371, in iter_errors cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.248627Z [info     ]     for error in errors:       cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.248772Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/jsonschema/_keywords.py", line 296, in properties cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.249131Z [info     ]     yield from validator.descend( cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.249445Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/jsonschema/validators.py", line 419, in descend cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.249590Z [info     ]     for error in errors:       cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.249758Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/jsonschema/_keywords.py", line 285, in type cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.249949Z [info     ]     if not any(validator.is_type(instance, type) for type in types): cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.250080Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/jsonschema/_keywords.py", line 285, in <genexpr> cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.250201Z [info     ]     if not any(validator.is_type(instance, type) for type in types): cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.250325Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/jsonschema/validators.py", line 442, in is_type cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.250443Z [info     ]     return self.TYPE_CHECKER.is_type(instance, type) cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.250568Z [info     ]   File "/Users/jacob/code/pairteam/analytics-etl/.meltano/loaders/target-snowflake/venv/lib/python3.10/site-packages/jsonschema/_types.py", line 110, in is_type cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.250693Z [info     ]     fn = self._type_checkers[type] cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.250811Z [info     ] TypeError: argument 'key': unhashable type: 'dict' cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.328176Z [info     ] 2024-04-09 20:26:53,327 | INFO     | snowflake.connector.connection | closed cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.328376Z [info     ] 2024-04-09 20:26:53,327 | INFO     | snowflake.connector.connection | No async queries seem to be running, deleting session cmd_type=elb consumer=True name=target-snowflake-arc-postgres producer=False stdio=stderr string_id=target-snowflake-arc-postgres
2024-04-09T18:26:53.407359Z [error    ] [Errno 32] Broken pipe

Ah, I think it should be

type: [object, null]

Hmm, I think I tried that too without success. Ok, I'm rerunning again with this config 🏃 🏃

schema:
      public-versions:
        object:
          type: [object, null]
        object_changes:
          type: [object, null]

This is far from the first time I have to correct myself after suggesting it 😅. Sorry, try

type: [object, "null"]

(ie with quotes)

i'm correcting myself alll day🤞we got this

schema:
      public-versions:
        object:
          type: [object, "null"]
        object_changes:
          type: [object, "null"]

So the issue was the type's were made up

public-versions:
        object:
          type: [variant, {}]

target-snowflake saw these and picked string as the data type and caused issues, right?

That seems to be the case. Worth reporting in the target repo imo.