One more question while I'm at it :slightly_smilin...
# plugins-generalb
bob_swain
07/18/2022, 8:54 PMOne more question while I'm at it 🙂. Is it possible to do full table append only with target-postgres and target-snowflake? During my testing it seems like it always does updates?
a
aaronsteers
07/18/2022, 9:22 PMHi, @bob_swain! There's a trick to doing append-only inserts with no merge behavior regardless of the target being used. If you override
key_propertiesb
bob_swain
07/19/2022, 12:18 AM@aaronsteers Awesome, thanks!
bob_swain
07/19/2022, 1:19 AM@aaronsteers I tried doing what you said but I'm still just getting updates. Is it in the wrong spot in my meltano.yml?
a
aaronsteers
07/19/2022, 11:33 PM@bob_swain - Sorry for the delay. In reviewing the above and comparing to the Singer Spec, It looks like I gave you the wrong casing and it should be kebab-casing. Can you try with
key-propertiestable-key-propertieskey_propertiesaaronsteers
07/19/2022, 11:37 PMAlso - some targets will use the target table's metadata to drive merges, since those primary keys cannot be (easily) modified and since the primary key on the target table will determine what kinds of inserts are legal on the destination table. When experimenting, you may want to rename/archive the target table so a prior run's primary key declarations do not drive the subsequent execution/insert logic. (Might not be necessary but raising here for completeness.)
b
bob_swain
07/20/2022, 1:06 PM@aaronsteers thanks for the reply! I did end up trying all the different key properties possibilities 🙂
bob_swain
07/20/2022, 1:07 PMstill no luck unfortunately. I'm using target-postgres. it still just updates no matter what I do rather than insert/append a new copy of the table.
bob_swain
07/20/2022, 1:08 PMyeah, I was thinking I could add a dbt transform step that appends the data to an archive table. That should do it if I can't get the taps and targets to do it out of the box
a
aaronsteers
07/21/2022, 4:31 PM@bob_swain - Thanks for trying every possibility. Sorry to hear this still isn't working for you. Did you also try deleting the target table to see if (now with all of these permutations), the table will be created with a primary key? Also, can you confirm for the target table that is already created, does that have a primary key set defined on it?