I am stumped so need help. I am running a tap-postgres in INCREMENTAL replication mode by specifying a surrogate key as replication key. Although source data has not changed. I see Meltano updating target table although I am expecting no change since no new id has been inserted. Is this the expected behavior?

@timothy_suh That's not expected behavior, no. Do you have the

meltano elt

output for the run that updated the target table unexpectedly?

My meltano.yml config:    metadata:     idme_core-public-appointment_slots:      replication-method: INCREMENTAL      replication-key: id

meltano     | No state was found, complete import.

Here's your issue 🙂 Apparently Meltano is not able to find the most recent run's state (https://meltano.com/docs/integration.html#pipeline-state), so it's not able to tell the extractor where the last run left off and where it should start off now, so the extractor is just starting from scratch again

Let me check

If you run

meltano --log-level=debug elt ...

(debug mode: https://meltano.com/docs/command-line-interface.html#debugging), you should be able to see the

RECORD

message output by

tap-postgres

extractors:  - name: tap-postgres   namespace: core   pip_url: tap-postgres   executable: tap-postgres   capabilities:   - discover   - properties   - state

Looks like that's by design: https://github.com/singer-io/tap-postgres/blob/3dce12abc2729fee2719d782739b0945591ec0c9/tap_postgres/sync_strategies/incremental.py#L79

You are right, it's greater than or equal to WHERE {} >= '{}'::{}

I'd argue that that's incorrect, since not all targets handle duplicate records correctly, but I'm not sure why Stitch decided to do it this way

In general, incremental should not work this way. I agree w/ you.

Is this behavior acceptable for you, now that state tracking is actually working?

I think so. Thank you for your help.

Awesome, my pleasure!