What is the correct format of target schema and table (schema mapping) in meltano.yml? I am trying to copy a simple table from Oracle source (schema PLASZPA:SIMPLE_SOURCE) to Postgres destination (schema public.simple_target). I know that everything is correct except, I think, the schema-mapping, for which I am not finding any example anywhere. I did correctly test Oracle target connection using:

meltano invoke tap-oracle > out

And this works. Regarding the destination, I did several experiments and I think meltano correctly logs in to the target, because if I change the password to something wrong it throws an error. With the configuration below, it completes "successfully", that is, no error. Even if I set log-level debug, there are no errors. I run:

meltano run tap-oracle target-postgres

And I receive "Block run completed". However, the target does not get populated with any data. What's more, if I change the values of schema-mapping below to something silly, it gets silently ignored. This is how I know that the problem must lie in the final schema-mapping part of the config file below, but I could not find the correct format in the documentation (by the way, why isn't there an error message?)

version: 1

default_environment: dev

project_id: e3cb5953-cc6c-4546-8311-a4d841f8d5b8

environments:

- name: dev

- name: staging

- name: prod

plugins:

extractors:

- name: tap-github

variant: meltanolabs

pip_url: git+<https://github.com/MeltanoLabs/tap-github.git>

- name: tap-oracle

variant: s7clarke10

pip_url: git+<https://github.com/s7clarke10/pipelinewise-tap-oracle.git>

config:

host: ***

port: 1597

common_service_name: ***

user: PLASZPA

service_name: ***

filter_schemas: PLASZPA

filter_tables:

- PLASZPA-SIMPLE_SOURCE

metadata:

PLASZPA-SIMPLE_SOURCE:

replication-method: INCREMENTAL

replication-key: KW_UPDATE_DATE

- name: tap-csv

variant: meltanolabs

pip_url: git+<https://github.com/MeltanoLabs/tap-csv.git>

config:

files:

- ./test.csv

loaders:

- name: target-postgres

variant: meltanolabs

pip_url: meltanolabs-target-postgres

settings:

- name: host

value: localhost

- name: port

value: 5432

- name: user

value: postgres

- name: password

value: mypassword

- name: database

value: mydatabase

- name: schema_mapping

value:

PLASZPA:

target_schema: public

tables:

SIMPLE_SOURCE: simple_target

Update: In the log, I see two entries that may give some clue:

025-01-14T08:25:07.708595Z [info     ] time=2025-01-14 09:25:07 name=singer level=INFO message=Resuming Incremental replication from KW_UPDATE_DATE = 2024-06-15T00:00:00.00+00:00 + INTERVAL '0' SECOND cmd_type=elb consumer=False job_name=dev:tap-oracle-to-target-postgres name=tap-oracle producer=True run_id=43b148ed-d4c9-481e-a087-b04c276a6bee stdio=stderr string_id=tap-oracle

2025-01-14T08:25:07.851158Z [info     ] 2025-01-14 09:25:07,850 | INFO     | target-postgres.PLASZPA-SIMPLE_SOURCE | Inserting with SQL: INSERT INTO b7c75d2f_a7e4_4e65_b28b_7bd4ab721870 ("KW_UPDATE_DATE", _sdc_extracted_at, _sdc_received_at, _sdc_batched_at, _sdc_deleted_at, _sdc_sequence, _sdc_table_version, _sdc_sync_started_at) VALUES (:KW_UPDATE_DATE, :_sdc_extracted_at, :_sdc_received_at, :_sdc_batched_at, :_sdc_deleted_at, :_sdc_sequence, :_sdc_table_version, :_sdc_sync_started_at) cmd_type=elb consumer=True job_name=dev:tap-oracle-to-target-postgres name=target-postgres producer=False run_id=43b148ed-d4c9-481e-a087-b04c276a6bee stdio=stderr string_id=target-postgres

I think the first entry above indicates that the reason nothing gets written is that incremental load somehow did set the state to the youngest record of 2024-06-15, eventhough the older records never got written. I wonder how to reset the state but also understand why this have happened and prevent this from happening. The second entry, if I am correct, indicates that the insert statement is is also faulty. I would expect

INSERT INTO public.simple_target ("KW_UPDATE_DATE") VALUES...

Update 2: For the moment, to isolate the problem better, I changed the replication method to FULL_TABLE. I also added to meltano.yml the variable add_record_metadata, following the hints from here. The data still does not get populated, but it seems to me there are many clues in the log. The relevant log entries are:

2025-01-14T10:16:50.359653Z [info     ] time=2025-01-14 11:16:50 name=singer level=INFO message=select SELECT to_char( "KW_UPDATE_DATE" ), ORA_ROWSCN cmd_type=elb consumer=Fals

e job_name=dev:tap-oracle-to-target-postgres name=tap-oracle producer=True run_id=f98306be-36d6-4ad6-8178-2887665738c2 stdio=stderr string_id=tap-oracle

2025-01-14T10:16:50.359847Z [info     ]                                 FROM PLASZPA.SIMPLE_SOURCE cmd_type=elb consumer=False job_name=dev:tap-oracle-to-target-postgres name=t

ap-oracle producer=True run_id=f98306be-36d6-4ad6-8178-2887665738c2 stdio=stderr string_id=tap-oracle

2025-01-14T10:16:50.359980Z [info     ]                                ORDER BY ORA_ROWSCN ASC cmd_type=elb consumer=False job_name=dev:tap-oracle-to-target-postgres name=tap-o

racle producer=True run_id=f98306be-36d6-4ad6-8178-2887665738c2 stdio=stderr string_id=tap-oracle

2025-01-14T10:16:50.458891Z [info     ] time=2025-01-14 11:16:50 name=singer level=INFO message=METRIC: b'{"type":"counter","metric":"record_count","value":2,"tags":{"schema":"

PLASZPA","table":"SIMPLE_SOURCE"}}' cmd_type=elb consumer=False job_name=dev:tap-oracle-to-target-postgres name=tap-oracle producer=True run_id=f98306be-36d6-4ad6-8178-28876657

38c2 stdio=stderr string_id=tap-oracle

2025-01-14T10:16:50.459451Z [info     ] 2025-01-14 11:16:50,459 | INFO     | target-postgres.PLASZPA-SIMPLE_SOURCE | An activate version message for 'PLASZPA-SIMPLE_SOURCE' was

received. Draining... cmd_type=elb consumer=True job_name=dev:tap-oracle-to-target-postgres name=target-postgres producer=False run_id=f98306be-36d6-4ad6-8178-2887665738c2 std

io=stderr string_id=target-postgres

2025-01-14T10:16:50.479410Z [info     ] 2025-01-14 11:16:50,478 | INFO     | target-postgres.PLASZPA-SIMPLE_SOURCE | Inserting with SQL: INSERT INTO "93902c9f_a35d_4875_9ecf_3b

1dfa13b059" ("KW_UPDATE_DATE", _sdc_extracted_at, _sdc_received_at, _sdc_batched_at, _sdc_deleted_at, _sdc_sequence, _sdc_table_version, _sdc_sync_started_at) VALUES (:KW_UPDAT

E_DATE, :_sdc_extracted_at, :_sdc_received_at, :_sdc_batched_at, :_sdc_deleted_at, :_sdc_sequence, :_sdc_table_version, :_sdc_sync_started_at) cmd_type=elb consumer=True job_na

me=dev:tap-oracle-to-target-postgres name=target-postgres producer=False run_id=f98306be-36d6-4ad6-8178-2887665738c2 stdio=stderr string_id=target-postgres

2025-01-14T10:16:50.505376Z [info     ] 2025-01-14 11:16:50,505 | INFO     | target-postgres.PLASZPA-SIMPLE_SOURCE | Hard delete: False cmd_type=elb consumer=True job_name=dev:

tap-oracle-to-target-postgres name=target-postgres producer=False run_id=f98306be-36d6-4ad6-8178-2887665738c2 stdio=stderr string_id=target-postgres

2025-01-14T10:16:50.519219Z [info     ] 2025-01-14 11:16:50,519 | INFO     | target-postgres      | Target 'target-postgres' completed reading 6 lines of input (1 schemas, 2 re

cords, 0 batch manifests, 2 state messages). cmd_type=elb consumer=True job_name=dev:tap-oracle-to-target-postgres name=target-postgres producer=False run_id=f98306be-36d6-4ad6

-8178-2887665738c2 stdio=stderr string_id=target-postgres

2025-01-14T10:16:50.532380Z [info     ] 2025-01-14 11:16:50,532 | INFO     | target-postgres.PLASZPA-SIMPLE_SOURCE | Cleaning up PLASZPA-SIMPLE_SOURCE cmd_type=elb consumer=Tru

e job_name=dev:tap-oracle-to-target-postgres name=target-postgres producer=False run_id=f98306be-36d6-4ad6-8178-2887665738c2 stdio=stderr string_id=target-postgres

2025-01-14T10:16:50.532713Z [info     ] 2025-01-14 11:16:50,532 | INFO     | singer_sdk.metrics   | METRIC: {"type": "counter", "metric": "record_count", "value": 2, "tags": {"

stream": "PLASZPA-SIMPLE_SOURCE", "pid": 463639}} cmd_type=elb consumer=True job_name=dev:tap-oracle-to-target-postgres name=target-postgres producer=False run_id=f98306be-36d6

-4ad6-8178-2887665738c2 stdio=stderr string_id=target-postgres

2025-01-14T10:16:50.532847Z [info     ] 2025-01-14 11:16:50,532 | INFO     | target-postgres      | Emitting completed target state {"bookmarks": {"PLASZPA-SIMPLE_SOURCE": {"la

st_replication_method": "FULL_TABLE", "version": 1736849810358, "ORA_ROWSCN": null}}, "currently_syncing": null} cmd_type=elb consumer=True job_name=dev:tap-oracle-to-target-po

stgres name=target-postgres producer=False run_id=f98306be-36d6-4ad6-8178-2887665738c2 stdio=stderr string_id=target-postgres

2025-01-14T10:16:50.542641Z [info     ] Incremental state has been updated at 2025-01-14 10:16:50.542620+00:00.

2025-01-14T10:16:50.626925Z [info     ] Block run completed.           block_type=ExtractLoadBlocks err=None set_number=0 success=True

Hey @Pawel Plaszczak. I'll go each of your issues/questions in order:

I wonder how to reset the state

try

meltano run tap-oracle target-postgres

but also understand why this have happened and prevent this from happening.

The only reason I think this could've happened is there was a successful pipeline at some point.