I was fighting through a couple of issues on my tap-mongodb[singer-io] -> target-postgres[datamill-co] pipeline related to schema and null values. I sorted it out mostly and figured I should post something here so I have a place to come back to the next time I can't figure it out. 1 Nullable fields/columns. You need to specify this like this.

type: ['null', string]

not like this

type: [null, string]

Then it flows through the tap-mongodb into the SCHEMA message and target-postgres will make the column nullable. 2 Extra common SCHEMA message generated by tap-mongodb. If you specify a schema the tap still outputs the common schema that is a by anyOf mess that screws up the target-postgres. If you run

meltano invoke tap-name

and see a SCHEMA that looks like this after the one you expected from your schema in meltano.yml, then you the problem I was working through.

{
  "type": "SCHEMA",
  "stream": "wemlostaffs",
  "schema": {
    "type": "object",
    "properties": {
      "reportTo": {
        "anyOf": [
          {}
        ]
      },
      "createdOn": {
        "anyOf": [
          {
            "type": "string",
            "format": "date-time"
          },
          {}
        ]
      },
      "reportees": {
        "anyOf": [
          {}
        ]
      }
    }
  },
  "key_properties": [
    "_id"
  ]
}

not sure why a couple of fields made it through that. I hacked a fix for it in tap_mongodb.sync_strategies.full_table.sync_collection() by adding a check for a schema on the stream around line 112.

schema_build_start_time = time.time()
            if common.row_to_schema(schema, row):
                # Hack to no output common schema if one is provided in meltano.yml and tap.properties.json.
                if "schema" not in stream:
                    singer.write_message(singer.SchemaMessage(
                        stream=common.calculate_destination_stream_name(stream),
                        schema=schema,
                        key_properties=['_id']))
                common.SCHEMA_COUNT[stream['tap_stream_id']] += 1
            common.SCHEMA_TIMES[stream['tap_stream_id']] += time.time() - schema_build_start_time

I'll get a pull request in for this if anyone else is having problems with this.

@nate_atkins Thanks for sharing! Sounds like https://github.com/singer-io/tap-mongodb/pull/40 isn't working as well as it should, if it's leaving empty

{}

s in

anyOf

. I think it's worth filing an issue about that bug in that repo, describing your workaround of explicitly setting the schema in the catalog and ignoring the auto-generated one. The singer-io maintainers aren't likely to address or merge it, unfortunately, but it would be useful to other users. If you think you could do a better job maintaining your own fork, reviewing contributions etc, we can also consider making your fork the default in Meltano