Hello! I am running meltano + airflow in an AWS ECS + ECR + RDS setup, and I'm having some issues with reconciling the

target-redshift

ticketswap variant plugin's behavior when setting the

load_method

config parameter in the meltano.yml file. I know that we have three option as described below:

The method to use when loading data into the destination. append-only will always write all input records whether that records already exists or not. upsert will update existing records and insert new records. overwrite will delete all existing records and insert all input records.

*However, it seems that

target-redshift

will always upsert the records no matter what I set the value to, e.g.,

append-only

results in an upsert*; I've been verifying the constant upsert behavior by checking the record counts and by confirming that some of the records have

updated_at

times that are at the time of the newest etl run while others do not have the

updated_at

timestamp updated. I haven't set the

TARGET_REDSHIFT_LOAD_METHOD

env var anywhere, so it's not clear to me what's resulting in this unexpected behavior. I have tested the following scenarios, all resulting in upserts: 1. Deleting the etl-specific bookmarking data in the meltano backend db 2. Updating the etl-specific bookmarking data in the meltano backend db to 0 3. Running the etl pipeline with the

--full-refresh

flag 4. Running the etl pipeline with the bookmarking data deleted without emptying the target redshift table, which in theory should result in double the records, but instead results in the same number of records as before the run a.

"initial_full_table_complete": true

<-- this shows up when running etl pipeline for the first time after deleting bookmarking data On a related note, within the logs, does the "merging" language imply that an upsert is always occurring, or is it generically referring to the fact that records are being injected into the Redshift tables and has no bearing on what type of update is occurring (e.g., append-only, overwrite, upsert)? I ask because I've tried running the etl pipeline with

overwrite

and

upsert

load_method settings and the logs appear the same, which doesn't give me much insight into what behavior is actually occuring.

merging 13075 records into meltano_stitch_replacement_poc.sales_invoice

Any tips would be appreciated--thank you!

Can you share your meltano.yml for your tap? Have you defined primary keys for your stream? I don't use the tap myself, but the only place I see

'merging'

in the code. If you follow that through, it looks like having a PK or not determines append or upsert behaviour, https://github.com/TicketSwap/target-redshift/blob/main/target_redshift/sinks.py#L138 https://github.com/TicketSwap/target-redshift/blob/main/target_redshift/sinks.py#L215 Perhaps if you have primary keys defined on the stream, you need to remove them to get append behaviour?

hi @Michael Bi, target-redshift ticketswap does not support

load_method

. it also does not do

overwrite

, i had to fork to add that. It is correct that

append_only

vs

upsert

is determined by whether key_properties is set on a stream

Thank you both @Holly Evans and @Andy Carter. Yes, the replication keys are defined in the catalog.json for each stream, which would explain why appending never worked. It is strange that

load_method

is described as a toggle-able feature in the target docs but doesn't actually work in reality.

@Michael Bi are you able to delete the target table in redshift and start from a blank slate? In case the primary key is already defined there. You can also try to

invoke

your tap and see if the schema definition in the json matches expected for primary keys i.e. None.

@Andy Carter I thought invoking only works for taps though. The custom catalog I have defined that doesn't have the keys is defined for the redshift target.

The custom catalog I have defined that doesn't have the keys is defined for the redshift target.

@Michael Bi Perhaps @Edgar Ramírez (Arch.dev) can confirm but I thought catalogs only apply to taps? That might explain the issue.

@Andy Carter My apologies my brain was off, you're right, my catalog is defined under my mysql tap.

@Michael Bi i think the

invoke

advice is still helpful. check the schema message that comes from invoking your tap to make sure you're not getting any key properties

Thanks, employer backlogging this particular issue for now but will circle back later--appreciate all the advice given here.