Is there a way to set the target snowflake so that it insert Meltano #plugins-general

Is there a way to set the target-snowflake so that...

jaye_howell

12/30/2022, 6:54 PM

Is there a way to set the target-snowflake so that it inserts changed rows instead of updating? I see it extract the correct records, but instead of inserting records it updates the existing records. I am guessing this is in the target-snowflake plugin and not the tap-postgres. Dont see a configurable item in either. Any help is welcomed

Henning Holgersen

12/31/2022, 3:14 PM

Generally, targets do upserts when there is a key property present. If your source can be configured to not use/send a key, that would perhaps be the simplest. Otherwise, you might want to have a look at mappers, which (I think) is able to remove key properties. This option is a little more complex though.

jaye_howell

12/31/2022, 3:25 PM

So the only property set on the tap side is the replication key which is not the primary key. At this point assuming it is getting the primary key from the schema it pulls from the source DB so changing it would be a bad idea. We are using the postgres tap. I will look into the mappers. Out of curosity, why was upsert chosen? In my expirence the reason for building DW is to keep history outside of opperational system.

christoph

01/02/2023, 9:00 PM

Out of curosity, why was upsert chosen? In my expirence the reason for building DW is to keep history outside of opperational system.

I think this is a convention that is inferred from the

key_properties

specification. The Singer specification does not describe what taps and targets should do, it only describe the format in which the taps and targets should communicate. It seems the natural thing to do for targets, that whenever the

key_properties

list is non-empty, database-style targets would upsert instead of append. So, therefore, the

key_properties

field becomes the controlling mechanism that allows you as the engineer to determine how the data should ultimately be stored in your destination.

christoph

01/02/2023, 9:05 PM

At this point assuming it is getting the primary key from the schema it pulls from the source DB so changing it would be a bad idea.

There are a few different ways to look at the

key_properties

field of your tap in meltano: 1.

meltano select

command:

meltano select tap-postgres --list

will highlight the key properties for each of your streams 2.

meltano invoke tap-postgres

will allow you to manually inspect the Singer formatted stream. You would need to look for the "SCHEMA" message lines, which will have the

key_properties

field in them.

christoph

01/02/2023, 9:28 PM

Otherwise, you might want to have a look at mappers,

The easiest way would be to use the meltanolabs variant of tap-postgres, which supports the

stream_maps

config option:

Copy code

plugins:
  extractors:
  - name: tap-postgres
    variant: meltanolabs
    pip_url: git+<https://github.com/MeltanoLabs/tap-postgres.git>
    config:
      sqlalchemy_url: postgresql:///mydatabasename
      stream_maps:
        public-mytablename:
          __key_properties__: []

Open in Slack

Previous Next